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