Transactions with MariaDB Connector/J

Overview

Java developers can use MariaDB Connector/J to perform basic DML (Data Manipulation Language) operations inside a transaction with MariaDB database products.

Transactions

A database transaction is a single unit of logic. A transaction may consist of one or more database operations. Transactions are useful and sometimes essential in several types of data operations.

For example, many applications require that a set of SQL statements either complete, or fail, as a single unit. The common characteristics of transactions are atomicity, consistency, isolation, and durability, what is termed as ACID (atomic, consistent, isolated, durable) transactions.

MariaDB transactions are ACID compliant.

Auto-Commit Behavior

When auto-commit is enabled, each SQL statement is executed in its own transaction.

Enable Auto-Commit

MariaDB Connector/J 3.x does not enable or disable auto-commit by default. Connections made by MariaDB Connector/J 3.x use the value of the server's autocommit system variable by default. Since MariaDB Server sets autocommit=ON by default, auto-commit is enabled by default in most environments. If autocommit=OFF has been set on the server, the application must enable auto-commit by setting the autocommit option or calling the Connection.setAutoCommit() method as shown in the examples below.

MariaDB Connector/J 2.7 and earlier enable auto-commit by default, so it does not need to be manually enabled.

For new connections, auto-commit can be enabled by adding autocommit=true to the connection string:

jdbc:mariadb://HOST/DATABASE?autocommit=true

For an existing connection, auto-commit can be enabled by calling Connection.setAutoCommit():

conn.setAutoCommit(true);

Confirm Auto-Commit

Confirm that auto-commit is enabled by calling Connection.getAutoCommit():

if (conn.getAutoCommit()) {
   // do something
}

Multi-Statement Transactions

MariaDB Connector/J supports multi-statement transactions when auto-commit is disabled.

Disable auto-commit by calling Connection.setAutoCommit():

conn.setAutoCommit(false);

When auto-commit is disabled, a new transaction is automatically started when the current transaction is manually committed or rolled back. This means your application does not need to manually start each new transaction with BEGIN or START TRANSACTIONSTART TRANSACTION.

The transaction can be manually managed by performing the following operations:

Code Example: Transactions

The following example program updates the test.accounts table created in the example setup.

import java.sql.*;

public class App {
   public static void main(String[] argv) {
      try (Connection conn = DriverManager.getConnection(
            "jdbc:mariadb://192.0.2.1:3306?user=db_user&password=db_user_password"
      )) {
         conn.setAutoCommit(false);

         try {
            try (PreparedStatement prep = conn.prepareStatement(
                  "INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)")) {
               prep.setString(1, "John");
               prep.setString(2, "Smith");
               prep.setString(3, "john.smith@example.com");
               prep.setDouble(4, 900.00);
               prep.executeQuery();
            }

            try (PreparedStatement prep = conn.prepareStatement(
                  "UPDATE test.accounts SET amount = ? WHERE email = ?")) {
               prep.setDouble(1, 1000.00);
               prep.setString(2, "john.smith@example.com");
               prep.executeQuery();
            }

            conn.commit();
         } catch (SQLException e) {
            e.printStackTrace();
            conn.rollback();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Confirm that the test.accounts table was properly updated using MariaDB Client to execute a SELECTSELECT statement:

SELECT * FROM accounts;

Example output:

+----+------------+-----------+------------------------+---------+
| id | first_name | last_name | email                  | amount  |
+----+------------+-----------+------------------------+---------+
|  1 | John       | Smith     | john.smith@example.com | 1000.00 |
+----+------------+-----------+------------------------+---------+

Connector/J 3.0

In MariaDB Connector/J 3.0, TLS is enabled for connections to SkySQL using the sslMode parameter.

import java.sql.*;

public class App {
   public static void main(String[] argv) {
      try (Connection conn = DriverManager.getConnection(
            "jdbc:mariadb://HOST:PORT?user=db_user&password=db_user_password" +
            "&sslMode=verify-full&serverSslCert=/path/to/skysql_chain.pem"
      )) {
         conn.setAutoCommit(false);

         try {
            try (PreparedStatement prep = conn.prepareStatement(
                  "INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)")) {
               prep.setString(1, "John");
               prep.setString(2, "Smith");
               prep.setString(3, "john.smith@example.com");
               prep.setDouble(4, 900.00);
               prep.executeQuery();
            }

            try (PreparedStatement prep = conn.prepareStatement(
                  "UPDATE test.accounts SET amount = ? WHERE email = ?")) {
               prep.setDouble(1, 1000.00);
               prep.setString(2, "john.smith@example.com");
               prep.executeQuery();
            }

            conn.commit();
         } catch (SQLException e) {
            e.printStackTrace();
            conn.rollback();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Confirm that the test.accounts table was properly updated using MariaDB Client to execute a SELECT statement:

SELECT * FROM accounts;

Example output:

+----+------------+-----------+------------------------+---------+
| id | first_name | last_name | email                  | amount  |
+----+------------+-----------+------------------------+---------+
|  1 | John       | Smith     | john.smith@example.com | 1000.00 |
+----+------------+-----------+------------------------+---------+

Connector/J 2.7

In MariaDB Connector/J 2.7 and before, TLS is enabled for connections to SkySQL using the useSsl parameter.

import java.sql.*;

public class App {
   public static void main(String[] argv) {
      try (Connection conn = DriverManager.getConnection(
            "jdbc:mariadb://HOST:PORT?user=db_user&password=db_user_password" +
            "&useSsl=true&serverSslCert=/path/to/skysql_chain.pem"
      )) {
         conn.setAutoCommit(false);

         try {
            try (PreparedStatement prep = conn.prepareStatement(
                  "INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)")) {
               prep.setString(1, "John");
               prep.setString(2, "Smith");
               prep.setString(3, "john.smith@example.com");
               prep.setDouble(4, 900.00);
               prep.executeQuery();
            }

            try (PreparedStatement prep = conn.prepareStatement(
                  "UPDATE test.accounts SET amount = ? WHERE email = ?")) {
               prep.setDouble(1, 1000.00);
               prep.setString(2, "john.smith@example.com");
               prep.executeQuery();
            }

            conn.commit();
         } catch (SQLException e) {
            e.printStackTrace();
            conn.rollback();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

Confirm that the test.accounts table was properly updated using MariaDB Client to execute a SELECT statement:

SELECT * FROM accounts;

Example output:

+----+------------+-----------+------------------------+---------+
| id | first_name | last_name | email                  | amount  |
+----+------------+-----------+------------------------+---------+
|  1 | John       | Smith     | john.smith@example.com | 1000.00 |
+----+------------+-----------+------------------------+---------+