Where to close a JDBC Connection while I want to return the ResultSet

Question

It seems that the ResultSet will be automatically closed when I close the Connection. But I want to return the ResultSet and use it in another method, then I don't know where to close Connection and PreparedStatement.

public ResultSet executeQuery(String sql, String[] getValue)
{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try
    {
        conn = getConn();
        pstmt = conn.prepareStatement(sql);
        if (getValue != null)
        {
            for (int i = 0; i < getValue.length; i++)
            {
                pstmt.setString(i + 1, getValue[i]);
            }
        }
        rs = pstmt.executeQuery();
    } catch (Exception e)
    {
        e.printStackTrace();
        closeAll(conn, pstmt, rs);
    }
    return rs;
}

I've moved closeAll(conn, pstmt, null); into catch block because I found that if I put it in finally block I'll lost my rs immediately just before it returns. Now when I want to close the rs, I can't close the conn and pstmt. Is there any solution?

Answer 1

Use CachedRowSet for holding info after disconnecting

Connection con = ...
ResultSet rs = ...

CachedRowSet rowset = new CachedRowSetImpl();
rowset.populate(rs);

con.close()

Answer 2

One clean way of coding this is to pass in an object that has a callback method that takes a result set.

Your other method creates the object with the callback method with it's resultSet handling code, and passes that to the method that executes the SQL.

That way, your SQL & DB code stays where it belongs, your result set handling logic is closer to where you use the data, and your SQL code cleans up when it should.

  interface ResultSetCallBack{
    void handleResultSet(ResultSet r);
  }

  void executeQuery(..., ResultSetCallBack cb){
    //get resultSet r ...
    cb.handleResultSet(r);
    //close connection
  }

  void printReport(){
    executeQuery(..., new ResultSetCallBack(){
      public void handleResultSet(ResultSet r) {
        //do stuff with r here
      }
    });
  }