Android 对数据库的增删改查操作

一. 操作对象

packagecom.example.model;

publicclassPerson {

privateIntegerpersonId;

privateStringname;

privateStringphone;

publicInteger getPersonId() {

returnpersonId;

}

publicvoidsetPersonId(Integer personId) {

this.personId= personId;

}

publicString getName() {

returnname;

}

publicvoidsetName(String name) {

this.name= name;

}

publicString getPhone() {

returnphone;

}

publicvoidsetPhone(String phone) {

this.phone= phone;

}

publicPerson(Integer personId, String name, String phone) {

this.personId= personId;

this.name= name;

this.phone= phone;

}

publicPerson(String name, String phone) {

this.name= name;

this.phone= phone;

}

@Override

publicString toString() {

return"Person [personId="+ personId+", name="+ name + ",phone=" +phone+ "]";

}

}

二.操作类

packagecom.example.service;

importandroid.content.Context;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.database.sqlite.SQLiteOpenHelper;

publicclassDBOpenHelperextendsSQLiteOpenHelper {

publicDBOpenHelper(Context context) {

/**

* context上下文name数据库名称factory游标工厂，使用系统默认游标工厂可以用null表示version版本号

*版本号要求不能为0

*/

//super(context, name, factory, version);

//数据库文件默认保存在当前应用包/database目录下

super(context,"andriod.db",null,2);

}

//在数据库每一次被创建的时候调用

/**

*dbOperate数据库操作实例都可以通过SQLiteDatabase类操作数据库

*/

@Override

publicvoidonCreate(SQLiteDatabase dbOperate) {

String sql ="CREATE TABLE person(personidINTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))";

dbOperate.execSQL(sql);

}

//在数据库的版本号变化时被调用

@Override

publicvoidonUpgrade(SQLiteDatabase dbOperate, intoldVersion,intnewVersion) {

String sql ="ALTERTABLE person ADD phone VARCHAR(12) NULL";

dbOperate.execSQL(sql);

}

}

三.具体操作方法

具体操作方法分两种情况

1.

packagecom.example.service;

importjava.util.ArrayList;

importjava.util.List;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importcom.example.model.Person;

publicclassPersonService {

privateDBOpenHelperdbOpenHelper;

publicPersonService(Context context) {

this.dbOpenHelper=newDBOpenHelper(context);

}

publicvoidsave(Person person) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

//采用占位符，防止sql注入

String sql ="INSERT INTO person(name, phone)VALUES(?, ?)";

Object[] params =newObject[] { person.getName(), person.getPhone() };

dbOperate.execSQL(sql,params);

}

publicvoiddelete(Integer id) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

String sql ="DELETE FROM person WHERE personid= ?";

Object[] params =newObject[] { id };

dbOperate.execSQL(sql,params);

}

publicvoidupdate(Person person) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

String sql ="UPDATE person SET name = ?, phone= ? WHERE personid = ?";

Object[] params =newObject[] { person.getName(), person.getPhone(), person.getPersonId()};

dbOperate.execSQL(sql,params);

}

publicPerson find(Integer id) {

//当数据库存储文件满的时候用dbOpenHelper.getReadableDatabase()得到的数据库实例只能读而不能写

//如果数据库存储文件没有满，那么得到的数据库存储实例和dbOpenHelper.getWritableDatabase()得到的一样

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

String sql ="SELECT * FROM person WHEREpersonid = ?";

String[] params =newString[] { String.valueOf(id)};

Cursor cursor =dbOperate.rawQuery(sql, params);

if(cursor.moveToFirst()) {

intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));

String name =cursor.getString(cursor.getColumnIndex("name"));

String phone =cursor.getString(cursor.getColumnIndex("phone"));

Person person =newPerson(personId, name, phone);

returnperson;

}

cursor.close();

returnnull;

}

publicList<Person> getScrollData(intoffset,intmaxResult) {

List<Person>persons =newArrayList<Person>();

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

String sql ="SELECT * FROM person ORDER BYpersonid ASC LIMIT ?, ?";

String[] params =newString[] { String.valueOf(offset),String.valueOf(maxResult)};

Cursor cursor =dbOperate.rawQuery(sql, params);

while(cursor.moveToNext()) {

intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));

String name =cursor.getString(cursor.getColumnIndex("name"));

String phone =cursor.getString(cursor.getColumnIndex("phone"));

Person person =newPerson(personId, name, phone);

persons.add(person);

}

cursor.close();

returnpersons;

}

publiclonggetCount() {

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

String sql ="SELECT COUNT(*) FROM person";

Cursor cursor =dbOperate.rawQuery(sql,null);

cursor.moveToFirst();

longresult = cursor.getLong(0);

returnresult;

}

}

2.

packagecom.example.service;

importjava.util.ArrayList;

importjava.util.List;

