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!
