JPA Criteria - Using between with Subquery

Asked

Viewed 161 times

2

"The numbers at the beginning of the consultations and the "criterias" were inserted to facilitate the explanation. (1-...)(2-...)(3-...)"

I need to do this on JPA criteria:

SELECT DISTINCT service.*
  FROM service AS service 
INNER JOIN service_item AS serviceItem ON serviceItem.fk_service = service.id
INNER JOIN follow_up AS followUp ON followUp.fk_service = service.id
WHERE service.fk_branch = 241 AND service.fk_employee = 403
  AND service.tenant_id = 246 AND customer.status = 'PROSPECT'
  AND service.see_in BETWEEN '2010-01-01' AND '2018-12-31'
  1-AND (SELECT DATEDIFF(MIN(serviceItem.arrival_date), MAX(serviceItem.departure_date))) = 6 
  2-AND (SELECT COUNT(followUp.id)) > 0
  3-AND (SELECT MAX(followUp.date)) BETWEEN '201-01-01' AND '2018-12-31';

The query was created with criteria like this:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Service> criteriaQuery = criteriaBuilder.createQuery(Service.class);
Root<Service> from = criteriaQuery.from(Service.class);
CriteriaQuery<Service> select = criteriaQuery.select(from).distinct(Boolean.TRUE);

1-Subquery<Long> subQueryDaysTraveling = select.subquery(Long.class);
Root<ServiceItem> subQueryFromDaysTraveling = subQueryDaysTraveling.from(ServiceItem.class);
subQueryDaysTraveling.select(criteriaBuilder.function("DATEDIFF", Long.class, criteriaBuilder.least(subQueryFromDaysTraveling.<Date>get("arrivalDate")),
                                                                                  criteriaBuilder.greatest(subQueryFromDaysTraveling.<Date>get("departureDate"))));
subQueryDaysTraveling.where(criteriaBuilder.equal(from.join("serviceItens"), subQueryFromDaysTraveling));

2-Subquery<Long> subQueryAmountFollowUp = select.subquery(Long.class);
Root<FollowUp> subQueryFromAmountFollowUp = subQueryAmountFollowUp.from(FollowUp.class);
subQueryAmountFollowUp.select(criteriaBuilder.count(subQueryFromAmountFollowUp));
subQueryAmountFollowUp.where(criteriaBuilder.equal(from.join("followUp"), subQueryFromAmountFollowUp));

3-Subquery<Date> subQueryLastFollowUp = select.subquery(Date.class);
Root<FollowUp> subQueryFromLastFollowUp = subQueryLastFollowUp.from(FollowUp.class);
subQueryLastFollowUp.select(criteriaBuilder.greatest(subQueryFromLastFollowUp.<Date>get("date")));
subQueryLastFollowUp.where(criteriaBuilder.equal(from.join("followUp"), subQueryFromLastFollowUp));

...
"Predicates simples, WHERE-ANDs"
...

1-predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subQueryDaysTraveling, daysTraveling)));

2-predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subQueryAmountFollowUp, amountFollowUp)));

3-predicates.add(criteriaBuilder.and(criteriaBuilder.between(subQueryLastFollowUp, lastFollowUpInitialDate, lastFollowUpFinalDate)));
...
Restante da criteria
...

The 3 subquerys are in the same model, what differs is the comparison, the two that use "Equal" work the one that uses "between" not and generates the following error:

Java.lang.ClassCastException: org.eclipse.persistence.internal.jpa.querydef.SubQueryImpl cannot be cast to org.eclipse.persistence.internal.jpa.querydef.ExpressionImpl

If I change predicate 3 to the following line, the error no longer occurs:

predicates.add(criteriaBuilder.between(subQueryFromLastFollowUp.<Date>get("date"), lastFollowUpInitialDate, lastFollowUpFinalDate));

This is the result in the generated query log. Subquery 1 and 2 perform the a select and compare with the table "serviceItem" the third represented by T5 only the between

1-AND ((SELECT DATEDIFF(MIN(t6.arrival_date), MAX(t6.departure_date)) FROM service_item t6 WHERE ((t6.id_service_item = serviceItem.id_service_item) AND (t6.tenant_id = ?))) = ?))
2-AND ((SELECT COUNT(t7.id) FROM follow_up t7 WHERE ((t7.id = followUp.id) AND (t7.tenant_id = ?))) = ?)) 
3-AND (t5.date BETWEEN ? AND ?))
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.