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

Question

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?

Thanks.

Answer 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.