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 withConnection
, if my connection is not closed after each query, why not keep thePreparedStatement
alive and reuse it in other methods?- if we create
PreparedStatement
each query, does the database knows it is the samePreparedStatement
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?
Thanks.
Best Answer
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:
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.