Result set stored in hashMap giving zero row count

Question

I have two methods in my class, First I am calling method dbExecuteStatement(), which execute the sql query. After execution of sql query, I get a ResultSet object. I am saving this ResultSet object in a static hashMap, so that on my next method call fetchResults(), I can use the existing result set to retrieve the results. Reason for saving the ResultSet object in a map is ,in fetchResults() method request parameter, I will get the max fetch row size, and on basis of that value I will be iterating the result set. Both of this methods are supposed to be called individual from the client side.

Now the problem, I am facing is that, When I am iterating the ResultSet object in fetchResults() method, I am getting the row count zero. If I fetch the same ResultSet from a hashMap in dbExecuteStatement(), I get the actual row count i.e 5 in my case. I checked the ResultSet object that I have put in the hash map in fetchResults() method and dbExecuteStatement(), it is the same object. But If get the ResultSetMetaData object in fetchResults() method and dbExecuteStatement(), they are coming different. Can someone help me in understanding the cause, Why I am getting the result count zero.

Below is the code:

  public class HiveDao1 {
    private static Map<Object,Map<Object,Object>> databaseConnectionDetails 
        = new HashMap<Object,Map<Object,Object>>();

    //This method will execute the sql query and will save the ResultSet obj in a hashmap for later use
     public void dbExecuteStatement(DbExecuteStatementReq dbExecuteStatementReq){
      //I already have a connection object saved in map
      String uniqueIdForConnectionObject = dbExecuteStatementReq.getDbUniqueConnectionHandlerId();
      Map<Object,Object> dbObject = databaseConnectionDetails.get(uniqueIdForConnectionObject);
      Connection connection = (Connection) dbObject.get(DatabaseConstants.CONNECTION);

      try {

    Statement stmt = connection.createStatement() ;
        // Execute the query
    ResultSet resultSet = stmt.executeQuery(dbExecuteStatementReq.getStatement().trim()) ;

    //save the result set for further use, Result set will be used in fetchResult() call
    dbObject.put(DatabaseConstants.RESULTSET, resultSet);

    /*
     * Now below is the debugging code,which I put to compare the result set
     *  iteration dbExecuteStatement() and fetchResults method
     */
    ResultSet rs = (ResultSet) dbObject.get(DatabaseConstants.RESULTSET);

    ResultSetMetaData md = (ResultSetMetaData) dbObject.get(DatabaseConstants.RESULTSETMETADATA);

     System.out.println("==ResultSet fethced in dbExecuteStatement=="+rs);
     System.out.println("==ResultSet metadata fetched in dbExecuteStatement ==="+rs.getMetaData());

    int count = 0;
    while (rs.next()) {
        ++count;
    }

     if (count == 0) {
        System.out.println("No records found");
    }

    System.out.println("No of rows found from result set in dbExecuteStatement is "+count);

    } catch (SQLException e) {
    e.printStackTrace();
    }

}

     /*
      * This method fetch the result set object from hashMap
      * and iterate it on the basis of fetch size received in req parameter
      */
public void fetchResults(FetchResultsReq fetchResultsReq){

    String uniqueIdForConnectionObject = fetchResultsReq.getDbUniqueConnectionHandlerId();
    Map<Object,Object> dbObject = databaseConnectionDetails.get(uniqueIdForConnectionObject);

    try {
        //Fetch the ResultSet object that was saved by dbExecuteStatement()
        ResultSet rs = (ResultSet) dbObject.get(DatabaseConstants.RESULTSET);
        ResultSetMetaData md = (ResultSetMetaData) dbObject.get(DatabaseConstants.RESULTSETMETADATA);

        System.out.println("ResultSet fethced in fetchResults at server side dao layer======"+rs);
        System.out.println("ResultSet metadata fetched in fetchResults at server side dao layer======"+md);

       int count = 0;
       while (rs.next()) {
           ++count;
        }

        if (count == 0) {
         System.out.println("No records found");
        }
        //Here the row count is not same as row count in dbExecuteStatement()            
        System.out.println("No of rows found from result set in fetchResults is "+count);

    } catch (SQLException e) {
    e.printStackTrace();
    }
    }

}

Answer 1

Expanding on my comment (And @Glenn's):

Using a ResultSet more than once

When you write debug code that iterates a ResultSet, the cursor moves to the end of the results. Of course, if you then call the same object and use next(), it will still be at the end, so you won't get any more records.

If you really need to read from the same ResultSet more than once, you need to execute the query such that it returns a scrollable ResultSet. You do this when you create the statement:

Statement stmt = connection.createStatement( 
                              ResultSet.TYPE_SCROLL_INSENSITIVE,
                              ResultSet.CONCUR_READ_ONLY );

The default statement created by connection.createStatement() without parameters returns a result set of type ResultSet.TYPE_FORWARD_ONLY, and that ResultSet object can only be read once.

If your result set type is scroll insensitive or scroll sensitive, you can use a statement like rs.first() to reset the cursor and then you can fetch the records again.

Keeping the statement in scope

@Glenn's comment is extremely important. The way your program works right now, it may work fine throughout the testing phase, and then suddenly in production, you'll sometimes have zero records in your ResultSet, and the error will be reproducible only occasionally - a debug nightmare.

If the Statement object that produces the ResultSet is closed, the ResultSet itself is also closed. Since you are not closing your Statement object yourself, this will be done when the Statement object is finalized.

The stmt variable is local, and it's the only reference to that Statement that we know of. Therefore, it will be claimed by the garbage collector. However, objects that have a finalizer are relegated to a finalization queue, and there is no way of knowing when the finalizer will be called, and no way to control it. Once it happens, the ResultSet becomes closed out of your control.

So be sure to keep a reference to the statement object alongside your ResultSet. And make sure you close it properly yourself once you are done with the ResultSet and will not be using it anymore. And after you close it remember to remove the reference you have kept - both for the statement and the result set - to avoid memory leaks. Closing is important, and relying on finalizers is a bad strategy. If you don't close it yourself, you might run out of cursors at some point in your database (depending on the DBMS and its configuration).