How to Use Methods getResultSet or getUpdateCount to Retrieve the Results?

When you use execute method of Statement to execute a given SQL statement, it might return multiple result sets and output parameters. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are

• executing a stored procedure that you know may return multiple results.
• dynamically executing an unknown SQL string.

The execute method excutes an SQL statement and returns a boolean value. When the value is true, the first result returned from the statements is a result set. When the value is false, the first result returned was an update count. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).

When the result of a SQL statement is not a result set, the method getResultSet will return null. This can mean that the result is an update count or that there are no more results. The only way to find out what the null really means in this case is to call the method getUpdateCount, which will return an integer. This integer will be the number of rows affected by the calling statement or -1 to indicate either that the result is a result set or that there are no results. If the method getResultSet has already returned null, which means that the result is not a ResultSet object, then a return value of -1 has to mean that there are no more results. In other words, there are no results (or no more results) when the following is true:

((stmt.getResultSet() == null) && (stmt.getUpdateCount() == -1))

If one has called the method getResultSet and processed the ResultSet object it returned, it is necessary to call the method getMoreResults to see if there is another result set or update count. If getMoreResults returns true, then one needs to again call getResultSet to actually retrieve the next result set. As already stated above, if getResultSet returns null, one has to call getUpdateCount to find out whether null means that the result is an update count or that there are no more results.

public static void executeStatementSample(Connection con) {
   try {
      String sqlStringWithUnknownResults = "....";
      Statement stmt = con.createStatement();
      boolean results = stmt.execute(sqlStringWithUnknownResults);
      int count = 0;
      do {
         if (results) {
            ResultSet rs = stmt.getResultSet();
            System.out.println("Result set data displayed here.");
         } else {
            count = stmt.getUpdateCount();
            if (count >= 0) {
               System.out.println("DDL or update data displayed here.");
            } else {
               System.out.println("No more results to process.");
            }
         }
         results = stmt.getMoreResults();
      } while (results || count != -1);
      rs.close();
      stmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

Printer-friendly version | Mail this to a friend