Managing commit over multiple JDBC statements

One thing I found a bother to deal with in JDBC is the commit.

Basic commit management

If you want to execute a serie of SQL commands and only if all of them succeed commit to the database, you must do the following :

• make sure the connection's autocommit is off
• if not, turn it off
• execute your SQL commands
• if all succeeded, commit, otherwise rollback
• turn autocommit on if you had to turn it off

These aren't very hard things to do, but it takes several lines of code and variables to do it properly. This is quite a burden to do it everywhere you have transactionnal SQL commands so I wrote the TransactionHelper class.

This class is pretty easy to use:

• create a new instance and give it your connection
• tell the object your transaction starts
• tell the object your transaction succeeded (no need to tell it failed)
• tell the object to end the transaction (ie. commit or rollback and restore the autocommit if required)

Since the last step must be completed no matter what, this object must be used with a try catch bloc.

TransactionHelper transactionHelper = new TransactionHelper(conn, commit);
try {
  transactionHelper.startTransaction();
  [... sql code here...]
  if ([transaction is successful]) {
    transactionHelper.transactionOk();
  }
}
finally {
  try {
    transactionHelper.endTransaction();
  }
  catch(SQLException e) {
    // log here that the transaction could not be commited/rollbacked
  }
}                                                                            

Methods and commit management

Say you have several methods each containing SQL commands working as a transaction, how do you manage a transaction that would use two or more of these methods ?

My solution to this problem is the following. Each method containing SQL commands takes a boolean parameter called "commit" and returns a value (a boolean at least) indicating if the method was successful.

The meaning of the "commit" parameter is :

• true: the method manages the commit, performing a commit or a rollback at the end of the transaction
• false: the method must not manage the commit, it is dealed with by the calling method

The TransactionHelper supports this "commit" parameter. The class's constructor takes it as a parameter to provide the expected behavior.

public boolean parentMethod(Connection conn) throws SQLException {
  // commit is true, this instance of TransactionHelper controls the commit
  TransactionHelper transactionHelper = new TransactionHelper(conn, true); 
  try {
    transactionHelper.startTransaction();
    boolean success = subMethod1(conn, false); // commit is false
    if (!success) {
      return false;
    }
    success = subMethod2(conn, false); // commit is false
    if (!success) {
      return false;
    }
    transactionHelper.transactionOk();
    return true;
  }
  finally {
    try {
      transactionHelper.endTransaction();
    }
    catch(SQLException e) {
      // log here that the transaction could not be commited/rollbacked
    }
  }
}

public boolean subMethod1(Connection conn,
                          boolean commit) throws SQLException {
  TransactionHelper transactionHelper = new TransactionHelper(conn, commit);
  try {
    transactionHelper.startTransaction();
    //[... sql code here...]
    if ([transaction is successful]) {
      transactionHelper.transactionOk();
      return true;
    }
    return false;
  }
  finally {
    try {
      transactionHelper.endTransaction();
    }
    catch(SQLException e) {
      // log here that the transaction could not be commited/rollbacked
    }
  }
}

public boolean subMethod2(Connection conn,
                          boolean commit) throws SQLException {
  // code similar of the code of subMethod1 here
  return true;
}