Java PreparedStatement setting NULL parameters

Question

Java PreparedStatement allows you to set a value for a given parameter or to set it to NULL

SELECT *
FROM ADDRESS
WHERE ADDRESS_LINE1 = ?

so we could write code to set this parameter:

 if (addressLine1 != null) {
      preparedStatement.setString(1, addressLine1);
    } else {
      preparedStatement.setNull(1, VARCHAR);
    }

however it is also declared that you should never compare a value to null using the = operator and actually use the IS operator for null comparisons.

Is the prepared statement actually swapping the = operator for IS under the covers here or is the null just being inserted still using the = operator? If the latter then when is it ever useful to use this and are you actually getting the expected outcome?

I am asking this on the back of the following stack overflow question

SQL is null and = null

I suspect that if you try and select addresses using null as a parameter you will get no results, because `= NULL` will never be true. — khelwood, May 15 '19 at 11:14
you can also just change your preparedStatement based on the value of addressLine — Stultuske, May 15 '19 at 11:14
so what is the point of: preparedStatement.setNull(1, VARCHAR); ever? — David, May 15 '19 at 11:15
@David To insert NULL, for one example. If you have a statement that inserts a row with a bunch of parameters, you can set some of those to NULL. — khelwood, May 15 '19 at 11:16
Well, it is not useful for `WHERE x = ?` but you may want to set the NULL to other places where it does work (`INSERT ? INTO X` for example) — Thilo, May 15 '19 at 11:16
I guess the only way out from this is for you to manually change your query based on the parameter you have, like `if (addressLine1 != null) query = "SELECT * FROM ADDRESS WHERE ADDRESS_LINE1 = ?"; else query = "SELECT * FROM ADDRESS WHERE ADDRESS_LINE1 IS NULL"; ` — vc73, May 15 '19 at 11:18
yes I do that currently however the number of combinations and permutations for this SQL statement make it quite tricky to then know the parameter indexes for the subsequent parameters that aren't null — David, May 15 '19 at 11:19
You could also change the query to handle the parameter being null or not, which is a bit messy, particularly with numbered (rather than named) parameters. — Alex Poole, May 15 '19 at 11:33
Note that `setString(1, null)` should work the same as `setNull(1, Type.VARCHAR)` in almost all cases, the `setNull` is more intended for primitive types, but is so generic it will also work for non-primitive types. — Mark Rotteveel, May 15 '19 at 12:03

score 3 · Accepted Answer · answered May 15 '19 at 12:08

No, the prepared statement is not swapping out = for IS. Your code is responsible for that. Just like you would be responsible for queries literally using something = NULL instead of something IS NULL.

The point of being able to set things to null, is for use in for example update or insert statement, or in cases where you do correctly use is or is distinct from.

It is also used for null-conditional checks like where ? is null or something = ? with

String something = "xyz"; // or String something = null;
statement.setString(1, something);
statement.setString(2, something);

Java PreparedStatement setting NULL parameters

1 Answers1