CODEWITHSUNDEEP
javaoracle-databasejdbcnull
Peter Porter
Peter Porter

Reputation: 397

JDBC setNull() vs. Oracle "is null" produce different results

I am working on a PreparedStatement query in JDBC against an Oracle 11g database and find that the results of passing a null parameter differs from defining "is null" in the query itself.

For instance, this query:

String sql = "SELECT col1 FROM tbl WHERE col2 = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setNull(1, java.sql.Types.INTEGER);
ps.execute();

differs from this query:

String sql = "SELECT col1 FROM tbl WHERE col2 is null";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setNull(1, java.sql.Types.INTEGER);
ps.execute();

I am curious why this is the case, and how I can avoid defining two separate SQL statements to cover both "col2 = value" and "col2 is null" cases.

Upvotes: 4

Views: 5801

Answers (3)

Roland
Roland

Reputation: 21

If you are definitely using an Oracle database then you can use this statement, it will also accept NULL as equal to NULL:

select col1 from tbl where decode(col2, ?, 1, 0)=1

Is not VERY readable but better than the expression above. Also, you avoid giving the same value twice to the prepared statement. Works for me on Oracle 11 or 12.

Upvotes: 0

Lai Xin Chu
Lai Xin Chu

Reputation: 2482

I believe that ps.setNull(1, java.sql.Types.INTEGER) was meant for inserting NULL data values into the database.

You should use IS NULL if you are searching for NULL data values in an sql query.

After all, doing col2 = NULL won't work either because you cannot compare for NULL values using =

Upvotes: 3

Thilo
Thilo

Reputation: 262584

This has nothing to do with Java, really, this is how NULL works in Oracle.

NULL is always false when compared to anything (even to NULL), you have to use IS NULL.

This will also return no rows:

SELECT col1 FROM tbl WHERE col2 = NULL

or even

SELECT col1 FROM tbl WHERE NULL = NULL

Upvotes: 6

Related Questions