Android 数据库交易

我建立了一个数据库。我想做交易。SaveCustomer()包含一个以上的语句，用于同时将记录插入到 Customer, CustomerControl, Profile, Payment表中。

当用户调用 SaveCustomer()方法时，这些数据将转到这4个表中。那么如何进行事务处理呢？如果一个表插入失败，那么需要回滚所有内容。例如，当第三个表插入记录时我得到了一个错误，那么也需要回滚前两个表的插入记录。

看我的代码:

public void saveCustomer(){
DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(RetailerOrderKeyActivity.this);
dbAdapter.openDataBase();
ContentValues initialValues = new ContentValues();
initialValues.put("CustomerName",customer.getName());
initialValues.put("Address",customer.getAddress());
initialValues.put("CustomerPID",strPID);
initialValues.put("Date",strDateOnly);
long n = dbAdapter.insertRecordsInDB("Customer", null, initialValues);


}

同样其他的陈述也在那里。

DBAdter 代码是:

public long insertRecordsInDB(String tableName, String nullColumnHack,ContentValues initialValues) {
long n =-1;
try {
myDataBase.beginTransaction();
n = myDataBase.insert(tableName, nullColumnHack, initialValues);


myDataBase.endTransaction();
myDataBase.setTransactionSuccessful();
} catch (Exception e) {
// how to do the rollback
e.printStackTrace();
}


return n;
}

这是完整的密码:

public class DBAdapter extends SQLiteOpenHelper {


private static String DB_PATH = "/data/data/com.my.controller/databases/";
private static final String DB_NAME = "customer";
private SQLiteDatabase myDataBase;
private final Context myContext;
private static DBAdapter mDBConnection;




private DBAdapter(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
DB_PATH = "/data/data/"
+ context.getApplicationContext().getPackageName()
+ "/databases/";
// The Android's default system path of your application database is
// "/data/data/mypackagename/databases/"
}




public static synchronized DBAdapter getDBAdapterInstance(Context context) {
if (mDBConnection == null) {
mDBConnection = new DBAdapter(context);
}
return mDBConnection;
}




public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if (dbExist) {
// do nothing - database already exist
} else {
// By calling following method
// 1) an empty database will be created into the default system path of your application
// 2) than we overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}




private boolean checkDataBase() {
SQLiteDatabase checkDB = null;


try {
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READONLY);


} catch (SQLiteException e) {
// database does't exist yet.
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}




private void copyDataBase() throws IOException {
InputStream myInput = myContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer)) > 0) {
myOutput.write(buffer, 0, length);
}
// Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}


/**
* Open the database
* @throws SQLException
*/
public void openDataBase() throws SQLException {
String myPath = DB_PATH + DB_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
}




@Override
public synchronized void close() {
if (myDataBase != null)
myDataBase.close();
super.close();
}


/**
* Call on creating data base for example for creating tables at run time
*/
@Override
public void onCreate(SQLiteDatabase db) {
}




@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("ALTER TABLE WMPalmUploadControl ADD Testing int");


}


public void upgradeDb(){
onUpgrade(myDataBase, 1, 2);
}


public Cursor selectRecordsFromDB(String tableName, String[] tableColumns,
String whereClase, String whereArgs[], String groupBy,
String having, String orderBy) {
return myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
groupBy, having, orderBy);
}




public ArrayList<ArrayList<String>> selectRecordsFromDBList(String tableName, String[] tableColumns,
String whereClase, String whereArgs[], String groupBy,
String having, String orderBy) {


ArrayList<ArrayList<String>> retList = new ArrayList<ArrayList<String>>();
ArrayList<String> list = new ArrayList<String>();
Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
groupBy, having, orderBy);
if (cursor.moveToFirst()) {
do {
list = new ArrayList<String>();
for(int i=0; i<cursor.getColumnCount(); i++){
list.add( cursor.getString(i) );
}
retList.add(list);
} while (cursor.moveToNext());
}
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
return retList;


}




