I've used JDBC with MySQL forever, but funnily enough, never tried issuing multiple statements in a single query, which results in multiple resultsets.

If you ever get this SQLException ResultSet is from UPDATE. No Data., then read on my friend.

Here's the lowdown:

1. Add ?allowMultiQueries=true to your JDBC URL, like so

jdbc:mysql://localhost/mydatabase?allowMultiQueries=true

Note: if you don't perform this step, the MySQL JDBC driver doesn't tell you you need to. It just complains with the usual syntax blah: 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version...

2. Make your usual JDBC connection plumbing, then create a Statement object and call execute().

Statement stmt = conn.createStatement();
stmt.execute(sql);

3. Now the fun part. Since you're issuing multiple statements, some may return resultsets, and others may not.

The 3 methods that's going to help us navigate the multiple resultsets are getUpdateCount(), getMoreResults() and getResultSet(). Here's one loop that binds them all.

 while(true) {
      if(stmt.getUpdateCount() > -1) {
        stmt.getMoreResults();
        continue;
      }
      if(stmt.getResultSet() == null) break;
      ResultSet rs = stmt.getResultSet();
      while (rs.next()) {
        // do something
      }
    }

1 Comment »