sqljpaspring-data-jpaspring-datajpa-criteria

JPA Criteria API and parentheses


I have the following JPA Criteria API method:

public List<AggregationTask> findNonExpiredTasksBy(String entityName, Map<String, String> columnValues) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<AggregationTask> query = cb.createQuery(AggregationTask.class);
        Root<AggregationTask> root = query.from(AggregationTask.class);

        Predicate entityNamePredicate = cb.equal(root.get("edaEntityName"), entityName);

        Predicate columnPredicate = cb.conjunction();
        if (columnValues != null && !columnValues.isEmpty()) {
            List<Predicate> columnConditions = new ArrayList<>();

            for (Map.Entry<String, String> entry : columnValues.entrySet()) {
                Predicate condition = cb.and(
                        cb.equal(root.get("edaEntityColumnName"), entry.getKey()),
                        cb.equal(root.get("edaEntityColumnValue"), entry.getValue())
                );
                columnConditions.add(condition);
            }
            columnPredicate = cb.or(columnConditions.toArray(new Predicate[0]));
        }

        Predicate expiresAtPredicate = cb.greaterThan(root.get("expiresAt"), cb.currentTimestamp());

        query.where(cb.and(entityNamePredicate, columnPredicate, expiresAtPredicate));

        return entityManager.createQuery(query).getResultList();
    }

Which produces the following query:

select
   * 
from
   aggregation_tasks at1_0 
where
   at1_0.eda_entity_name =? 
   and 
   (
      at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
      or at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
      or at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
      or at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
      or at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
      or at1_0.eda_entity_column_name =? 
      and at1_0.eda_entity_column_value =? 
   )
   and at1_0.expires_at > localtimestamp

The problem is that I need to group the following condition pairs in parentheses using OR:

and 
(
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?) 
    or 
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?) 
    or 
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?) 
    or 
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?) 
    or 
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?) 
    or 
    (at1_0.eda_entity_column_name =? and at1_0.eda_entity_column_value =?)
)

What am I doing wrong in my code, and how can I achieve this?

UPDATED

I reimplement the method with JPQA Query based on strings concat:

public List<AggregationTask> findNonExpiredTasksBy(String entityName, Map<String, String> columnValues) {

        StringBuilder sql = new StringBuilder("SELECT * FROM aggregation_tasks WHERE eda_entity_name = :entityName ");

        sql.append("AND expires_at > CURRENT_TIMESTAMP ");

        if (MapUtils.isNotEmpty(columnValues)) {
            sql.append("AND (");
            int count = 0;
            for (Map.Entry<String, String> entry : columnValues.entrySet()) {
                if (count > 0) {
                    sql.append(" OR ");
                }
                sql.append("(")
                        .append("eda_entity_column_name = :columnName" + count)
                        .append(" AND eda_entity_column_value = :columnValue" + count)
                        .append(")");
                count++;
            }
            sql.append(") ");
        }

        Query query = entityManager.createNativeQuery(sql.toString(), AggregationTask.class);

        query.setParameter("entityName", entityName);

        if (columnValues != null && !columnValues.isEmpty()) {
            int count = 0;
            for (Map.Entry<String, String> entry : columnValues.entrySet()) {
                query.setParameter("columnName" + count, entry.getKey());
                query.setParameter("columnValue" + count, entry.getValue());
                count++;
            }
        }

        return query.getResultList();
    }

and the query works fine. So I still don't understand what I'm doing wrong with the version of this logic in the JPA Criteria API...


Solution

  • The fact that the query is build without parenthesis is due to the fact that the and operator has precedence over the or operator in logic (and in SQL). Putting parenthesis around the and terms does not modify the condition evaluation order, as it is in math when you have multiplications over additions.

    Please check also this issue: SQL Logic Operator Precedence: And and Or