This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes Querying tables with a one-to-many foreign key relationship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Querying tables with a one-to-many foreign key relationship" Watch "Querying tables with a one-to-many foreign key relationship" New topic
Author

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

Alyssa Kay
Greenhorn

Joined: Jan 20, 2009
Posts: 8
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

Joined: Jan 20, 2009
Posts: 8
Or could you point me towards a good reference?

Cheers Alyssa
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
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.


Rahul Babbar
Alyssa Kay
Greenhorn

Joined: Jan 20, 2009
Posts: 8
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

Joined: Jan 20, 2009
Posts: 8
Is there a work around for this?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Querying tables with a one-to-many foreign key relationship
 
Similar Threads
Simple mapping issue for Hibernate newbie
HQL Query
help regarding EJB-QL
Problem in hibernate join result
Hibernate - Using Join table - one-to-many association - Lazy Exception