jQuery in Action, 2nd edition*
The moose likes Object Relational Mapping and the fly likes using collection in where clause: JPAQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "using collection in where clause: JPAQL " Watch "using collection in where clause: JPAQL " New topic
Author

using collection in where clause: JPAQL

Sneha Teck
Greenhorn

Joined: Mar 20, 2007
Posts: 7
I have a ManytoOne and a OneToMany association relation b/w two entities ( created by eclipse link)

Now the parent entity : states :
//bi-directional many-to-one association to CsscBffrdCmdtyGrpCmdty (CHILD )
@OneToMany(mappedBy = "csscBufferedCmdtyGroup")
private List<CsscBffrdCmdtyGrpCmdty> csscBffrdCmdtyGrpCmdties;


//bi-directional many-to-one association to CsscBufferedCmdtyGroup
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "BUF_CMDTY_GRP_INTL_ID", nullable = false)
private CsscBufferedCmdtyGroup csscBufferedCmdtyGroup;

Now I want to query such that I can send across one parent attribute and one child attribute in the where clause . Can that be done ?

I have written a query -
" select b from CsscBufferedCmdtyGroupb where " +
" b.csscBffrdCmdtyGrpCmdties.bufferCommodityId = :bufferCommodityId " +
" and b.projectId =:projectId ")

Now the error I get is 'csscBffrdCmdtyGrpCmdties' cannot be navigated .
So should I be using b.csscBffrdCmdtyGrpCmdties.bufferCommodityId in (:bufferCommodityId )

Will this work ?

James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
You need to use a JOIN,

See,

http://en.wikibooks.org/wiki/Java_Persistence/JPQL#JOIN


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: using collection in where clause: JPAQL