无废话Android 系列教程35 [Android下的数据库SQLite事务概念及使用&&测试]

1. 事务的作用：我们在一些特殊业务需要时。会有如果要求：要保证一个操作要么同时成功，要么同时失败；不允许上个逻辑成功后突然出错/异常(如空指针异常)，导致下一个操作失败，这是绝对不允许的。

2. 事务经典实例：如银行转帐业务，A用户转出100元给B用户，这时要么A用户少100元 B用户同时多100元；或转帐失败，A用户钱没减少B用户钱也没增加，提示用户转帐失败；不允许A用户钱少100元后，由于某些异常导致B用户没收到100元钱。

3. com.andy.shiwu 包下的PersonSQLiteOpenHelper.java

package com.andy.shiwu;

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

/**
 * 创建PersonSQLiteOpenHelper类 继承SQLiteOpenHelper抽象类,此时必须创建构造方法
 * @author huiqinbo
 *
 */
public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

	//通过getSimpleName()方法获取到值为"PersonSQLiteOpenHelper"
	private static final String tag = PersonSQLiteOpenHelper.class.getSimpleName();

	/**
	 * 数据库的构造方法, 用来定义数据库的名称/数据库的查询结果集/数据库的版本号
	 * 
	 * 当版本号发生变化时才可以调用onUpgrade方法; 如版本号从1 →2
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public PersonSQLiteOpenHelper(Context context) {
//		super(context, "person.db", null, 1);
		super(context, "person.db", null, 4);
		// TODO Auto-generated constructor stub
	}
	
	

	/**
	 * 数据库第一次被创建的时候调用的方法
	 * @param db 被创建的数据库
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table person (id integer primary key autoincrement, name varchar(20), number varchar(20))");
	}

	
	/**
	 * sqlite中ALTER TABLE语句不支持DROP COLUMN,只有RENAME 和ADD 语句.
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.i(tag, "数据库的版本发生变化了");
		db.execSQL("alter table person add account varchar(20)");

	}

}

注：请关注 51  52行，这里更改数据库person的表结构，增加了account字段！

4. com.andy.shiwu.dao包下面的PersonDao2.java

package com.andy.shiwu.dao;

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

import com.andy.shiwu.PersonSQLiteOpenHelper;
import com.andy.shiwu.domain.Person;

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

public class PersonDao2 {
	private PersonSQLiteOpenHelper helper;
	public PersonDao2(Context context){
		helper = new PersonSQLiteOpenHelper(context);
	}
	
	/*
	 * 插入用户名和手机号到SQLite数据库
	 * 
	 * the row ID of the newly inserted row, or -1 if an error occurred
	 */
	public long addPerson(String name, String number,int account){
		
		//此时就获取到了数据库
		SQLiteDatabase db = helper.getWritableDatabase();
		//db.execSQL("insert into person (name,number) values (?,?)", new Object[]{name,number});
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("number", number);
		values.put("account", account);
		long row_id = db.insert("person", null, values);
		db.close();
		return row_id;
	}
	
	/**
	 * 删除一条数据，按用户名的条件
	 * @param name
	 * 
	 * WhereClause :the optional WHERE clause to apply when deleting. Passing null will delete all rows.
	 */
	public int deletePerson(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
//		db.execSQL("delete from person where name =?", new Object[]{name});
		int row_id = db.delete("person", "name=?", new String[]{name});
		db.close();
		
		return row_id;
	}
	
	
	/**
	 * 修改一条数据,按用户名的条件
	 * @param name
	 * @param number
	 */
	public int updatePerson(String name, String number){
		SQLiteDatabase db = helper.getWritableDatabase();
//		db.execSQL("update person set number = ? where name = ?", new Object[]{name,number} );
		ContentValues values = new  ContentValues();
		values.put("number", number);
		
		int row_id = db.update("person", values, "name=?", new String[]{name});
		db.close();
		
		return row_id;
	}
	
	/**
	 * 查询一条数据,按用户名的条件
	 * @param name
	 * @param number
	 * selection : A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
	 * selectionArgs:  You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
	 */
	public boolean findPerson(String name){
		SQLiteDatabase db = helper.getWritableDatabase();
//		Cursor cursor = db.rawQuery("select * from person where name=?", new String[]{name});
		Cursor cursor = db.query("person", new String[]{"id", "name", "number"}, "name=?", new String[]{name}, null, null, null);
		boolean result = cursor.moveToNext();
		cursor.close();
		db.close();
		return result;
	}
	
	
	public List<Person> findAllPerson(){
		SQLiteDatabase db = helper.getWritableDatabase();
//		Cursor cursor = db.rawQuery("select * from person", null);
		Cursor cursor = db.query("person", new String[]{"id","name","number"}, null, null, null, null, null);
		List<Person> persons = new ArrayList<Person>(); 
		Person person;
		while(cursor.moveToNext()){
			int id = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String number = cursor.getString(cursor.getColumnIndex("number"));
			person = new Person(id,name,number);
			persons.add(person);
		}
		cursor.close();
		db.close();
		return persons;
	}

}

5. com.andy.shiwu.domain 包下的Person.java 类

package com.andy.shiwu.domain;

public class Person {
	private int id;
	private String name;
	private String number;
	
	public Person(int id, String name, String number){
		this.id = id;
		this.name= name;
		this.number = number;
	}
	
	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", number=" + number
				+ "]";
	}
	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;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number;
	}
	
	

}

6. com.andy.shiwu.test 包下的TestPersonDao2.java

package com.andy.shiwu.test;

import java.util.List;

import com.andy.shiwu.PersonSQLiteOpenHelper;
import com.andy.shiwu.dao.PersonDao2;
import com.andy.shiwu.domain.Person;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import android.util.Log;

/**
 * 
 * @author huiqinbo
 *
 */
public class TestPersonDao2 extends AndroidTestCase {

	private static final String tag = TestPersonDao2.class.getSimpleName();
	public PersonDao2 pd;

	/**
	 * getContext() 是测试框架给提供的上下文件 来源:android.test.AndroidTestCase.getContext()
	 * 
	 * 第一次执行时调用PersonSQLiteOpenHelper.java 内的onCreate方法，
	 * 当版本号发生变化时，会调用PersonSQLiteOpenHelper.java 内的onUpgrade方法.
	 */
	public void testCreatePersonDb() {
		PersonSQLiteOpenHelper db = new PersonSQLiteOpenHelper(getContext());
		// 打开或创建一个可写的数据库
		db.getWritableDatabase();
	}

	public void testAddPerson() {
		pd = new PersonDao2(getContext());
		pd.addPerson("张三", "123456789", 5000);
		pd.addPerson("李四", "987654321", 10000);
	}



	/**
	 * 这是没有加上事务的方法.
	 */
	public void testTransaction() {
		PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
		// 打开或创建一个可写的数据库
		SQLiteDatabase db = helper.getWritableDatabase();
		db.execSQL("update person set account=account-1000 where name ='张三'");
		db.execSQL("update person set account=account+1000 where name ='李四'");
		db.close();
	}

	/*
	 * 这是加上事务的方法. 假如在执行到"张三"SQL语句执行成功了，到李四SQL语句执行前出现了异常,导致李四操作没成功,这是不可以的。
	 * 所以要使用「事务」，要么同时成功，要么同时失败.
	 */
	public void testTransactionShiWu() {
		PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper(getContext());
		// 打开或创建一个可写的数据库
		SQLiteDatabase db = helper.getWritableDatabase();
		
//		String s = null;
		//1. 开启数据库的事务
		db.beginTransaction();
		try {
			db.execSQL("update person set account=account-1000 where name ='张三'");
//			s.equals("这里是制造空指针异常语句,与事务无关！");
			db.execSQL("update person set account=account+1000 where name ='李四'");
			
			
			//2. 标记当前数据库事务执行成功.
			//(当认为事务执行成功时,就必须执行db.setTransactionSuccessful(), 如果不执行这段代码，默认情况认为执行失败，数据就不会不commit, 如果commit会回滚roll back)
			//通过Andy个人实验确实是:当没有setTransactionSuccessful这个方法时张三&李四的操作同时不成功
			db.setTransactionSuccessful();
		} catch (Exception e){
			Log.i(tag, "事务中代码内出现异常, 请检查");
		}
		finally {
			
			//3. 结束数据库的事务
			//通过Andy个人实验确实是:当没有setTransactionSuccessful这个方法时张三&李四的操作同时不成功
			//它会检查如果前面有db.setTransactionSuccessful() 它会commit数据，否则会rolled back回滚数据
			db.endTransaction();
			db.close();
		}
	}
}

 7.  使用adb 测试数据库

 8 . 源代码，请参照附件