Preferred way to query a database multiple times?

Question

When using JDBC in Java, the generally accepted method of querying a database is to acquire a connection, create a statement from that connection, and then execute a query from that statement.

// load driver
Connection con = DriverManager.getConnection(..);
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("SELECT..");
// ...

However, I am unsure of how to treat a second query to the same database.

1. Can another query be executed safely on the same Statement object, or must another statement be created from the Connection object in order to execute another query?

2. If the same Statement object can be used for multiple queries, what is the purpose of the Statement class (since it would then make more sense for a Connection.executeQuery() method to exist)?

Answer 1

Programmatically, you can reuse the same connection and the same statement for more than one query and close the statement and the connection at the end.

However, this is not a good practice. Application performance is very sensitive to the way database is accessed. Ideally, each connection should be open for the least amount of time possible. Then, the connections must be pooled. Going by that, you would enclose each query in a block of {open connection, create a prepared statement, run query, close statement, close connection}. This is also the way most SQL Templates are implemented. If concurrency permits, you can fire several such queries at the same time using a thread pool.

Answer 2

Yes you can reuse the Statement object, but the ResultSet objects returned by the executeQuery closes already opened resultsets.

See the javadoc for the explanation

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

So the following occurs:

// load driver
Connection con = DriverManager.getConnection(..);
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery("select ..");
// do something with result ... or not
ResultSet result2 = stmt.executeQuery("select ...");
// result is now closed, you cannot read from it anymore
// do something with result2
stmt.close(); // will close the resultset bound to it

For example you can find an open source implementation of Statement in the jTDS project. In the Statement.executeQuery() method you can see a call to initialize() that closes all the resultsets already opened

protected void initialize() throws SQLException {
    updateCount = -1;
    resultQueue.clear();
    genKeyResultSet = null;
    tds.clearResponseQueue();
    // FIXME Should old exceptions found now be thrown instead of lost?
    messages.exceptions = null;
    messages.clearWarnings();
    closeAllResultSets();
}

Answer 3

I have one thing to add should you use Connection and Statement in a threaded environment. My experience shows that stmt.executeQuery(..) is save to use in a parallel environment but with the consequence that each query is serialized and thus processed sequencially, not yielding any speed-ups. So it es better to use a new Connection (not Statement) for every thread.

For a standard sequential environment my experience has shown that reusing Statements is no problem at all and ResultSets need not be closed manually.