How can NULL and regular values be handled without using different prepared statements?

Question

Consider this simple method:

public ResultSet getByOwnerId(final Connection connection, final Integer id) throws SQLException {
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM MyTable WHERE MyColumn = ?");
    statement.setObject(1, id);
    return statement.executeQuery();
}

The example method is supposed to select everything from some table where a column's value matches, which should be simple. The ugly detail is that passing NULL for the id will result in an empty ResultSet, regardless how many rows there are in the DB because SQL defines NULL as not euqaling anyting, not even NULL. The only way to select those rows I know of is using a different where clause:

SELECT * FROM MyTable WHERE MyColumn IS NULL

Unfortunately the only way to make the method work properly in all cases seems to be to have two entirely different execution pathes, one for the case where the id argument is null and one for regular values.

This is very ugly and when you have more than one column that can be nulled can get very messy quickly. How can one handle NULL and normal values with the same code path/statement?

Answer 1

I haven't tried this, but the way to send nulls to preparedStatements is to use the setNull() option

   PreparedStatement statement = connection.prepareStatement("SELECT * FROM MyTable WHERE MyColumn = ?");
    if ( id == null)
       statement.setNull(1, java.sql.Types.INTEGER);
    else    
      statement.setObject(1, id);
    return statement.executeQuery();

EDIT : Thanks for the responses @Gabe and @Vache. It does not look like there is an easy way to do this other than taking a more longwinded approach to dynamically create the preparedstatment.

Something like this should work --

String sql = " SELECT * FROM MyTable WHERE " + (id==null)?"MyColumn is null":"MyColumn = ?" ;
       PreparedStatement statement = connection.prepareStatement(sql);
        if ( id != null)
          statement.setObject(1, id);
        return statement.executeQuery();