File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "using collection in where clause: JPAQL " Watch "using collection in where clause: JPAQL " New topic

using collection in where clause: JPAQL

Sneha Teck

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,


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
subject: using collection in where clause: JPAQL
Similar Threads
Criteria for retriving data involving multiple joins
Possible memory leak during recursion
Multiple Column Join
Association problem in delete
Heap exception while loading complete table data from database