Indexed element access in JPQL

Question

Is it possible to do indexed element access in JPQL like in HQL :

select o from Order o where o.items[0].id = 1234

I couldn't find something related in the JPA 2 specs,

I am targeting EclipseLink JPA here, so if you come up with an EclipseLink solution, that's ok as well, although a JPQL standard solution is preferred.

Answer 1

The answer of @Pascal Thivent is correct. In order to make INDEX() work you also need to add a @OrderedColumn on the entity's collection field.

Be careful, there is a bug in Hibernate, which makes INDEX() not work as expect, it doesn't support @OrderedColumn and mappedBy together: JPA 2.0 @OrderColumn annotation in Hibernate 3.5

Answer 2

The INDEX function should do the trick (actually I tested it and it does):

SELECT o
FROM Order o JOIN o.items i
WHERE i.id = 1234
AND INDEX(i) = 0

From the JPA 2.0 specification (4.6.17.2.2 Arithmetic Functions):

The INDEX function returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables denoting types for which an order column has been specified.