Hello,
I probably haven't considered all the possible cases because I rarely use
IS NULL. When dealing with dynamic SQL, the code behind a typical "search
window" is more like :
if( StringUtils.isBotBlank( field.getText() ) )
//add condition on column
else
//do nothing (no null logic)
In case you need to write a DAO method that can match some column against
multiple values, null included, and don't want too much if-else logic, you
could use IS NOT DISTINCT FROM which works well with NULL.
I'd say it's not that bad the way it is now as I am not aware of any valid
use for a "column = null" predicate. Moreover, it seems that jdbc itself
tends to translate java null to SQL NULL with some horrible side effects
like
Long longValue = null;
stmt.setLong( longValue ); //NPE because it calls setLong(long) due to
autoboxing
but honestly, I'd choose to use isNull anyway before relying on any kind of
special treatement of NULL on the API side. Jooq makes dynamic where
clauses very easy to write so I'd say it doesn't hurt to write a pair of
if-else to decide between isNull or Equal and be 100% safe.
Another possibility would be to use a dedicated constand, like c# DbNull
that can be easily differentiated...
T_BOOK.idAuthor.eq( DB_NULL )
but it would almost certainly cause headaches with generic parameters for
little gain.
So in order of preference I'd say
1) Remove it if possible, one of the biggest strength of jooq resides in
its SQL-centric DSL that lets you guess the corresponding query without
obscure alteration.
2) Keep it the way it is now, after all it would make things backward
compatible and it's probably not used that often
3) As a last resort, add the boolean flag but imho configurable APIs tend
to become inelegant and confusing (Java SAX parsers are good examples).
Best
On Wednesday, December 12, 2012 9:06:48 PM UTC+1, Lukas Eder wrote:
>
> Hello group
>
> I have received interesting feedback about jOOQ's handling of NULL in
> comparison predicates. This is about a user who uses jOOQ for SQL
> rendering. They use jOOQ's support for named parameters as documented in
> the manual, here:
>
> http://www.jooq.org/doc/2.6/manual/sql-building/bind-values/named-parameters/
>
> The issue can be seen here:
> https://github.com/jOOQ/jOOQ/issues/2001
>
> As some of you may be aware of, the jOOQ .equal() and .notEqual() methods
> have a "special" behaviour, when passing a Java null value. This is
> documented in the Javadoc:
>
> If value == null, then this will return a condition equivalent to
> isNull()<http://www.jooq.org/javadoc/latest/org/jooq/Field.html#isNull()> for
> convenience. SQL's ternary NULL logic is rarely of use for Java
> programmers.
>
>
> Taken from:
> http://www.jooq.org/javadoc/latest/org/jooq/Field.html#equal(T)
>
> Now, while this may be quite convenient, as in 80% of the use cases, users
> really don't want to deal with SQL's ternary NULL/UNKNOWN logic, it can
> cause quite some confusion when users actually want to extract SQL and bind
> values from jOOQ queries, as jOOQ is then "swallowing" these particular
> null bind values.
>
> My questions to the group:
>
> 1. Do you find this "feature" really useful (as opposed to doing null
> checks and handling IS NULL manually)? Or do you think it should be
> removed?
> 2. Do you think a new Setting should be introduced to allow for
> distinguishing the two use-cases for jOOQ? (my favourite option, so far,
> even if I don't like adding to many settings)
> 3. Do you see any other solution to deal with the problem?
>
> Thanks for any feedback!
>
> Cheers
> Lukas
>
