Java Blog

After posting about the proper use of Prepared Statements last time, I have another topic today that deals with JDBC: the art of closing your database-ressources in a correct way.

Let’s assume you have a method that uses a Connection to a database, a Statement that executes a query and a ResultSet that holds the result of a query.

I’ll start with some practical examples, how you should not handle your database-resources.

In listing 1 no closing is done at all. That’s a really bad idead, folks!

 Java | 
 copy code |
?
 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = getConnection();
04
		Statement statement = connection.createStatement();
05
 
06
		ResultSet resultSet = statement
07
				.executeQuery("SELECT some crazy stuff FROM table");
08
 
09
		while (resultSet.next())
10
			System.out.println(resultSet.getString(1));
11
	}

The coder of listing 2 remembered the reasons why database-ressources should be closed, but if the statement.executeQuery()-call throws an SQLException, the closing of the resources at the end of the method is ignored.

 Java | 
 copy code |
?
 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = getConnection();
04
		Statement statement = connection.createStatement();
05
 
06
		ResultSet resultSet = statement
07
				.executeQuery("SELECT some crazy stuff FROM table");
08
 
09
		while (resultSet.next())
10
			System.out.println(resultSet.getString(1));
11
 
12
		resultSet.close();
13
		statement.close();
14
		connection.close();
15
	}

Listing 3 shows the correct use of a finally-block. The only thing that had been forgotten: resultSet.close(), statement.close() and connection.close() can throw SQLExceptions themselves!
So the Statement and the Connection remain open, if the resultSet.close()-call throws such an exception.

 Java | 
 copy code |
?
 
01
private void badExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			resultSet.close();
19
			statement.close();
20
			connection.close();
21
		}
22
	}

A correct example how resources should be closed can be found in listing 4: the close()-calls are exectued in a finally-block and wrapped itself in a try-catch-clause. Even if the resultSet.close()-call fails, your application tries to close the Statement and the Connection.

 Java | 
 copy code |
?
 
01
private void goodExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			try {
19
				resultSet.close();
20
			} catch (SQLException e) {
21
				e.printStackTrace();
22
			}
23
 
24
			try {
25
				statement.close();
26
			} catch (SQLException e) {
27
				e.printStackTrace();
28
			}
29
			try {
30
				connection.close();
31
			} catch (SQLException e) {
32
				e.printStackTrace();
33
			}
34
		}
35
	}

Though the example above is a correct one, it’s very ugly to have all that try-catch-stuff multiple times in your method. Suppose how the finally-block would look like, if you need more than one Connection, multiple Statement-objects and a couple of ResultSets in your method!

Unfortunately the classes mentioned above do not implement a common Closeable-interface with a close()-method. If they would do, a single method would be sufficient, that takes Closeables, checks upon != null and closes the Closeables (wrapped in a try-catch-block, of course). By the way: you can do that with the various InputStream-/OutputStream- and Reader-/Writer-classes.

Because the described Closeable is not available, we have to go a different way: we combine the concept of overloading methods with a feature the Java-guys provided with Java 5: Varargs!

A sample-implementation that uses overloading and varargs is listed below.

 Java | 
 copy code |
?
 
01
import java.sql.Connection;
02
import java.sql.ResultSet;
03
import java.sql.SQLException;
04
import java.sql.Statement;
05
 
06
/**
07
 * A class that closes any open database-resources.
08
 * 
09
 * @author Tino for http://www.java-blog.com
10
 * 
11
 */
12
public class JDBCCloser {
13
 
14
	/**
15
	 * Closes the provided ResultSets
16
	 * 
17
	 * @param resultSets
18
	 *            ResultSets that should be closed
19
	 */
20
	public static void close(ResultSet... resultSets) {
21
 
22
		if (resultSets == null)
23
			return;
24
 
25
		for (ResultSet resultSet : resultSets)
26
			if (resultSet != null)
27
				try {
28
					resultSet.close();
29
				} catch (SQLException e) {
30
					/* Do some exception-logging here. */
31
					e.printStackTrace();
32
				}
33
	}
34
 
35
	/**
36
	 * Closes the provided Statements
37
	 * 
38
	 * @param statements
39
	 *            Statements that should be closed
40
	 */
41
	public static void close(Statement... statements) {
42
		/*
43
		 * No need to create methods for PreparedStatement and
44
		 * CallableStatement, because they extend Statement.
45
		 */
46
 
47
		if (statements == null)
48
			return;
49
 
50
		for (Statement statement : statements)
51
			if (statement != null)
52
				try {
53
					statement.close();
54
				} catch (SQLException e) {
55
					/* Do some exception-logging here. */
56
					e.printStackTrace();
57
				}
58
	}
59
 
60
	/**
61
	 * Closes the provided Connections
62
	 * 
63
	 * @param connections
64
	 *            Connections that should be closed
65
	 */
66
	public static void close(Connection... connections) {
67
 
68
		if (connections == null)
69
			return;
70
 
71
		for (Connection connection : connections)
72
			if (connection != null)
73
				try {
74
					connection.close();
75
				} catch (SQLException e) {
76
					/* Do some exception-logging here. */
77
					e.printStackTrace();
78
				}
79
	}
80
}

It can be called very easily like that:

 Java | 
 copy code |
?
 
01
private void goodAndElegantExample() throws SQLException {
02
 
03
		Connection connection = null;
04
		Statement statement = null;
05
		ResultSet resultSet = null;
06
 
07
		try {
08
			connection = getConnection();
09
			statement = connection.createStatement();
10
 
11
			resultSet = statement
12
					.executeQuery("SELECT some crazy stuff FROM table");
13
 
14
			while (resultSet.next())
15
				System.out.println(resultSet.getString(1));
16
 
17
		} finally {
18
			JDBCCloser.close(resultSet);
19
			JDBCCloser.close(statement);
20
			JDBCCloser.close(connection);
21
		}
22
	}

Looks much more elegant, doesn’t it?

Why should database-resources be closed after use at all?

• Closing resources gives chance to the garbage collector to recollect memory as soon as possible. Keep in mind that especially ResultSets may occupy lots of memory depending on your query.


• If your JDBC-driver uses Connection- or Statement-pooling, non-closed Connections/Statements might not be returned to the pool and are therefore not marked as free. This may cause performance issues.


• A JDBC application does not only allocate resources in Java, but also on your database-server. So wasting resources affects your Java application as well as the database you’re connecting to.


• And last but not least from the Java docs : ” It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.”

I will end my posts with two additional comments:

• It’s essential that your resources are closed in the opposite order to that in which you opened them! This means: close ResultSets, then close Statements, then close Connections.


• It’s a good idead to close the resources on the same layer, where they had been created/occupied. This reduces headaches when some resources are being adressed that should be open (but are closed instead). It also decreases the risk that closing is forgotten completely.

Happy Coding,

Tino

tino JDBC, Java Java, JDBC