JDBC Batch Processing

Summary: in this tutorial, you will learn about JDBC batch processing and how to use it to execute multiple SQL statements as a batch.

JDBC batch processing allows you to execute multiple SQL statements as a batch. It may improve the performance of your database operations by decreasing the number of round trips between the Java application and the database server.

Here are the steps for batch processing:

• First, open a new database connection.
• Second, create a new PreparedStatement object that accepts an UPDATE, INSERT, or DELETE statement.
• Third, set the parameters for the statement by calling the set* methods of the PreparedStatement object.
• Fourth, call the addBatch() method of the PreparedStatement to add the statement to a batch for batch processing.
• Fifth, call the executeBatch() to execute the statement to perform statements in a batch.
• Finally, close the database connection.

JDBC batch processing example

We’ll insert multiple rows into the products table using JDBC batch processing:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        String[] names = {"Crew Neck T-shirt", "V-neck T-shirt", "Polo T-shirt", "Graphic T-shirt", "Long Sleeve T-shirt"};
        double[] prices = {19.99, 24.99, 29.99, 22.99, 27.99};

        var sql = "INSERT INTO products (name, price) VALUES (?, ?)";

        try (var connection = DBConnection.connect();
             var pstmt = connection.prepareStatement(sql)) {

            for (int i = 0; i < names.length; i++) {
                pstmt.setString(1, names[i]);
                pstmt.setDouble(2, prices[i]);
                pstmt.addBatch();
            }

            int[] rowsInserted = pstmt.executeBatch();

            System.out.println(rowsInserted.length + " rows inserted successfully.");


        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: JavaScript (javascript)

How it works.

First, declare and initialize variables for holding the names and prices of products:

String[] names = {"Crew Neck T-shirt", "V-neck T-shirt", "Polo T-shirt", "Graphic T-shirt", "Long Sleeve T-shirt"};
double[] prices = {19.99, 24.99, 29.99, 22.99, 27.99};Code language: SQL (Structured Query Language) (sql)

Second, construct an INSERT statement:

var sql = "INSERT INTO products (name, price) VALUES (?, ?)";Code language: SQL (Structured Query Language) (sql)

In this syntax, the question marks (?) are placeholders that correspond to the name and price. When you execute the statement, you need to provide both of them.

Third, create a new connection to the database:

var connection = DBConnection.connect()Code language: SQL (Structured Query Language) (sql)

Fourth, create a PreparedStatement object with the input SQL statement

var pstmt = connection.prepareStatement(sql)Code language: SQL (Structured Query Language) (sql)

Fifth, set the values for the name and price by calling the setString() and setDouble() methods. Additionally, call the addBatch() method to assign SQL statements to the PreparedStatment object for execution in the same batch:

for (int i = 0; i < names.length; i++) {
    pstmt.setString(1, names[i]);
    pstmt.setDouble(2, prices[i]);
    pstmt.addBatch();
}Code language: SQL (Structured Query Language) (sql)

Sixth, execute the INSERT statements and returns a list of inserted rows:

int[] rowsInserted = pstmt.executeBatch();Code language: SQL (Structured Query Language) (sql)

Seventh, assign a message based on the number of inserted rows and display it:

System.out.println(rowsInserted.length + " rows inserted successfully.");Code language: SQL (Structured Query Language) (sql)

Finally, display the error message in the catch block if an exception occurs:

System.err.println(e.getMessage());Code language: SQL (Structured Query Language) (sql)

If you run a program and no exception occurs, you’ll see the following message:

5 rows inserted successfully.Code language: SQL (Structured Query Language) (sql)

Verify the insert

First, connect to the MariaDB server using the bob account:

mysql -u bob -pCode language: plaintext (plaintext)

It’ll prompt you for a password for the bob user account. Please enter it and press the Enter key to connect.

Second, change the current database to sales:

use sales;Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the products table:

select * from products;Code language: SQL (Structured Query Language) (sql)

Output:

+----+---------------------+-------+
| id | name                | price |
+----+---------------------+-------+
|  1 | T-Shirt             | 11.95 |
|  2 | Crew Neck T-shirt   | 19.99 |
|  3 | V-neck T-shirt      | 24.99 |
|  4 | Polo T-shirt        | 29.99 |
|  5 | Graphic T-shirt     | 22.99 |
|  6 | Long Sleeve T-shirt | 27.99 |
+----+---------------------+-------+
6 rows in set (0.00 sec)Code language: plaintext (plaintext)

Finally, quit the mysql program:

quitCode language: SQL (Structured Query Language) (sql)

Summary

• Use JDBC batch processing to reduce overhead and improve your database operation performance.