importandroid.content.ContentValues;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importcom.example.model.Person;

publicclassOtherPersonService {

privateDBOpenHelperdbOpenHelper;

publicOtherPersonService(Context context) {

this.dbOpenHelper=newDBOpenHelper(context);

}

publicvoidsave(Person person) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

ContentValuesvalues =newContentValues();

values.put("name",person.getName());

values.put("phone",person.getPhone());

//空值字段作用：当第三个参数为空或者Map为空时用上第二个参数

//insert into person () values ();语法错误

//insert into person( name) values (NULL);语法正确

dbOperate.insert("person",null,values);

}

publicvoiddelete(Integer id) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

dbOperate.delete("person","personid = ?",newString[] { id.toString() });

}

publicvoidupdate(Person person) {

SQLiteDatabasedbOperate =dbOpenHelper.getWritableDatabase();

ContentValuesvalues =newContentValues();

values.put("name",person.getName());

values.put("phone",person.getPhone());

dbOperate.update("person",values,"personid = ?",newString[] { person.getPersonId().toString() });

}

publicPerson find(Integer id) {

//当数据库存储文件满的时候用dbOpenHelper.getReadableDatabase()得到的数据库实例只能读而不能写

//如果数据库存储文件没有满，那么得到的数据库存储实例和dbOpenHelper.getWritableDatabase()得到的一样

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

String[] columns =newString[] { "personid","name","phone"};

String[] params =newString[] { String.valueOf(id)};

//如果columns为null就表示查询所有字段

Cursor cursor =dbOperate.query("person",columns,"personid = ?",params,null,null,null);

if(cursor.moveToFirst()) {

intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));

String name =cursor.getString(cursor.getColumnIndex("name"));

String phone =cursor.getString(cursor.getColumnIndex("phone"));

Person person =newPerson(personId, name, phone);

returnperson;

}

cursor.close();

returnnull;

}

publicList<Person> getScrollData(intoffset,intmaxResult) {

List<Person>persons =newArrayList<Person>();

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

Cursor cursor =dbOperate.query("person",null,null,null,"personid ASC",null,null,offset +","

+maxResult);

while(cursor.moveToNext()) {

intpersonId = cursor.getInt(cursor.getColumnIndex("personid"));

String name =cursor.getString(cursor.getColumnIndex("name"));

String phone =cursor.getString(cursor.getColumnIndex("phone"));

Person person =newPerson(personId, name, phone);

persons.add(person);

}

cursor.close();

returnpersons;

}

publiclonggetCount() {

SQLiteDatabasedbOperate =dbOpenHelper.getReadableDatabase();

Cursor cursor =dbOperate.query("person",newString[] { "COUNT(*)"},null,null,null,null,null);

cursor.moveToFirst();

longresult = cursor.getLong(0);

returnresult;

}

}

四.测试方法

packagecom.example.test;

importjava.util.ArrayList;

importjava.util.List;

importcom.example.model.Person;

importcom.example.service.PersonService;

importandroid.test.AndroidTestCase;

importandroid.util.Log;

publicclassPersonServiceTestextendsAndroidTestCase {

privatestaticfinalStringTAG="PersonServiceTest";

privatePersonServicepersonService;

/**

*为什么不把personService= new PersonService(getContext());这句代码提出来

*解答：提出来之后personService是类PersonServiceTest的成员变量,而PersonServiceTest要先去实例化，实例化之后才会有上下文对象

*而成员变量是在类实例化过程中执行的，而此时还没有上下文对象，所以此时的getContect为NULL，所以不能提出来

*放到构造方法里也是不行的原因和上述原因一样

*放到setUp方法里是可以的

*/

publicvoidtestSave()throwsException {

personService=newPersonService(getContext());

Person person =newPerson("vector","123");

personService.save(person);

}

publicvoidtestDelete()throwsException {

personService=newPersonService(getContext());

personService.delete(1);

this.testScrollData();

}

publicvoidtestUpdate()throwsException {

personService=newPersonService(getContext());

Person person =personService.find(1);

person.setName("lisinihao");

personService.update(person);

Log.i(TAG,person.toString());

}

publicvoidtestFind()throwsException {

personService=newPersonService(getContext());

Person person =personService.find(2);

Log.i(TAG,person.toString());

}

publicvoidtestScrollData()throwsException {

personService=newPersonService(getContext());

List<Person>persons =newArrayList<Person>();

persons =personService.getScrollData(0,3);

for(Person person : persons) {

Log.i(TAG,person.toString());

}

}

publicvoidtestCount()throwsException {

personService=newPersonService(getContext());

longresult =personService.getCount();

Log.i(TAG,"Person "+ String.valueOf(result));

}

/* @Override

protected voidsetUp() throws Exception {

//TODOAuto-generated method stub

super.setUp();

}*/

}