Android中数据库事务处理
使用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语句在同一个事务中执行。
1:Person 类
package cn.itcast.domain;
public class Person {
private Integer id;
private String name;
private Integer amount;
public Integer getAmount() {
return amount;
}
public void setAmount(Integer amount) {
this.amount = amount;
}
public Person(){}
public Person(Integer id, String name) {
this.id = id;
this.name = name;
}
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;
}
@Override
public String toString() {
return "Person [amount=" + amount + ", id=" + id + ", name=" + name
+ "]";
}
}
2:DBOpenHelper类
package cn.itcast.service;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper {
private static final String DATABASENAME = "itcast.db"; //数据库名称
private static final int DATABASEVERSION = 2;//数据库版本
public DBOpenHelper(Context context) {
super(context, DATABASENAME, null, DATABASEVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)");//执行有更改的sql语句
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS person");
onCreate(db);
}
}
3(1):通过SQlite访问方法一:PersonService类
package cn.itcast.service;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import cn.itcast.domain.Person;
public class PersonService {
private DBOpenHelper dbOpenHelper;
public PersonService(Context context) {
this.dbOpenHelper = new DBOpenHelper(context);
}
public void payment(){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.beginTransaction();//事启事务
try{
db.execSQL("update person set amount=amount-10 where personid=?", new Object[]{1});
db.execSQL("update person set amount=amount+10 where personid=?", new Object[]{2});
db.setTransactionSuccessful();//设置事务标志为成功,当结束事务时就会提交事务
}finally{
db.endTransaction();
}
}
public void save(Person person){
//如果要对数据进行更改,就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person (name,amount) values(?,?)",
new Object[]{person.getName(),person.getAmount()});
}
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=? where personid=?",
new Object[]{person.getName(),person.getId()});
}
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?", new Object[]{id.toString()});
}
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"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
Person person = new Person(personid, name);
person.setAmount(amount);
return person;
}
return null;
}
public List<Person> getScrollData(Integer offset, Integer maxResult){
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person limit ?,?",
new String[]{offset.toString(), maxResult.toString()});
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
int amount = cursor.getInt(cursor.getColumnIndex("amount"));
Person person = new Person(personid, name);
person.setAmount(amount);
persons.add(person);
}
cursor.close();
return persons;
}
public Cursor getCursorScrollData(Integer offset, Integer maxResult){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
return db.rawQuery("select personid as _id, name, amount from person limit ?,?",
new String[]{offset.toString(), maxResult.toString()});
}
public long getCount() {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person", null);
cursor.moveToFirst();
return cursor.getLong(0);
}
}
3(2)数据增删改查之测试:PersonServiceTest
package cn.itcast.db;
import java.util.List;
import cn.itcast.domain.Person;
import cn.itcast.service.DBOpenHelper;
import cn.itcast.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class PersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testCreateDB() throws Throwable{
DBOpenHelper dbOpenHelper = new DBOpenHelper(this.getContext());
dbOpenHelper.getWritableDatabase();//第一次调用该方法就会创建数据库
}
public void testSave() throws Throwable{
PersonService personService = new PersonService(this.getContext());
Person person = new Person();
person.setName("xiaoxiao");
person.setAmount(100);
personService.save(person);
person = new Person();
person.setAmount(50);
person.setName("zhangliming");
personService.save(person);
person = new Person();
person.setAmount(45);
person.setName("libaobao");
personService.save(person);
person = new Person();
person.setAmount(190);
person.setName("taobao");
personService.save(person);
}
public void testUpate() throws Throwable{
PersonService personService = new PersonService(this.getContext());
Person person = personService.find(1);
person.setName("lili");
personService.update(person);
}
public void testDelete() throws Throwable{
PersonService personService = new PersonService(this.getContext());
personService.delete(1);
}
public void testFind() throws Throwable{
PersonService personService = new PersonService(this.getContext());
Person person = personService.find(1);
Log.i(TAG, person.toString());
}
public void testGetScrollData() throws Throwable{
PersonService personService = new PersonService(this.getContext());
List<Person> persons = personService.getScrollData(0, 30);
for(Person person : persons){
Log.i(TAG, person.toString());
}
}
public void testGetCount() throws Throwable{
PersonService personService = new PersonService(this.getContext());
Log.i(TAG, personService.getCount()+"");
}
public void testPayment() throws Throwable{
PersonService personService = new PersonService(this.getContext());
personService.payment();
}
}
4(1):通过SQlite访问方法二:PersonService类
package cn.itcast.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 cn.itcast.domain.Person;
public class OtherPersonService {
private DBOpenHelper dbOpenHelper;
public OtherPersonService(Context context) {
this.dbOpenHelper = new DBOpenHelper(context);
}
public void save(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
db.insert("person", null, values);
}
public void update(Person person){
// update person set name =? where personid =?
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("name", person.getName());
db.update("person", values, "personid=?", new String[]{person.getId().toString()});
}
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.delete("person", "personid=?", new String[]{id.toString()});
}
public Person find(Integer id){
//如果只对数据进行读取,建议使用此方法
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[]{"personid", "name"},
"personid=?", new String[]{id.toString()}, null, null, null);
//select personid,name from person where personid=? order by ... limit 3,5
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
return new Person(personid, name);
}
return null;
}
public List<Person> getScrollData(Integer offset, Integer maxResult){
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", null, null, null, null, null, null, offset+","+ maxResult);
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
Person person = new Person(personid, name);
persons.add(person);
}
cursor.close();
return persons;
}
public long getCount() {// select count(*) from person
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);
cursor.moveToFirst();
return cursor.getLong(0);
}
}
4(2)数据增删改查之测试:OtherPersonServiceTest
package cn.itcast.db;
import java.util.List;
import cn.itcast.domain.Person;
import cn.itcast.service.DBOpenHelper;
import cn.itcast.service.OtherPersonService;
import cn.itcast.service.PersonService;
import android.test.AndroidTestCase;
import android.util.Log;
public class OtherPersonServiceTest extends AndroidTestCase {
private static final String TAG = "PersonServiceTest";
public void testSave() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
Person person = new Person();
person.setName("xiaoxiao");
personService.save(person);
person = new Person();
person.setName("zhangliming");
personService.save(person);
person = new Person();
person.setName("libaobao");
personService.save(person);
person = new Person();
person.setName("taobao");
personService.save(person);
}
public void testUpate() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
Person person = personService.find(1);
person.setName("lili");
personService.update(person);
}
public void testDelete() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
personService.delete(1);
}
public void testFind() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
Person person = personService.find(1);
Log.i(TAG, person.toString());
}
public void testGetScrollData() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
List<Person> persons = personService.getScrollData(0, 10);
for(Person person : persons){
Log.i(TAG, person.toString());
}
}
public void testGetCount() throws Throwable{
OtherPersonService personService = new OtherPersonService(this.getContext());
Log.i(TAG, personService.getCount()+"");
}
}
5:事务处理与数据显示
package cn.itcast.db;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import cn.itcast.domain.Person;
import cn.itcast.service.PersonService;
import android.app.Activity;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.database.Cursor;
import android.net.Uri;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;
public class MainActivity extends Activity {
private PersonService personService;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
this.personService = new PersonService(this);
ListView listView = (ListView) this.findViewById(R.id.listView);
Cursor cursor = personService.getCursorScrollData(0, 5);
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.item, cursor,
new String[]{"_id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});
listView.setAdapter(adapter);
listView.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
ListView lView = (ListView)parent;
Cursor data = (Cursor)lView.getItemAtPosition(position);
int personid = data.getInt(data.getColumnIndex("_id"));
Toast.makeText(MainActivity.this, personid+"", 1).show();
}
});
/*
List<Person> persons = personService.getScrollData(0, 5);
List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();
for(Person person : persons){
HashMap<String, Object> item = new HashMap<String, Object>();
item.put("id", person.getId());
item.put("name", person.getName());
item.put("amount", person.getAmount());
data.add(item);
}
SimpleAdapter adapter = new SimpleAdapter(this, data, R.layout.item,
new String[]{"id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});
listView.setAdapter(adapter);
listView.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
ListView lView = (ListView)parent;
HashMap<String, Object> item = (HashMap<String, Object>)lView.getItemAtPosition(position);
Toast.makeText(MainActivity.this, item.get("id").toString(), 1).show();
}
});
*/
Button button = (Button) this.findViewById(R.id.insertbutton);
button.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
ContentResolver contentResolver = getContentResolver();
Uri insertUri = Uri.parse("content://cn.itcast.providers.personprovider/person");
ContentValues values = new ContentValues();
values.put("name", "itcastliming");
values.put("amount", 100);
Uri uri = contentResolver.insert(insertUri, values);
Toast.makeText(MainActivity.this, "添加完成", 1).show();
}
});
}
}