Multiple OR statements in ORMLite

Question

According to this post :Multiple, combined OR conditions in ORMLite

I want to make queries that can depend on ArrayList of condition and Strings in function

List<String> cat = new ArrayList<String>();
List<Person> line = new ArrayList<>();
Dao<Person, Integer> personDAO = getHelper().getPersonDAO();
cat.add("category1");
cat.add("category2");
QueryBuilder<Person, Integer> queryBuilder = personDAO.queryBuilder();
Where<Person, Integer> where = queryBuilder.where();

if(filterOn)
    for (int i = 0; i <  cat.size(); ++i)
       where.eq("category", cat.get(i)).or().eq("name", cat.get(i));
where.or(cat.size());

if(searchBar.size()!=0){
    for (int i = 0; i <  cat.size(); ++i)
       where.eq("category", cat.get(i)).or().eq("name", cat.get(i));
    where.like("category", constrainName).or().like("name", constrainName);
    where.or(cat.size()+1);
}
line = personDAO.queryBuilder().query();

But Ive got throw out of application without any exception whenever the first iteration of loop is finished

UPDATE: I solve my problem. solution was:

for (int i = 0; i <  cat.size(); ++i)
    where.eq("category",cat.get(i)).or().eq("name",cat.get(i));
where.or(cat.size());

if(data){
    where.like("category", t).or().like("name", t);
    l = personDAO.query(where.and(2).prepare());
} else
    l = personDAO.query(where.or(1).prepare());

Answer 1

But I've got throw out of application without any exception whenever the first iteration of loop is finished

Ok, first off, unless the JVM quit on you there was an exception, just that it was not properly caught or logged somehow. If you enable logging I suspect the exception would have explained the problem.

There are number of problems with your code. I'm not sure which one (or something else) is causing the exception to be thrown. The first problem is:

if (filterOn) {
    for (String entry : cat) {
       where.eq("category", entry);
       where.eq("name", entry);
    }
    where.or(cat.size() * 2);
}

The fix here is to only add the ORs if the filterOn is set. Otherwise you would have generated an invalid query. I recommend always using {} so you can see these sorts of issues. I do one call to where.or(...) with the size * 2 for the category and name.

if(searchBar.size()!=0){

So this seems to be adding the category and name equals comparisons in as well. If filterOn and !searchBar.isEmpty() can be true at the same time then you are going to get duplicate WHERE entries which is inefficient. However, it might also generate an incomplete query because if you add a number of OR entries that match the category/name and then do it again, you need one more OR to link the two sets. For example:

if (filterOn) {
   where.eq("category", entry);
   where.eq("name", entry);
   where.or(2);
}
if (!searchBar.isEmtpy()) {
   where.eq("category", entry);
   where.eq("name", entry);
   where.like("category", entry);
   where.like("name", entry);
   where.or(4);
}
// MISSING LAST OR HERE if both conditions are true

This code would generate an invalid query exception because you are missing the last OR which ties the two sets of comparisons together.

What I would recommend is to count the number of ORs added to the query:

int orCount = 0;
if (filterOn) {
   // loop here
   for loop {
       where.eq("category", entry);
       where.eq("name", entry);
       orCount += 2;
   }
}
if (!searchBar.isEmtpy()) {
   for loop {
       where.eq("category", entry);
       where.eq("name", entry);
       orCount += 2;
   }
   where.like("category", entry);
   where.like("name", entry);
   orCount += 2;
}
where.or(orCount);

This will put the appropriate number of ORs into your query at the end.

Couple other comments:

• cat should be cats at least to show that it is storing multiple. Same for line.
• Consider using the for (String cat : cats) type of for instead of using for (int i = 0; ...) { ... cats.get(i) ... }. The first type is less prone to errors with the counter.