As I was on Oracle’s JDBC tutorial page, I noticed it was using a Statement rather than a PreparedStatement. I grumbled to myself about how this is teaching people to develop using SQL Injection and decided to Google for an example so I could tweet about it.

I was looking for an example of using try-with-resources automatic resource management using PreparedStatements. I searched on google for “try with resources jdbc”. This didn’t go well. I found a lot more of the same example including one from Martjin and Ben whom I respect. It is even that anyone’s example is bad. It is just oversimplified and implies that using createStatement is more common than using prepareStatement.

I then searched for “try with resources preparedstatement” to be more specific about it and found:

• Informit does use a PreparedStatement but one without any binding variables.  Which means as an example, it isn’t much better.
• JavaCodeGeeks does the same.
• Someone on StackOverflow asked how best to do it and got an answer involving a nested try.  Which does work, but the nested try seem less readable than it needs to be.

I propose:

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn);  ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

return list;

}

private PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

PreparedStatement ps = conn.prepareStatement(SQL);

ps.setString(1,  "test");

return ps;

}

The StackOverflow post proposes:

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = conn.prepareStatement(SQL)) {

stmt.setString(1, "test");

try (ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

}

return list;

}

The StackOverflow answer is shorter.  I think the reason I like mine better is that is is easier to template out so the JDBC plumbing is only in a superclass.  Leaving us with

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn); ResultSet rs = stmt.executeQuery()) {

return processResultSet(rs);

}

return list;

}

The subclass then has two methods to implement:

1. PreparedStatement createPreparedStatement(Connection conn)
2. T processResultSet(ResultSet rs)  [templated to return type of subclass’ choosing]

Which approach do you like better?