How Can I Execute Multiple SQL Queries in a Single Java Statement?

Executing Multiple Queries in Java in a Single Statement

JDBC typically raises an exception when attempting to execute multiple queries in a single statement, a feature available in MySQL's query browser. However, there are two approaches that facilitate this functionality:

Approach 1: Configuring Multiple Queries

Set the "allowMultiQueries" connection property to "true" in the database URL to enable multiple queries separated by semi-colons. This should be specified during the connection request.

String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";

Instead of using "executeBatch," employ "execute(String sql)" to retrieve query execution results. Iterate through the results and process them using the following steps:

boolean hasMoreResultSets = stmt.execute(multiQuerySqlString);
READING_QUERY_RESULTS:
while (hasMoreElementsResultSets || stmt.getUpdateCount() != -1) {
    if (hasMoreElementsResultSets) {
        ResultSet rs = stmt.getResultSet();
        // Handle result set here
    } else { // DDL/DML operation
        int queryResult = stmt.getUpdateCount();
        if (queryResult == -1) { // No more queries
            break READING_QUERY_RESULTS;
        }
        // Handle success, failure, or generated keys here
    }
    hasMoreElementsResultSets = stmt.getMoreResults();
}

Approach 2: Stored Procedures with Cursors

Create a stored procedure that executes multiple queries, including select and DML statements. Call this procedure from Java using "CallableStatement." This approach allows you to capture multiple ResultSets. However, DML results cannot be captured directly.

CallableStatement cstmt = con.prepareCall("call multi_query()");
boolean hasMoreResultSets = cstmt.execute();
READING_QUERY_RESULTS:
while (hasMoreElementsResultSets) {
    ResultSet rs = stmt.getResultSet();
    // Handle result set here
}

The above is the detailed content of How Can I Execute Multiple SQL Queries in a Single Java Statement?. For more information, please follow other related articles on the PHP Chinese website!