sqlite 数据库的事务详解,所有的解释都在代码中进行了注释
关于事务的应用主要都在MainActivity的onClickTransferAccountsSuccess和onClickTransferAccountsError方法中做的注释,请运行体会
运行结果如下图
1.布局文件activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity" >
<Button
android:id="@+id/btn_success"
android:onClick="onClickTransferAccountsSuccess"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="正确转账(正确的事物)" />
<Button
android:id="@+id/btn_error"
android:onClick="onClickTransferAccountsError"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="错误转账(异常的事物)" />
<ListView
android:id="@+id/lv_many"
android:layout_width="match_parent"
android:layout_height="match_parent"
></ListView>
</LinearLayout>
2.ListView的item布局文件item_money.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/tv_id"
android:layout_width="40dp"
android:layout_height="wrap_content"
/>
<TextView
android:id="@+id/tv_name"
android:layout_width="60dp"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tv_money"
android:layout_width="60dp"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tv_phone"
android:layout_width="100dp"
android:layout_height="wrap_content"/>
<TextView
android:id="@+id/tv_age"
android:layout_width="50dp"
android:layout_height="wrap_content"/>
</LinearLayout>
3.activity的实现类MainActivity.java
package com.smartdot.transaction;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
private static final String TAG = "MainActivity";
private OpenHelper helper;
private ListView lv_many;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
helper = new OpenHelper(this);
lv_many = (ListView) findViewById(R.id.lv_many);
setBaseAdapter();//读取数据库的数据,并设置lv_many列表的数据
}
/**
* 正确的转账方法
*/
public void onClickTransferAccountsSuccess(View v) {
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();//开启事务
try {
//马汉缺钱要王超给他转账100元
db.execSQL("update user set money=money-100 where name=? and money>=100",new String[]{"王朝"});
db.execSQL("update user set money=money+100 where name=? and (select money from user u where u.name=?)>=100",new String[]{"马汉","王朝"});
db.setTransactionSuccessful();//事务正确结束,如果此方法不执行,则所有的数据增删改操作都会回滚
Toast.makeText(MainActivity.this, "转账成功!", Toast.LENGTH_SHORT).show();
Log.i(TAG, "转账成功!");
} catch (Exception e){
Toast.makeText(MainActivity.this, "服务器忙,请稍后!", Toast.LENGTH_SHORT).show();
Log.e(TAG, "服务器忙,请稍后!");
} finally {
db.endTransaction();//关闭事务
setBaseAdapter();
}
}
/**
* 错误的转账方法
*/
public void onClickTransferAccountsError(View v) {
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();//开启事务
try {
//马汉缺钱要给他王超转账100元
db.execSQL("update user set money=money-100 where name=?",new String[]{"王朝"});
int i = 10/0;//故意设置一个异常
db.execSQL("update user set money=money+100 where name=?",new String[]{"马汉"});
db.setTransactionSuccessful();//事务正确结束,如果此方法不执行,则所有的数据增删改操作都会回滚
Toast.makeText(MainActivity.this, "转账成功!", Toast.LENGTH_SHORT).show();//此处永远不会执行
} catch (Exception e){
Toast.makeText(MainActivity.this, "服务器忙,请稍后!", Toast.LENGTH_SHORT).show();
Log.e(TAG, "服务器忙,请稍后!");//真正企业开发,一般会报服务器忙,请稍后。不会抛出异常给用户!!!
} finally {
db.endTransaction();//关闭事务
setBaseAdapter();
}
}
/**
* 重新设置列表的适配器
*/
public void setBaseAdapter() {
List<Map<String,String>> list = query();//转完账查询记录
MyBaseAdapter mBaseAdapter = new MyBaseAdapter(list);//设置listView的适配器
lv_many.setAdapter(mBaseAdapter);
}
/**
* 查询记录
* @return
*/
public List<Map<String,String>> query() {
SQLiteDatabase db = helper.getWritableDatabase();
Cursor cursor = db.query("user", new String[]{"name","money","age","phone","_id"}, null, null, null, null, null);
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
Map<String,String> map;
while(cursor.moveToNext()) {
map = new HashMap<String, String>();
map.put("name", cursor.getString(0));
map.put("money", cursor.getString(1));
map.put("age", cursor.getInt(2) + "");
map.put("phone", cursor.getString(3));
map.put("id", cursor.getInt(4) + "");
list.add(map);
}
return list;
}
class MyBaseAdapter extends BaseAdapter {
public List<Map<String,String>> list ;
public MyBaseAdapter() {
}
public MyBaseAdapter(List<Map<String,String>> list) {
this.list = list;
}
@Override
public int getCount() {
return list == null?0:list.size();
}
@Override
public Object getItem(int position) {
// TODO Auto-generated method stub
return list == null?null:list.get(position);
}
@Override
public long getItemId(int position) {
// TODO Auto-generated method stub
return position;
}
/**
* 通过适配器把数值填充到ListView中
*/
@Override
public View getView(int position, View convertView, ViewGroup parent) {
ConvertViewTag tag ;//用于缓存内部的find的对象
TextView tv_id = null;
TextView tv_name = null;
TextView tv_age = null;
TextView tv_phone = null;
TextView tv_money = null;
if(convertView == null) {
convertView = View.inflate(MainActivity.this, R.layout.item_money,null);
tv_id = (TextView) convertView.findViewById(R.id.tv_id);
tv_name = (TextView) convertView.findViewById(R.id.tv_name);
tv_money = (TextView) convertView.findViewById(R.id.tv_money);
tv_age = (TextView) convertView.findViewById(R.id.tv_age);
tv_phone = (TextView) convertView.findViewById(R.id.tv_phone);
tag = new ConvertViewTag();
tag.tv_id = tv_id;
tag.tv_money = tv_money;
tag.tv_age = tv_age;
tag.tv_name = tv_name;
tag.tv_phone = tv_phone;
convertView.setTag(tag);//缓存获得的View对象,如果数据多的时候避免二次创建
} else {
tag = (ConvertViewTag) convertView.getTag();//获取缓存的对象
tv_id = tag.tv_id;
tv_name = tag.tv_name;
tv_money = tag.tv_money;
tv_age = tag.tv_age;
tv_phone = tag.tv_phone;
}
Map<String, String> map = list.get(position);
tv_id.setText(map.get("id"));
tv_name.setText(map.get("name"));
tv_money.setText(map.get("money"));
tv_age.setText(map.get("age"));
tv_phone.setText(map.get("phone"));
return convertView;
}
class ConvertViewTag {
public TextView tv_id;
public TextView tv_name;
public TextView tv_money;
public TextView tv_age;
public TextView tv_phone;
}
}
}
4.SQLiteOpenHelper的实现类OpenHelper.java
package com.smartdot.transaction;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* 实现android提供的抽象类SQLiteOpenHelper,用于sqlite数据库的操作
*
* @author wangguang
*
*/
public class OpenHelper extends SQLiteOpenHelper {
/**
* 第一次调用数据库,对出具库进行访问。
*
* @param context
*/
public OpenHelper(Context context) {
// 数据版本version只能比之前高,不得低于之前设定的版本号
super(context, "transaction.db", null, 1);// 系统创建数据库
}
/**
* 当数据库(表)发生改变时可调用此方法
*
* @param Context
* context 上下文
* @param int version 数据库版本
*/
public OpenHelper(Context context, int version) {
// 数据版本version只能比之前高,不得低于之前设定的版本号
super(context, "smartdot.db", null, version);
}
/**
* 当数据库第一次创建的时候创建<br>
* 这个表特别适合做创建表的操作
*/
@Override
public void onCreate(SQLiteDatabase db) {
String creatTable = "create table user (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name varchar,money varchar(20),phone varchar,age int,sex int)";
db.execSQL(creatTable);
db.execSQL(
"INSERT INTO USER (NAME,money,PHONE,SEX,AGE) VALUES(?,?,?,?,?)",
new Object[] { "王朝", "100000", "18701545309", 1, 30 });// 添加测试数据
db.execSQL(
"INSERT INTO USER (NAME,money,PHONE,SEX,AGE) VALUES(?,?,?,?,?)",
new Object[] { "马汉", "1000", "18701545309", 1, 30 });// 添加测试数据
}
/**
* 当数据库版本发生改变的时候调用此方法,此方法适合修改数据库的处理修改表,添加表,删除表<br>
*
* @param SQLiteDatabase
* db<br>
* @param int oldVersion 老版本号<br>
* @param int newVersion 新版本号<br>
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}