try-with-resources and jdbc without sql injection

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?

2 thoughts on “try-with-resources and jdbc without sql injection

  1. you have to pass the list to the processResultSet method as your current code always returns an empty list in the last code listing.

Leave a Reply

Your email address will not be published. Required fields are marked *