Using prepared statements with JDBC

So far, we have executed a SQL statement supplied "on the fly" to the Statement.execute() method. Although it's occasionally necessary to do this, there are some serious problems with passing an arbitrary SQL string to the database in this way:

What is a prepared statement?

A prepared statement is a template for a SQL query in which the place of any variable parameters is unambiguously signalled, and the actual parameter values are supplied when the query is run. For example:

SELECT Id, FirstName, Surname FROM Users WHERE UserName = ?

Here, the question mark represents "the value that I will supply when I run the query". The idea is that we:

The PreparedStatement class in Java

In Java, the above steps look as follows:

Connection c = getConnection();
...
public int getUserId(String userName)
    throws SQLException, NoSuchUserException {
  String sql = "SELECT Id FROM Users WHERE UserName = ?";
  PreparedStatement ps = c.prepareStatement(sql);
  try {
    ps.setString(1, userName);
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
      return rs.getInt(1);
    }
    throw new NoSuchUserException();
  } finally {
    try { ps.close(); } catch (Exception ignore) {}
  }
}

In this case, we prepare the statement each time we need it. This gives us the security protection, but not any performance gain. Alternatively, we could hold on to the same PreparedStatement object between calls (and only close the ResultSet).