Batch Operations with MariaDB Connector/J

Overview

Java developers can use MariaDB Connector/J to perform batch operations with MariaDB database products.

Batch Operations

SQL statements and prepared statements can involve multiple calls to the database, which can incur the performance overhead of multiple round trips between the application and the database.

Batch operations group multiple SQL commands for the application to send to the database as a single call. Using batch operations can improve performance by reducing the number of calls and subsequently the number of round trips to the database an SQL statement requires.

By default, MariaDB Connector/J does not batch SQL statements, instead running statements one query at a time.

Create a Batch

A batch can be created by calling Statement.addBatch(String sql) for each SQL command to be added to a batch:

Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO ... VALUES (...)");
stmt.addBatch("INSERT INTO ... VALUES (...)");
stmt.addBatch("UPDATE .... SET ... WHERE ...");

When using a prepared statement, a batch can be created by calling PreparedStatement.addBatch() for each set of parameters:

PreparedStatement ps = conn.prepareStatement(...);

   stmnt.setString(1, ...);
   stmnt.setString(2, ...);
   stmnt.setString(3, ...);

   ps.addBatch();

   stmnt.setString(1, ...);
   stmnt.setString(2, ...);
   stmnt.setString(3, ...);

   ps.addBatch();

Run a Batch

A batch can be run by calling Statement.executeBatch():

stmt.executeBatch();

ps.executeBatch();

Clear a Batch

A batch can be cleared, or emptied, by calling Statement.clearBatch():

stmt.clearBatch();

ps.clearBatch();

Code Example: Batch Queries

The following example program adds data to the test.contacts table created in the example setup using a batched prepared statement.

import java.sql.*;
import java.util.Properties;

public class App {

   public static void main(String[] argv) {
      Properties connConfig = new Properties();
      connConfig.setProperty("user", "db_user");
      connConfig.setProperty("password", "db_user_password");

      try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", connConfig)) {
         try (PreparedStatement stmnt = conn.prepareStatement(
                         "INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
            stmnt.setString(1, "John");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "john.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Jon");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "jon.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Johnny");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "johnny.smith@example.com");

            stmnt.addBatch();

            stmnt.executeBatch();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

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

Example output:

SELECT * FROM contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+

Connector/J 3.0

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

import java.sql.*;
import java.util.Properties;

public class App {

   public static void main(String[] argv) {
      Properties connConfig = new Properties();
      connConfig.setProperty("user", "db_user");
      connConfig.setProperty("password", "db_user_password");
      connConfig.setProperty("sslMode", "verify-full");
      connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

      try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", connConfig)) {
         try (PreparedStatement stmnt = conn.prepareStatement(
                         "INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
            stmnt.setString(1, "John");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "john.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Jon");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "jon.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Johnny");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "johnny.smith@example.com");

            stmnt.addBatch();

            stmnt.executeBatch();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

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

Example output:

SELECT * FROM contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+

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.*;
import java.util.Properties;

public class App {

   public static void main(String[] argv) {
      Properties connConfig = new Properties();
      connConfig.setProperty("user", "db_user");
      connConfig.setProperty("password", "db_user_password");
      connConfig.setProperty("useSsl", "true");
      connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

      try (Connection conn = DriverManager.getConnection("jdbc:mariadb://HOST:PORT", connConfig)) {
         try (PreparedStatement stmnt = conn.prepareStatement(
                         "INSERT INTO test.contacts (first_name, last_name, email) VALUES (?, ?, ?)")) {
            stmnt.setString(1, "John");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "john.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Jon");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "jon.smith@example.com");

            stmnt.addBatch();

            stmnt.setString(1, "Johnny");
            stmnt.setString(2, "Smith");
            stmnt.setString(3, "johnny.smith@example.com");

            stmnt.addBatch();

            stmnt.executeBatch();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

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

Example output:

SELECT * FROM contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+
|  2 | Jon        | Smith     | jon.smith@example.com  |
+----+------------+-----------+------------------------+
|  3 | Johnny     | Smith     |johnny.smith@example.com|
+----+------------+-----------+------------------------+