Writing JDBC code: resource cleanup and exception handling

No matter how nice an ORM tool used in the project, CMP, Hibernate or JPA, You will eventually have to start writing “low-level” JDBC code. Most often for performance reasons, sometimes just to utilize some database-vendor-specific feature.

Or calling stored procedures…

I’ve written lots of “low-level” JDBC code – most done in the days of 1.0..2.1 EJB CMP where some fastlane pattern was often necessary to deal with complex queries.

Most “query” code looks familiar to the sample below:

Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
	con = getConnection();
	ps = con.prepareStatement(sql);
	//... set parameters
	rs = ps.executeQuery();
	while (rs.next()) {
		//...handle result
	}
} catch (SQLException e) {
	//...exception handling
} finally {
	if (rs != null) {
		rs.close();
	}
	if (ps != null) {
		ps.close();
	}
	if (con != null) {
		con.close();
	}
}

This particular piece of code has always been a nuiscance to me, for the following reasons:

• I prefer variables to be declared as close to where they are first used as possible. In the sample they are all declared in the beginning of the method as in the good oooold C-days
• Visibility: con, ps and rs are all known outside the block where they are “allocated”, used and “deallocated”.
• Cludgy code… The cleanup part in the finally block looks bad. It looks even worse if we are to be 100% all resources are closed if a “close()” method fails (note: the sample does not handle this !)

Recently I came up with the following way of doing the same – to me it looks a lot nicer and cleaner:

try {
	Connection con = getConnection();
	try {
		PreparedStatement ps = con.prepareStatement(sql);
		try {
			//... set parameters
			ResultSet rs = ps.executeQuery();
			try {
				while (rs.next()) {
					//...handle result
				}
			} finally {
				rs.close();
			}
		} finally {
			ps.close();
		}
	} finally {
		con.close();
	}
} catch (SQLException e) {
	//...exception handling
}

Basically, whenever a resource has been successfully allocated (Connection, Statement, ResultSet), a new try-finally block is started where the resource is closed in the finally! The “outer” try-catch block is for exception handling only.

KISS 🙂