File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes SubQuery in Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "SubQuery in Hibernate" Watch "SubQuery in Hibernate" New topic
Author

SubQuery in Hibernate

Ritesh Singal
Greenhorn

Joined: Mar 31, 2006
Posts: 2
Hi!!!

There's problem in Hibernate.

I have three tables as :

Department(Dno,Dname)
Employee(Eno,Dno,Salary)
Project(Pno,Dno) i.e Here Dno represents The Department that controls that project.

Now I Want Output as :
Dname,sum(Salary),count(Project)

i.e. Total Salary paid to each Department and total number of projects controlled by each department.

i.e. Group by dno in Employee for Sum(Salary) and Group by dno in Project for count(*)
Then Join both results with Department to get desired results.


In Sql we write the Query as :

SELECT d.dname, a.s, b.c
FROM Department d,
(SELECT dno, sum(salary) s FROM Employee GROUP BY dno) a,
(SELECT dno, count(*) c FROM Project GROUP BY dno) b
WHERE d.dno=a.dno and a.dno=b.dno


I get the correct result from the above query.

Now Problem is how to write the same query in Hibernate.

DOES HIBERNATE SUPPORTS SUBQUERY IN FROM CLAUSE AS USED IN ABOVE SQL.


In java we have three classes as :

Department(dno,dname)
Employee(eno,salary and Object of Department dept)
Project(pno and Object of Department dept)


In hibernate I have write a query as :

SELECT d.dname,a.s,b.c
FROM Department d, (SELECT e.dept.dname dn,sum(salary) s FROM Employee e group by dn) a,
(SELECT p.dept.dname dnm, count(*) s FROM Project p group by dnm) b
Where d.dname = a.dn and a.dn = b.dnm

But Hibernate gives error in From clause where "(" starts.
I think Hibernate does not support Subquery in From clause.

Individually Employee group by and Project group by is alright in Hibernate.

Help me to Solve above Problem

Bye!!!
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
Review using "Criteria" objects in your query. This is how I got around doing a subquery across tables.


Jeremy Wilson
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SubQuery in Hibernate