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
      }
    });
  }

Answer 3

You should never pass ResultSet (or Statement or Connection) into the public outside the method block where they are to be acquired and closed to avoid resource leaks. A common practice is just to map the ResultSet to a List<Data> where Data is just a javabean object representing the data of interest.

Here's a basic example:

public class Data {
    private Long id;
    private String name;
    private Integer value;
    // Add/generate public getters + setters.
}

and here's a basic example of how to handle it correctly:

public List<Data> list() throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Data> list = new ArrayList<Data>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, name, value FROM data");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Data data = new Data();
            data.setId(resultSet.getLong("id"));
            data.setName(resultSet.getString("name"));
            data.setValue(resultSet.getInt("value"));
            list.add(data);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return list;
}

you can use it as follows:

List<Data> list = dataDAO.list();

To learn more about the best practices with JDBC you may find this basic kickoff article useful as well.

Answer 4

The cleaner way is to use CachedRowSetImpl. But on MySQL 5.x+ there are some bugs with selecting columns by name or label.

For use with MySQL use this version: https://stackoverflow.com/a/17399059/1978096

Answer 5

The way you have it right now, the connection would never close which would cause problems later (if not immediately) for your program and the RDBMS. It would be better to create a Java class to hold the fields from the ResultSet and return that. The ResultSet is linked to the connection, so returning it and closing the connection is not possible.

Answer 6

You can't use ResultSet after you've closed Connection and/or PreparedStatement. So, you need to pass an object on which to make a callback into this method.

All cleanup should be done in finally blocks.

Rewrite it like this

public ResultSet executeQuery(
    String sql,
    String[] getValue,
    CallbackObj cbObj
  ) throws SQLException
{
  final Connection conn = getConn( );

  try
  {
    final PreparedStatement pstmt = conn.prepareStatement(sql);

    try
    {
      if (getValue != null)
      {
        for (int i = 0; i < getValue.length; i++)
        {
          pstmt.setString(i + 1, getValue[i]);
        }
      }

      final ResultSet rs = pstmt.executeQuery();

      try
      {
        cbObj.processResultSet( rs );
      }
      finally
      {
        // You may want to handle SQLException
        // declared by close
        rs.close( );
      }
    }
    finally
    {
      // You may want to handle SQLException
      // declared by close
      pstmt.close( );
    }
  }
  finally
  {
    // You may want to handle SQLException
    // declared by close
    conn.close( );
  }
}

Answer 7

You can call ResultSet.getStatement to retrieve the Statement, and Statement.getConnection to retrieve the Connection.

From these you can write a closeResultSet utility method that will close all 3 for you, given nothing but the ResultSet.

Answer 8

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

Actually, you've almost answered that question yourself. As you experimented, closing the Connection will release the JDBC resources associated to it (at least, this is how things should work). So, if you want to return a ResultSet (I'll come back on this later), you need to close the connection "later". One way to do this would be obviously to pass a connection to your method, something like this:

public ResultSet executeQuery(Connection conn, String sql, String[] getValue);

The problem is that I don't really know what is your final goal and why you need so low level stuff so I'm not sure this is a good advice. Unless if you are writing a low level JDBC framework (and please, don't tell me you are not doing this), I would actually not recommend returning a ResultSet. For example, if you want to feed some business class, return some JDBC-independent object or a collection of them as other have advised instead of a ResultSet. Also bear in mind that a RowSet is a ResultSet so if you should not use a ResultSet then you should not use a RowSet.

Personally, I think you should use some helper class instead of reinventing the wheel. While Spring may be overkill and has a bit of learning curve (too much if you don't know it at all), Spring is not the only way to go and I strongly suggest to look at Commons DbUtils. More specifically, look at QueryRunner and especially this query() method:

public <T> T query(String sql,
                   ResultSetHandler<T> rsh,
                   Object... params)
        throws SQLException

As you can see, this method allows to pass a ResultSetHandler which exposes a callback method to convert ResultSets into other objects as described in z5h's answer and DbUtils provides several implementations, just pick up the one that will suit your needs. Also have a look at the utility methods of the DbUtils class, for example the various DbUnit.close() that you may find handy to close JDBC resources.

Really, unless you have very good reasons to do so (and I'd be curious to know them), don't write yet another JDBC framework, use an existing solution, it will save you some pain and, more important, some bugs and you'll benefit from proven good design. Even for low level stuff, there are existing (and simple) solutions as we saw. At least, check it out.

Answer 9

You really shouldn't handle with JDBC at the lower level. Use a framework like spring instead, it will handle all required close() operations for you.

Answer 10

I'd recommend that you do something more like this:

public List<Map> executeQuery(Connection connection, String sql) throws SQLException
{
    List<Map> rows = new ArrayList<Map>();

    PreparedStatement stmt = null;
    ResultSet rs = null;

    try
    {
        pstmt = conn.prepareStatement(sql);
        rs = stmt.execute();
        int numColumns = rs.getMetaData().getColumnCount();

        while (rs.next())
        {
            Map<String, Object> row = new LinkedHashMap<String, Object>();
            for (int i = 0; i < numColumns; ++i)
            {
                String column = rs.getColumnName(i+1);
                Object value = rs.getObject(i+1);
                row.put(column, value);
            }
            rows.add(row);
        }
    } 
    finally
    {
        close(rs);
        close(stmt);
    }

    return rows;
}

public static void close(Statement s)
{
    try
    {
        if (s != null)
        {
            s.close();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static void close(ResultSet rs)
{
    try
    {
        if (rs != null)
        {
            rs.close();
        }
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}