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;}@Overridepublic 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);}//数据库第一次被创建时调用 @Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_TABLE);}//版本升级时被调用 @Overridepublic 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());}}