Multiple queries executed in java in a single statement

java

jdbc

resultset

transactions

byNikita Barsukov·Aug 8, 2024

Batch processing with addBatch() and executeBatch() methods in Java can optimise your multiple queries execution. Here's a taste of PreparedStatement utilization for batching:

PreparedStatement ps = connection.prepareStatement("INSERT INTO myTable VALUES (?, ?)");

ps.setString(1, "Value1");  // Inserting a row
ps.setString(2, "Value2");  // Just like the SQL dentist asked: Open wide and say ah...
ps.addBatch();

ps.setString(1, "Value3");  // Follow-up checkup
ps.setString(2, "Value4");  // No cavities here, onto the next...
ps.addBatch();

int[] results = ps.executeBatch();  // One small step for code, one giant leap for efficiency.

Note: Perfect for bulk DML operations (INSERT, UPDATE, DELETE). But DDL actions are as welcome in batching as a bull in a china shop.

To execute a mix of different queries, ensure allowMultiQueries=true in your JDBC URL and use a Statement:

Statement stmt = connection.createStatement();
String query = "SELECT * FROM myTable; INSERT INTO myTable VALUES (5, 'Value5');";
boolean hasResults = stmt.execute(query);  // We're cooking two dishes at the same time. Who says men can't multitask?

When dealing with multiple results, don't forget to treat your ResultSet with the respect it deserves.

Navigating the sea of multiple results

Multiple merriment with `execute()`

The Swiss army knife for queries - execute() method. This is your go-to for a combo of SELECT, INSERT, UPDATE, or DELETE sentences. It can juggle multiple ResultSet items or update counts:

boolean isResultSet = stmt.execute(query);
while (true) {
    if (isResultSet) {
        ResultSet rs = stmt.getResultSet();  // The SELECT whisperer at work
        // Process result set
    } else {
        if (stmt.getUpdateCount() == -1) {
            break; // All results have been served. Thank you, come again.
        }
        // Process update count
    }
    isResultSet = stmt.getMoreResults();
}

Multiple `ResultSet` extravaganza

When a single stored procedure call throws a party with multiple ResultSet objects, it’s time to mingle:

CallableStatement callableStatement = connection.prepareCall("{call sp_return_multiple_cursors()}");
boolean results = callableStatement.execute();
int resultSetCount = 0;

while (results) {
    ResultSet rs = callableStatement.getResultSet();  // It's raining ResultSets, Hallelujah!

    while (rs.next()) {
        // ...retrieve data...
    }

    resultSetCount++;
    results = callableStatement.getMoreResults(); // Onto the next party.
}

Note: Not all JDBCs are party animals, check your DBMS compatibility before you send out the invites.

Common goof-ups and pro-tips

Connection chit-chat

Without the proper database connection greeting, attempting to execute multiple queries could show you the SQLException monster. Tame it with the allowMultiQueries=true magic charm in your JDBC URL:

jdbc:mysql://localhost:3306/mydb?allowMultiQueries=true

Batch or multiple queries: the dilemma

Batches - fabulous for DML action and performance. However, mixing queries and gathering data (like SELECT then INSERT) - not batch-friendly. And remember, DDL statements and batching go together like oil and water.

Keeping your transactions sane

When multiple DML operations samba together, neatly wrap them in a transaction for consistent rhythm and smooth rollback moves in case somebody steps on a toe:

connection.setAutoCommit(false);

try {
    // Execute multiple queries here
    connection.commit();  // Let's conga line to consistency!
} catch (SQLException sqle) {
    connection.rollback();  // Oops, tripped! Let's roll back and try again.
    // Handle exception
}

connection.setAutoCommit(true);

Syntax and code precision

Syntax: Be exact or go home

Your code syntax needs to be as accurate as a Swiss watch when setting up multiple queries. A misplaced semicolon or missing property can toss your execution off the cliff.

Testing: Take it for a spin.

Take your multi-query code on a long drive in a petri dish environment. Each statement should have a seatbelt on, and exceptions should find a safe landing spot.

The ResultSet Matrix.

Changes made in DML operations may like to play hide-and-seek within ResultSet in the same transaction. This could make your application logic wobbly, be on the lookout!

explain-codes / Java / Multiple queries executed in java in a single statement

Linked

Preparedstatement IN clause alternatives?



Most efficient conversion of ResultSet to JSON?



C# SQL Server - Passing a list to a stored procedure



Why do people hate SQL cursors so much?



How do I efficiently iterate over each entry in a Java Map?

