How to Execute Multiple SQL Statements as One in JDBC

1. Overview

When working with a database using Java and JDBC, there are scenarios where we need to execute multiple SQL statements as a single operation. It can help us improve the application performance, ensure atomicity, or manage complex workflows more effectively.

In this tutorial, we’ll explore various ways to execute multiple SQL statements in JDBC.

We’ll cover examples using Statement object, Batch processing, and Stored procedures to demonstrate how to execute multiple SQL queries efficiently. For this tutorial, we’ll use MySQL as the database.

2. Setting up JDBC and Database

Before diving into the code, let’s first ensure that our project is set up correctly and the database is configured.

2.1. Maven Dependencies

To get started, we’ll add the following dependency in our pom.xml file to include the MySQL JDBC driver:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId 
    <version>8.0.33</version>
</dependency>

2.2. Database Configuration

We’ll create a MySQL database named user_db and a table named users for this example and execute multiple insert queries for it:

CREATE DATABASE user_db;
USE user_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

With this database setup, we’re ready to run multiple SQL statements using JDBC.

3. Executing Multiple Queries as One in Java

Before executing multiple queries, we must ensure the database connection is configured correctly to allow multiple statements. Specifically, for MySQL, the connection URL should include the property allowMultiQueries=true to enable this functionality. First, we’ll set up the connection:

public Connection getConnection() throws SQLException {
    String url = "jdbc:mysql://localhost:3306/user_db?allowMultiQueries=true";
    String username = "username";
    String password = "password";

    return DriverManager.getConnection(url, username, password);
}

This configuration will ensure that MySQL accepts multiple SQL statements separated by semicolons in a single execution. By default, MySQL doesn’t allow multi-statement execution in a single execute() call unless the connection string includes the allowMultiQueries=true property.

There are three primary methods for executing multiple SQL statements in JDBC.

3.1. Using Statement Object

The Statement object allows us to execute multiple SQL queries by combining them into a single string separated by semicolons. Here’s an example where we insert multiple records to the newly created users table:

public boolean executeMultipleStatements() throws SQLException {
    String sql = "INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');" +
      "INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');";

    try (Statement statement = connection.createStatement()) {
        statement.execute(sql);
        return true;
    }
}

We’ll also add a test to verify the functionality of executing multiple SQL statements using a single Statement object. The SQL logic used ensures correct insertion into the database:

@Test
public void givenMultipleStatements_whenExecuting_thenRecordsAreInserted() throws SQLException {
    boolean result = executeMultipleStatements(connection);
    assertTrue(result, "The statements should execute successfully.");

    try (Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
          "SELECT COUNT(*) AS count FROM users WHERE name IN ('Alice', 'Bob')");) {
        resultSet.next();
        int count = resultSet.getInt("count");
        assertEquals(2, count, "Two records should have been inserted.");
    }
}

Please note, that executing multiple SQL statements in a single execute() call varies across database systems. Some databases support this feature natively, while others either require additional configurations or do not support it at all.

We have seen the example with the MySQL database above, which supports multi-statement execution, but it requires enabling the connection string property allowMultiQueries=true in the connection URL. PostgreSQL and SQL Server databases allow multiple SQL statements to be executed in a single execute() call without extra configuration.

Others, like Oracle and H2 databases, don’t support executing multiple statements in a single execute() call. Each SQL statement must be executed individually using separate calls to execute() or through batch processing using addBatch().

3.2. Using Batch Processing

Batch processing is a more efficient way to execute multiple queries when they don’t need to be executed as a single atomic unit.

public int[] executeBatchProcessing() throws SQLException {
    try (Statement statement = connection.createStatement()) {
        connection.setAutoCommit(false);

        statement.addBatch("INSERT INTO users (name, email) VALUES ('Charlie', '[email protected]')");
        statement.addBatch("INSERT INTO users (name, email) VALUES ('Diana', '[email protected]')");

        int[] updateCounts = statement.executeBatch();
        connection.commit();

        return updateCounts;
    }
}

To verify this works as expected, we’ll add a test case:

