Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

@ManyToMany and Select query

 
ishan dave
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 132
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
not sure if this helps you but you may want to check out:
jdijkmeijer.blogspot.com jsf solutions for many2many (september and october).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic