How to create PreparedStatements thats return multiple ResultSet

Posted on 2004-09-22
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 60 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. :-)
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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 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(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.Connection.execSQL(
at com.mysql.jdbc.PreparedStatement.executeQuery(
at MySQLPreparedStatementTester.<init>(
at MySQLPreparedStatementTester.main(

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 ( )
System.out.println( resultSet.getString(2) );



resultSet = preparedStatement.getResultSet();

while ( )
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

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
stackato and cloud 4 85
custom annotations 9 38
Why method in Java which is called from Runnable run() doesn't need to be 'static'? 1 24
javap bin 2 34
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

770 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