Number of result sets returned by the method execute

Question

For example I have a stored procedure which returns a different number of result sets based on the input parameters. Therefore, in Java, I use the method execute from the Statement class. Is there a method to determine the number of result sets returned by the method execute() ?

Answer 1

I don't believe there's any function that allows retrieving the result-set count directly, unfortunately.

One possible solution, if you really need the total count prior to processing, would be to iterate through all result-sets, collect them into an array, and then process them in a subsequent loop after getting the count. Here's a working (albeit postgres-specific) demo:

import java.util.List;
import java.util.ArrayList;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ResultSetCountDemo {

    public static void main(String[] args) throws Exception {

        Class.forName("org.postgresql.Driver");

        Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/main", "postgres", args.length >= 1 ? args[0] : "" );

        // execute query
        Statement st = con.createStatement();
        boolean b = st.execute("select 14, 'a' union all select 15, 'b'; select 26, 'x' union all select 27, 'y';");

        // collect and count result sets
        List<ResultSet> rsList = new ArrayList<ResultSet>();
        int count = 0;
        if (b) {
            rsList.add(st.getResultSet());
            for (++count; st.getMoreResults(Statement.KEEP_CURRENT_RESULT); ++count)
                rsList.add(st.getResultSet());
        } // end if
        System.out.println(count+" set"+(count == 1 ? "" : "s"));

        // process and close result sets
        for (int s = 1; s <= count; ++s) {
            ResultSet rs = rsList.get(s-1);
            for (int r = 1; rs.next(); ++r) {
                System.out.println("set "+s+" ("+r+","+1+") == "+rs.getInt(1));
                System.out.println("set "+s+" ("+r+","+2+") == "+rs.getString(2));
            } // end for
            rs.close();
        } // end for

        System.exit(0);

    } // end main()

} // end class ResultSetCountDemo

Output:

2 sets
set 1 (1,1) == 14
set 1 (1,2) == a
set 1 (2,1) == 15
set 1 (2,2) == b
set 2 (1,1) == 26
set 2 (1,2) == x
set 2 (2,1) == 27
set 2 (2,2) == y