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:
- PreparedStatement createPreparedStatement(Connection conn)
- T processResultSet(ResultSet rs) [templated to return type of subclass’ choosing]
Which approach do you like better?