I have a java entity class UserBean with a list of events:
@OneToMany
private List<EventBean> events;
EventBean has Date variable:
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date eventDate;
Now in UserBean I want to create a NamedQuery that returns all dates that fall within a specific range:
@NamedQuery(name="User.findEventsWithinDates",
query="SELECT u.events FROM UserBean u WHERE u.name = :name AND u.events.eventDate > :startDate AND u.events.eventDate < :endDate")
The above query does not compile though. I get this error:
The state field path 'u.events.eventDate' cannot be resolved to a valid type.
By the way, I use EclipseLink version 2.5.0.v20130507-3faac2b.
What can I do to make this query work? Thanks.
Path u.events.eventDate is an illegal construct in JPQL, because it is not allowed to navigate via a collection valued path expression. In this case u.events is a collection valued path expression. In JPA 2.0 specification this is told with following words:
It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection. For example, if o designates Order, the path expression o.lineItems.product is illegal since navigation to lineItems results in a collection. This case should produce an error when the query string is verified. To handle such a navigation, an identification variable must be declared in the FROM clause to range over the elements of the lineItems collection.
This problem can be solved by using JOIN:
SELECT distinct(u)
FROM UserBean u JOIN u.events e
WHERE u.name = :someName
AND e.eventDate > :startDate
AND e.eventDate < :endDate