CODEWITHSUNDEEP
javajdbcresultset
user4099884
user4099884

Reputation:

Get next ResultSet in JDBC

I have 2 queries and therefore 2 ResultSet's returned from MySQL in Java through createStatement(). The queries are like

SELECT * FROM abc;
SELECT * FROM def;

These queries are run simultaneously in single createStatement() like

CreateConnection();
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from ABC; select * from DEF;");
while(rs.next()) {
    //Iterate through first resultset 
}
rs.close();
stat.close();
conn.close();

How can I get the next ResultSet returned by second query?

Upvotes: 2

Views: 3107

Answers (1)

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85779

Use Statement#getMoreResults and Statement#getResultSet methods:

ResultSet rs = stat.executeQuery("select * from ABC; select * from DEF;");
while(rs.next()) {
    //Iterate through first resultset 
}
rs.close();
if (stat.getMoreResults()) {
    rs = stat.getResultSet();
    while(rs.next()) {
        //Iterate through second resultset 
    }
}
stat.close();

In order that make this method to work, you should add allowMultiQueries=true property to your connection by appending this property to your connection url:

String url = "jdbc:mysql://yourServer:yourPort/yourDatabase?allowMultiQueries=true";

Note that you can perform multiple queries per Statement using a single Connection object:

Connection con = ...
List<String> sqlStatements = ... //a list with all the SELECT statements you have
for (String query : sqlStatements) {
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
        //do your logic here...
    }
    rs.close();
    stmt.close();
}
conn.close();

Upvotes: 2

Related Questions