Hi,

I want to fetch data from 3 tables in 1:* association having join table in between shown below-

Table ProductCategory ==>J Table ProductCategory_Product ==> Table Product ==> J Table Product_ProdSpec ==> Table ProdSpec

Now I want to retrieve record based on Product Specification and show it in HTML table.

I have used hibernate Criteria query for that as follows-

Session session = HibernateUtil.getSessionFactory().openSession();
Criteria offerCriteria = session.createCriteria(ProductCategory.class)
.createAlias("productList","productList")
.createAlias("productList.prodSpecList","prodSpecList")
.add(Restrictions.like("prodSpecList.prodSpecDesc", "%dual Sim%"))
.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);

List list = offerCriteria.list();

Hibernate is creating following queries -

Hibernate: select this_.productCategoryNumber as productC1_0_2_, this_.ProductCategoryType as ProductC2_0_2_, productlis4_.productCategoryNumber as productC1_0_, productlis1_.productNumber as productN2_, productlis1_.productNumber as productN1_1_0_, productlis1_.productCompanyName as productC2_1_0_, prodspecli6_.productNumber as productN1_1_, prodspecli2_.prodSpecNumber as prodSpec2_, prodspecli2_.prodSpecNumber as prodSpec1_2_1_, prodspecli2_.prodSpecDesc as prodSpec2_2_1_ from ProductCategory this_ inner join ProductCategory_Product productlis4_ on this_.productCategoryNumber=productlis4_.productCategoryNumber inner join Product productlis1_ on productlis4_.productNumber=productlis1_.productNumber inner join Product_ProdSpec prodspecli6_ on productlis1_.productNumber=prodspecli6_.productNumber inner join ProdSpec prodspecli2_ on prodspecli6_.prodSpecNumber=prodspecli2_.prodSpecNumber where prodspecli2_.prodSpecDesc like ?
Hibernate: select productlis0_.productCategoryNumber as productC1_0_1_, productlis0_.productNumber as productN2_1_, product1_.productNumber as productN1_1_0_, product1_.productCompanyName as productC2_1_0_ from ProductCategory_Product productlis0_ inner join Product product1_ on productlis0_.productNumber=product1_.productNumber where productlis0_.productCategoryNumber=?
Hibernate: select prodspecli0_.productNumber as productN1_1_1_, prodspecli0_.prodSpecNumber as prodSpec2_1_, prodspec1_.prodSpecNumber as prodSpec1_2_0_, prodspec1_.prodSpecDesc as prodSpec2_2_0_ from Product_ProdSpec prodspecli0_ inner join ProdSpec prodspec1_ on prodspecli0_.prodSpecNumber=prodspec1_.prodSpecNumber where prodspecli0_.productNumber=?
Hibernate: select prodspecli0_.productNumber as productN1_1_1_, prodspecli0_.prodSpecNumber as prodSpec2_1_, prodspec1_.prodSpecNumber as prodSpec1_2_0_, prodspec1_.prodSpecDesc as prodSpec2_2_0_ from Product_ProdSpec prodspecli0_ inner join ProdSpec prodspec1_ on prodspecli0_.prodSpecNumber=prodspec1_.prodSpecNumber where prodspecli0_.productNumber=?
Hibernate: select prodspecli0_.productNumber as productN1_1_1_, prodspecli0_.prodSpecNumber as prodSpec2_1_, prodspec1_.prodSpecNumber as prodSpec1_2_0_, prodspec1_.prodSpecDesc as prodSpec2_2_0_ from Product_ProdSpec prodspecli0_ inner join ProdSpec prodspec1_ on prodspecli0_.prodSpecNumber=prodspec1_.prodSpecNumber where prodspecli0_.productNumber=?

and it is returning following result.

productCategoryType :- Mobile[productCompanyName :- Samsung prodSpecList :- [GPS navigation, music (MP3), video (MP4) playback, dual Sim, video calling, built-in cameras, games, office, Bluetooth, WiFi, instant messaging, Internet, e-mail, browsing], productCompanyName :- LG prodSpecList :- [GPS navigation, video (MP4) playback, video calling, games, Bluetooth, instant messaging, e-mail], productCompanyName :- Sony prodSpecList :- [music (MP3), dual Sim, built-in cameras, office, WiFi, Internet, browsing]]

I have following queries -

1) Why it is creating too much queries as only 1st query is sufficient and ok?
2) Why it is returning all data? Whether all queries get fired in data base?
3) Please give me code sample to use?

Can anybody assist me to get the solution.