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
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
I agree. Here's the link:
subject: using collection in where clause: JPAQL
It's not a secret anymore!