Multiple, combined OR conditions in ORMLite

Question

I like to have a query like this:

select data from table
 where (x > 1 and x < 100)
    or (x > 250 and x < 300)

In ORMlite, that's possible using this code:

final QueryBuilder<Data,Integer> qb = queryBuilder();
final Where<Data, Integer> w = qb.where();

w.or(
    w.gt("x", 1).and().lt("x", 100),
    w.gt("x", 250).and().lt("x", 300)
)

While thats great if one knows the conditions beforehand & at the time of coding, I need the conditions to be dynamically added.

Basically that method public com.j256.ormlite.stmt.Where<T,ID> or(com.j256.ormlite.stmt.Where<T,ID> left, com.j256.ormlite.stmt.Where<T,ID> right, com.j256.ormlite.stmt.Where<T,ID>... others) is not enough. It needs another or method that supports a ArrayList of Where conditions.

Thanks for any suggestions.

Answer 1

Do you understand what the ... part of the declaration means? It means that you can pass in an array (and that the compiler will construct an array for you if you just specify values).

So just create a list if you want, then convert it to an array (for all but the first condition) and then call the method. You may well want to make a static method to do the last part easily:

public static <T, ID> void or(Where<T, ID> target,
                              List<Where<T, ID>> conditions)
{
    // TODO: Argument validation
    Where<T, ID> first = conditions.get(0);
    Where<T, ID> second = conditions.get(1);
    List<Where<T, ID>> rest = conditions.subList(2, conditions.size());
    // You'll to suppress a warning here due to generics...
    Where<T, ID>[] restArray = rest.toArray(new Where[0]);
    target.where(first, second, restArray);
}

Answer 2

While thats great if one knows the conditions beforehand & at the time of coding, I need the conditions to be dynamically added.

In ORMLite Where.or(Where<T, ID> left, Where<T, ID> right, Where<T, ID>... others) is a bit of a syntax hack. When you call:

w.or(
    w.gt("x", 1).and().lt("x", 100),
    w.gt("x", 250).and().lt("x", 300)
);

What the or() method gets is:

w.or(w, w);

You really could rewrite it as:

w.gt("x", 1).and().lt("x", 100);
w.gt("x", 250).and().lt("x", 300);
w.or(w, w);

The or method there is only using the arguments to count how many clauses it needs to pop off of the stack. When you call gt and lt and others, it pushes items on a clause stack. The and() method pulls 1 item off the stack and then takes another item in the future. We do these syntax hacks because we want to support linear, chained, and argument based queries:

w.gt("x", 1);
w.and();
w.lt("x", 100);

versus:

w.gt("x", 1).and().lt("x", 100);

versus:

w.and(w.gt("x", 1), w.lt("x", 100));

But this means that you have the power to simplify your code immensely by using the Where.or(int many) method. So in the or example above can also be:

w.gt("x", 1).and().lt("x", 100);
w.gt("x", 250).and().lt("x", 300);
// create an OR statement from the last 2 clauses on the stack
w.or(2);

So you don't need the conditions list at all. All you need is a counter. So you could do something like:

int clauseC = 0;
for (int i : values) {
    if (i == 1) {
        w.le(C_PREIS, 1000);
        clauseC++;
    } else if (i == 2) {
        w.gt(C_PREIS, 1000).and().le(C_PREIS, 2500);
        clauseC++;
    } else if (i == 3) {
        w.gt(C_PREIS, 2500).and().le(C_PREIS, 5000);
        clauseC++;
    } else if (i == 4) {
        w.gt(C_PREIS, 5000).and().le(C_PREIS, 10000);
        clauseC++;
    } else if (i == 5) {
        w.gt(C_PREIS, 10000);
        clauseC++;
    }
}
// create one big OR(...) statement with all of the clauses pushed above
if (clauseC > 1) {
    w.or(clauseC);
}

If i can only be 1 to 5 then you can just use values.size() and skip the clauseC. Notice that if we are only adding one clause then we can skip the OR method call entirely.

Oh, and the following statement will not work:

target.or().raw(first.getStatement());

because target and first are the same object. first.getStatement() dumps the entire SQL WHERE clause which I don't think is what you want.