You might want to know more about JDBC than is covered by Chapter 10 of our book, OCP: Oracle Certified Professional Java SE 8 Programmer II Study Guide: Exam 1Z0-809. This blog post teaches you about autocommit and savepoints and assumes you have completed reading chapter 10 of our OCP 8 book and are looking to learn more. (Note: we’ve heard rumors this topic is in fact on the exam. It wasn’t when we took the beta but was on the Java 7 exam. So either the questions are left over or it was added back.)

In the book, you saw how to run one statement at a time and have the database automatically see the changes right away. This is called automatic commit or auto-commit for short. A commit updates the database. JDBC provides options for more control by running your code in transactions. A transaction allows a group of statements to go together.

Rollback/Commit

By default, auto-commit is set to true which means each statement is committed in the database as soon as it is run. You can set it to false so statements and prepared statements do not automatically commit data.

The following example gives that control. If anything goes wrong update either of the rows, the entire transaction is rolled back and the database doesn’t change either row. If both are successful, the database sees both.

conn.setAutoCommit(false);
String sql = "update species name = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
try {
  ps.setString(1, "Elephant");
  ps.setInt(2, 1);
  ps.executeUpdate();
  ps.setString(1, "Monkey");
  ps.setInt(2, 4);
  ps.executeUpdate();
  
  conn.commit();      // send data to database
} catch (SQLException e) {
  conn.rollback();    // don't update database
}

Let’s rewrite this example to focus more on the commit/rollback code:

conn.setAutoCommit(false);
String sql = "update species name = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
try {
  updateRecord(1);
  updateRecord(2);
  conn.commit(); // send data to database 
} catch (SQLException e) {
  conn.rollback(); // don't update database 
} 

Setting a Rollback point

JDBC has a type called a Savepoint which is like putting a bookmark in a book so you can easily get back there.

conn.setAutoCommit(false);
String sql = "update species name = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
try {
  updateRecord(1);
  Savepoint sp = conn.setSavepoint();
  updateRecord(2);
  updateRecord(3);
  // oops - let's go back like this didn't happen
  conn.rollback(sp);
  // proceed from there
  updateRecord(4);
  conn.commit(); // send data to database 
} catch (SQLException e) { 
  conn.rollback(); // don't update database 
}