Question in Hibernate Criteria Query

[Saravanan Vijayappan]

Hello,

Could any one please help, I referred so many articles but don't get information. I have question in my below Criteria query:-

List<?> menuGroups = HibernateUtil.getSession().createCriteria(Menugroup.class).
add( Restrictions.eq("published", true)).addOrder( Property.forName("name").asc()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).
setFetchMode("catgroups", FetchMode.JOIN).
createCriteria("catgroups").addOrder( Property.forName("name").asc()).
add( Restrictions.eq("published", true) ).setFetchMode("dealitems", FetchMode.DEFAULT)
.list();

When you closely look at the query, the association is, Menugroups--(1 to many)-->Catgroups---(1 to many)---> Dealitems. I have configured lazy fetching and have set up batch-size=10 in config file.

When the above query is fired, hibernate generates the below sqls,

Hibernate: 
    select
        this_.MENUGROUP_ID as MENUGROUP1_0_1_,
        this_.NAME as NAME0_1_,
        this_.DESCRIPTION as DESCRIPT3_0_1_,
        this_.PUBLISHED as PUBLISHED0_1_,
        this_.LASTUPDATE as LASTUPDATE0_1_,
        catgroup1_.CATGROUP_ID as CATGROUP1_1_0_,
        catgroup1_.MENUGROUP_ID as MENUGROUP2_1_0_,
        catgroup1_.NAME as NAME1_0_,
        catgroup1_.DESCRIPTION as DESCRIPT4_1_0_,
        catgroup1_.IMGURL as IMGURL1_0_,
        catgroup1_.PUBLISHED as PUBLISHED1_0_,
        catgroup1_.SUGESSTION as SUGESSTION1_0_,
        catgroup1_.LASTUPDATE as LASTUPDATE1_0_ 
    from
        cyddev.MENUGROUP this_ 
    inner join
        cyddev.CATGROUP catgroup1_ 
            on this_.MENUGROUP_ID=catgroup1_.MENUGROUP_ID 
    where
        this_.PUBLISHED=? 
        and catgroup1_.PUBLISHED=? 
    order by
        this_.NAME asc,
        catgroup1_.NAME asc
Hibernate: 
    select
        catgroups0_.MENUGROUP_ID as MENUGROUP2_1_,
        catgroups0_.CATGROUP_ID as CATGROUP1_1_,
        catgroups0_.CATGROUP_ID as CATGROUP1_1_0_,
        catgroups0_.MENUGROUP_ID as MENUGROUP2_1_0_,
        catgroups0_.NAME as NAME1_0_,
        catgroups0_.DESCRIPTION as DESCRIPT4_1_0_,
        catgroups0_.IMGURL as IMGURL1_0_,
        catgroups0_.PUBLISHED as PUBLISHED1_0_,
        catgroups0_.SUGESSTION as SUGESSTION1_0_,
        catgroups0_.LASTUPDATE as LASTUPDATE1_0_ 
    from
        cyddev.CATGROUP catgroups0_ 
    where
        catgroups0_.MENUGROUP_ID in (
            ?, ?, ?
        )

My issue here is, the one of the criteria restriction published=true is not applied in the 2nd SQLs. So I am getting unpublished catgroups in the result. Could any one help please what is wrong in my Criteria query and what needs to be added more ?

Reply is appreciated.

Thanks
Sarav.
[ July 12, 2008: Message edited by: Saravanan Vijayappan ]

[Mark Spritzler]

Please refrain from duplicate posting. I am going to close this dup thread. I am sorry someone hasn't answered the other thread, but hopefully someone will soon.

Mark