• 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

Querying tables with a one-to-many foreign key relationship

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi everyone,

I am having trouble querying a one to many foreign key relationship. For example one product can have many parts. I would like to retrieve the part name, part number etc relating to a product id. The Product table stores a Set of Parts and the Part table stores the Product object.

Code:


Error:
18:44:50,801 INFO TransactionFactoryFactory:34 - Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 101 [from hibernate.Product p, hibernate.Part a where a.getProduct().getId()=p.id]

If you can help please post a response I have tried many different variations including inner join etc.

Thanks

Alyssa
 
Alyssa Kay
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Or could you point me towards a good reference?

Cheers Alyssa
 
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I think You query should be



Since there is already an association between Part and product on the basis on id, you dont need to put it in the where clause.
just "a.product p"should generate the corresponding sql for you.
 
Alyssa Kay
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the response. I really appreciate your help. I'm really not sure how to fix this.

I tried the following, it still has an error. It says Product is not mapped, I assumed the following portion of the parts mapping file would define this.





I eventually with this query want to return the product name as well.

Below are the mapping files:

Product.hbm.xml



Part.hbm.xml


Cheers Alyssa
 
Alyssa Kay
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there a work around for this?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic