Executing Multiple SQL Statements in Java Using JDBC

Introduction

In database application development, there is often a need to execute multiple related SQL statements within a single database session. The traditional approach involves executing each statement separately, but this method incurs performance overhead and transaction consistency issues. This article provides an in-depth exploration of how to efficiently execute multiple SQL statements using JDBC in Java.

Method 1: Configuring Connection Property for Multiple Queries

The MySQL JDBC driver provides the allowMultiQueries connection parameter, which enables including multiple SQL statements separated by semicolons within a single string. When this feature is enabled, multiple queries can be sent to the database server for execution in one go.

To enable multiple query functionality, the corresponding parameter must be added to the database connection URL:

String dbUrl = "jdbc:mysql://localhost:3306/databaseinjection?allowMultiQueries=true";

Parameter values can be true, false, yes, or no, with any other values throwing an SQLException at runtime.

Executing multiple queries requires using the Statement.execute(String sql) method:

String multiQuery = "SELECT * FROM users; INSERT INTO log VALUES (NOW(), 'query_executed');";
boolean hasResults = statement.execute(multiQuery);

Processing multiple query results requires iterating through all result sets:

PROCESS_RESULTS:
while (hasResults || statement.getUpdateCount() != -1) {
    if (hasResults) {
        ResultSet resultSet = statement.getResultSet();
        while (resultSet.next()) {
            // Process query results
            String userName = resultSet.getString("username");
            System.out.println("User: " + userName);
        }
        resultSet.close();
    } else {
        int updateCount = statement.getUpdateCount();
        if (updateCount == -1) {
            break PROCESS_RESULTS;
        }
        System.out.println("Rows affected: " + updateCount);
    }
    hasResults = statement.getMoreResults();
}

Method 2: Using Stored Procedures

Stored procedures represent another effective approach for executing multiple SQL statements. By encapsulating multiple operations within database-side stored procedures, network round-trips can be reduced and execution efficiency improved.

First, create a stored procedure containing multiple queries:

DELIMITER //
CREATE PROCEDURE execute_multiple_operations()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
    INSERT INTO audit_log (action_time, description) VALUES (NOW(), 'Batch operation executed');
    SELECT LAST_INSERT_ID() AS new_log_id;
    SELECT * FROM users WHERE status = 'active';
END //
DELIMITER ;

Call the stored procedure from Java:

CallableStatement callableStatement = connection.prepareCall("{call execute_multiple_operations()}");
boolean hasResultSets = callableStatement.execute();

while (hasResultSets) {
    ResultSet resultSet = callableStatement.getResultSet();
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    
    while (resultSet.next()) {
        for (int i = 1; i <= columnCount; i++) {
            String columnValue = resultSet.getString(i);
            String columnName = metaData.getColumnName(i);
            System.out.println(columnName + ": " + columnValue);
        }
    }
    resultSet.close();
    hasResultSets = callableStatement.getMoreResults();
}
callableStatement.close();

Method Comparison and Selection Guidelines

Both methods have distinct advantages and disadvantages, making them suitable for different application scenarios:

Connection Property Method is ideal for dynamically generated query statements, offering high flexibility but requiring attention to SQL injection risks. When query statements are dynamically constructed at runtime, this method provides maximum flexibility.

Stored Procedure Method offers advantages in performance and security, particularly for complex business logic. Stored procedures are pre-compiled on the database server, resulting in higher execution efficiency and reduced network transmission overhead.

In practical projects, selection should be based on factors such as query complexity, performance requirements, security considerations, and maintenance costs. For simple batch operations, the connection property method is more convenient; for complex business logic, stored procedures represent a better choice.

Error Handling and Best Practices

When executing multiple queries, potential exceptions must be properly handled:

try {
    // Multiple query execution code
} catch (SQLException e) {
    System.err.println("SQL execution error: " + e.getMessage());
    System.err.println("SQL state: " + e.getSQLState());
    System.err.println("Error code: " + e.getErrorCode());
} finally {
    try {
        if (statement != null) statement.close();
        if (connection != null) connection.close();
    } catch (SQLException e) {
        System.err.println("Resource closure error: " + e.getMessage());
    }
}

Best practices include: using parameterized queries to prevent SQL injection, properly managing database connection resources, implementing appropriate retry mechanisms, and maintaining detailed execution logs.

Performance Optimization Considerations

Multiple query execution can impact database performance, especially in high-concurrency environments. Recommendations include: appropriately configuring connection pool parameters, optimizing SQL statements, utilizing database indexes, and monitoring execution plans. For bulk operations involving large data volumes, consider using JDBC's batch processing functionality:

Statement batchStatement = connection.createStatement();
batchStatement.addBatch("UPDATE products SET price = price * 1.1 WHERE category = 'electronics'");
batchStatement.addBatch("INSERT INTO sales_log (product_id, sale_date) VALUES (123, NOW())");
int[] updateCounts = batchStatement.executeBatch();

Batch processing is suitable for multiple independent DML operations but cannot mix SELECT and DML statements.

Conclusion

Executing multiple SQL statements through JDBC is a common requirement in database applications. The two methods discussed in this article—configuring connection properties and using stored procedures—provide developers with flexible options. In practical applications, the most appropriate method should be selected based on specific requirements, while following best practices to ensure code reliability, security, and performance.