JDBC and NULL

How do JDBC and SQLJ deal with null values in the subject and predicate of a SQL statement? My gut feeling was that null values in the subject should use setNull() and that setNull() was not appropriate for the predicate; one should use is (not) NULL syntax instead.

Deconstructing SQLJ/JDBC

I started with basic google research and didn't find a whole lot that was relevant. (the application uses SQLJ) After reaching a dead end there, I started to peel back the onion and look at some SQLJ classes to see what they really did.

After decompiling source and reading docs, the SQLJ process flows like this:

• Create class:
  • Source.sqlj --customize--+ Source.java --compile--+ Source.class
• Run class:
  • start --perform query--+ jdbc interface --invoke--+ sqlj jdbc wrapper implementation classes --invoke--+ jdbc --native--+ database

So at the end of the day everything goes through JDBC... that narrows it down a bit. The SQLJ wrapper class methods look like this:

public void setLongWrapper(int i, Long long1)
        throws SQLException
    {
        if(long1 == null)
            preparedStmt.setNull(i, -5);
        else
            preparedStmt.setLong(i, long1.longValue());
    }

Back to the problem

So the problem at hand is does the following SQLJ query work properly?

String val1 = null;
Long keyVal2 = null;
String keyVal3 = "ABC";

UPDATE Table t
SET col = :val1
WHERE
t.key2 = :keyVal2
AND t.key3 = :keyVal3

The answer is no. The query does not work as planned because keyVal2 in the predicate is being set to null via setNull() (see implementation above) and no updates will ever happen because no records in the database will satisfy the messed up predicate. How do I know this?

It is obvious from the wrapper classes that setNull() is being invoked on null values but how do I know that setNull() won't work in the predicate? I know this from two sources: 1) trial and error and 2) a sun bug submission.

In order to achieve the desired effect the query would need to have two forms (assuming the variables are dynamic).. one using IS NULL and one passing the parameter. If you have NULL possibilities in the predicate, have fun with the permutations! The following pseudo-code should make the point..

String val1 = [[dynamic];
Long keyVal2 = [[dynamic];
String keyVal3 = "ABC";

if (keyVal2 == null){
   UPDATE Table t
   SET col = :val1
   WHERE
   t.key2 IS NULL
   AND t.key3 = :keyVal3
} else {
   UPDATE Table t
   SET col = :val1
   WHERE
   t.key2 = :keyVal2
   AND t.key3 = :keyVal3
}