Am I closing the DB connection correctly? JDBC - DBCP

Question

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

public void insertProjectIntoDatabase(Project project) {
        String insertProjectIntoDatabase =
                "INSERT INTO projects(Project_Id, Project_Name, Project_StartDate, Deadline) " +
                        "VALUES (?, ?, ?, ?)";
        try {
            preparedStatement = DBCPDataSource.getConnection().prepareStatement(insertProjectIntoDatabase);
            preparedStatement.setInt(1, project.getProjectId());
            preparedStatement.setString(2, project.getName());
            preparedStatement.setDate(3, java.sql.Date.valueOf(project.getStartDate()));
            preparedStatement.setDate(4, java.sql.Date.valueOf(project.getDeadline()));
            preparedStatement.execute();

            preparedStatement.close();
        }
        catch (SQLException e)
        {
            System.out.println("Error happened in ProjectRepository at insertProjectIntoDatabase(): " + e.getMessage());
        }
    }

Bonus question:

I have created performance tests for creating a new connection each time an object needs one, Singleton connection and connection pool.

Singleton - Fastest

Creating a new connection each time - Slower (1.2s than the one above)

Connection Pool - Slowest (First connection - 2-3s slower than the one above, following tests are 0.4s slower than the one above)

I am using Apache Commons DBCP for the connection pool.

I thought using connection pools, would be just a little slower than Singleton connection.

Have I done something wrong?

Answer 1

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.

Answer 2

Pools are meant to improve performance, not degrade it. DBCP is naive, complicated, and outdated. I don't think it's appropriate for a production application, especially when so many drivers support pooling in their DataSource natively. The entire pool gets locked the whole time a new connection attempt is made to the database. So, if something happens to your database that results in slow connections or timeouts, other threads are blocked when they try to return a connection to the pool—even though they are done using a database. Even C3PO performs terribly. Please try using one of the two connection pools tomcat_connection_pool or HikariCP

Now coming to your main part of the question if you have closed the connection correctly? Whenever you use a connection pool and you fetch an available connection from the pool you need not have to close the connection that you fetched in your Dao layer. The pool manages the connections that you have created and each connection that the pool lends has a timeout associated with it before which it has to return to the pool. When the pool is shut down all the connections shutdown too.

For more information on how to configure these properties in your connection pool. Please check the links above for each of the connection pools.