posted 16 years ago
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