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 the 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 the method Commit also known as Java SQL transaction. In Java JDBC transactions we can perform several related SQL statements.

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 the method Commit of connection object or declined for Rollback method. JDBC starts transactions right after set auto-commit to false and SQL queries are prepared.

In this particular example, we try to group two SQL queries for employee updates in one transaction. At first, we provide salary and then we update employee work experience in years. Both queries are completed with the method Commit. For unexpected exceptions, we try to roll back the transaction.

JDBC Transaction Isolation Levels

Sometimes transactions help to prevent the integrity of data in tables. In some cases, we can read and then update/insert new data into DB. These operations for reading data can be performed with some isolation levels. See please the table below. The main difference is that you can read committed or not committed data it can be TRANSACTION_READ_COMMITTED or TRANSACTION_READ_UNCOMMITTED.

Additionally, repeatable reads mean the repeatable operation of reading data. It can be several selects of data where the middle operation can be an update of data. Each time of new selection of data must be refreshed.

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