Closing Jdbc Connections in Pool

Closing JDBC Connections in Pool

When using Connection Pool, should one close the Connection at the end? If so, isn't the purpose of pooling lost? And if not, how does the DataSource know when a particular instance of Connection is freed up and can be reused? I am a little confused on this one, any pointers appreciated.

Yes, certainly you need to close the pooled connection as well. It's actually a wrapper around the actual connection. It wil under the covers release the actual connection back to the pool. It's further up to the pool to decide whether the actual connection will actually be closed or be reused for a new getConnection() call. So, regardless of whether you're using a connection pool or not, you should always close all the JDBC resources in reversed order in the finally block of the try block where you've acquired them. In Java 7 this can be further simplified by using try-with-resources statement.

Is the following method anything close to standard? Looks like an attempt to get a connection from the pool, and if DataSource cannot be established, use the old fashioned DriverManager. We are not even sure which part is getting executed at runtime. Repeating the question above, should one close the Connection coming out of such a method?

The example is pretty scary. You just need to lookup/initialize the DataSource only once during application's startup in some constructor / initialization of an applicationwide DB config class. Then just call getConnection() on the one and same datasource throughout the rest of application's lifetime. No need for synchronization nor nullchecks.

Am I closing the DB connection correctly? JDBC - DBCP

You asked:

Will closing the preparedStatement also close and return the connection to the connection pool?

Start with the documentation:

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.
Calling the method close on a Statement object that is already closed has no effect.
Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

No mention of closing the connection.

Try intuition: Do we ever run more than one statement in SQL? Yes, obviously. So logically the connection needs to survive across multiple statements to be useful.

Lastly: Try it yourself, an empirical test. Call Connection#isOpen after calling Statement#close.

➥ No, closing the statement does not close the connection.

For the simplest code, learn to use try-with-resources syntax to auto-close your database resources such as result set, statement, and connection. You’ll find many examples of such code on this site, including some written by me.

As for connection pools, yes, calling close on a connection retrieved from a pool causes the connection object to be be returned to the pool. The pool may choose to re-use the connection, or the pool may choose to close the connection. (Not our concern.)

The only point to a connection pool is speed. If opening a connection to the database takes a significant amount of time, we can save that time by re-using existing connection. Generating and re-using connections is the job of a connection pool.

If a connection pool is showing the slowest results in your testing, then here is something seriously wrong with either your pool or your tests. You did not reveal to us your tests, so we cannot help there. Note: As Marmite Bomber commented, be sure your tests do not include the time needed to establish the connection pool.

Frankly, I have found in my experience that opening a database connection does not take a significant amount of time. Furthermore, the details involved in properly implementing a connection pool are complex and treacherous as evidenced by the list of failed and abandoned connection pool implementation projects. That, combined with the inherent risks such as a transaction being left open on a retrieved connection, led me to avoiding the use of connection pools. I would posit that using a connection pool before collecting proof of an actual problem is a case of premature optimization.

I suggest using an implementation of the interface DataSource as a way to mask from the rest of your code whether you are using a pool and to hide which pool implementation you are currently using. Using DataSource gives you the flexibility to to change between using or not using a connection pool, and the flexibility to change between pools. Those changes become deployment choices, with no need to change your app programming.

Do you need to close the connection you get from jdbc connection pool?

The answer is yes, and check Closing JDBC Connections in Pool and JDBC Connection Pooling Best Practices for more information.

Is opening/closing of JDBC connection instead of maintaining it, desirable for my application?

It's depend on the architecture of your server side codes and the way you insert/update your tables:

Do you maintain creating transactions or not? Or your connection is AutoCommit=true?
Are you doing a number of operations in a batch or doing single operations using different connections?

The most efficient and publicly used way is creating a connection pool with a rational number of initial connections. This helps with the expensive operation of creating connections. So you are doing this operation (creating connection pool) only once when your server is started.

Then in your codes you should refactor creating connections and replace it with getting a connection from the pool. Then after you used the connection for the CRUD operations, you should close it to make sure you are returning it to the pool.

However in small applications with limited number of connections it doesn't matter how you open/close connections. But having a managed connection pool is a very important step in designing and implementing a large enterprise application.

You can implement your own way of connection pooling using these examples:

How to set up a MySQL connection pool in Java

Java Database Connection Pooling

Also if your server application is managed and run by a web server like tomcat you can let the tomcat handle creating and managing the connection pool for you. You should just define a datasource in tomcat. See this tutorial:

Connection Pooling in a Java Web Application with Tomcat

Hope this would be helpful.

Should I close a Connection obtained from a DataSource manually?

A connection obtained from a connection pool should be used exactly the same as a normal connection. The JDBC 4.2 specification (section 11.1) says about pooling:

When an application is finished using a connection, it closes the logical connection
using the method Connection.close. This closes the logical connection but does
not close the physical connection. Instead, the physical connection is returned to the
pool so that it can be reused.

Connection pooling is completely transparent to the client: A client obtains a pooled
connection and uses it just the same way it obtains and uses a non pooled
connection.

(emphasis mine)

This means that when you are done with a connection, you always call Connection.close()! It doesn't matter if it is a physical connection, or a logical connection from the pool.

The reason is that whether a connection is a physical (direct) connection or a logical connection should be purely a matter of configuration, not a concern of the application code that merely uses the connection.

In the case of a connection pool, the close() will - details may vary, and some implementations are buggy in this respect - invalidate the logical connection and signal to the connection pool that the underlying physical connection is available for re-use. The connection pool may do some validity checks and then return the (physical) connection into the pool or close it (eg if the pool has too many idle connections, or the connection is too old, etc).

Calling close() is not only allowed, it is even vital for the correct working of a connection pool. Not calling close() usually requires some helper thread to close (reclaim) logical connections that have been in use for too long. As this timeout is usually longer than normal application needs, it might lead to exhaustion of the pool, or to configuration where the pool needs a higher maximum number of connections than is really necessary.

Tomcat JDBC connection pool (releasing connection)

Since you call the close() on a method obtained by the pool it is up to the pool what to do inside this method call. It does not neccessarily have to close the pooled database connection - it may do some cleanup and then add the connetion back to the pool.

This is already answered in Closing JDBC Connections in Pool

Closing Jdbc Connections in Pool