title: Android Database Transaction
body: <p>I have created a database. I want to do the Transaction. <code>SaveCustomer()</code> contains more than one statement to insert records into <code>Customer, CustomerControl, Profile, Payment</code> table at that time.</p>
  
  <p>When a user calls  <code>SaveCustomer()</code> method then that data will go to these 4 tables.so How can I do the transaction? If one table inserts failed then need to roll back everything. For example, when 3rd table inserts the record I got an error, then need to roll back previous two table's insert records also.</p>
  
  <p>See my code:</p>
  
  <pre><code>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);
  
  }
  </code></pre>
  
  <p>Likewise  other statement also there.</p>
  
  <p>DBAdpter code is :</p>
  
  <pre><code>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;
  }
  </code></pre>
  
  <p>This is the full code:</p>
  
  <pre><code>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)) &gt; 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&lt;ArrayList&lt;String&gt;&gt; selectRecordsFromDBList(String tableName, String[] tableColumns,
              String whereClase, String whereArgs[], String groupBy,
              String having, String orderBy) {        
  
          ArrayList&lt;ArrayList&lt;String&gt;&gt; retList = new ArrayList&lt;ArrayList&lt;String&gt;&gt;();
            ArrayList&lt;String&gt; list = new ArrayList&lt;String&gt;();
            Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
                      groupBy, having, orderBy);        
            if (cursor.moveToFirst()) {
               do {
                   list = new ArrayList&lt;String&gt;();
                   for(int i=0; i&lt;cursor.getColumnCount(); i++){                   
                       list.add( cursor.getString(i) );
                   }   
                   retList.add(list);
               } while (cursor.moveToNext());
            }
            if (cursor != null &amp;&amp; !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) &gt; 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&lt;ArrayList&lt;String&gt;&gt; selectRecordsFromDBList(String query, String[] selectionArgs) {       
            ArrayList&lt;ArrayList&lt;String&gt;&gt; retList = new ArrayList&lt;ArrayList&lt;String&gt;&gt;();
            ArrayList&lt;String&gt; list = new ArrayList&lt;String&gt;();
            Cursor cursor = myDataBase.rawQuery(query, selectionArgs);            
            if (cursor.moveToFirst()) {
               do {
                   list = new ArrayList&lt;String&gt;();
                   for(int i=0; i&lt;cursor.getColumnCount(); i++){                   
                       list.add( cursor.getString(i) );
                   }   
                   retList.add(list);
               } while (cursor.moveToNext());
            }
            if (cursor != null &amp;&amp; !cursor.isClosed()) {
               cursor.close();
            }
            return retList;
         }
  
  }
  </code></pre>
  
  <p><a href="https://stackoverflow.com/questions/6951506/database-lock-problem-in-htc-desire/6955195#6955195">database lock issue in HTC Desire</a>.</p>
  
  <p>I want to roll back if there any issues occurred when inserting the table data.</p>
  
  <p>Please help me</p>
  
  <p>Thanks.</p>
  
  <p>I looked this same related question : </p>