How to deal with (maybe) null values in a PreparedStatement?

java null prepared-statement
Zeemee picture Zeemee · Nov 18, 2010 · Viewed 27.9k times · Source

The statement is

SELECT * FROM tableA WHERE x = ?

and the parameter is inserted via java.sql.PreparedStatement 'stmt'

stmt.setString(1, y); // y may be null

If y is null, the statement returns no rows in every case because x = null is always false (should be x IS NULL). One solution would be

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)

But then i have to set the same parameter twice. Is there a better solution?

Thanks!

Answer

Paul Tomblin picture Paul Tomblin · Nov 18, 2010

I've always done it the way you show in your question. Setting the same parameter twice is not such a huge hardship, is it?

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);

Related questions

How to avoid null checking in Java?

I use object != null a lot to avoid NullPointerException. Is there a good alternative to this? For example I often use: if (someobject != null) { someobject.doCalc(); } This checks for a NullPointerException for the someobject object in the above snippet.

Read More
How to check if my string is equal to null?

I want to perform some action ONLY IF my string has a meaningful value. So, I tried this. if (!myString.equals("")) { doSomething } and this if (!myString.equals(null)) { doSomething } and this if ( (!myString.equals("")) && (!myString.equals(null))) { doSomething } …

Read More
Why is my Spring @Autowired field null?

Note: This is intended to be a canonical answer for a common problem. I have a Spring @Service class (MileageFeeCalculator) that has an @Autowired field (rateService), but the field is null when I try to use it. The logs show …

Read More