Android开发之用SQLite实现增上改查操作和事务操作

一）开发前准备工作，下载SQLite Expert工具（SQLite的可视化工具），大家可以去搜一下

二）工具准备好了，咱们开始开发吧

先贴一下项目的目录结构：

1.写一个获得创建修改数据库的工具类，这个类继承自SQLiteOpenHelper

package com.xiaobo.service;

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

public class DbOpenHelper extends SQLiteOpenHelper{

	public DbOpenHelper(Context context) {// 初始化时把数据库名和版本带上
		super(context, "itxiaobo.db", null, 3);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {// 数据库第一次被创建的时候调用的
		db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 数据库版本更改后执行这个方法
		db.execSQL("ALTER TABLE person ADD amount INTEGER NULL ");
	}

}

2.然后再下一个测试类PersonServiceTest，测试一下是否成功创建的数据库和表

package com.xiaobo.juit;

import java.util.List;

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

import com.xiaobo.domain.Person;
import com.xiaobo.service.DbOpenHelper;
import com.xiaobo.service.PersonService;

public class PersonServiceTest extends AndroidTestCase{
	// 本测试类的专有TAG标签，利于LogCat调试
	private final static String TAG = "PersonServiceTest";
	
	/**
	 * 测试创建或更改数据库
	 * @throws Throwable
	 */
	public void testCreateDb() throws Throwable{
		DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext());
		dbOpenHelper.getWritableDatabase();
	}
}

   备注：写单元测试时得在AndroidManifest.xml配置一下，看下图

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.xiaobo.db"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="3"
        android:targetSdkVersion="17" />

    <instrumentation android:name="android.test.InstrumentationTestRunner" 
	    			 android:targetPackage="com.xiaobo.db" 
					 android:label="JUnit Test"/> 
    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <!-- 单元测试的library -->
        <uses-library android:name="android.test.runner" /> 
        <activity
            android:name="com.xiaobo.db.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

3.写业务逻辑类PersonService，其中包含了增上改查，看代码吧

package com.xiaobo.service;

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

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

import com.xiaobo.domain.Person;

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();
		db.execSQL("insert into person(name, phone, amount) values(?,?)", new Object[]{person.getName(), person.getPhone(), person.getAmount()});
	}
	
	/**
	 * 根据id删除记录
	 * @param id
	 */
	public void delete(Integer id){
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from person where personid=?", new Object[]{id});
	}
	
	/**
	 * 更新记录
	 * @param person
	 */
	public void update(Person person){
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update person set name=?, phone=?, amount=? where personid=?", new Object[]{person.getName(), person.getPhone(),person.getAmount(), person.getId()});
	}
	
	/**
	 * 根据id查找一条记录
	 * @param id
	 * @return
	 */
	public Person find(Integer id){
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from person where personid=?", 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"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			return new Person(personid, name, phone, amount);
		}
		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();
		Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset), String.valueOf(maxResult)});
		while(cursor.moveToNext()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			persons.add(new Person(personid, name, phone, amount));
		}
		cursor.close();
		return persons;
	}
	
	/**
	 * 获取记录总数
	 * @return
	 */
	public long getCount(){
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from person", null);
		cursor.moveToFirst();
		long count = cursor.getLong(0);
		return count;
	}
	
	/**
	 * 转账汇款事务
	 * @param fromId 汇款账户
	 * @param toId 接收账户
	 */
	public void payment(Integer fromId, Integer toId){
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		db.beginTransaction();	// 开启事务
		try{
			db.execSQL("update person set amount=amount-500 where personid=?", new Object[]{fromId});
			db.execSQL("update person set amount=amount+500 where personid=?", new Object[]{toId});
			// 结束事务有两种情况，要么commit,要么rollback,是根据事务的标志决定的，默认为False，如果为True,就会提交
			db.setTransactionSuccessful();
		}finally{
			db.endTransaction();	// 结束事务
		}
	}
	
}

4.在先前创建的PersonServiceTest类里面写其他的测试方法，上代码

package com.xiaobo.juit;

import java.util.List;

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

import com.xiaobo.domain.Person;
import com.xiaobo.service.DbOpenHelper;
import com.xiaobo.service.PersonService;

public class PersonServiceTest extends AndroidTestCase{
	// 本测试类的专有TAG标签，利于LogCat调试
	private final static String TAG = "PersonServiceTest";
	
	/**
	 * 测试创建或更改数据库
	 * @throws Throwable
	 */
	public void testCreateDb() throws Throwable{
		DbOpenHelper dbOpenHelper = new DbOpenHelper(getContext());
		dbOpenHelper.getWritableDatabase();
	}
	
	/**
	 * 测试保存
	 * @throws Throwable
	 */
	public void testSave() throws Throwable{
		PersonService personService = new PersonService(getContext());
		personService.save(new Person(1, "yellow", "18706487378", 2000));
		personService.save(new Person(2, "yuan", "18706697820", 3000));
		personService.save(new Person(3, "zhen", "13658662813", 4000));
	}
	
	/**
	 * 测试根据id查找一条记录
	 * @throws Throwable
	 */
	public void testFind() throws Throwable{
		PersonService personService = new PersonService(getContext());
		Person person = personService.find(1);
		Log.i(TAG, person.toString());
	}
	
	/**
	 * 测试更新记录
	 * @throws Throwable
	 */
	public void testUpdate() throws Throwable{
		PersonService personService = new PersonService(getContext());
		personService.update(new Person(2, "yellow", "18706487378", 5000));
		personService.update(new Person(3, "yuan", "18706697820", 5000));
	}
	
	/**
	 * 测试删除记录
	 * @throws Throwable
	 */
	public void testDelete() throws Throwable{
		PersonService personService = new PersonService(getContext());
		personService.delete(1);
	}
	
	/**
	 * 测试获得记录总条数
	 * @throws Throwable
	 */
	public void testGetCount() throws Throwable{
		PersonService personService = new PersonService(getContext());
		Log.i(TAG, String.valueOf(personService.getCount()));
	}
	
	/**
	 * 测试分页记录
	 * @throws Throwable
	 */
	public void testGetScrollData() throws Throwable{
		PersonService personService = new PersonService(getContext());
		List<Person> persons = personService.getScrollData(0, 5);
		for(Person person :persons){
			Log.i(TAG, person.toString());
		}
	}
	
	/**
	 * 测试转账汇款
	 * @throws Throwable
	 */
	public void testUpdateAmount() throws Throwable{
		PersonService personService = new PersonService(getContext());
		Person p01 = personService.find(2);
		Person p02 = personService.find(3);
		System.out.println(p01.toString());
		System.out.println(p02.toString());
		p01.setAmount(500);
		p02.setAmount(600);
		personService.update(p01);
		personService.update(p02);
		Log.i(TAG, p01.getAmount().toString());
		Log.i(TAG, p02.getAmount().toString());
	}
	
	/**
	 * 测试转账汇款2
	 * @throws Throwable
	 */
	public void testPayment() throws Throwable{
		PersonService personService = new PersonService(getContext());
		Person p01 = personService.find(2);
		Person p02 = personService.find(3);
		// 打印转账之前账户信息
		Log.i(TAG, p01.toString());
		Log.i(TAG, p02.toString());
		personService.payment(2, 3);
		// 再次打印转账之后账户信息
		Log.i(TAG, p01.toString());
		Log.i(TAG, p02.toString());
	}
	
}

5.大家可以下源代码看一下，不是很会写原理，呵呵，希望大家共同进步