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

Execute multiple database queries using a single statement in Java

MySQL allows executing multiple queries in one statement using the semicolon (;) delimiter. However, handling this operation in Java using JDBC may throw exceptions by default.

Method 1: Use connection attributes

To enable multiple query executions, you can specify the allowMultiQueries connection attribute when establishing the connection:

<code class="language-java">String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";

Connection connection = DriverManager.getConnection(dbUrl, username, password);</code>

Method 2: Use stored procedures

Alternatively, you can create a stored procedure in MySQL that contains multiple queries. You can then call the stored procedure from Java using CallableStatement. An example is as follows:

MySQL stored procedure:

<code class="language-sql">CREATE PROCEDURE multi_query()
BEGIN
  SELECT COUNT(*) AS name_count FROM tbl_mq;
  INSERT INTO tbl_mq (name) VALUES ('ravi');
  SELECT LAST_INSERT_ID();
  SELECT * FROM tbl_mq;
END;</code>

Java code:

<code class="language-java">CallableStatement cstmt = connection.prepareCall("{call multi_query()}");
boolean hasMoreResultSets = cstmt.execute();

while (hasMoreResultSets) {
  ResultSet rs = cstmt.getResultSet();
  // 处理结果集
  while (rs.next()) {
    // 处理每一行数据
  }
  hasMoreResultSets = cstmt.getMoreResults();
}</code>

Handle multiple queries

For both methods, you can iterate over the results using the Statement or CallableStatement object's getResultSet() methods. The getMoreResults() method returns true to indicate there are more results to process, ensuring you capture all results for multiple queries.

Note:

Remember that DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE do not return a result set, so you should handle them individually. When processing result sets, be sure to close ResultSet and Statement objects to release resources. It is recommended to use the try-with-resources statement to ensure automatic closing of resources.

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