JDBC Transaction Processing

[jsulman]

We currently have a class that preforms a number of SQL statements that delete records from variouse tables. What I cannot figure out is how to rollback the changes if an exception error occurs in any one of them. Once we are in the catch block the Connection object looses scope so we cannot access it to do the rollback.

Any ideas?

Jeff Sulman

 

[meadandale]

try {

Connection c = ConnectionManager.getConnection(...);
try {
// do something with connection
} catch (SQLException e) {
try {
c.rollback();
} catch (SQLException sqle) {
// Now we are really bummin
sqle.printStackTrace();
}
}

// close the connection and cleanup
} catch (Exception e) {
e.printStackTrace();
}

 

[wushutwist]

This is a common mistake. Like meadandale pointed out you should declare your Connection object outside the try-catch block. Another tip is use finally to close the Connection. Many people forget this and their Connection goes unclosed if an exception is caught. I prefer not to nest my try-catch blocks if possible so my example looks like:

Connection conn = null;
try {
   conn = ConnectionManager.getConnection(...);
   /* Do your stuff */
}
catch (SQLException sqle) {
   /* Rollback for Transaction */
   try {
      c.rollback();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
   sqle.printStackTrace();
}
catch (Exception e) {
   /* Something else happened */
   e.printStackTrace();
}
finally {
   /* Insure Connection is closed */
   try { if conn != null {conn.close();} }
   catch (Exception e) { e.printStackTrace(); }
}

Wushutwist

 

[illasreenu]

In order to be able to do a roll back you will have to set autocommit to false before you start your Transacations.

Ex. con.setAutoCommit(false);

Sreeni