Inner join in Hibernate

[kashwini Kulkarni]

Hello,

I have the following scenario : -

Three tables
Product
Customer
Customer_Product(having customerId as well as productId)

I have POJO's for all the three tables and also hbm.xml files

I want to execute a query which will fetch all the products purchased by a particular customer

SELECT productName,productDetail,customerId from Product p inner join Customer_Product cp
ON p.productId=cp.productId AND cp.customerId = 2

which gives me a ClassCastException if i try to fetch objects from the returning list
query.list()

my POJO's are simple having properties mapping table columns.
Do i need to change them if i need to use inner join?



Thanks in Advance
Kashwini

[Bogdan Baraila]

To what class are you trying to cast the object. Note that your query will return a list of objects in which each object has 3 fields: your productName, productDetail and customerId.

[kashwini Kulkarni]

Hello,


Thanks a lot for your reply.But the problem was solved the same day i posted it.
But i am now facing some different challenge.
It will be really nice if you can help with that.

I want to know how can we map 3 tables (with many-to-many relationship) in hibernate.

I have 3 tables :

Product
Market_strategy
AssignedMarketStrategy having productId as well as MarketStrategyId but also some extra columns.
Which means i need to have a separate class for the AssignedMarketStrategy table and a mapping file too.

I am not getting what changes i need to make to my product.hbm.xml.
Also how to proceed further with 3 classes having many-to-many relationship.

I searched the net but was unable to find useful content.
Can you ( or anyone) please suggest me a link which may have tutorial of this kind?




Thanks in Advance
Kashwini

[Bogdan Baraila]

If your many-to-many relation table (AssignedMarketStrategy) also contains extra columns then you need to treat your AssigneMarketStrategy as a separate object that it will have a Product and a MarketStrategy. Also each Product and Market_strategy will have a list of AssignedMarketStrategy:

<set name="marketStrategies" inverse="true" cascade="all-delete-orphan">
<key column="productId"/>
<one-to-many class="path.AssignedMarketStrategy" />
</set>

<set name="marketStrategies" inverse="true" cascade="all-delete-orphan">
<key column="MarketStrategyId "/>
<one-to-many class="path.AssignedMarketStrategy" />
</set>

And in AssignedMarketStrategy something like this both for product and market strategy:

<many-to-one
name="product"
column="productId"
class="path.Profuct"
update="true"
insert="true"
foreign-key="PRODUCT_STRATEGY_FK"
cascade="none"/>

[kashwini Kulkarni]

Hello ,


Thanks a lot for your reply.
I will surely try this







Regards,
Kashwini

[kashwini Kulkarni]

Hi,


I was trying with your solution but got stuck in middle due to some other error.
It will be very helpful if you can tell me whats going wrong.


I am stuck with the following error
Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer]

which occurs because of my composite-id

Situation is as follows : -

I have a two tables and a third table with their many-to-many relationship
The third table however has some extra columns.

So i created a separate entity class and hbm.xml for the third table.
In my third table i have primary key (which is combination of three keys from three different tables)

hence in my hbm.xml i created <composite-id> for that primary key.
Also a separate java file for that composite-id.
It looks like this:

<composite-id name="assignedMarketId" class="hibernate.AssignedMarketId">
<key-property name="custId" column="customerId" type="java.lang.Integer" length="10" ></key-property>
<key-property name="prodId" column="prodId" type="java.lang.Integer"></key-property>
<key-property name="mkId" column="mkId" type="java.lang.Integer"></key-property>
</composite-id>

where AssignedMarketId is a separate class with all above Id's (Getter and Setter)

but still i am getting error
Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer] at line <key-property>
If i remove this execution proceeds further.



I searched the net but could not find any pointers in the direction.
Can anyone please let me know if i am missing something?



Thanks in Advance
Kashwini

[Prakash Mani - Attur]

Hi Kulkarni

What made you solved the ClassCastException. Did you tried to cast query.list() to ArrayList or something ? If you have asked some question here and it got solved plese tell us how you did it. It would be helpful for many.

[kashwini Kulkarni]

hi,

my problem got solved when i replaced my code (with query) by criteria as follows : -

(No casting needed)

Criteria crit=session.createCriteria(Product.class);
crit.createCriteria("custSet").add(Restrictions.eq("customerId",request.getSession().getAttribute("session_custId")));

request.setAttribute("prodList", crit.list());

where Product is my class having product details and custSet is a HashSet in that class.
I did this because i had was supposed to show all products for logged in customer.
In database i had a third table having primary keys of both Product and Customer.
Same was mapped in my .hbm file.




Regards,
Kashwini

[Prakash Mani - Attur]

Thanks Kashwini!!!