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 05: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 05: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 11:11

James Sutherland
Messages: 1834
Registered: July 2009

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

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 11:13

James Sutherland
Messages: 1834
Registered: July 2009

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

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 16: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

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

Mon, 05 July 2010 10:04

James Sutherland
Messages: 1834
Registered: July 2009

Senior Member

Just remove the "where a.id = :id"

James : Wiki : Book : Blog

Report message to a moderator

Previous Topic:	cached sql insert statement executed during query exection
Next Topic:	Query to MappedSuperclass fails

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 21:24:52 EDT 2013