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

hhajhh

　　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 . 源代码，请参照附件