Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

How to Write Sub Query in Hibernate

 
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
 
Ranch Hand
Posts: 162
Hibernate Spring Java
  • 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
 
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 Spring Java
  • 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
 
You don't like waffles? Well, do you like this tiny ad?
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic