Batch Operations with MariaDB Connector/J

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Connector/J

Topics on this page:

Overview
Batch Operations
Create a Batch
Run a Batch
Clear a Batch
Related Connection Options
Code Example: Batch Queries
- Connector/J 3.0
- Connector/J 2.7

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();

Related Connection Options

The following connection options/parameters are related to batch operations:

Option Name	Description	Data Type	Default Value	Version Added	Version Removed
`useBulkStmts`	Use dedicated `COM_STMT_BULK_EXECUTE` protocol for batch insert when possible. Batches without `Statement.RETURN_GENERATED_KEYS` and streams to have faster batch. Use for optimistic update, but cannot confirm if update succeeded or not.	`boolean`	`false`	2.1.0
`useBulkStmtsForInserts`	Use bulk batching exclusively for `INSERT` statements. If `useBulkStmts` is enabled, it supersedes the setting of `useBulkStmtsForInserts`.	`boolean`	`true`	3.2.0
`continueBatchOnError`	Permits batch to continue when an exception occurs. When executing a batch and an error occurs, must the batch continue and finish remaining batch before throwing exception.	`boolean`	`true`	1.4.0	3.0.3
`rewriteBatchedStatements`	Rewrites statements for `INSERT` into multi-valued inserts when `executeBatch()` is called, for optimization that results in faster queries. For insert queries only, rewrites batched statement to execute in a single `executeQuery`. `INSERT INTO ab (i) VALUES (1)` with first batch, and `INSERT INTO ab (i) VALUES (2)` with second batch will be rewritten as `INSERT INTO ab (i) values (1) (2)`. If query cannot be rewritten in "multi-values", rewrite will use multi queries: `INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?` with values `[1,2]` and `[2,3]` will be rewritten as `INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4` When this option is active, the `useServerPrepStmts` option is set to `false` Beginning with driver version 1.5.9, using option `rewriteBatchedStatements` permits rewrite when query has column/table that contains 'select' keyword.	`boolean`	`false`	1.1.8	3.0.3
`useBatchMultiSend`	`PreparedStatement.executeBatch(`) will send many queries corresponding to option `useBatchMultiSendNumber` value, before reading result packets. Results will be read afterwards, avoiding a lot of network latency, especially when client, and server aren't on same host. Reads begin asynchronously after the first send command, driver will then wait to have read all results corresponding to sent data before sending new data. Since version 1.5.5 this option is disabled for Aurora, since it can hang connection.	`boolean`	`true`	1.5.0	3.0.3
`useBatchMultiSendNumber`	When using `useBatchMultiSend`, indicates maximum number of queries that can be sent at a time before reading results.	`int`	`100` or according to server variable `@@max_allowed_packet` if packet size cannot permit to send as many queries.	1.5.0	3.0.3

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 SELECT 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 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|
+----+------------+-----------+------------------------+