This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes Object Relational Mapping and the fly likes How to Write Sub Query in Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to Write Sub Query in Hibernate" Watch "How to Write Sub Query in Hibernate" New topic
Author

How to Write Sub Query in Hibernate

Syed Ali
Greenhorn

Joined: Oct 08, 2008
Posts: 29
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

Joined: Jul 30, 2008
Posts: 162

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

Joined: Oct 08, 2008
Posts: 29
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

Joined: Oct 16, 2007
Posts: 110
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

Joined: Jul 30, 2008
Posts: 162

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
 
 
subject: How to Write Sub Query in Hibernate