Using @ElementCollection in CriteriaQuery (or Querying over the content of an @ElementCollection)

Question

public enum ReportStatus { 
    SUCCCEED, FAILED;
}

public class Work {
    @ElementCollection
    @Enumerated(EnumType.STRING)
    List<ReportStatus> reportStatuses;
}

Given the following structure, I'd like to perform a query to find all the work filtered by reportStatuses. It works fine with the following hql syntax :

public List<Long> queryHQL() {
    final String query = "SELECT w.id FROM Work w JOIN w.reportStatuses s WHERE s in (:rs)";

    final List<ReportStatus> reportStatuses = new ArrayList<ReportStatus>();
    reportStatuses.add(ReportStatus.FAILED);

    return this.entityManager.createQuery(query).setParameter("rs", reportStatuses).getResultList();
}

But I'd like to use the criteria API (jpa2), and can't figure out how to do it. Here is my closest try I think :

public List<Long> query() {
    final List<ReportStatus> reportStatuses = new ArrayList<ReportStatus>();
    reportStatuses.add(ReportStatus.FAILED);

    final CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();

    final CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
    final Root<Work> workModel = criteriaQuery.from(Work.class);

    final ListJoin<Work, ReportStatus> status = workModel.joinList("reportStatuses");

    final Predicate predicate = status.in(reportStatuses);

    criteriaQuery.where(predicate);
    criteriaQuery.select(workModel.<Long> get("id"));

    return this.entityManager.createQuery(criteriaQuery).getResultList();
}

I've also tried with the hibernate criteria API, but as the jpa2 one I've failed to find the correct the syntax.

Answer 1

I would use following CriteriaQuery syntax for doing the same thing.

EntityManager em = entityManagerFactory.createEntityManager();

final List<ReportStatus> reportStatuses = new ArrayList<ReportStatus>();
reportStatuses.add(ReportStatus.FAILED);

final CriteriaBuilder builder = em.getCriteriaBuilder();

final CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
final Root<Work> _work = criteriaQuery.from(Work.class);

/*final ListJoin<Work, ReportStatus> status = _work.joinList("reportStatuses");
final Predicate predicate = status.in(reportStatuses);
criteriaQuery.where(predicate);*/

final Expression<List<ReportStatus>> _status = _work.get(Work_.reportStatuses);
_status.in(reportStatuses);

criteriaQuery.select(_work.get(Work_.id));

List<Long> list =  em.createQuery(criteriaQuery).getResultList();

UPDATE

Thanks, but we do not use the generated metamodel. So unfortunately I can't try with your answer. :(

If you don't use Metamodel, just replace _work.get(Work_.reportStatuses) with _work.get("reportStatuses"). It'll work. :)

Answer 2

You can create this HQL.

String query = "SELECT w.id FROM Work w, IN(w.reportStatuses) s WHERE s = :rs";
return this.entityManager.createQuery(query).setParameter("rs", ReportStatus.FAILED).getResultList();

Answer 3

I'm confused. The call to in(..) returns a predicate but doesn't seem to actually enforce it (it doesn't seem to be integrated into the query—at least for me it returned all members of the root whether or not their collections intersected with reportStatuses. The debug log shows the simple query select distinct work0_.id as id18_ from Work work0_).

Also, why bother putting reportStatuses in a list if the caller is only interested in those matching one value? How would you do the query w/ just using ReportStatus.FAILED instead of constructing a list for it?