• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

@ManyToMany and Select query

 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 132
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
reply
    Bookmark Topic Watch Topic
  • New Topic