android:sqlite 数据库的事务详解

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;@Overrideprotected 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;}@Overridepublic int getCount() {return list == null?0:list.size();}@Overridepublic Object getItem(int position) {// TODO Auto-generated method stubreturn list == null?null:list.get(position);}@Overridepublic long getItemId(int position) {// TODO Auto-generated method stubreturn position;}/** * 通过适配器把数值填充到ListView中 */@Overridepublic 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> * 这个表特别适合做创建表的操作 */@Overridepublic 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> */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}