I'm stuck in a query, I am using Spring Data JPA, JpaRepository and JpaSpecificationExecutor. So I have to use the Predicate from CriteriaBuilder .
Right now I'm just doing:
Specifications<MyEntity> spec = Specifications.where(MySpec.isTrue()); List<MyEntity> myEntities = myRepository.findAll(spec);
where MySpec.isTrue ():
public static Specification<MyEntity> isTrue() { return new Specification<MyEntity>() { @Override public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) { SetJoin<MyEntity, JoinEntity> j = root.join(MyEntity.joinEntities, JoinType.LEFT); return cb.isTrue(j.get(JoinEntity_.attr)); } }; }
So this, of course, will result in this SQL:
SELECT e.* FROM MyEntity e LEFT OUTER JOIN JoinEntity j ON j.myEntityId = e.id WHERE j.attr = true
But I'm only interested in the unique MyEntity s MyEntity . Therefore, in JPQL I would write:
SELECT DISTINCT(e) FROM MyEntity e LEFT JOIN e.joinEntities j WHERE j.attr = true
Now my solution is:
List<MyEntity> myEntities = myRepository.findAll(spec); Set<MyEntity> entitiesSet = new HashSet<MyEntity>(myEntities);
There should be a nicer way;)
How does this work with CriteriaBuilder (and JpaSpecificationExecutor )?
Solution (s):
The first idea was simple:
public static Specification<MyEntity> isTrue() { return new Specification<MyEntity>() { @Override public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) { SetJoin<MyEntity, JoinEntity> j = root.join(MyEntity.joinEntities, JoinType.LEFT); query.distinct(true);
It works, but the curious destroys the meaning of these small parts of the Specification. So I came up with a solution using a subquery. This may take some extra time, but for me it does not matter at the moment:
public static Specification<MyEntity> isTrue() { return new Specification<MyEntity>() { @Override public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) { Subquery<JoinEntity> subquery = query.subquery(JoinEntity.class); Root<JoinEntity> subRoot = subquery.from(JoinEntity.class); subquery.select(subRoot); subquery.where(cb.isTrue(subRoot.get(JoinEntity_.attr))); subquery.groupBy(subRoot.get(JoinEntity_.myEntity)); return cb.exists(subquery); } }; }