This week's giveaway is in the Testing forum.
We're giving away four copies of TDD for a Shopping Website LiveProject and have Steven Solomon on-line!
See this thread for details.
Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

@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).
 
It will give me the powers of the gods. Not bad for a tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic