Java Prepared Statement with IS NULL

Question

Let say I have a MySQL table like this:

VARCHAR 100 | VARCHAR 100 | VARCHAR 100

[ ID ] [ NAME ] [ NICKNAME ] [ FAVORITE_COLOR ]

1 John Johnny RED

2 Eric NULL GREEN

I want to select the 2nd row where Nickname is NULL using the following prepared statement in Java:

statement = "SELECT * FROM my_table WHERE name = ? AND nickname = ?"

prepared = connection.prepareStatement(statement)
prepared.setString(1, "Eric")
prepared.setString(2, null)

result = prepared.executeQuery()

This query does not work. The result set is empty.

Another option I tried is:

statement.setNull(2,java.sql.Types.VARCHAR)

This also does not work and the result set is empty.

Lastly, I tried a different SQL but its clearly wrong and returns too many rows (because it's not strict enough):

statement = "SELECT * FROM my_table WHERE name = ? AND (nickname IS NULL) OR (nickname = ?)"

This selects too many rows in my case.

So my question is: How, using a Java PreparedStatement, can I select a row using the MySQL 'IS NULL'?

Answer 1

This is a well known limit of SQL databases. For most databases, you must write ... WHERE field IS NULL to test for NULL values of field. Neither ... field = NULL, nor ... field = :param where :param is a parameter of a parameterized query ever match a NULL value.

So the only solution is to explicitely write field IS NULL is your query. Said differently you need 2 different queries, one for non null values (and only one parameter) and the other one for null values (and 2 parameter).

statement = "SELECT * FROM my_table WHERE name = ? AND nickname = ?";
statement = "SELECT * FROM my_table WHERE name = ? AND nickname IS NULL";

You can use the trick (beware of parentheses as explained by JBNizet)

statement = "SELECT * FROM my_table WHERE name = ? AND (nickname = ? OR nickname IS NULL)";

If you want to still use 2 parameters for the query asking for NULL values.

Answer 2

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

For null to work update your query to: statement = "SELECT * FROM my_table WHERE name = ? AND nickname <=> ?"