Where to close a JDBC Connection while I want to r

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

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 4

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 5

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 6

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.