18_在SQLite中使用事務
1.轉賬的事物實現:
update person set amount=amount-10 where personid=1;
update person set amount=amount+10 where personid=2;
———————————————————–
2.使用SQLiteDatabase的beginTransaction()方法可以開啟一個事務，程序執行到
endTransaction() 方法時會檢查事務的標志是否為成功，如果程序執行到endTransaction()
之前調用瞭setTransactionSuccessful() 方法設置事務的標志為成功則提交事務，如果沒有
調用setTransactionSuccessful() 方法則回滾事務。使用例子如下： SQLiteDatabase db
= ….;
db.beginTransaction();//開始事務
try {
db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"傳智播
客", 4});
db.execSQL("update person set name=? where personid=?", new Object[]{"傳智",
1});
db.setTransactionSuccessful();//調用此方法會在執行到endTransaction() 時提交當
前事務，如果不調用此方法會回滾事務
} finally {
db.endTransaction();//由事務的標志決定是提交事務，還是回滾事務
}
db.close();
上面兩條SQL語句在同一個事務中執行。
——————————————————————-
3.在SQLite中使用事務的代碼實現:
DBSQLIte項目,註意是在這個項目基礎上改的,就是在原來的person數據庫中加瞭一個
amount字段;
———————–
/DBSQLIte/src/com/credream/entity/Person.java
package com.credream.entity;
public class Person
{
private Integer id;
private String name;
private String phone;
private Integer amount;
public Person()
{
}
public Person(Integer id, String name, String phone,Integer amount)
{
this.id = id;
this.name = name;
this.phone = phone;
}
/*public Person(String name, String phone)
{
this.name = name;
this.phone = phone;
}*/
public Person(String name, String phone, Integer amount)
{
this.name = name;
this.phone = phone;
this.amount = amount;
}
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
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;
}
public Integer getAmount()
{
return amount;
}
public void setAmount(Integer amount)
{
this.amount = amount;
}
@Override
public String toString()
{
return "Person [amount=" + amount + ", id=" + id + ", name=" + name
+ ", phone=" + phone + "]";
}
}
————————————————————————–
使用代碼,給person表添加amount列:
/DBSQLIte/src/com/credream/service/DBOpenHelter.java
package com.credream.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
public class DBOpenHelter extends SQLiteOpenHelper
{
//父類沒有默認構造器,需要顯示調用
public DBOpenHelter(Context context)
{
//super(context, "credream.db", null, 2);
super(context, "credream.db", null, 3);
//數據庫創建完成後,默認會保存在<包>/database/文件夾下
//當修改版本號時候,會觸發:onUpgrade方法
//第二個:指定數據庫名稱,
//第三個:遊標工廠,用來迭代,查詢後的結果集,null代表使用系統默認的
遊標工廠
//版本號,大於0
}
/**
* 這個方法是在數據庫第一次被創建的時候調用的
*/
@Override
public void onCreate(SQLiteDatabase db)
{
//SQLiteDatabase這個類,封裝瞭增刪改查操作,也叫做數據庫操作實例
db.execSQL("CREATE TABLE person (personid integer primary key
autoincrement, name varchar(20))");
//這裡也可以不寫name的數據類型,因為sqlite是數據類型無關的,就是寫
瞭varchar(20),也可以寫入超過20的內容
}
/**
* 當數據庫的版本號變更的時候被調用
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
//db.execSQL("alter table person add phone varchar(12) null");
db.execSQL("alter table person add amount integer");
}
}
—————————————————————–
/DBSQLIte/src/com/credream/service/PersonService.java
package com.credream.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.credream.entity.Person;
public class PersonService
{
private DBOpenHelter dbOpenHelter;
public PersonService(Context context){
this.dbOpenHelter=new DBOpenHelter(context);
}
public void payment (){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
/*db.beginTransaction();//開啟事物
db.execSQL("update person set amount=amount-10 where
personid=1");
db.execSQL("update person set amount=amount+10 where personid=2");
db.setTransactionSuccessful();//設置事物的標志位true
db.endTransaction();//結束事物有兩種情況,commit,rollback;
//註意以上代碼書寫方式不好,因為當db.execSQL,db.execSQL這兩個執行發生異常的時候,代
碼就不在往下
//執行瞭,這樣的話 db.endTransaction(),就不能被執行到,那麼也就是說,用完後,程序沒有
主動的關閉事物
//而是由數據庫在超時的情況下,幫你關閉的;這樣會影響並發性能,以下代碼可以保證
db.endTransaction()
//的執行:
*/
db.beginTransaction();//開啟事物
try
{
db.execSQL("update person set amount=amount-10 where
personid=1");
db.execSQL("update person set amount=amount+10 where
personid=2");
db.setTransactionSuccessful();//設置事物的標志位true
} catch (Exception e)
{
}finally{
db.endTransaction();
}
//事物的提交或者回滾是由事物的標志所決定的,默認情況下事物的標志位
false
//如果事物的標志位true,那麼事物會提交,否則事物會回滾
}
/**
* 添加記錄
* @param person
*/
public void save (Person person){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
//SQLiteDatabase db2=dbOpenHelter.getWritableDatabase();
//這裡獲得的數據庫實例和db這個數據庫實例是一樣的,因為數據庫有緩存
功能
//在源碼裡進行瞭判斷,如果得到的數據庫實例不為null,並且已經打開,並
且是隻讀的,那麼
//就直接返回這個實例
//dbOpenHelter.getWritableDatabase().execSQL(sql);這裡和db.execSQL("作用
是一樣的
//db.execSQL("insert into person (name,phone) values
('"+person.getName()+"','"+person.getPhone()+"')");
//上面這種寫法是錯誤的,因為當用戶輸入cre'dream的時候那麼sql語句就成瞭
//insert into person (name,phone) values
('cre'dream','15066659146')這樣會出現sql語法錯誤
//所以,應該用轉義字符
db.execSQL("insert into person (name,phone,amount) values
(?,?,?)",new Object[]{person.getName(),person.getPhone(),person.getAmount()});
//db.close();//數據庫也可以不關閉,因為這樣的話可以提升性能,因為不用頻繁的
開關數據庫
}
/**
* 刪除記錄
* @param person
*/
public void delete (Integer id){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
db.execSQL("delete from person where personid=?",new Object[]
{id});
}
/**
* 更新記錄
* @param person
*/
public void update(Person person){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
db.execSQL("update person set name=?,phone=? ,amount=? where
personid=?",new Object[]{person.getName(),person.getPhone(),person.getAmount
(),person.getId()});
}
/**
* 查找記錄
* @param id
* @return
*/
public Person find (Integer id){
SQLiteDatabase db=dbOpenHelter.getReadableDatabase();
//getReadableDatabase();這個方法裡面調用瞭getWritableDatabase();
方法,來取得數據庫操作實例,隻有調用
//失敗的時候就會發現異常,當數據磁盤滿瞭,會拋出異常,這時候會打開這
個實例的數據庫
//然後開始讀取,這樣當磁盤滿的時候,
//當數據庫磁盤空間沒有滿的時候,返回的數據庫操作實例是一樣的:可讀
可寫,當數據庫磁盤滿瞭,返回的數據庫
//操作實例是隻讀的
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);
}//當有數據會返回true,否則為false
cursor.close();
return null;
}
//處理分頁
/**
* 分頁獲取記錄
* offset:跳過前面幾條記錄
* maxlength:獲取幾條
*/
public List<Person> getScrollData(int offset,int MaxResult){
List<Person> persons=new ArrayList<Person>();
SQLiteDatabase db=dbOpenHelter.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;
}
public long getCount(){
SQLiteDatabase db=dbOpenHelter.getReadableDatabase();
Cursor cursor=db.rawQuery("select count(*) from person " ,null);
//select count(*) from person註意這裡至少會獲得一條數據
cursor.moveToFirst();
long result=cursor.getLong(0);
return result;
}
}
—————————————————————
/DBSQLIte/src/com/credream/service/OtherPersonService.java
package com.credream.service;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.credream.entity.Person;
public class OtherPersonService
{
private DBOpenHelter dbOpenHelter;
public OtherPersonService(Context context){
this.dbOpenHelter=new DBOpenHelter(context);
}
/**
* 添加記錄
* @param person
*/
public void save (Person person){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
//SQLiteDatabase db2=dbOpenHelter.getWritableDatabase();
//這裡獲得的數據庫實例和db這個數據庫實例是一樣的,因為數據庫有緩存
功能
//在源碼裡進行瞭判斷,如果得到的數據庫實例不為null,並且已經打開,並
且是隻讀的,那麼
//就直接返回這個實例
//dbOpenHelter.getWritableDatabase().execSQL(sql);這裡和db.execSQL("作用
是一樣的
//db.execSQL("insert into person (name,phone) values
('"+person.getName()+"','"+person.getPhone()+"')");
//上面這種寫法是錯誤的,因為當用戶輸入cre'dream的時候那麼sql語句就成瞭
//insert into person (name,phone) values
('cre'dream','15066659146')這樣會出現sql語法錯誤
//所以,應該用轉義字符
ContentValues values=new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
values.put("amount", person.getAmount());
db.insert("person", null, values);
//這裡第二個字段,是空值字段,如果用戶傳過去的字段是空集合那麼就無
法組拼接sql
//比如:db.insert("person", "name",null );這時候,就是代表name可以
為null
//insert into person(name) values(NULL);
//所以當第三個參數不是null,的時候第二個參數是沒用的直接設置為
null就可以瞭
//db.insert("person", "personid",null );對應的sql:insert into
person(personid) values(NULL);
//這時候主鍵為null,按理說是不正確的,但是sqlite做瞭處理,這個時候其
實取瞭主鍵自增值作為personid的值
//在源代碼裡,也是通過構造sql語句來完成,數據的添加的
//db.execSQL("insert into person (name,phone) values(?,?)",new
Object[]{person.getName(),person.getPhone()});
//db.close();//數據庫也可以不關閉,因為這樣的話可以提升性能,因為不用頻繁的
開關數據庫
}
/**
* 刪除記錄
* @param person
*/
public void delete (Integer id){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
db.delete("person", "personid=?", new String[]{id.toString()});
//這裡拼接的時候自動的給加瞭where不要重復加
//db.execSQL("delete from person where personid=?",new Object[]
{id});
}
/**
* 更新記錄
* @param person
*/
public void update(Person person){
SQLiteDatabase db=dbOpenHelter.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("name", person.getName());
values.put("phone", person.getPhone());
values.put("amount", person.getAmount());
db.update("person", values,"personid=?",new String[]
{person.getId().toString()} );
//db.execSQL("update person set name=?,phone=? where
personid=?",new Object[]{person.getName(),person.getPhone(),person.getId()});
}
/**
* 查找記錄
* @param id
* @return
*/
public Person find (Integer id){
SQLiteDatabase db=dbOpenHelter.getReadableDatabase();
//getReadableDatabase();這個方法裡面調用瞭getWritableDatabase();
方法,來取得數據庫操作實例,隻有調用
//失敗的時候就會發現異常,當數據磁盤滿瞭,會拋出異常,這時候會打開這
個實例的數據庫
//然後開始讀取,這樣當磁盤滿的時候,
//當數據庫磁盤空間沒有滿的時候,返回的數據庫操作實例是一樣的:可讀
可寫,當數據庫磁盤滿瞭,返回的數據庫
//操作實例是隻讀的
//Cursor cursor=db.query("person", new String[]{"personid","name","phone"},
"personid=?", new String[]{id.toString()}, null, null, null);
Cursor cursor=db.query("person", null, "personid=?", new String
[]{id.toString()}, null, null, null);
//Passing null will return all columns,第二個參數傳入null,將會獲
取所有的列
//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);
}//當有數據會返回true,否則為false
cursor.close();
return null;
}
//處理分頁
/**
* 分頁獲取記錄
* offset:跳過前面幾條記錄
* maxlength:獲取幾條
*/
public List<Person> getScrollData(int offset,int MaxResult){
List<Person> persons=new ArrayList<Person>();
SQLiteDatabase db=dbOpenHelter.getReadableDatabase();
Cursor cursor=db.query("person", null, null, null, null,null,
"personid asc", offset+","+MaxResult);
//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;
}
public long getCount(){
SQLiteDatabase db=dbOpenHelter.getReadableDatabase();
Cursor cursor=db.query("person", new String[]{"count(*)"}, null,
null, null, null, null);
//Cursor cursor=db.rawQuery("select count(*) from person "
,null);
//select count(*) from person註意這裡至少會獲得一條數據
cursor.moveToFirst();
long result=cursor.getLong(0);
return result;
}
}
——————————————————————-
/DBSQLIte/src/com/credream/test/OtherPersonServiceTest.java
package com.credream.test;
import java.util.List;
import com.credream.entity.Person;
import com.credream.service.DBOpenHelter;
import com.credream.service.OtherPersonService;
import com.credream.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class OtherPersonServiceTest extends AndroidTestCase
{
//PersonService service=new PersonService(this.getContext());
//不可以寫到這裡,因為這段代碼是在實例化過程中被調用的,如果剛剛開始的就直
接實例化這個對象
//這時候是取不到上下文對象的,隻有實例化過後才可以獲取上下文對象
//如果非要把這個單獨拿出來的話,可以寫到setUp()方法裡面,因為每個方法執行之
前都會首先執行這個方法
private static final String TAG="PersonServiceTest";
//創建數據庫,在<包>/database/
public void testCreateDB()throws Exception{
DBOpenHelter dbOpenHelter=new DBOpenHelter(getContext());
dbOpenHelter.getWritableDatabase();
}
public void testSave()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
//for(int i=0;i<20;i++){
//Person person=new Person("lidewei"+i,"15163245754"+i);
Person person=new Person("xiaoyue","15163245754",100);
service.save(person);
//}
}
public void testUpdate()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
Person person=service.find(1);
person.setName("mydream");
service.update(person);
}
public void testDelete()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
service.delete(22);
}
public void testFind()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
Person person=service.find(1);
Log.i(TAG,person.toString());
}
public void testScrollData()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
List<Person> persons=
service.getScrollData(0, 50);
for(Person person :persons){
Log.i(TAG,person.toString());
}
//翻到第2頁
}
public void testCount()throws Exception{
OtherPersonService service=new OtherPersonService
(this.getContext());
long result=service.getCount();
Log.i(TAG, result+"");
}
//除瞭以上的數據庫操作為,還為我們提供瞭專門方法:
}
——————————————————————–
/DBSQLIte/src/com/credream/test/PersonServiceTest.java
package com.credream.test;
import java.util.List;
import com.credream.entity.Person;
import com.credream.service.DBOpenHelter;
import com.credream.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class PersonServiceTest extends AndroidTestCase
{
private static final String TAG="PersonServiceTest";
//創建數據庫,在<包>/database/
public void testCreateDB()throws Exception{
DBOpenHelter dbOpenHelter=new DBOpenHelter(getContext());
dbOpenHelter.getWritableDatabase();
}
public void testSave()throws Exception{
PersonService service=new PersonService(this.getContext());
for(int i=0;i<20;i++){
Person person=new Person("lidewei"+i,"15163245754"+i,200);
service.save(person);
}
}
public void testUpdate()throws Exception{
PersonService service=new PersonService(this.getContext());
Person person=service.find(1);
person.setName("credream");
service.update(person);
}
public void testDelete()throws Exception{
PersonService service=new PersonService(this.getContext());
service.delete(21);
}
public void testFind()throws Exception{
PersonService service=new PersonService(this.getContext());
Person person=service.find(1);
Log.i(TAG,person.toString());
}
public void testScrollData()throws Exception{
PersonService service=new PersonService(this.getContext());
List<Person> persons=
service.getScrollData(5, 5);
for(Person person :persons){
Log.i(TAG,person.toString());
}
//翻到第2頁
}
public void testCount()throws Exception{
PersonService service=new PersonService(this.getContext());
long result=service.getCount();
Log.i(TAG, result+"");
}
//除瞭以上的數據庫操作為,還為我們提供瞭專門方法:
public void testUpdateAmount()throws Exception{
PersonService service=new PersonService(this.getContext());
Person person1=service.find(1);
Person person2=service.find(2);
person1.setAmount(100);
person2.setAmount(50);
service.update(person1);
service.update(person2);
}
public void testPayMent()throws Exception{
PersonService service=new PersonService(this.getContext());
service.payment();
}