CODEWITHSUNDEEP
javamysqlprepared-statement
John Anderson
John Anderson

Reputation: 97

Java Prepared Statement with IS NULL

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'?

Upvotes: 5

Views: 6307

Answers (3)

jbaptperez
jbaptperez

Reputation: 696

@John Anderson, you validated an answer which actually doesn't work. Let me explain.

Let's take back the validated comparison and focus on the second WHERE clause:

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

In the case ? = 'something', you have:

AND (nickname = 'something' OR nickname IS NULL)

-- which is equivalent to:
AND (NULL OR nickname IS NULL)

-- which is equivalent to:
AND (nickname IS NULL)

So you always get the line with NULLs as nickname, whatever the ? value is (side effect when ? is actually not NULL). Actually, you always get the NULL lines AND the ones you are looking for in the case ? is not NULL.

Below is one way to actually do the trick (tested personally):

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

Details of the 2 main cases:

  1. ? is NULL:
AND ((NULL IS NULL AND nickname IS NULL) OR (NULL IS NOT NULL AND nickname = ?))

-- which is equivalent to:
AND ((TRUE AND nickname IS NULL) OR (FALSE AND nickname = NULL))

-- which is equivalent to:
AND ((nickname IS NULL) OR (FALSE AND NULL))

-- which is equivalent to:
AND ((nickname IS NULL) OR (FALSE))

-- which is equivalent to:
AND (nickname IS NULL)

So you exactly get lines where the nickname is NULL.

  1. ? = 'something':
AND (('something' IS NULL AND nickname IS NULL) OR ('something' IS NOT NULL AND nickname = 'something'))

-- which is equivalent to:
AND ((FALSE AND nickname IS NULL) OR (TRUE AND nickname = 'something'))

-- which is equivalent to:
AND (FALSE OR (nickname = 'something'))

-- which is equivalent to:
AND (nickname = 'something')

So you exactly get lines where the nickname = 'something'.

Upvotes: 0

Serge Ballesta
Serge Ballesta

Reputation: 148910

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 explicitly write field IS NULL in 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.

Upvotes: 3

Gabe Gates
Gabe Gates

Reputation: 1000

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 <=> ?"

Upvotes: 3

Related Questions