When to close Connection, Statement, PreparedStatement and ResultSet in JDBC

Few questions on JDBC coding:

1. For a single client application, do we need a Connection pool?
2. Is it a good idea to create a Connection at the beginning and keep it alive without close it until application exit? Why?
3. PreparedStatement is associated with Connection, if my connection is not closed after each query, why not keep the PreparedStatement alive and reuse it in other methods?
4. if we create PreparedStatement each query, does the database knows it is the same PreparedStatement and ignore unnecessary actions after the first time?
5. PreparedStatement is not create once and reuse many times statement? If yes, why need to close it each time?

I know the call to close() will release the resource. But If we know we are going to use it later, why release it and then request it again later?

How about a multi-client application? We need a connection pool and so we need to create and close Connection, Statement, and PreparedStatement each time?

Solution 1:

Personally I'd use a pool as this will take care of all of the resource management for you. If your connection requirements change then you can easily modify the pool configuration. With a pool in place you can open/close connections and prepared statements according to best-practice and leave the resource management to the pool.

Typically, when using a pool:

• closing a connection will actually just return it to the pool
• the act of preparing a statement will either retrieve a previously prepared statement from the Connection's statement cache, or if one is not available, create a new statement and cache it for later use.
• the act of closing a PreparedStatement will actually just return it to the connection's statement cache.

Furthermore - depending on the pool implementation - it may be able to notify you when there are resource leaks making it easier to identify these sorts of problems in your code.

Take a look at the source of an example implementation like DBCP - it's quite interesting to see how they work.

Solution 2:

1. Even if you have a single client, a connection pool may still be beneficial. Connecting to the database may take a significant time so doing it very often may slow down your application with slow network requests. Moreover as @teabot explains, a pool may help identifying if any connection is not being closed.

2. It is not a good idea to open a connection and leave it open forever for two reasons. First the connection may die if there is a temporary network interruption. The longer it is open the more likely that it is dead when required. Second, a failed transaction may leave the connection in a state not suitable for continuing operation. The best is usually to open a few connections, reuse them for five or ten minutes, then recycle them.

3. Depending on the database and the driver, the connection may have a prepared statement cache. Even if using a different connection, the RDBMS usually caches statements that are exactly the same including it parameters. Therefore SELECT * FROM table WHERE value=? as a prepared statement will be cached across connections, but if you specify the parameter value like SELECT * FROM table WHERE value='your_data' then probably it won't be cached server side.

4. As explained in 3, depends on the RDBMS implementation, do a benchmark.

5. There is no need to close and prepare again a statement which is going to be reused with different parameters. Just set again the parameters and execute.

For multiple clients, the database will always have a concurrent connection limit which is not usually any big number. If all the clients go through a webapp then a pool like DBCP is all right. But obviously it's undesirable to create a pool for each client with several connections open permanently.