How to Write Sub Query in Hibernate

[Syed Ali]

please help me write the following sub query in hibernate.
i ran the following subquery in MYSQL it works perfect in MYSQL

select count(*) as totalCount , category_name, category_id from b_entry_category
where entry_id in
( select entry_id from b_entries where blog_id = 1132)
group by category_name";

BlogsEntryCategory is the class for b_entry_category table
BlogsEntry is the class for b_entries table

thanks ali

[Kuladip Yadav]

Hi Syed,

You can use Subqueries class for your query.

So you can write your query as fallows :

    DetachedCriteria bolgsEntries = DetachedCriteria.forClass(BlogsEntry.class)
            .setProjection( Property.forName("entry_id") );
 
            session.createCriteria(RuleSet .class)
                .setProjection( Projections.projectionList()
                        .add( Projections.rowCount() ) )
                .add( Subqueries.geAll("entry_id", bolgsEntries) )
                .list(); 

You can refer this documentation for more information


Thanks
- Kuldeep
[ November 11, 2008: Message edited by: Kuldeep Yadav ]

[Syed Ali]

Hello Kuldeep

Thanks for your help, when i ran your code it returns me an empty List.
As i mentioned i ran the same query in MYSQL it runs perfect, but when i ran your code it returns a list of Zero size. Following is my query in MYSQL.

BlogsEntryCategory is the class for b_entry_category table
BlogsEntry is the class for b_entries table
-----------------------------------------------------------

select count(*) as totalCount , category_name, category_id
From b_entry_category
where entry_id in( select entry_id from b_entries where blog_id = 1132)
group by category_name";

I need a count of all the catefory names depending on the Blog-ID passed in the sub-query and grouped by the category name. please I need an urgent
help on this issue.....thanks Ali

[Rama Krishna]

Ali,

As a (decently experienced) hibernate developer, I think that the query might not work because it is simply not a group by function on category_id.

i.e., this will work

select count(*) as totalCount, category_name 
where entry_id in
( select entry_id from b_entries where blog_id = 1132)
group by category_name";

and the following will also work as only you know that there is only one category_id when you group by category_name but not the database or instead you can also go for an extra "group by category_id" query at the end without an aggregate function on category_id such as "max(category_id)"

select count(*) as totalCount, category_name, max(category_id) // any other aggregate function here
where entry_id in
( select entry_id from b_entries where blog_id = 1132)
group by category_name";

let me know what exactly you are looking for or your requirements and I may be able to help you a little more!

Regards
Krishna

[Kuladip Yadav]

Hi,

YOu can solve this in 3 ways:

1.

  DetachedCriteria bolgsEntries = DetachedCriteria.forClass(BlogsEntry.class)
        .setProjection( Property.forName("entry_id") );
 
        session.createCriteria(BlogsEntryCategory.class)
            .setProjection( Projections.projectionList()
                    .add( Projections.rowCount() )
                    .add( Projections.property( "category_name" )) 
                    .add( Projections.property( "category_id" )) 
                    .add( Projections.groupProperty( "category_name"  ) ))
            .add( Subqueries.geAll("entry_id", bolgsEntries) )
            .list();  

Please replace "entry_id" , "category_name" AND "category_id"
with respective properties field in your POJO class , Don't use actual column name .

Or
2.
In hibernet you can run sql Query Directly using

  session.createSQLQuery("Your Query");  

using

Or

3.
You Can Write Hql As

  session.createQuery("select count(*) as totalCount , cat.category_name, cat.category_id From BlogsEntryCategory cat " +
            "where cat.entry_id in ( select blog.entry_id from BlogsEntry blog where blog.blog_id = 1132) " +
            "group by cat.category_name")
 
   

Please dont use actual column here use respective POJO class properties.


Thanks

- Kuldeep