Android--SQLite（增，删，改，查）操作

需要5个类：

1.实体类：Person.java

2.抽象类:SQLOperate.java(封装了对数据库的操作)

3.助手类：DBOpenHelper.java(继承SQLiteOpenHelper)

4.实现类:SQLOperateImpl.java(实现抽象类SQLOperate.java)

5.测试类：Test.java(继承AndroidTestCase)

 

 

 

1.Person.java

 

package com.mrzhu.sqltite;

public class Person {
	
	private int _id;
	private String name;
	
	public int getId() {
		return _id;
	}
	
	public void setId(int _id) {
		this._id = _id;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	@Override
	public String toString() {
		return "Person [id=" + _id + ", name=" + name + "]";
	}
	
	public Person() {
		super();
	}
	
	public Person(int _id, String name) {
		super();
		this._id = _id;
		this.name = name;
	}
}

 

 

 

2.SQLOperate.java

 

package com.mrzhu.sqltite;

import java.util.List;

/**
 * 增删改查
 * @author ZLQ
 *
 */
public interface SQLOperate {
	public void add(Person p);
	public void delete(int id);
	public void updata(Person p);
	public List<Person> find();
	public Person findById(int id);
}

 

 

 

3.DBOpenHelper.java

 

package com.mrzhu.sqltite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 助手类
 * @author ZLQ
 *
 */
public class DBOpneHelper extends SQLiteOpenHelper {

	private static final int VERSION = 1;//版本
	private static final String DB_NAME = "people.db";//数据库名
	public static final String STUDENT_TABLE = "student";//表名
	public static final String _ID = "_id";//表中的列名
	public static final String NAME = "name";//表中的列名
	//创建数据库语句，STUDENT_TABLE，_ID ，NAME的前后都要加空格
	private static final String CREATE_TABLE = "create table " + STUDENT_TABLE + " ( " + _ID + " Integer primary key autoincrement," + NAME + " text)";
	
	public DBOpneHelper(Context context) {
		super(context, DB_NAME, null, VERSION);
	}

	//数据库第一次被创建时调用 
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE);
	}

	//版本升级时被调用 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}

 

 

4.SQLOperateImpl.java

 

package com.mrzhu.sqltite;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SQLOperateImpl implements SQLOperate{

	private DBOpneHelper dbOpenHelper;
	
	public SQLOperateImpl(Context context) {
		dbOpenHelper = new DBOpneHelper(context);
	}

	/**
	 * 增，用insert向数据库中插入数据
	 */
	public void add(Person p) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBOpneHelper._ID, p.getId());
		values.put(DBOpneHelper.NAME, p.getName());
		db.insert(DBOpneHelper.STUDENT_TABLE, null, values);
	}

	/**
	 * 删，通过id删除数据
	 */
	public void delete(int id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.delete(DBOpneHelper.STUDENT_TABLE, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)});
	}

	/**
	 * 改，修改指定id的数据
	 */
	public void updata(Person p) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBOpneHelper._ID, p.getId());
		values.put(DBOpneHelper.NAME, p.getName());
		db.update(DBOpneHelper.STUDENT_TABLE, values, DBOpneHelper._ID + "=?", new String[]{String.valueOf(p.getId())});
	}

	/**
	 * 查，查询表中所有的数据
	 */
	public List<Person> find() {
		List<Person> persons = null;
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, null, null, null, null, null);
		if(cursor != null){
			persons = new ArrayList<Person>();
			while(cursor.moveToNext()){
				Person person = new Person();
				int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID));
				String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME));
				person.setId(_id);
				person.setName(name);
				persons.add(person);
			}
		}
		return persons;
	}

	/**
	 * 查询指定id的数据
	 */
	public Person findById(int id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query(DBOpneHelper.STUDENT_TABLE, null, DBOpneHelper._ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
		Person person = null;
		if(cursor != null && cursor.moveToFirst()){
			person = new Person();
			int _id = cursor.getInt(cursor.getColumnIndex(DBOpneHelper._ID));
			String name = cursor.getString(cursor.getColumnIndex(DBOpneHelper.NAME));
			person.setId(_id);
			person.setName(name);
		}
		return person;
	}
}

 

 

 

5.Test.java

 

在AndroidManifest.xml中的<application></application>外添加

（targetPackage是当前工程的包名）

 

 <instrumentation

android:targetPackage="com.mrzhu.sqltite"

android:name="android.test.InstrumentationTestRunner">

</instrumentation>

 

在<application></application>中添加<uses-library android:name="android.test.runner"/>

 

 

package com.mrzhu.sqltite;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

public class Test extends AndroidTestCase {
	public void testAdd() throws Exception{
		SQLOperateImpl test = new SQLOperateImpl(getContext());
		Person person = new Person(2, "Peter");
		test.add(person);
	}
	
	public void testDelete() throws Exception{
		SQLOperateImpl test = new SQLOperateImpl(getContext());
		test.delete(1);
	}
	
	public void testUpdata() throws Exception{
		SQLOperateImpl test = new SQLOperateImpl(getContext());
		Person person = new Person(1, "Tom");
		test.updata(person);
	}
	
	public void testFind() throws Exception{
		SQLOperateImpl test = new SQLOperateImpl(getContext());
		List<Person> persons = test.find();
		for (Person person : persons) {
			Log.i("System.out", person.toString());
		}
	}
	
	public void testFindById() throws Exception{
		SQLOperateImpl test = new SQLOperateImpl(getContext());
		Person person = test.findById(2);
		Log.i("System.out", person.toString());
	}
}