10

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.

Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
user473453
  • 894
  • 2
  • 11
  • 23

1 Answers1

23

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
Marc Audet
  • 46,011
  • 11
  • 63
  • 83
Mikko Maunu
  • 41,366
  • 10
  • 132
  • 135
  • Thanks that works! I only want to select s user with a specific name though. So I thought if I add 'WHERE u.name = :name' before the JOIN statement would do just that. I get this error though: The expression is not a valid conditional expression. What would be a valid conditional expression to select a user by name? – user473453 Sep 09 '13 at 21:27
  • Added :name to answer. Such a condition goes to existing WHERE clause - additional WHERE clauses before JOIN statement are not possible. – Mikko Maunu Sep 10 '13 at 06:12
  • Yay, that did it. The final query: SELECT DISTINCT u.events FROM UserBean u JOIN u.events e WHERE u.name = :name AND e.eventDate > :startDate AND e.eventDate < :endDate Thanks a lot for your help. I'm quite new to JPA, is this the most efficient way to retrieve the data I need from the database? The query seems to be slow to execute, but my sometimes slow internet connection might be the reason for that. – user473453 Sep 10 '13 at 12:19
  • 1
    Why is the distinct needed here? – PDStat Aug 09 '17 at 14:19