File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes Collection search with many to many association Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Collection search with many to many association" Watch "Collection search with many to many association" New topic

Collection search with many to many association

chets patel
Ranch Hand

Joined: Apr 27, 2010
Posts: 77

I have two POJOs as below.

Assuming ID and all other hbm xml mapping works fine as I am able to save and update the objects perfectly fine.
I want to search those companies having particular master comapnies associated with it.
I write below code for searching the company:

When I see the query generated by hibernate, it is as below.

select company0_.COMPANY_ID as COMPANY1_0_, company0_.COMPANY_NAME as COMPANY2_0_, company0_.ADDRESS as ADDRESS0_, company0_.CITY as CITY0_, company0_.DISTRICT as DISTRICT0_, company0_.STATE as STATE0_, company0_.PHONE as PHONE0_, company0_.MOBILE as MOBILE0_, company0_.EMAIL as EMAIL0_, company0_.URL as URL0_, company0_.COUNTRY as COUNTRY0_ from COMPANY company0_ inner join MASTER_CLIENT mastercomp1_ on company0_.COMPANY_ID=mastercomp1_.COMPANY_ID inner join MASTER_COMPANY mastercomp2_ on mastercomp1_.MASTER_COMPANY_ID=mastercomp2_.MASTER_COMPANY_ID where mastercomp2_.MASTER_COMPANY_NAME in (?)

Above query seems ok to me but I get following error.

Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'company0_.COMPANY_ID=mastercomp1_.COMPANY_ID inner join MASTER_COMPANY mastercomp2_ on mastercomp1_.MASTER_COMPANY_ID=mastercomp2_.MASTER_COMPANY_ID'.

I googled all the examples and they are giving the same query as above.

Can any one please help what is wrong with it.
I wasted whole day but didnt get solution.
I agree. Here's the link:
subject: Collection search with many to many association
It's not a secret anymore!