Remove template from db code

Question

It seems that every time I want to execute a db request, I need to write the following:

Connection conn = null; Statement stmt = null; ResultSet rset = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); // ...set stmt params rset = stmt.executeQuery(); while(rset.next()) { // Do something interesting } } finally { try { if (rset != null) rset.close(); } catch(SQLException e) { } try { if (stmt != null) stmt.close(); } catch(SQLException e) { } try { if (conn != null) conn.close(); } catch(SQLException e) { } } 

Is this really the best way to do this? Is there a way to at least reduce some of the clutter?

Edited: as some of the comments pointed out, this code was not long enough.

Answer 1

If you already have a DataSource, you can use Spring JdbcTemplate for:

• significantly reduced template code
• have a good hierarchy of sql exceptions to handle common database problems with specific runtime exceptions
• (later followed by Spring) uses declarative transaction management

If at the moment it seems too heavy, you can implement some useful classes and methods for the "template part". Studying the source of JdbcTemplate should help in this case.

Answer 2

Yes, use the Sping JDBC template classes ( http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html ).

Or, if you are not using Spring, copy the template that they use in your own code.

Answer 3

DbUtils is a very useful infrastructure, I used it for small projects where Spring and Hibernate are redundant. It is also capable of matching objects.

Answer 4

Make helper method?

 public class DBHelper { public static Object run(string sql, List params, ResultHandler rhandler) { Connection conn = null; Statement stmt = null; ResultSet rset = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); int i = 0; for(Object p in params) { stmt.setObject(++i, p); } rset = stmt.executeQuery(); return rhandler.handle(rset); } finally { try { rset.close(); } catch(Exception e) { } try { stmt.close(); } catch(Exception e) { } try { conn.close(); } catch(Exception e) { } } } } public interface ResultHandler { public Object handle(ResultSet) } public class Test { public static void main(String[] args) { String s = (String)DBHelper.run("select * from mytable where col = ?", Arrays.asList({"foo"}), new ResultHandler { public Object handle(ResultSet r) { r.first(); return r.getString("col2"); } }(); } } 

Answer 5

I would use hibernate or JPA to reduce clutter ...