When working with databases in Java, it’s common to execute multiple SQL statements in a single execution. This improves efficiency and reduces the number of database round trips. JDBC provides several ways to achieve this, including batch execution and using multiple statements in a single SQL execution. Let us delve into understanding how Java JDBC executes multiple statements efficiently to improve database performance and reduce execution time.
1. What is JDBC?
Java Database Connectivity (JDBC) is an API that allows Java applications to interact with relational databases. It provides a set of classes and interfaces for connecting to a database, executing SQL queries, and retrieving results. JDBC acts as a bridge between Java applications and various database management systems (DBMS), enabling seamless data access and manipulation.
JDBC is a part of Java’s Java Standard Edition (Java SE) and is widely used in enterprise applications. It follows a standard set of steps, including loading the database driver, establishing a connection, creating SQL statements, executing queries, and processing the results.
JDBC supports various types of drivers, including JDBC-ODBC Bridge Driver, Native-API Driver, Network Protocol Driver, and Thin Driver (Type-4), which is the most commonly used. For more details on how JDBC works, visit the official JDBC API Documentation.
2. Configuring JDBC and Database Setup
2.1 Database and Table creation
We are using MySQL as our preferred choice of database. If you haven’t installed MySQL yet, refer to the
MySQL Installation Guide. Once the database is installed, use the following SQL to experiment with the code examples.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- create databaseCREATE DATABASE some_db;-- using the databaseUSE some_db;-- create employees tableCREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT NOT NULL);-- create departments tableCREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);-- insert data into departments tableINSERT INTO departments VALUES (101, “HR”);INSERT INTO departments VALUES (102, “IT”); |
2.2 JDBC Connection
Before executing multiple SQL statements, we need to set up a JDBC connection. A properly established connection ensures seamless communication between the Java application and the database. Below are the essential steps to follow:
- Load the JDBC Driver: This step is required for older versions of Java, where the driver must be explicitly loaded using
Class.forName(). However, modern JDBC drivers, such as MySQL Connector/J, are auto-registered. - Establish a Connection: We use the
DriverManager.getConnection()method to connect to the database using a valid JDBC URL, username, and password. - Create a Statement Object: The
StatementorPreparedStatementobject is used to execute SQL queries. - Execute SQL Queries: SQL commands such as
SELECT,INSERT,UPDATE, andDELETEare executed using methods likeexecute(),executeUpdate(), orexecuteBatch().
For the sake of brevity, the boilerplate code for setting up the JDBC connection is omitted from this article. However, if you’re looking for the getConnection() code snippet, you can refer to the code below, but remember to change the connection string as per your settings.
1 2 3 4 5 6 | public static Connection getConnection() throws SQLException { return DriverManager.getConnection( "jdbc:mysql://localhost:3306/mydatabase?allowMultiQueries=true", "root", "password");} |
Make sure to include the allowMultiQueries=true parameter in the connection string, as MySQL does not allow multiple queries in a single statement by default.
3. Executing Multiple Queries as One in Java
We can execute multiple queries as a single execution using Statement.execute() or batch execution with Statement.addBatch().
3.1 Using execute() method
We can use the execute() method to execute multiple queries at once.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 | public class ExecuteMultipleQueries { public static void main(String[] args) { try (Connection conn = JDBCExample.getConnection(); Statement stmt = conn.createStatement()) { String sql = "INSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30); " + "INSERT INTO employees (id, name, age) VALUES (2, 'Jane Smith', 28);"; boolean result = stmt.execute(sql); System.out.println("Execution successful: " + result); } catch (SQLException e) { e.printStackTrace(); } }} |
3.1.1 Code Explanation and Output
The above Java program demonstrates how to execute multiple SQL statements as a single execution using JDBC. It establishes a database connection using the JDBCExample.getConnection() method and creates a Statement object. The SQL query consists of two INSERT statements concatenated into a single string. The execute() method is used to send the SQL commands to the database in one execution. Upon successful execution, the method will return a Boolean indicating whether the result is a ResultSet. Please note that if the database supports multiple queries in a single execution, both INSERT statements will be executed successfully. Any SQL exceptions encountered during execution are caught and printed using printStackTrace(). Below is the output upon successful execution of the code:
1 | Execution successful: false |
Since the execute() method returns false for INSERT statements as they do not return a ResultSet, the console output will be false.
3.2 Using addBatch() method
Batch processing is another efficient way to execute multiple statements.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 | public class BatchExecutionExample { public static void main(String[] args) { try (Connection conn = JDBCExample.getConnection(); Statement stmt = conn.createStatement()) { conn.setAutoCommit(false); stmt.addBatch("INSERT INTO employees (id, name, age) VALUES (3, 'Alice Brown', 25)"); stmt.addBatch("INSERT INTO employees (id, name, age) VALUES (4, 'Bob White', 35)"); int[] updateCounts = stmt.executeBatch(); conn.commit(); System.out.println("Batch execution completed. Rows affected: " + updateCounts.length); } catch (SQLException e) { e.printStackTrace(); } }} |
3.2.1 Code Explanation and Output
This Java program demonstrates batch execution using JDBC. Inside the main method, it establishes a database connection via JDBCExample.getConnection() and creates a Statement object. The connection’s auto-commit mode is disabled to allow transaction control. Two SQL INSERT statements are added to the batch, inserting employee records into the employees table. The executeBatch() method runs all queries as a batch, and upon successful execution, conn.commit() ensures the changes are saved. The number of affected rows is printed to the console. If an SQL exception occurs, it is caught and printed. The use of try-with-resources ensures automatic resource management. Below is the output upon successful execution of the code:
1 | Batch execution completed. Rows affected: 2 |
3.3 Using Stored Procedure
There is another option to execute multiple SQL statements by creating a stored procedure in the database and calling that procedure from the Java client code. This approach improves performance, enhances security, and reduces network traffic. Let us take a look at the step-by-step approach.
3.3.1 Creating a Stored Procedure
1 2 3 4 5 6 7 8 9 | DELIMITER //CREATE PROCEDURE InsertEmployees()BEGIN INSERT INTO employees (id, name, age) VALUES (5, 'Jane Brown', 25); INSERT INTO employees (id, name, age) VALUES (6, 'Alex White', 35);END //DELIMITER ; |
This stored procedure named InsertEmployees inserts two records into the employees table.
3.3.2 Calling the Stored Procedure
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 | import java.sql.Connection;import java.sql.CallableStatement;import java.sql.SQLException;public class CallStoredProcedure { public static void main(String[] args) { try (Connection conn = JDBCExample.getConnection(); CallableStatement cstmt = conn.prepareCall("{CALL InsertEmployees()}")) { boolean result = cstmt.execute(); System.out.println("Stored procedure executed successfully: " + result); } catch (SQLException e) { e.printStackTrace(); } }} |
In this Java program, we establish a connection and use a CallableStatement to call the stored procedure InsertEmployees(). The execute() method triggers the procedure, which runs the multiple INSERT statements inside it. Below is the output upon successful execution of the code:
1 | Stored procedure executed successfully: false |
Similar to executing multiple statements with execute(), the output is false since the stored procedure does not return a ResultSet. This approach is more efficient when dealing with multiple statements that are executed frequently.
3.4 Executing Multiple SELECT Statements at Once in JDBC
We can execute multiple SELECT queries in a single execution and process multiple result sets using Statement.execute().
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | import java.sql.*;public class ExecuteMultipleSelectQueries { public static void main(String[] args) { try (Connection conn = JDBCExample.getConnection(); Statement stmt = conn.createStatement()) { // Multiple SELECT queries String sql = "SELECT * FROM employees; SELECT * FROM departments;"; boolean hasResultSet = stmt.execute(sql); // Process the first result set while (hasResultSet) { try (ResultSet rs = stmt.getResultSet()) { ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); System.out.println("=== Result Set ==="); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { System.out.print(rs.getString(i) + "\t"); } System.out.println(); } } // Check if there are more result sets hasResultSet = stmt.getMoreResults(); } } catch (SQLException e) { e.printStackTrace(); } }} |
3.4.1 Code Explanation and Output
The above Java program demonstrates how to execute multiple SELECT queries in a single execution using JDBC. It establishes a database connection using JDBCExample.getConnection() and creates a Statement object. The SQL string contains two SELECT statements that fetch data from the employees and departments tables. The execute() method runs the queries, and a loop processes multiple ResultSet objects using getResultSet(). The program retrieves metadata to determine the number of columns, iterates through the result set and prints each row dynamically. After processing one result set, it checks for additional result sets using getMoreResults(). If the database supports multiple result sets, both queries execute successfully, displaying results from both tables. Any SQL errors encountered during execution are caught and printed using printStackTrace(). This approach reduces database round trips and efficiently handles multiple query results in a single execution. Below is the output upon successful execution of the code:
01 02 03 04 05 06 07 08 09 10 11 | === Result Set ===1 John Doe 302 Jane Smith 283 Alice Brown 254 Bob White 355 Jane Brown 256 Alex White 35=== Result Set ===101 HR102 IT |
The output shows the data retrieved from both the employees and departments tables. The first result set contains employee details, while the second result set displays department information. If an error occurs, an SQL exception stack trace will be printed instead.
4. Conclusion
In this article, we explored multiple ways to execute multiple SQL statements in JDBC, including using Statement.execute() for executing multiple queries in a single call, Statement.addBatch() for batch processing, and CallableStatement for executing stored procedures that handle multiple SQL commands. We also demonstrated how to retrieve multiple result sets efficiently using getMoreResults() when executing multiple SELECT statements. Each approach has its advantages—batch execution improves performance for multiple insert/update operations, stored procedures reduce network overhead and enhance security while handling multiple result sets allows efficient data retrieval from different tables in one execution. Choosing the right method depends on the use case, database support, and performance considerations. By leveraging these techniques, developers can optimize database interactions, reduce latency, and improve the efficiency of Java JDBC applications.
