PreparedStatement and NULLs in the WHERE Clause

The java.sql.PreparedStatement comes with a method for setting nulls as values for the parameters: setNull(int, int). This method is very tricky and does not work as expected when it comes to the SQL WHERE clause.

This article talks about a common pitfall that may developers fall in and never test. More details about the prepared statement interface can be found at http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

The Problem

Consider the following query SELECT * FROM EMPLOYEES WHERE MANAGED_ID = ?. This query returns all employees for a given manager id. The following code fragment illustrates how this query is used to retrieve all employees for manager id 100.

  PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM EMPLOYEES WHERE MANAGED_ID = ?");
  statement.setLong(1, 100L);
  ResultSet resultSet = statement.executeQuery();

The above code works well for any manager id. But what if we would like to select all employees that do not have a manager, that is, the manager id column is set to NULL (SQL NULL)? If we do the following we will get nothing back, even if there are employees with no manager assigened.

  PreparedStatement statement = connection.prepareStatement(
      "SELECT * FROM EMPLOYEES WHERE MANAGED_ID = ?");
  statement.setNull(1, Types.INTEGER);
  ResultSet resultSet = statement.executeQuery();

Why? The advantage of the prepared statement is that the statement is compiled once and it is executed many times. This provides performance benefits but comes with a cost. That is, once the statement is compiled, the only things that can change are the parameters' values. The equals sign stays there. This instead of having our SQL statement WHERE MANAGER_ID IS NULL we have WHERE MANAGER_ID = NULL, which does not yield the same results.

The Solution

The solution is very simple. We have to modify the string version of the SQL statement to handle NULLs. Assume we have the following method that returns a list of employees for a given manager id, where a connection is already established and available to the same method.

  public List getEmployeesWithManagerId(Long managerId)
                                        throws SQLException {
    List employees = new ArrayList();
    String select = "SELECT * FROM EMPLOYEES WHERE MANAGER_ID "
                    + (managerId == null?"IS NULL":"= ?");
    PreparedStatement statement = 
        connection.prepareStatement(select);
    if(managerId != null){
      statement.setLong(1, 100L);
    }
    ResultSet resultSet = statement.executeQuery();
    while(resultSet.next()){
      // Get the employee details and add it to the list
    }
    return employees;
  }

Why are we still using the prepared statement? Can we use the select statement instead and concatenate the values? Yes you can but not suggested as the prepared statement also provides protection against SQL injections.

Conclusion

Even though very simple, this is a common bug which can be easily iron out with proper testing. Since the database columns can accept NULLs, refrain from using Java primitives and use their wrappers instead. Note that the result set getXXX methods return the primitive values which can be tricky too. A 0 (zero - the default value for most of the primitives) is returned if the database field was NULL. In that case make sure to use to the method wasNull().