Eclipse Community Forums: EclipseLink » Returning 1 member of a collection using jpql

Home » Eclipse Projects » EclipseLink » Returning 1 member of a collection using jpql(Is it possible to return the first member of a collection in jpql)

Show: Today's Messages :: Show Polls :: Message Navigator

Returning 1 member of a collection using jpql [message #542537]

Fri, 25 June 2010 09:15

Olu

Messages: 29
Registered: October 2009

Junior Member

Hi,

Suppose I have an object from a class which has a collection.

e.g.

 entity class A {
...
Collection col
}

Is it possible to write a jpql statement to only return the first element of the collection?

thanks,

[Updated on: Fri, 25 June 2010 09:15]

Report message to a moderator

Re: Returning 1 member of a collection using jpql [message #543161 is a reply to message #542537]

Mon, 28 June 2010 15:11

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

You could do this a few different ways,

Query query = em.createQuery("Select a.col from A a where a.id = :id");
query.setMaxResults(1);

Query query = em.createQuery("Select b from A a join a.col b where a.id = :id");
query.setMaxResults(1);

"Select b from B b where b.id = (Select max(b2.id) from A a join a.col b2 where a.id = :id)"

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: Returning 1 member of a collection using jpql [message #543162 is a reply to message #542537]

Mon, 28 June 2010 15:13

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

Also if it is a JPA 2.0 indexed list mapping, you can use the INDEX() function in JPQL.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: Returning 1 member of a collection using jpql [message #543256 is a reply to message #543161]

Mon, 28 June 2010 20:08

Olu

Messages: 29
Registered: October 2009

Junior Member

Thanks for this James.

Your third option above would have worked only I need to return a member of the collection col from an arbitary number of calss A objects rather than for a given instance of A.

For example, if there were X class A objects I need a member of the collection (could actually be any member of the colllection) for each A - i.e. a member for X1, another for X2, etc.

You examples above did get me thinking but the jpql:-

select b from A a join a.col b

means b iterates through ALL collections in all of A OR - using your examples - just a given A.

I need an expression which allows me to extract a member from col on a per instance of A basis from any number of instances of A?

thanks,

Report message to a moderator