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();
}
}
--------------------------------------------------------------
以上是sqlite使用事物的实例代码