How to Retrieve Multiple Result Sets from a Stored Procedure by JDBC in java?

How to Retrieve Multiple Result Sets from a Stored Procedure by JDBC in java?

A stored procedure the process related to data and return multiple result of sets, this way to may save fewer and some calls to database  in server. So need to include code to retrieve the result sets, Java JDBC Statement provide the getResultSet method to retrieve  result set. You can access to the first result set by calling the getResultSet method on your Statement object. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods. To determine if more result sets are available, you can call the getMoreResults method in Statement, which returns a boolean value of true if more result sets are available. If more result sets are available, you can call the getResultSet method again to access them, continuing the process until all result sets have been processed. If the getMoreResults method returns false, there are no more result sets to process. Calling getMoreResults() implicitly closes any previously returned ResultSet object(s) from method getResultSet.

 

In the following example, an open connection to the Database SQL Server is passed in to the function, and the stored procedure is returns n result sets of datan :

public static void executeProcedure(Connection con) {

   try {

      CallableStatement stmt = con.prepareCall(...);

      .....  //Set call parameters, if you have IN,OUT, or IN/OUT parameters

      boolean results = stmt.execute();

      int rsCount = 0;

      //Loop through the available result sets.

     while (results) {

           ResultSet rs = stmt.getResultSet();

           //Retrieve data from the result set.

           while (rs.next()) {

....// using rs.getxxx() method to retieve data

           }

           rs.close();

        //Check for next result set

        results = stmt.getMoreResults();

      } 

      stmt.close();

   }

   catch (Exception e) {

      e.printStackTrace();

   }

}

When you make the call to the getMoreResults() ,method of the Statement class, the previously returned result set is implicitly closed. How to keep result sets open when you check the next availiable result set. You can call the one with a parameter getMoreResults(int current) method. The parameter current indicates what should happen to current ResultSet objects obtained using the method getResultSet. You can specify one of these constants:

Statement.KEEP_CURRENT_RESULT : Checks for the next ResultSet, but does not close the current ResultSet.

Statement.CLOSE_CURRENT_RESULT : Checks for the next ResultSet, and closes the current ResultSet.

Statement.CLOSE_ALL_RESULTS : Closes all ResultSets that were previously kept open