Dear Joachim,
I think you misunderstood my post
That's the simple SQL uses. However, more advanced uses tend to turn that
> perspective into an unavailable luxury.
>
> Once you start writing outer joins, null handling becomes really
> important. For example, to test whether a relationship exists, you can test
> any primary key column: if it's null, the corresponding related record
> didn't exist.
> This isn't important when doing interactive SQL but quite common in batch
> processing, where you try to shove as much work as possible into the query
> optimizer.
>
>
Of course, I am not discussing whether testing columns for NULL makes sense
or not because I know it *does* have its use. I was asking myself if there
were use cases in which translating java code such as "field.eq( null)" to
"field IS NULL" was a real gain. My personal answer is "no" because I
rarely need to mix both.
In a typical DAO method like findCustomerByCriteria( String name, Integer
idGroup, String countryCode ... ), (such as the first snippet you quoted),
passing "idGroup = Null" in parameter I'd expect the query to ignore the
group filter, not to search for customers having no group.
In case I'd really need to search for null explicitly, a simple if (idGroup
== null) { //use field.isNull() } else {//use field.eq} would not be a big
deal. I don't many examples where you'd take advantage of translating =NULL
to IS NULL automatically.
> The other usage is where a null value has a real semantics, different from
> that of an empty string or a zero value.
> In fact, I have several fields where zero means "upper limit is zero, i.e.
> don't allow anything", and null is "no upper limit, i.e. allow anything"
> (quantities to purchase, in this case).
> I even built a JTextField descendant that clearly differentiates between
> the two, and shows a different representation for a null value if it
> doesn't have focus.
>
>
Here again, I am not saying that NULLs have no use. My point is entirely at
the API level.
In you upper limit example, the fact that you wrote a special widget for
your nullable upper limit field is a good indicator that properly handling
NULL needs separate code. As a result, you probably don't really benefit
from an API mixing both cases.
You would not consider the absence of input in your GUI as the result of an
end-user wanting to list all products without a purchase limit, so you
can't just send the raw input directly from your GUI to a select query
saying "Oh yeah, jooq will translate this =NULL to IS NULL" ? You'd use a
dedicated field or a checkbox for this and some logic to decide if you need
to check for null OR match a value.
Imho, there aren't many use cases where you can make your life easier by
automatically transforming eq(null) to IS NULL, a NULL in a column is
rarely just a value like any other, rather a special case.like for example
the absence of a value. So it's pretty rare that you can use the same code
for selecting, filtering etc...
> For strings, the difference between null and an empty string isn't so easy
> to represent and in fact rarely if ever needed.
> In those cases where I do have a nullable string fields, it's always been
> an enumeration, and these I'm converting between DB representation and
> screen representation anyway.
>
Totally agreed, I heard that in the past, using NULLs people were using
nulls to save disk space, but considering space availability and
optimizations made to rdbms, I don't think it's relevant nowadays.
