how to write subquery using criteria

Question

please help me out writing criteria builder for this query

SELECT * 
FROM XYZ 
WHERE date_v < "2020/01" AND 
      id NOT IN (SELECT id FROM XYZ WHERE date_v = '2020/01')

i have looked at using subqueries in jpa criteria api but i am unable to figure it

I have tried using subquery and joins but it throwing different error after all i get to know that i need to get more clarity about query criteria usages. any help much appreciated

Answer 1

I have mentioned the corrections in comments for the answer but I feel providing full solution seems good and helps others:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Entity> query = cb.createQuery(Entity.class);
Root<Entity> root = query.from(Entity.class);
// subquery
Subquery<Long> subQuery = query.subquery(Long.class);
Root<Entity> subRoot = subQuery.from(Entity.class);
Predicate subPredicate = cb.equal(subRoot.get("date_v"), dateValue);

subQuery.select(subRoot.get("id")).where(subPredicate);

// query predicates
Predicate datePredicate = cb.lessThan(root.get("date_v"), dateValue);
Predicate notExistsPredicate = root.get("id").in(subQuery).not();

// query result
query.select(root).where(datePredicate, notExistsPredicate);
Query d = entityManager.createQuery(query);
List<Entity> resultList = d.getResultList()