File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes IN clause filtering on OneToMany/ManyToMany associations in JPQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "IN clause filtering on OneToMany/ManyToMany associations in JPQL" Watch "IN clause filtering on OneToMany/ManyToMany associations in JPQL" New topic
Author

IN clause filtering on OneToMany/ManyToMany associations in JPQL

Matthias Sommer
Greenhorn

Joined: May 20, 2009
Posts: 3
Hi there,

I'd like to ask for an advice with a problem regarding OneToMany/ManyToMany mappings. Basically, what I'd like to do is to create named query like this:

<code>"SELECT ch FROM channel ch WHERE ch.channelTypes IN (:typeList)"</code>

where :typeList parameter is collection of desired ChannelType instances (or Integers, if necessary).

channelTypes field in Channel entity is ManyToMany mapping defined as following:

<code>
@ManyToMany(targetEntity=ChannelType.class, cascade = CascadeType.ALL)
@JoinTable(name = "channel_type_rel", joinColumns = { @JoinColumn(name = "channel_id", referencedColumnName = "channel_id") }, inverseJoinColumns = { @JoinColumn(name = "channel_type_id", referencedColumnName = "channel_type_id") })
private List<ChannelType> channelTypes;
</code>

Resulting SQL query then should look like this:

<code>SELECT ch.* FROM channel ch LEFT JOIN channel_type_rel ctr ON (ctr.channel_id = ch.channel_id) LEFT JOIN channel_type ct ON (ctr.channel_type_id = ct.channel_type_id) WHERE ct.channel_type_id IN (:channelTypes) </code>

Apparently, this does not works, so I'd like to ask if there's some way how to achieve this (does not matter how), preferably with pure JPQL, as code is deployed to two environments, one with Hibernate and the other with TopLink. If it's not possible with pure JPQL, HQL is preferred then.

many thanks in advance
Matthes
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
I am not sure whether i understood your requirements, but it looks like you need an inner join instead of the outer Join.

Can you see if the following HQL is fine.

"SELECT ch FROM channel ch Inner Join ch.channelTypes ct WHERE ct IN (:typeList)"


Rahul Babbar
Matthias Sommer
Greenhorn

Joined: May 20, 2009
Posts: 3
Thank you, man! That's what I needed - I could not figure out right expression for WHERE clause and that's it: WHERE ct IN (:typeList)! Now it works as desired.

thanks again
Matthias
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: IN clause filtering on OneToMany/ManyToMany associations in JPQL
 
Similar Threads
incomplete @JoinColumns
@ManyToMany and Select query
Persistence in the Enterprise - efficiency
Mysql auto generated PK used for another table and JPA
JPA: Count object relations in n:m relationship - do I need a native query?