android连接sqlite进行简单的增删改查和事务管理

Android连接数据库sqlite并进行简单的表创建和增删改查功能参考代码，使用Android单元测试进行验证，首先新建项目进行配置单元测试环境请参考博客：http://blog.csdn.net/bq1073100909/article/details/37885457

项目资源目录图：

Person.java代码：（实体类）

package org.dyb.domain;public class Person {private Integer personid;private String name;private String phone;public Person(){}public Person(String name,String phone){this.name=name;this.phone=phone;}public Person(Integer personid,String name,String phone){this.personid=personid;this.name=name;this.phone=phone;}public Integer getPersonid() {return personid;}public void setPersonid(Integer personid) {this.personid = personid;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}}

DBOpenHelper.java 继承SQLiteOpenHelper抽象类，实现数据库的连接和创建表

package org.dyb.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DBOpenHelper extends SQLiteOpenHelper {public DBOpenHelper(Context context) {//参数说明：第二个是数据库文件名称，第三个是游标工厂，第四个是版本号super(context, "dyb.db", null, 2);//默认放在<包>/databases}//数据库第一次被创建的时候调用@Overridepublic void onCreate(SQLiteDatabase db) {String sql="create table person(personid integer primary key autoincrement,name varchar(20))";db.execSQL(sql);}//super(context, "dyb.db", null, 1);数据库文件的版本号发生变更的时候调用（例如把上面1修改为2）@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {String sql="alter table person add phone varchar(12) null";db.execSQL(sql);}}

对表内容进行增删改查：

PersonService.java代码如下：

package org.dyb.service;import java.util.ArrayList;import java.util.List;import org.dyb.domain.Person;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class PersonService {private DBOpenHelper dbOpenHelper;public PersonService(Context context){this.dbOpenHelper=new DBOpenHelper(context);}/** * 保存用户 * @param person */public void save(Person person){SQLiteDatabase db=dbOpenHelper.getWritableDatabase();String sql="insert into person(name,phone) values(?,?)";db.execSQL(sql,new Object[]{person.getName(),person.getPhone()});db.close();}/** * 删除用户 * @param id */public void delete(Integer id){SQLiteDatabase db=dbOpenHelper.getWritableDatabase();String sql="delete from person where personid=?";db.execSQL(sql,new Object[]{id});db.close();}/** * 更新用户 * @param person */public void update(Person person){SQLiteDatabase db=dbOpenHelper.getWritableDatabase();String sql="update person set name=?,phone=? where personid=?";db.execSQL(sql,new Object[]{person.getName(),person.getPhone(),person.getPersonid()});}/** * 通过ID查找用户  * @param id * @return */public Person find(Integer id){SQLiteDatabase db=dbOpenHelper.getReadableDatabase();String sql="select * from person where personid=?";Cursor cursor = db.rawQuery(sql, new String[]{id.toString()});if(cursor.moveToFirst()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));String phone = cursor.getString(cursor.getColumnIndex("phone"));return new Person(personid,name,phone);}cursor.close();return null;}/** * 分页查询 * @param offset 跳过前面多少条记录 * @param maxResult 每页获取多少条记录 * @return */public List<Person> getScrollData(int offset,int maxResult){List<Person> persons=new ArrayList<Person>();SQLiteDatabase db=dbOpenHelper.getReadableDatabase();String sql="select * from person order by personid asc limit ? offset ?";Cursor cursor=db.rawQuery(sql, new String[]{String.valueOf(maxResult),String.valueOf(offset)});while(cursor.moveToNext()){int personid = cursor.getInt(cursor.getColumnIndex("personid"));String name = cursor.getString(cursor.getColumnIndex("name"));String phone = cursor.getString(cursor.getColumnIndex("phone"));persons.add(new Person(personid,name,phone));}return persons;}/** * 得到数据库数据总个数 * @return */public long getCount(){SQLiteDatabase db=dbOpenHelper.getReadableDatabase();String sql="select count(*) from person ";Cursor cursor = db.rawQuery(sql, null);cursor.moveToFirst();long result=cursor.getLong(0);return result;}/** * 事务测试，同时执行成功提交事务 */public void updateTransaction()throws Exception{SQLiteDatabase db=dbOpenHelper.getWritableDatabase();Person person1=this.find(1);person1.setName("x1");Person person2=this.find(2);person2.setName("x2");db.beginTransaction();//事务开启try{this.update(person1);this.update(person2);db.setTransactionSuccessful();//设置事务成功，这样关闭事务时候会提交数据}finally{db.endTransaction();//如果事务成功提交事务 否则回滚}db.close();}public DBOpenHelper getDbOpenHelper() {return dbOpenHelper;}public void setDbOpenHelper(DBOpenHelper dbOpenHelper) {this.dbOpenHelper = dbOpenHelper;}}

单元测试类 PersonServiceTest.java代码如下：

package org.dyb.test;import java.util.List;import org.dyb.domain.Person;import org.dyb.service.DBOpenHelper;import org.dyb.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class PersonServiceTest extends AndroidTestCase {public void testCreateDB()throws Exception{DBOpenHelper dbOpenHelper=new DBOpenHelper(getContext());dbOpenHelper.getWritableDatabase();}public void testSave()throws Exception{PersonService personService=new PersonService(this.getContext());for(int i=0;i<20;i++){Person person=new Person("zhangsan"+i,"123456789");personService.save(person);}}public void testUpdate()throws Exception{PersonService personService=new PersonService(this.getContext());Person person = personService.find(1);person.setName("lisi");personService.update(person);}public void testDelete()throws Exception{PersonService personService=new PersonService(this.getContext());personService.delete(12);}public void testFind()throws Exception{PersonService personService=new PersonService(this.getContext());Person person = personService.find(3);Log.i("find", person.getName());}public void testScrollData()throws Exception{PersonService personService=new PersonService(this.getContext());List<Person> persons = personService.getScrollData(5, 4);for(Person per:persons){Log.i("fenye", per.getPersonid()+"");}}public void testCount()throws Exception{PersonService personService=new PersonService(this.getContext());Log.i("count",personService.getCount()+"");}/** * 测试事务，只有两个更新都成功才提交事务 * @throws Exception */public void testUpdateTransaction()throws Exception{PersonService personService=new PersonService(this.getContext());personService.updateTransaction();}}

首先创建表，执行保存用户，id为1的name是zhangsan，然后执行更新测试，id为1的name是lisi，为了方便测试分页显示输出，循环添加数据，执行删除测试，删除掉id是12的用户，执行事务管理测试，把id是1和2的同时更新为x1和x2，事务提交。参考图如下：（我使用的是RE管理器打开的数据库文件dyb.db，数据库文件默认在项目包名的databases目录下）