Using try-with-resources to close database connections

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

This tutorial shows how to use a Java SE 7 feature called try-with-resources to handle Connection, Statement and ResultSet objects which are retrieved from a JBoss / WildFly Datasource.
Prior to Java SE 7, developers needed a finally block to ensure that a resource is closed regardless of whether the try statement successfully completed or not. This required quite a long piece of code to handle safe closing of every objects, especially in the case of a database connection object. 

An example is worth 1000 words:

@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) {  }
    }
}

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!


Advertisement

Related articles available on mastertheboss.com

How to deploy a DataSource in jboss at application level ?

  Do you need to deploy your DataSource along with your Enterpri

JBoss Datasource HA

This tutorial has been updated for the new release of JBoss Appli

JBoss run out of Connections ?

Have you got No ManagedConnections available error message ? well

How to connect to a DataSource from a remote client?

  If you want to connect to your JBoss Connection Pool from a re

JBoss Datasource configuration

A Datasource is a Java Naming and Directory Interface (JNDI) obje

How do I get the list of Datasources available ?

JBOSS AS 4/5 Users You can use either twiddle: $ twiddle.sh que