Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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. :-)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

610 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