Using try-with-resources to close database connections

Managing database connections is a crucial aspect of Java programming, especially when working with relational databases. Java provides a convenient way to handle resources, including database connections, using the try-with-resources statement introduced in Java 7. This feature simplifies resource management by automatically closing resources when they are no longer needed, reducing the risk of resource leaks.

In this tutorial, we will explore how to leverage try-with-resources to establish and close database connections seamlessly. We’ll cover the basic setup, executing database operations, and ensuring that resources are closed properly.

The Standard way to Close connections in Java

Here is a sample Class which gets injected a Datasources and use it to extract a Database connection and perform some Queries:

@Resource(lookup = "java:/MySQLDS")
private DataSource ds;
. . . .    
String sql = "select * from customer";
List list = new ArrayList();

Connection con =null;

PreparedStatement ps =null;

ResultSet rs =null;
try {

   con = ds.getConnection();
   ps = con.prepareStatement(sql);
   
   rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getInt("id"));
   }
    
} catch (SQLException e) {
    e.printStackTrace();
}
 finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {  }
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) {  }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {  }
    }
}

Although the above statement is correct, it is quite verbose to manage the proper closing of all Statements and Connections

Rewriting the example with try-with-resource

The try-with-resources statement is a try statement that declares one or more resources. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource. See the above example rewritten using try-with-resources:

@Resource(lookup = "java:/MySQLDS")
private DataSource ds;
. . . .   
String sql = "select * from customer";
List list = new ArrayList();
try (Connection con = ds.getConnection();
     PreparedStatement ps = con.prepareStatement(sql);) {
   
    try (ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            list.add(rs.getInt("id"));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

We saved about the half of the method coding! cool isn’t it ??
Now let’s do some testing! At first we will invoke the old way method, but removing the finally block so that you can see what happens if you forget to close your Connection/Statements and how to detect a connection leak.
If you execute the following CLI statament on your datasource, you will see that, after 5 times we have invoked the procedure, the Connection pool has not released any of these connections (inUseCount):

[standalone@localhost:9990 /] /subsystem=datasources/data-source=MySQLPool/statistics=pool:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => "6",
        "AvailableCount" => "15",
        "AverageBlockingTime" => "0",
        "AverageCreationTime" => "639",
        "AverageGetTime" => "640",
        "BlockingFailureCount" => "0",
        "CreatedCount" => "6",
        "DestroyedCount" => "0",
        "IdleCount" => "1",
        "InUseCount" => "5",
        "MaxCreationTime" => "1372",
        "MaxGetTime" => "1372",
        "MaxUsedCount" => "6",
        "MaxWaitCount" => "0",
        "MaxWaitTime" => "0",
        "TimedOut" => "0",
        "TotalBlockingTime" => "0",
        "TotalCreationTime" => "3838",
        "TotalGetTime" => "3843",
        "WaitCount" => "0"
    }
}		

Now we will clean statistics and execute the application using try-with-resources statement.

[standalone@localhost:9990 /] /subsystem=datasources/data-source=MySQLPool/statistics=pool:read-resource(include-runtime=true)
{
{
    "outcome" => "success",
    "result" => {
        "ActiveCount" => "1",
        "AvailableCount" => "20",
        "AverageBlockingTime" => "1",
        "AverageCreationTime" => "211",
        "AverageGetTime" => "109",
        "BlockingFailureCount" => "0",
        "CreatedCount" => "1",
        "DestroyedCount" => "0",
        "IdleCount" => "1",
        "InUseCount" => "0",
        "MaxCreationTime" => "211",
        "MaxGetTime" => "217",
        "MaxUsedCount" => "1",
        "MaxWaitCount" => "0",
        "MaxWaitTime" => "1",
        "TimedOut" => "0",
        "TotalBlockingTime" => "1",
        "TotalCreationTime" => "211",
        "TotalGetTime" => "219",
        "WaitCount" => "0"
    }
}		

InUseCount = 0. As you can see Connections, Statements and ResultSets have been magically closed for you!

Using the enhanced try-with-resources

In Java 9 and later, you can use the enhanced try-with-resources statement with effectively final variables (Aka JEP 213) . Here’s a more concise version of the provided example:

public class DatabaseExample {

    @Resource(lookup = "java:/MySQLDS")
    private DataSource ds;

    public List<Integer> fetchDataFromDatabase() {
        String sql = "select * from customer";
        List<Integer> list = new ArrayList<>();

        try (Connection con = ds.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                list.add(rs.getInt("id"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}

Conclusion

In this tutorial, we have covered the importance of using try-with-resources to manage database connections in Java. By employing this feature, you can ensure that resources are automatically closed when they are no longer needed, leading to cleaner and more robust code.