Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to Write Sub Query in Hibernate

 
Syed Ali
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 162
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Syed,

You can use Subqueries class for your query.

So you can write your query as fallows :



You can refer this documentation for more information


Thanks
- Kuldeep
[ November 11, 2008: Message edited by: Kuldeep Yadav ]
 
Syed Ali
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



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)"



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
Ranch Hand
Posts: 162
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

YOu can solve this in 3 ways:

1.



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


using

Or

3.
You Can Write Hql As


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


Thanks

- Kuldeep
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic