The following SELECT statement in code
stmt = DB.prepare("select id,name,state,zip " +
"from customer " +
"where " +
"phone = ? " +
"and active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();
where customerPhone and isActive are values you would pass in to the SELECT before its executed. Everything is fine when one day the value passed for customerPhone is NULL. For a database (Oracle is what I know most) a NULL will never be equal to NULL , the SELECT will not return rows where the customer.phone is NULL, leading to wrong results. The SELECT will have to be changed to
stmt = DB.prepare("select id,name,state,zip " +
"from customer " +
"where " +
"(phone IS NULL or phone = ?) " +
"and " +
"active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();
We could dynamically write the SELECT so that we don’t have to do the OR in the where clause, which could be expensive.
boolean hasPhone = false;
StringBuffer sqlQuery = new StringBuffer();
sqlQuery.append("select id,name,state,zip " +
"from customer " +
"where 1=1 ");
if (phoneNumber != null) {
sqlQuery.append("and phone=? ");
hasPhone = true;
} else {
sqlQuery.append("and phone IS NULL ");
}
sqlQuery.append("and isActive=?");
stmt = DB.prepare(sqlQuery.toString());
if (hasPhone) {
stmt.setString(1, phoneNumber);
stmt.setBoolean(2, isActive);
} else {
stmt.setBoolean(1, isActive);
}
stmt.execute();