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
– khelwood, Commented May 15, 2019 at 11:14
you can also just change your preparedStatement based on the value of addressLine — Stultuske
– Stultuske, Commented May 15, 2019 at 11:14
@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
– khelwood, Commented May 15, 2019 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
– Thilo, Commented May 15, 2019 at 11:16
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
– Mark Rotteveel, Commented May 15, 2019 at 12:03

Mark Rotteveel · Accepted Answer · 2019-05-15 12:08:39Z

3

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);

answered May 15, 2019 at 12:08

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java PreparedStatement setting NULL parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related