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 @ManyToMany and Select query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "@ManyToMany and Select query" Watch "@ManyToMany and Select query" New topic
Author

@ManyToMany and Select query

ishan dave
Ranch Hand

Joined: Apr 16, 2007
Posts: 39
Dear all,

I am facing a problem to fetch the data from db where a ManytoMany relationship is esablished.
I am having 3 tables
1.CATEGORYTABLE(pkl CATEGORYID, CATEGORYNAME)
2.VENDORTABLE (pkl VENDID, VENDNAME)
3.CATEGORYVENDORRELATION(fkl CATEGORYID, fkl VENDID)

I use Oracle 10g, Hibernate 3.3,JPA and Spring 2.0

now i want fire a simple select query like...

SELECT CATEGORY FROM CATEGORYTABLE WHERE CATEGORYID IN
(SELECT CATEGORYID FROM CATEGORYVENDORRELATION WHERE VENDID = ?);

but i don't know how to do the same task using JPQL

my main problem is how to fetch the data from many-to-many relationship table.details are as follwos.
======== Category Class===========

and following is the

====== Vendor Class ===========

---------------------------

now the data of these two's many to many realtion is being stored in CATEGORYVENDORRELATION table now i want to fetch
*Name of vendors from vendor table where category id is 2" so i need a simple sql query like....
SELECT CATEGORY FROM CATEGORYTABLE WHERE CATEGORYID IN (SELECT CATEGORYID FROM CATEGORYVENDORRELATION WHERE VENDID = ?); Am i right ?
now I tried
SELECT V FROM VENDORTABLE AS V WHERE :?1 MEMBER OF V.categories
and then I am passing an object of CategoryVO like query.setParameter(1,new CategoryVO(2,"Supply")) here I am setting the value of the object and then set to this query in my DAO class the snippet of DAO is....


but I am getting the following exception......

SEVERE: ORA-00972: identifier is too long

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
----------------


and thank you very much in advance for your reply to this blog.
_________________
With Best Regards,
Ishaan
jeroen dijkmeijer
Ranch Hand

Joined: Sep 26, 2003
Posts: 131
not sure if this helps you but you may want to check out:
jdijkmeijer.blogspot.com jsf solutions for many2many (september and october).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: @ManyToMany and Select query