I want to perform a SELECT with several conditions, using Ibator's generated Example classes.
As described here in the usage notes, it's fairly easy to use criteria.andFieldIsSomething() and example.or(example) to compose a WHERE clause with multiple OR and AND operators.
I could just write this:
example.createCriteria().andIntegerIsEqualTo(int).andDateIsEqualTo(someday);
example.or(example.createCriteria().andIntegerIsNull().andDateIsEqualTo(someday));
example.or(example.createCriteria().andIntegerIsEqualTo(int).andDateIsNull());
example.or(example.createCriteria().andIntegerIsNull().andDateIsNull());
But first, it's kind of tedious and redundant, and it would output the following code:
SELECT * FROM zeTable
WHERE (integer = int AND date = someday)
OR (integer IS NULL AND date = someday)
OR (integer = int AND date IS NULL)
OR (integer IS NULL AND date IS NULL);
A more elegant (and probably efficient) way of writing that would be:
SELECT * FROM zeTable
WHERE (integer IS NULL OR integer = int)
AND (date IS NULL OR date = someday);
The test for NULL prevents me from using the .andFieldSomethingIn(List values) method efficiently, and while I kept this example simple, the code I have to write implies crossing 5 or 6 fields like that, which could amount to 36 different Criteria.
This seems ridiculous to me, so I assume there must be a better way. Can someone suggest something ?
Tl;dr: is it not possible. Deal with it.
I spend some time investigating this, and this question was posted here for over a month. It seems there is no way to this with Ibator's Criteria and Example classes.
My guess is, it's not meant for that. Workaround: if you want complex clauses and effciency, write your own logic in the SQL_MAP.
It's what I ended up doing. If anyone else can offer a better answer later, I'll accept it.