CODEWITHSUNDEEP
javajdbcprepared-statement
yaylitzis
yaylitzis

Reputation: 5534

Prepared statement with set null in query doesn't return any record

I use prepared statements to read/write data in my DB (SQLite). In my table INVENTORY, there are records which have null value in the column paleta (the column is defined as VARCHAR in the table). I want to select these records and I tried:

sq = "SELECT * FROM INVENTORY WHERE paleta = ? AND product = ? AND lot = ?";
//...
stm = c.prepareStatement(sq);
stm.setNull(1, java.sql.Types.VARCHAR);
stm.setString(2, "theIdOftheProduct");
stm.setString(3, "theLotOftheProduct");

ResultSet rs = stm.executeQuery();

The above query doesn't return anything.. I removed the paleta = ? and I get the records I want.. How can I define the query like SELECT * FROM INVENTORY WHERE paleta is null etc.. using the query parameters?

Upvotes: 1

Views: 1057

Answers (2)

Mark Jeronimus
Mark Jeronimus

Reputation: 9543

I found my answer in https://stackoverflow.com/a/4215618/1052284

You'll have to decide upon an unused value. I simply kept it at '' since I don't have empty values.

sq = "SELECT * FROM INVENTORY WHERE IFNULL(paleta, '') = ? AND product = ? AND lot = ?";
//...
stm = c.prepareStatement(sq);
stm.setString(1, ""); // '' for NULL, otherwise a specific value
stm.setString(2, "theIdOftheProduct");
stm.setString(3, "theLotOftheProduct");

But beware if you many queries, it's VERY slow. I clock in at about 4000 times slower, on average, than queries without IFNULL. ~50ms instead of microseconds.

Upvotes: 0

Eran
Eran

Reputation: 393781

What you are trying to do is equivalent to writing SELECT * FROM INVENTORY WHERE paleta = NULL ..., which doesn't work.

Since you are essentially searching for rows having a constant value in the paleta column (which happens to be NULL), you can eliminate the first query parameter and explicitly check for null:

sq = "SELECT * FROM INVENTORY WHERE paleta IS NULL AND product = ? AND lot = ?";

stm = c.prepareStatement(sq);
stm.setString(1, "theIdOftheProduct");
stm.setString(2, "theLotOftheProduct");

Upvotes: 4

Related Questions