JDBC - Transaction management example

Posted on February 20, 2017

Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

A transaction is a set of one or more statements that is executed as a unit. Either all statements will be executed successfully or none of them.

In JDBC API, the Connection interface provides the setAutoCommit() , commit()  and rollback() methods to perform transaction management.

The following steps are required for transaction management in JDBC API.

  • Disable auto-commit mode by passing the false value to the setAutoCommit() method.
  • Call the commit() method to commit the transaction if all statements are executed successfully.
  • Call the rollback() method to cancel  the transaction if any one of statements fails.

Here is an example to demonstrate the above steps.


package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

 * @author imssbora
public class JDBCTransactionExample {
   private static final String INSERT_SQL = "INSERT INTO employee " 
         + "(EMP_ID, NAME, DOB) VALUES (?,?,?)";
   private static final String UPDATE_SQL = "UPDATE employee SET EMAIL=?, DEPT=? " 
         + " WHERE EMP_ID=?";

   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

         // STEP 1 - Disable auto commit mode

         try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_SQL); 
               PreparedStatement updateStmt = conn.prepareStatement(UPDATE_SQL);) {

            // Create insert statement
            insertStmt.setInt(1, 1);
            insertStmt.setString(2, "Michael");
            insertStmt.setDate(3, new Date(dateFormat.parse("1995-07-01").getTime()));

            // Create update statement
            updateStmt.setString(1, "[email protected]");
            updateStmt.setString(2, "HR Department");
            updateStmt.setInt(3, 1);

            // STEP 2 - Commit insert and update statement
            System.out.println("Transaction is commited successfully.");
         } catch (SQLException | ParseException e) {
            if (conn != null) {
               try {
                  //STEP 3 - Roll back transaction
                  System.out.println("Transaction is being rolled back.");
               } catch (Exception ex) {
      } catch (SQLException e) {


Transaction is commited successfully.