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...
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