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.

dira · Accepted Answer · 2010-12-10T12:11:57.597

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

Thanks, but we do not use the generated metamodel. So unfortunately I can't try with your answer. :( — Raphaël Brugier, Dec 10 '10 at 11:41

score 3 · Answer 2 · edited Dec 27 '11 at 17:12

3

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();

edited Dec 27 '11 at 17:12

answered Aug 20 '11 at 21:12

user904084

31
1

I'd like to point that some JPA SQL supporting tools will mark this as an error when you put a `@ElementCollection` in the `in(~)`, DO NOT trust those tools (in that instance) this works flowlessly and quite fast if you index the right fields (This answer actuallt saved me a few hours of work). – le-doude Feb 28 '14 at 02:46
Where did you find this language feature? I pored all over the web trying to solve this exact problem, and this answer is the only mention of using the IN clause as a pseudo-table that I found. It worked flawlessly, though! – stevevls Mar 10 '14 at 21:10

score 1 · Answer 3 · answered Jul 05 '11 at 15:59

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?

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

3 Answers3

UPDATE

Linked