public long insertRecordsInDB(String tableName, String nullColumnHack,ContentValues initialValues) {
long n =-1;
try {
myDataBase.beginTransaction();
n = myDataBase.insert(tableName, nullColumnHack, initialValues);


myDataBase.endTransaction();
myDataBase.setTransactionSuccessful();
} catch (Exception e) {
// how to do the rollback
e.printStackTrace();
}


return n;
}




public boolean updateRecordInDB(String tableName,
ContentValues initialValues, String whereClause, String whereArgs[]) {
return myDataBase.update(tableName, initialValues, whereClause,
whereArgs) > 0;
}


public int updateRecordsInDB(String tableName,
ContentValues initialValues, String whereClause, String whereArgs[]) {
return myDataBase.update(tableName, initialValues, whereClause, whereArgs);
}




public int deleteRecordInDB(String tableName, String whereClause,
String[] whereArgs) {
return myDataBase.delete(tableName, whereClause, whereArgs);
}




public Cursor selectRecordsFromDB(String query, String[] selectionArgs) {
return myDataBase.rawQuery(query, selectionArgs);
}




public ArrayList<ArrayList<String>> selectRecordsFromDBList(String query, String[] selectionArgs) {
ArrayList<ArrayList<String>> retList = new ArrayList<ArrayList<String>>();
ArrayList<String> list = new ArrayList<String>();
Cursor cursor = myDataBase.rawQuery(query, selectionArgs);
if (cursor.moveToFirst()) {
do {
list = new ArrayList<String>();
for(int i=0; i<cursor.getColumnCount(); i++){
list.add( cursor.getString(i) );
}
retList.add(list);
} while (cursor.moveToNext());
}
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
return retList;
}


}

HTC Desire 中的数据库锁问题。

如果在插入表数据时出现任何问题，我希望回滚。

请帮帮我

谢谢。

我看到了同样的相关问题:

99605 次浏览
小开

you should add endTransaction in your finally, not in your try block 

 finally {
myDataBase.endTransaction();
}

The changes will be rolled back if any transaction is ended without being marked as clean (by calling setTransactionSuccessful). Otherwise they will be committed.

小开

Actually you are doing wrong. You have to set begin transaction if you have multiple records to insert into database or if you have to rollback data from other table if there is a problem in inserting data in one of the database table.

For example

You have two tables

  1. A
  2. B

Now you want to insert data in these two tables but you will have to rollback transaction if you will get any error at the time of inserting data in the tables.

Now you have successfully insert data in table A and now you are trying to insert data in the table B.Now if you get error at the time of inserting data in the table B then you have to delete relevant data from table A that means you have to rollback the transaction.

How you can use database transaction in Android

  1. If you want to start the transaction there is a method beginTransaction()
  2. If you want to commit the transaction there is a method setTransactionSuccessful() which will commit the values in the database
  3. If you had start the transaction you need to close the transaction so there is a method endTransaction() which will end your database transaction

Now there are two main points

  1. If you want to set transaction successful you need to write setTransactionSuccessful() and then endTransaction() after beginTransaction()
  2. If you want to rollback your transaction then you need to endTransaction() without committing the transaction by setTransactionSuccessful().

You can get detailed information about the SQLite database transaction from here

In your case

You can call your saveCustomer() function in try and catch blocks

db.beginTransaction();
try {
saveCustomer();
db.setTransactionSuccessful();
} catch {
//Error in between database transaction
} finally {
db.endTransaction();
}
小开

Insert Record Using Transaction, This is very fast

String sql = "INSERT INTO table (col1, col2) VALUES (?, ?)";
db.beginTransaction();


SQLiteStatement stmt = db.compileStatement(sql);
for (int i = 0; i < values.size(); i++) {
stmt.bindString(1, values.get(i).col1);
stmt.bindString(2, values.get(i).col2);
stmt.execute();
stmt.clearBindings();
}


db.setTransactionSuccessful();
db.endTransaction();

提交答案