JPA CriteriaBuilder的OR/AND条件未正确添加括号问题求助

Hey there! I see the issue you're facing—your CriteriaBuilder code isn't generating the parentheses needed to enforce the right logical grouping in your SQL. Let's break down how to adjust this to match your final desired query.

The Root Problem #

When you combine AND and OR predicates without explicit grouping, SQL's operator precedence (where AND evaluates before OR) will mess up your intended logic. To get the parentheses you need, you have to explicitly group related predicates using nested criteriaBuilder.and() and criteriaBuilder.or() wrappers.

Modified Code to Match Your Final Requirement #

Here's the adjusted code that will generate your target SQL with proper grouping:

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

// Build predicate group A: last_import_cost=2.0 AND expenses=2.0 AND profit=2.0
Predicate groupA = criteriaBuilder.and(
    criteriaBuilder.equal(itemRoot.get("lastImportCost"), 2.0),
    criteriaBuilder.equal(itemRoot.get("expenses"), 2.0),
    criteriaBuilder.equal(itemRoot.get("profit"), 2.0)
);

// Build predicate group B: total_cost=2.0 AND cost=2.0 AND quantity=2.0
Predicate groupB = criteriaBuilder.and(
    criteriaBuilder.equal(itemRoot.get("totalCost"), 2.0),
    criteriaBuilder.equal(itemRoot.get("cost"), 2.0),
    criteriaBuilder.equal(itemRoot.get("quantity"), 2.0)
);

// Build predicate C: quantity_on_hand>0
Predicate groupC = criteriaBuilder.greaterThan(itemRoot.get("quantityOnHand"), 0);

// Combine group B and C with OR: (B OR C)
Predicate groupBOrC = criteriaBuilder.or(groupB, groupC);

// Build predicate group D: margin=1.0 AND cost_without_expense=1.0 AND markup=1.0
Predicate groupD = criteriaBuilder.and(
    criteriaBuilder.equal(itemRoot.get("margin"), 1.0),
    criteriaBuilder.equal(itemRoot.get("costWithoutExpense"), 1.0),
    criteriaBuilder.equal(itemRoot.get("markup"), 1.0)
);

// Combine all top-level groups with OR: A OR (B OR C) OR D
Predicate finalPredicate = criteriaBuilder.or(groupA, groupBOrC, groupD);

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

What This Does #

• We explicitly create each logical group (groupA, groupB, groupC, groupD) to keep the logic clear and maintainable.
• We nest groupB and groupC inside an OR predicate to get the (B OR C) grouping you need.
• Finally, we combine all top-level groups with OR—the CriteriaBuilder will automatically add the necessary parentheses to enforce this grouping in the generated SQL.

Generated SQL (Matches Your Final Requirement) #

This code will produce exactly the SQL you're looking for:

select * from item_inventory 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)

内容的提问来源于stack exchange，提问作者akhilesh