Criteria Query select join

Question

I need help to convert this JPQL query in Criteria Query:

SELECT u.documentList FROM Unit u WHERE u.id = :id

this is what i have tried:

CriteriaQuery<Document> query = builder.createQuery(Document.class); Root<Unit> root = query.from(Unit.class); Join<Unit, Document> join = root.join(Unit_.documentList); query.select(join); query.where(builder.equal(root.get(AbstractEntity_.id), entity.getId()));

executing this query result in a complex SQL query that returns an empty list.

IMO this should work, maybe it's a bug?

i'm using EclipseLink 2.5.2 as JPA provider and MySQL 5.6 as db.

here the generated SQL:

SELECT ... FROM UNIT t3 LEFT OUTER JOIN (DOCUMENT_RELATION t4 JOIN UNIT t0 ON (t0.ID = t4.CHILD_ID) JOIN DELIVERABLE t1 ON (t1.ID = t0.ID) JOIN DOCUMENT t2 ON (t2.ID = t0.ID)) ON (t4.PARENT_ID = t3.ID) WHERE (t3.ID = 58) SELECT ... FROM DOCUMENT_RELATION t6, DOCUMENT t5, DELIVERABLE t4, UNIT t3, DOCUMENT t2, DELIVERABLE t1, UNIT t0 WHERE (((t3.ID = 58) AND (((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document'))) AND ((((t5.ID = t3.ID) AND ((t4.ID = t3.ID) AND (t4.ID = t3.ID))) AND (t3.DTYPE = 'Document')) AND (((t6.PARENT_ID = t3.ID) AND (t0.ID = t6.CHILD_ID)) AND (((t2.ID = t0.ID) AND ((t1.ID = t0.ID) AND (t1.ID = t0.ID))) AND (t0.DTYPE = 'Document'))))) LIMIT 0, 10

here is the mapping:

@Entity @Inheritance(strategy = InheritanceType.JOINED) public abstract class Unit extends NamedEntity { @ManyToMany(fetch = FetchType.LAZY) @JoinTable(name = "DOCUMENT_RELATION", joinColumns = @JoinColumn(name = "PARENT_ID"), inverseJoinColumns = @JoinColumn(name = "CHILD_ID")) protected List<Document> documentList = new ArrayList<>(); }

and here is the class hierarchy:

AbstractEntity (abstract @MappedSuperClass) NamedEntity (abstract @MappedSuperClass) Unit (abstract @Entity joined-inheritance) Deliverable (abstract @Entity) Document (concrete @Entity)

found solution:

CriteriaQuery<Document> query = builder.createQuery(Document.class); Root<Unit> root = query.from(Unit.class); root.alias("root"); Root<Document> relation = query.from(Document.class); relation.alias("relation"); query.select(relation); query.where ( builder.equal(root.get(AbstractEntity_.id), item.getId()), builder.isMember(relation, root.get(Unit_.actionList)) );

zbig · Accepted Answer · 2014-09-17 13:19:08Z

What you are trying to achieve is forbidden by JPA spec. single-valued path expressions are valid in select clause but collection-valued path expressions are not valid. See chapter 4.8 of the spec. I cite Pro JPA book:

The following query is illegal: SELECT d.employees FROM Department d

The same is applies for Criteria queries. Why not just query for Unit and call getDocumentList()?

EDIT: You can also try to reverse the query:

SELECT d FROM Document d WHERE d.unit.id=:id

thanks i'll check the spec. I'm trying this because i need to enable pagination, filtering and sorting via JSF/PrimeFaces to relation collections. Some of these collections can have thousands of elements, so it's a performance issue too.
Thank you, i found the workaround: SELECT d FROM Unit u, Document d WHERE d member of u.documentList

Collectives™ on Stack Overflow

Criteria Query select join

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related