try { // begin the transaction: connection.setAutoCommit(false); // execute statement #1 // execute statement #2 // execute statement #3 // ... // commit the transaction connection.commit(); } catch (SQLException ex) { // abort the transaction connection.rollback(); } finally { // close statements connection.setAutoCommit(true); }Let’s explore this workflow in details.
connection.setAutoCommit(false);
connection.commit();So a transaction begins right after the auto commit is disabled and ends right after the connection is committed. Remember to execute SQL statements between these calls to ensure they are in the same transaction.
connection.rollback();Any changes made by the successful statements are discarded and the database is rolled back to the previous state before the transaction.
connection.setAutoCommit(true);In the default state (auto commit is enabled), each SQL is treated as a transaction and we don’t need to call the commit() method manually.
public void saveOrder(int productId, Date orderDate, float amount, int reportMonth) { PreparedStatement orderStatement = null; PreparedStatement saleStatement = null; try { conn.setAutoCommit(false); String sqlSaveOrder = "insert into orders (product_id, order_date, amount)"; sqlSaveOrder += " values (?, ?, ?)"; String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?"; sqlUpdateTotal += " where product_id = ? and report_month = ?"; orderStatement = conn.prepareStatement(sqlSaveOrder); saleStatement = conn.prepareStatement(sqlUpdateTotal); orderStatement.setInt(1, productId); orderStatement.setDate(2, orderDate); orderStatement.setFloat(3, amount); saleStatement.setFloat(1, amount); saleStatement.setInt(2, productId); saleStatement.setInt(3, reportMonth); orderStatement.executeUpdate(); saleStatement.executeUpdate(); conn.commit(); } catch (SQLException ex) { if (conn != null) { try { conn.rollback(); System.out.println("Rolled back."); } catch (SQLException exrb) { exrb.printStackTrace(); } } } finally { try { if (orderStatement != null ) { orderStatement.close(); } if (saleStatement != null ) { saleStatement.close(); } conn.setAutoCommit(true); } catch (SQLException excs) { excs.printStackTrace(); } } }And here is the full source code of a test program:
import java.sql.*; /** * JDBC Transaction Demo Program * * @author www.codejava.net */ public class JDBCTransactionsDemo { private String dbURL = "jdbc:mysql://localhost:3306/sales"; private String user = "root"; private String password = "P@ssw0rd"; private Connection conn; public void connect() { try { conn = DriverManager.getConnection(dbURL, user, password); System.out.println("Connected."); } catch (SQLException ex) { ex.printStackTrace(); } } public void disconnect() { try { conn.close(); System.out.println("Closed."); } catch (SQLException ex) { ex.printStackTrace(); } } public void saveOrder(int productId, Date orderDate, float amount, int reportMonth) { PreparedStatement orderStatement = null; PreparedStatement saleStatement = null; try { conn.setAutoCommit(false); String sqlSaveOrder = "insert into orders (product_id, order_date, amount)"; sqlSaveOrder += " values (?, ?, ?)"; String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?"; sqlUpdateTotal += " where product_id = ? and report_month = ?"; orderStatement = conn.prepareStatement(sqlSaveOrder); saleStatement = conn.prepareStatement(sqlUpdateTotal); orderStatement.setInt(1, productId); orderStatement.setDate(2, orderDate); orderStatement.setFloat(3, amount); saleStatement.setFloat(1, amount); saleStatement.setInt(2, productId); saleStatement.setInt(3, reportMonth); orderStatement.executeUpdate(); saleStatement.executeUpdate(); conn.commit(); } catch (SQLException ex) { if (conn != null) { try { conn.rollback(); System.out.println("Rolled back."); } catch (SQLException exrb) { exrb.printStackTrace(); } } } finally { try { if (orderStatement != null ) { orderStatement.close(); } if (saleStatement != null ) { saleStatement.close(); } conn.setAutoCommit(true); } catch (SQLException excs) { excs.printStackTrace(); } } } public static void main(String[] args) { JDBCTransactionsDemo demo = new JDBCTransactionsDemo(); int productId = 1; int reportMonth = 7; Date date = new Date(System.currentTimeMillis()); float amount = 580; demo.connect(); demo.saveOrder(productId, date, amount, reportMonth); demo.disconnect(); } }Here, you understand how the setAutoCommit(), commit() and rollback() methods are used in real code.
connection.rollback(savepoint)
This allows us to undo only changes after the save point in case something wrong happen. The changes before the save point are still committed. The following workflow helps you understand how save points are used in a transaction:try { // begin the transaction: connection.setAutoCommit(false); // execute statement #1 // execute statement #2 // statements #1 & #2 are executed successfully till this point: Savepoint savepoint = connection.setSavepoint(); // execute statement #3 // execute statement #4 if (/* something wrong */) { // roll back the transaction to the savepoint: connection.rollback(savepoint); } // execute statement #5 // ... // commit the transaction connection.commit(); } catch (SQLException ex) { // abort the transaction connection.rollback(); } finally { // close statements connection.setAutoCommit(true); }Now, let’s see a real code example. In the following program, the transaction consists of the following statement:
- insert a new product to the table products.
- insert a new order to the table orders.
- update total amount in the monthly sales.
In case the amount of the new order cannot help monthly sales > 10,000, the transaction is rolled back to the point where the new product was inserted.Here’s the code of the program that shows how to use save point in a transaction with JDBC:import java.sql.*; /** * JDBC Transaction with Save Point Demo Program * * @author www.codejava.net */ public class JDBCTransactionSavePointDemo { private String dbURL = "jdbc:mysql://localhost:3306/sales"; private String user = "root"; private String password = "P@ssw0rd"; private Connection conn; public void connect() { try { conn = DriverManager.getConnection(dbURL, user, password); System.out.println("Connected."); } catch (SQLException ex) { ex.printStackTrace(); } } public void disconnect() { try { conn.close(); System.out.println("Closed."); } catch (SQLException ex) { ex.printStackTrace(); } } public void saveOrder(String newProductName, float newProductPrice, int productId, Date orderDate, float orderAmount, int reportMonth) { PreparedStatement productStatement = null; PreparedStatement orderStatement = null; PreparedStatement saleStatement = null; PreparedStatement getTotalStatement = null; try { conn.setAutoCommit(false); String sqlSaveProduct = "insert into products (product_name, price)"; sqlSaveProduct += " values (?, ?)"; productStatement = conn.prepareStatement(sqlSaveProduct); productStatement.setString(1, newProductName); productStatement.setFloat(2, newProductPrice); productStatement.executeUpdate(); Savepoint savepoint = conn.setSavepoint(); String sqlSaveOrder = "insert into orders (product_id, order_date, amount)"; sqlSaveOrder += " values (?, ?, ?)"; orderStatement = conn.prepareStatement(sqlSaveOrder); orderStatement.setInt(1, productId); orderStatement.setDate(2, orderDate); orderStatement.setFloat(3, orderAmount); orderStatement.executeUpdate(); String sqlGetTotal = "select total_amount from monthly_sales"; sqlGetTotal += " where product_id = ? and report_month = ?"; getTotalStatement = conn.prepareStatement(sqlGetTotal); getTotalStatement.setInt(1, productId); getTotalStatement.setInt(2, reportMonth); ResultSet rs = getTotalStatement.executeQuery(); rs.next(); float totalAmount = rs.getFloat("total_amount"); rs.close(); if (totalAmount + orderAmount < 10000) { conn.rollback(savepoint); } String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?"; sqlUpdateTotal += " where product_id = ? and report_month = ?"; saleStatement = conn.prepareStatement(sqlUpdateTotal); saleStatement.setFloat(1, orderAmount); saleStatement.setInt(2, productId); saleStatement.setInt(3, reportMonth); saleStatement.executeUpdate(); conn.commit(); } catch (SQLException ex) { if (conn != null) { try { conn.rollback(); System.out.println("Rolled back."); } catch (SQLException exrb) { exrb.printStackTrace(); } } } finally { try { if (productStatement != null ) { productStatement.close(); } if (orderStatement != null ) { orderStatement.close(); } if (saleStatement != null ) { saleStatement.close(); } if (getTotalStatement != null ) { getTotalStatement.close(); } conn.setAutoCommit(true); } catch (SQLException excs) { excs.printStackTrace(); } } } public static void main(String[] args) { JDBCTransactionSavePointDemo demo = new JDBCTransactionSavePointDemo(); String newProductName = "iPod"; float newProductPrice = 399; int productId = 1; int reportMonth = 7; Date date = new Date(System.currentTimeMillis()); float orderAmount = 580; demo.connect(); demo.saveOrder(newProductName, newProductPrice, productId, date, orderAmount, reportMonth); demo.disconnect(); } }