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:

• Security loopholes: without very judicious validation of any parameters that you use to build up the SQL string, you are liable to various types of SQL insertion attacks;
• Performance issues: every time you execute the query, the database system has to parse it. A typical application will execute essentially the same SQL numerous times with different parameters. Having to re-parse what is essentially the same query each time this thus wasteful.

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:

• compile the statement into a PreparedStatement object;
• as many times as we want, execute this prepared statement, passing in the required parameter value (in this case, the user name) each time.

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).