CriteriaBuilder and or condition is not applying required/proper parenthesis

Question

I am new this board. I tried to find solution but could not find solution. In case it is available please share me that.

    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaQuery<DmdInventory> criteriaQuery = criteriaBuilder.createQuery(ItemInventory.class);
    Root<ItemInventory> itemRoot = criteriaQuery.from(ItemInventory.class);

    Predicate first = criteriaBuilder.equal(itemRoot.get("margin"), 1.0);
    first = criteriaBuilder.and(first, criteriaBuilder.equal(itemRoot.get("costWithoutExpense"), 1.0));
    first = criteriaBuilder.and(first, criteriaBuilder.equal(itemRoot.get("markup"), 1.0));

    Predicate second = criteriaBuilder.and(criteriaBuilder.equal(itemRoot.get("lastImportCost"), 2.0));
    second = criteriaBuilder.and(second, criteriaBuilder.equal(itemRoot.get("expenses"), 2.0));
    second = criteriaBuilder.and(second, criteriaBuilder.equal(itemRoot.get("profit"), 2.0));

    Predicate third = criteriaBuilder.and(criteriaBuilder.equal(itemRoot.get("totalCost"), 2.0));
    third = criteriaBuilder.and(third, criteriaBuilder.equal(itemRoot.get("cost"), 2.0));
    third = criteriaBuilder.and(third, criteriaBuilder.equal(itemRoot.get("quantity"), 2.0));
    Predicate allPredicate[] = { first, second, third };
    Predicate finalPredicate = criteriaBuilder.or(allPredicate);

    criteriaQuery.where(finalPredicate);
    List<ItemInventory> items = this.entityManager.createQuery(criteriaQuery).getResultList();

Current result

 select * from
    item_nventory inven 
where
    inven.last_import_cost=2.0 
    and inven.expenses=2.0 
    and inven.profit=2.0 
    or inven.total_cost=2.0 
    and inven.cost=2.0 
    and inven.quantity=2 
    or inven.margin=1.0 
    and inven.cost_without_expense=1.0 
    and inven.markup=1.0

Expected result

  select * from 
    item_nventory inven 
where
     inven.last_import_cost=2.0 
and inven.expenses=2.0 
and inven.profit=2.0 
or (inven.total_cost=2.0 
and inven.cost=2.0 
and inven.quantity=2 ) 
or (inven.margin=1.0 
and inven.cost_without_expense=1.0 
and inven.markup=1.0)

My final requirement will be something like below

 select * from 
    item_nventory inven 
where
    inven.last_import_cost=2.0 
and inven.expenses=2.0 
and inven.profit=2.0 
or ((inven.total_cost=2.0 
and inven.cost=2.0 
and inven.quantity=2 ) or (inven.quantity_on_hand>0))
or (inven.margin=1.0 
and inven.cost_without_expense=1.0 
and inven.markup=1.0)

Please help out me in this.

Answer 1

I think, problem is in gradual contruction of predicates. Try contruction below.

Predicate firstA = criteriaBuilder.equal(itemRoot.get("margin"), 1.0);
Predicate firstB = criteriaBuilder.equal(itemRoot.get("costWithoutExpense"), 1.0);
Predicate firstC = criteriaBuilder.equal(itemRoot.get("markup"), 1.0);
Predicate first = criteriaBuilder.and(firstA, firstB, firstC);

 . . .

Predicate finalPredicate = criteriaBuilder.or(first, second, third);

Or alternative solution with list - easy to add or remove another predicates (event dynamically)

List<Predicate> allPredList = new LinkedList<>();
{
  List<Predicate> firstPredList = new LinkedList<>();
  firstPredList.add(criteriaBuilder.equal(itemRoot.get("margin"), 1.0));
  firstPredList.add(criteriaBuilder.equal(itemRoot.get("costWithoutExpense"), 1.0));
  firstPredList.add(criteriaBuilder.equal(itemRoot.get("markup"), 1.0));
  allPredList.add(criteriaBuilder.and(firstPredList.toArray(new Predicate[0])));
}

 . . .

Predicate finalPredicate = criteriaBuilder.or(allPredList.toArray(new Predicate[0]));