How to create PreparedStatements thats return multiple ResultSet

Posted on 2004-09-22
Medium Priority
Last Modified: 2010-03-31

I belive that PreparedStatements can return multiple ResultSet since they got the method:
...that I belive jumps to the next ResultSet.

But when I try to create a PreparedStatement with a question that should give back more then one ResultSet like and rub with my mysql:

connection.prepareStatement("SELECT * FROM tbl_test; SELECT * FROM tbl_users;");

I just get a exception like there is something wrong with the SQL-statement.

Has any one tried this?

I also have an other problem, that may depend on the same as above, when I create PreparedStatement with more than one questions like above but which only should give back one ResultSet:

connection.prepareStatement("INSERT INTO tbl_test (text1, text2) VALUES(?, ?); SELECT LAST_INSERT_ID() FROM tbl_test;");

The exception is:
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near';SELECT LAST_INSERT_ID() FROM tbl_test; ' at line 1

So perhaps it something wrong how I create the SQL-statements but the strange thing is that it works fine from the commandprompt for mysql. Like if I just type:
INSERT INTO tbl_test (text1, text2) VALUES(?, ?); SELECT LAST_INSERT_ID() FROM tbl_test;

...that works fine in.

So if any one got a solution for this it would be great!

Best regards

BTW 30 points is all I got, sorry!
Question by:fredand44
  • 4
  • 2
LVL 14

Expert Comment

ID: 12126100
try {
        // Disable auto-commit
        // Create a prepared statement
        String sql = "INSERT INTO my_table VALUES(?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        // Insert 10 rows of data
        for (int i=0; i<10; i++) {
            pstmt.setString(1, ""+i);
        // Execute the batch
        int [] updateCounts = pstmt.executeBatch();
        // All statements were successfully executed.
        // updateCounts contains one element for each batched statement.
        // updateCounts[i] contains the number of rows affected by that statement.
        // Since there were no errors, commit
    } catch (BatchUpdateException e) {
        // Not all of the statements were successfully executed
        int[] updateCounts = e.getUpdateCounts();
        // Some databases will continue to execute after one fails.
        // If so, updateCounts.length will equal the number of batched statements.
        // If not, updateCounts.length will equal the number of successfully executed statements
        // Either commit the successfully executed statements or rollback the entire batch
    } catch (SQLException e) {
    public static void processUpdateCounts(int[] updateCounts) {
        for (int i=0; i<updateCounts.length; i++) {
            if (updateCounts[i] >= 0) {
                // Successfully executed; the number represents number of affected rows
            } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                // Successfully executed; number of affected rows not available
            } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                // Failed to execute
LVL 14

Accepted Solution

sudhakar_koundinya earned 180 total points
ID: 12126218

Expert Comment

ID: 12150750
why take all the trouble?
Wouldn't it be simple and more readable/maintainable if you run them separately?    

Even if u combine two queries, the database will have to do the same amount of work as it would have if they were separate queries.

The only advantage I see here is that you are saving one extra call to the database. This would help u save some time if your databae is in a remote slow network....

Unless you have such network latency issues, I would suggest that you run the queries in two separate prepared statements...
this  way the code will be simpler to understand and those who support that application in the future will be thankful to you. :-)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 12155829

Yes, you got a point there. But I really would like to know how to do this, and be able to save any extra calls to the database.


Author Comment

ID: 12224026
Hello Amigos!

I'm still struggling with mysql and PreparedStatements that I would like to be able to execute multiple select. Below is a question, with full test source that should be able to test shoot, that a wrote in a forum at mysql.org. But I never got any answers. So if any one would like to test run the code below an d se if you could get it to work.

Best regards


I got a problem with mysql and PreparedStatement. I would like to create a PreparedStatement that will return more then one ResultSet. I think that this is possible with other databases. I have heard that this is possible with Sybase for eg.

The problem seems to be at this line (given you have a database with a table called test):
PreparedStatement preparedStatement = connection.prepareStatement("select * from test where id = 1; select * from test where id = 2;");

With Sybase you should be able to do this but you should separate the 2 select-statement with a new-line. I have not tried it my self but I have heard that this should work.

But with mysql I get this exception:
java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax near '; select * from test where id = 2' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at MySQLPreparedStatementTester.<init>(MySQLPreparedStatementTester.java:31)
at MySQLPreparedStatementTester.main(MySQLPreparedStatementTester.java:51)

At the Java API for PreparedStatement at method execute it says:
"Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. SOME PREPARED STATEMENTS RETURN MULTIPLE RESULTS; the execute method handles these complex statements as well as the simpler form of statements handled by the methods executeQuery and executeUpdate."

So my guess is that this should be possible.

Below is a testcode, but remeber that you need a table to test on.

So if any one could help me out with this it would be great. Else I guess that there is a bug in the jdbc.

Best regards
Fredrik Andersson

import java.sql.*;

public class MySQLPreparedStatementTester
public MySQLPreparedStatementTester()


Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/albinoni?user=root&password=pass");

PreparedStatement preparedStatement = connection.prepareStatement("select * from test where id = 1; select * from test where id = 2;");


ResultSet resultSet = preparedStatement.getResultSet();

while ( resultSet.next() )
System.out.println( resultSet.getString(2) );



resultSet = preparedStatement.getResultSet();

while ( resultSet.next() )
System.out.println( resultSet.getString(2) );


catch(Exception e)

public static void main( String args[] )
MySQLPreparedStatementTester mySQLPreparedStatementTester = new MySQLPreparedStatementTester();

Author Comment

ID: 12278945

I got this reply at www.mysql.org

MySQL-4.1 is the first version of MySQL to support multiple statements, however it doesn't support multiple statements per prepared statement. You'll have to use 'normal' statements to accomplish this.

You'll also have to enable this feature on the JDBC side (as well as use Connector/J 3.1.x), as it isn't enabled by default, because multiple-query support is a SQL-injection security vulnerability if not used correctly by the end-user. You can do this by setting 'allowMultiQueries=true' in your JDBC-url

So I guess it doesnt work

Author Comment

ID: 12278996
I really can not figure out how to split points??



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question