JDBC Transaction Management in Java

Java has a lot of units dedicated to interacting with databases. One of them is the JDBC unit, which means an abbreviation of Java Database Connection.

The main purpose of JDBC is to provide a connection to the database via a specific driver with a special set of SQL statements and commands. By default, each operation with a database has a unique JDBC transaction. It means that each SQL operation will be called with a command Commit.

JDBC Auto-Commit. How to Disable?

As we discussed before that JDBC auto-commit mode is set by default. But it’s possible to disable this mode when we need to perform several SQL statements as a part of one operation. To do that see please code below. Where variable connection is an active connection to the database.

connection.setAutoCommit(false);

JDBC Transaction Example

After auto-commit is disabled you need to call metod Commit also known as Java SQL transaction. In Java JDBC transaction we can perform several related SQL statments.

public void updateEmployeeSalaryAndExperince(HashMap<String, Integer> employees) throws SQLException {
    String updateSalaryString =
      "update EMPLOYEE set SALARY = ? where NAME = ?";
    String updateExperienceStatment =
      "update EMPLOYEE set EXP_YEARS = EXP_YEARS + ? where NAME = ?";

    try (PreparedStatement updateSalary = con.prepareStatement(updateSalaryString);
         PreparedStatement updateExperience = con.prepareStatement(updateExperienceStatment))
    
    {
      connection.setAutoCommit(false);

      for (Map.Entry<String, Integer> e : employees.entrySet()) {
        updateSalary.setInt(1, e.getValue().intValue());
        updateSalary.setString(2, e.getKey());
        updateSalary.executeUpdate();

        updateExperience.setInt(1, e.getValue().intValue());
        updateExperience.setString(2, e.getKey());
        updateExperience.executeUpdate();
        connection.commit();
      }
    } catch (SQLException e) {
      System.err.print(e.getMessage());
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          connection.rollback();
        } catch (SQLException excep) {
          System.err.print(excep);
        }
      }
    }
  }

Java database transaction is completed when we call method Commit of connection object or declined for Rollback method. JDBC starts transaction right after set auto-commit to false and SQL queries are prepared.

In this particular example we try to group two SQL queiries for employee update in one transaction. At first we provide sallary and than we update employee work expiriense in years. Both queries are completed with method Commit. For unexpected exceptions we try to rollback the transaction.

JDBC Transaction Isolation Levels

Some times transactions help to prevent integrity of data in tables. In some cases we can read and than update/insert new data to DB. These operation for reading of data can be performed with some isolation levels. See please the table below. Main difference that you can read commited or not commited data it can be TRANSACTION_READ_COMMITTED or TRANSACTION_READ_UNCOMMITTED.

Additionally, repeateble reads means repetable oparation of reading data. It can be several selects of data where at the middle operation can be update of data. Each time of new select of data must be refreshed.

Phantom operations it’s also select operation where some amount of selected rows may change due to insert/delete oparation in the middle.

Isolation LevelTransactionsDirty ReadsNon-Repeatable ReadsPhantom Reads
TRANSACTION_NONE
TRANSACTION_READ_COMMITTED+PreventedAllowedAllowed
TRANSACTION_READ_UNCOMMITTED+AllowedAllowedAllowed
TRANSACTION_REPEATABLE_READ+PreventedPreventedAllowed
TRANSACTION_SERIALIZABLE+PreventedPreventedPrevented

Related posts

Leave a Comment