SQlite 增删改

sql基本语法

CREATE TABLE Person (id integer primary key autoincrement,name varchar(20),number varchar(20));

insert into Person (name,number) values ('zhangSan','18618618611')  插入

select * from Person		查找全部

select * from Person where name='zhangSan'   查找 zhangSan

update Person set number='18601797122' where name = 'zhangSan'  更新

delete from Person where name = 'liSi'		删除liSi

1.定义一个class 继承 SQLiteOpenHelper 实现其构造方法  与onCreate()方法

SQliteDB.java

package com.example.android_4_1;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
/**1.
 * 创建一个类 继承SQLiteOpenHelper
 * (数据库构造方法，数据库名称，数据库查询结果集，数据库版本，)
 * @author Administrator
 */
public class SQliteDB extends SQLiteOpenHelper {
	/**2.
	 * 构造方法
	 * @param context  上下文对象
 	 * @param name		数据库名称
	 * @param factory   数据库查询结果集，null为系统默认结果集
	 * @param version	数据库版本
	 */
	public SQliteDB(Context context, String name, CursorFactory factory,int version) {
		super(context, name, factory, version);
	}
	/**3.
	 * 数据第一次被创建的时候调用的方法
	 * db 被创建的数据库
	 * Person 表
	 * id  integer 
	 * name varchar
	 * number varchar
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		//执行sql 语句
		db.execSQL("create table Person (id integer primary key autoincrement,name varchar(20),number varchar(20)) ");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}	
}

2.定义 SQliteDomain.java 其中定义sql表中属性 

 

package SQlite.dao;
/* SQliteDomain
 * 返回查询数据库的list集合
 */
public class SQliteDomain {
	private String name;
	private int id;
	private String number;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	
	public SQliteDomain(String name, int id, String number) {
		this.name = name;
		this.id = id;
		this.number = number;
	}
	public SQliteDomain() {
	}
}

3.初始化SQlite 并执行sql语句

db.execSQL();

Cursor cursor = db.rawQuery();查询返回 cursor 结果集

package SQlite.dao;

import java.util.ArrayList;
import java.util.List;
import com.example.android_4_1.SQliteDB;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
/**
 * SQlite 初始化  执行sql语句
 * @author Administrator
 *
 */
public class SQliteDao {
	private SQliteDB  helper;
	//构造方法里面初始化helper
	public SQliteDao(Context context, String name, CursorFactory factory,int version) {
		helper = new SQliteDB(context, name, factory,version);
	}
	/**
	 * helper.getWritableDatabase(); 获取可写的数据库对象
	 * db.execSQL();  values (?,?) 对应数组 Object[]
	 * @param name  名称
	 * @param number  电话号码
	 */
	public void add(String name,String number){
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("insert into Person (name,number) values (?,?)", new Object[]{name,number});
		
		db.close();
	}
	/**
	 * helper.getReadableDatabase(); 只读的数据库对象
	 * Cursor cursor 查询返回结果集对象
	 * @param name
	 * @return true 查询存在 ，false 查询不存在
	 */
	public boolean find (String name){
		SQLiteDatabase db = helper.getReadableDatabase();
		//返回Cursor结构结果集
		Cursor cursor = db.rawQuery("select * from Person where name=?", new String[]{name});
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}
	/**
	 * 根据 name 修改 number
	 * @param name   
	 * @param newNumber
	 */
	public void update(String name,String newNumber){
		SQLiteDatabase db = helper.getReadableDatabase();
		db.execSQL("update Person set number=? where name = ?", new Object[]{newNumber,name});
		db.close();
	}
	/**
	 * delete DB name
	 * @param name
	 */
	public void delete(String name){
		SQLiteDatabase db = helper.getReadableDatabase();
		db.execSQL("delete from Person where name = ?", new String[]{name});
		db.close();
	}
	/**
	 * 查询返回cursor 数据库指针
	 * 通过cursor.moveToNext()遍历数据库
	 * 每次结果存到 SQliteDomain listDB
	 * 遍历结果存到 List<SQliteDomain> listArrayDB
	 * @return listArrayDB 数据集合
	 */
	public List<SQliteDomain> showDB(){
		SQLiteDatabase db = helper.getReadableDatabase();
		List<SQliteDomain> listArrayDB = new ArrayList<SQliteDomain>();
		Cursor cursor = db.rawQuery("select id,name,number from Person", null);
		while (cursor.moveToNext()) {
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String number = cursor.getString(cursor.getColumnIndex("number"));
			SQliteDomain listDB = new SQliteDomain(name, id, number);
			listArrayDB.add(listDB);
		}
		cursor.close();
		db.close();
		return listArrayDB;
	}
}

TestDB.java 测试方法

package SQlite.test;

import java.util.List;
import com.example.android_4_1.SQliteDB;
import SQlite.dao.SQliteDao;
import SQlite.dao.SQliteDomain;
import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
/**
 * 测试 数据库 方法
 * @author Administrator
 *
 */
public class TestDB extends AndroidTestCase {
	public void testCreateDB() throws Exception{
		//1.创建SQliteDB 对象
		SQliteDB helper = new SQliteDB(getContext(),"Persons.db", null, 1);
		//2.写入 DB
		SQLiteDatabase db = helper.getWritableDatabase();
	//	db.execSQL(String sql);
	//	db.rawQuery(sql, selectionArgs);//查询操作 
	}
	public void AddDB() throws Exception{
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 1);
		dao.add("aa", "123456789");
		dao.add("aaa", "12345678");
		dao.add("aaaa", "1234567");
		dao.add("aaaaa", "123456");
		dao.add("aaaaaa", "12345");
		dao.add("aaaaaaa", "1234");
		dao.add("aaaaaaaa", "123");
	}
	public void findDB(){
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 1);
		boolean result = dao.find("aaa");
	}
	public void updateDB(){
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 1);
		dao.update("aaaaa", "88888888");
	}
	public void deleteDB(){
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 1);
		dao.delete("aaaaaaa");
	}
	public void showDB(){
		SQliteDao dao = new SQliteDao(getContext(),"Persons.db", null, 1);
		List<SQliteDomain> SQListArray = dao.showDB();
		System.out.println(SQListArray);
	}
}

主配置文件添加测试环境

 <!-- 测试环境  / manifest-->
    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:targetPackage="com.example.android_4_1" />

<!-- 测试环境  /application-->
       	<uses-library android:name="android.test.runner" />

执行测试方法后PersonsDB结果