（Android)在SQLite中使用事务

举例：

转账

1> update person set amount = amount-10 where personid = 1;

2> update person set amount = amount+10 where personid = 2;

1.person.java

package com.example.domain;


public class Person {
private Integer id;
private String name;
private String phone;
private Integer amount;
public Person(){};


public Person(String name,String phone,Integer amount) {
super();
this.name = name;
this.phone = phone;
this.amount = amount;

}




public Person(Integer id, String name, String phone,Integer amount) {
super();
this.id = id;
this.name = name;
this.phone = phone;
this.amount = amount;
}


public Integer getAmount() {
return amount;
}




public void setAmount(Integer amount) {
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;
}




@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone
+ ", amount=" + amount + "]";
}

}

2.DBOpenHelper .java

package com.example.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, "itcast.db", null, 4);
// TODO Auto-generated constructor stub
}


@Override
public void onCreate(SQLiteDatabase db) {//是在数据库第一次创建的时候被调用的
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12) NULL)");
}


@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
db.execSQL("ALTER TABLE person ADD amount integer");
}


}

3.PersonService .java

package com.example.service;


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


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


import com.example.domain.Person;


public class PersonService {
private DBOpenHelper dbOpenHelper;

public PersonService(Context context) {
super();
this.dbOpenHelper = new DBOpenHelper(context);
}
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()});
}
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?",new Object[]{id});
}
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()});
}
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"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
return new Person(personid,name,phone,amount);
}
cursor.close();
return null;
}
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"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(personid,name,phone,amount));
}
cursor.close();
return persons;

}
public long getCount() {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;

}
public void payment() {
// TODO Auto-generated method stub
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
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){
e.printStackTrace();
}finally{
db.endTransaction();//结束事务，有两种情况，commit,rollback,事务的提交或回滚
//是由事务的标志决定的，如果事务的标志为true,则提交，否则回滚。默认情况下事务的标志为false;
}

}


}

4.MainActivity .java

package com.example.sqlite;


import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;


public class MainActivity extends Activity {


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}


@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}


}

5.PersonServiceTest .java

package com.example.test;


import java.util.List;


import com.example.domain.Person;
import com.example.service.DBOpenHelper;
import com.example.service.PersonService;


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


public class PersonServiceTest extends AndroidTestCase{
public void testCreateDB() throws Exception {
DBOpenHelper dbOpenHelp = new DBOpenHelper(getContext());
dbOpenHelp.getWritableDatabase();
}
public void testSave() throws Exception {
PersonService service = new PersonService(this.getContext());

for(int i=0;i<20;i++) {
Person person = new Person("zhabg"+1,"125467568",100);
service.save(person);
}

}
public void testDelete() throws Exception {
PersonService service = new PersonService(this.getContext());
service.delete(21);
}
public void testUpdate() throws Exception {
PersonService service = new PersonService(this.getContext());
Person person = service.find(1);

person.setName("zhangxiaoxiao");
service.update(person);
}
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(0, 5);
for(Person person:persons) {
Log.i("TAG", person.toString());
}
}
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();
}
}

6AndroidManifest.xml

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


    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="18" />


    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <uses-library android:name="android.test.runner" />
        <activity
            android:name="com.example.sqlite.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>

<instrumentation 
   android:name="android.test.InstrumentationTestRunner"
   android:label="Test for my app"
   android:targetPackage="com.example.sqlite"></instrumentation>

</manifest>