@Test
public void givenBatchProcessing_whenExecuting_thenRecordsAreInserted() throws SQLException {
    int[] updateCounts = executeBatchProcessing(connection);
    assertEquals(2, updateCounts.length, "Batch processing should execute two statements.");

    try (Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
          "SELECT COUNT(*) AS count FROM users WHERE name IN ('Charlie', 'Diana')");) {
        resultSet.next();
        int count = resultSet.getInt("count");
        assertEquals(2, count, "Two records should have been inserted via batch.");
    }
}

This test ensures that batch processing executes all the added statements correctly and verifies the number of rows inserted.

3.3. Handling Stored Procedures

Stored procedures precompile SQL code and store it in the database, allowing them to execute multiple statements in one call. To demonstrate it, we’ll create a stored procedure:

DELIMITER //

CREATE PROCEDURE InsertMultipleUsers()
BEGIN
    INSERT INTO users (name, email) VALUES ('Eve', '[email protected]');
    INSERT INTO users (name, email) VALUES ('Frank', '[email protected]');
END //

DELIMITER ;

To run this stored procedure, we’ll add the following code:

public boolean callStoredProcedure() throws SQLException {
    try (CallableStatement callableStatement = connection.prepareCall("{CALL InsertMultipleUsers()}")) {
        callableStatement.execute();
        return true;
    }
}

Next, we’ll add a test that validates that the stored procedure inserts the records into the database as expected:

@Test
public void givenStoredProcedure_whenCalling_thenRecordsAreInserted() throws SQLException {
    boolean result = callStoredProcedure(connection);
    assertTrue(result, "The stored procedure should execute successfully.");

    try (Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
          "SELECT COUNT(*) AS count FROM users WHERE name IN ('Eve', 'Frank')");) {
        resultSet.next();
        int count = resultSet.getInt("count");
        assertEquals(2, count, "Stored procedure should have inserted two records.");
    }
}

This approach ensures that multiple insert operations are executed efficiently within a single stored procedure call. Also, it enhances performance by reducing the overhead of multiple individual queries.

3.4. Executing Multiple Select Statements

In the above examples, we have seen multiple SQL insert statements. We’ll now see how to execute multiple SQL select statements in a single call using JDBC. It uses the statement.getMoreResults() method to iterate through the multiple result sets. It is useful when the SQL query contains multiple select statements separated by semicolons, and we want to process each result set individually.

public List<User> executeMultipleSelectStatements() throws SQLException {
    String sql = "SELECT * FROM users WHERE email = '[email protected]';" +
      "SELECT * FROM users WHERE email = '[email protected]';";

    List<User> users = new ArrayList<>();

    try (Statement statement = connection.createStatement()) {
        statement.execute(sql); // Here we execute the multiple queries

        do {
            try (ResultSet resultSet = statement.getResultSet()) {
                while (resultSet != null && resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String email = resultSet.getString("email");
                    users.add(new User(id, name, email));
                }
            }
        } while (statement.getMoreResults());
    }
    return users;
}

The statement.getMoreResults() method moves the cursor to the next result in the sequence, allowing us to handle multiple result sets sequentially. It returns true if the next result is a ResultSet and false if it is an update count or no more results are available.

We’ll add another test to validate the execution of the multiple SQL select statements and its result:

@Test
public void givenMultipleSelectStatements_whenExecuting_thenCorrectUsersAreFetched() 
  throws SQLException {
    MultipleSQLExecution execution = new MultipleSQLExecution(connection);
    execution.executeMultipleStatements();

    List<User> users = execution.executeMultipleSelectStatements();

    // Here we verify that exactly two users are fetched and their names match the expected ones
    assertThat(users)
      .hasSize(2)
      .extracting(User::getName)
      .containsExactlyInAnyOrder("Alice", "Bob");
}

The above method and the corresponding unit test simplify handling multiple queries in a single database call, ensuring efficient execution and easier result processing.

4. Conclusion

Executing multiple SQL statements in a single JDBC call can enhance performance and code readability. In this tutorial, we explored three different ways to execute multiple SQL statements in JDBC: using the Statement object, batch processing, and stored procedures. Each method has its use case, depending on whether performance or maintainability is the priority.

By understanding these techniques, we can make better decisions about how to structure our database operations and optimize our applications for efficiency and clarity.