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
	}
	
	//数据库第一次被创建的时候调用
	@Override
	public 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）
	@Override
	public 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目录下）