JDBC Transaction Management Example

A transaction is a set of SQL operations that need to be either executed all successfully or not at all. Failure to perform even one operation leads to an inconsistent and erroneous database.

 Learn a complete JDBC tutorial at https://www.javaguides.net/p/jdbc-tutorial.html.

JDBC - Transaction Management Example

A transaction is a set of one or more statements that are 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.

Technologies used

  • JDK - 1.8 or later
  • MySQL - 5.7.12
  • IDE - Eclipse Neon
  • JDBC API - 4.2
The following steps are required for transaction management in the JDBC API.
  1. Disable the auto-commit mode bypassing the false value to the setAutoCommit() method.
  2. Call the commit() method to commit the transaction if all statements are executed successfully.
  3. Call the rollback() method to cancel the transaction if any one of the statements fails. Here is an example to demonstrate the above steps.
package com.javaguides.jdbc.batch;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC Transaction Management Example
 * @author Ramesh Fadatare
 *
 */
public class JDBCTransactionExample {
    private static final String INSERT_USERS_SQL = "INSERT INTO users " +
        "  (id, name, email, country, password) VALUES " + " (?, ?, ?, ?, ?);";
    private static final String UPDATE_USERS_SQL = "update users set name = ? where id = ?;";

    private static final String jdbcUrl = "jdbc:mysql://localhost:3306/mysql_database?useSSL=false";
    private static final String username = "root";
    private static final String password = "root";

    public static void main(String[] args) {

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

            // STEP 1 - Disable auto commit mode
            conn.setAutoCommit(false);

            try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_USERS_SQL); PreparedStatement updateStmt = conn.prepareStatement(UPDATE_USERS_SQL);) {

                // Create insert statement
                insertStmt.setInt(1, 200);
                insertStmt.setString(2, "Tony");
                insertStmt.setString(3, "tony123@gmail.com");
                insertStmt.setString(4, "US");
                insertStmt.setString(5, "secret");
                insertStmt.executeUpdate();

                // Create update statement
                updateStmt.setString(1, "Ram");
                updateStmt.setInt(2, 200);
                updateStmt.executeUpdate();

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

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
Transaction is commited successfully.

References


Comments

Post a Comment