When to close Connection, Statement, PreparedStatement and ResultSet in JDBC
connection, java, jdbc, resultset
Solution
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.
Problem
Few questions on JDBC coding: - For a single client application, do we need a Connection pool? - Is it a good idea to create a `Connection` at the beginning and keep it alive without close it until application exit? Why? - `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? - if we create `PreparedStatement` each query, does the database knows it is the same `PreparedStatement` and ignore unnecessary actions after the first time? - `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?