WITH Clause : Subquery Factoring using Hibernate

Question

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;

How can we map the data retrieved from above query (mean temporary table dept_count that got created due to the usage of WITH CLAUSE) to the following java class?

My Java class is having the following attributes: employee_name, emp_dept_count.

score 0 · Answer 1 · answered Aug 05 '14 at 22:53

0

Use a native SQL query with a AliasToBeanResultTransformer ( http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/transform/AliasToBeanResultTransformer.html ).

answered Aug 05 '14 at 22:53

Maarten Winkels

2,407
16
15

Agree with usage of AliasToBeanResultTransformer for mapping data from pre defined entities (existing tables) to user defined java classes. But, in the above query, we are getting data from a temporary table (dept_count using WITH clause) where we cant add this to createCriteria of a session. Finally, is there any way to map the data from a temporary table (not an existing table) to a java class using hibernate? – Rajesh Voleti Aug 06 '14 at 02:12
No criteria, but native SQL. Just do createSqlQuery(..) with the Sql string you have, set the ResultTransfptmer and do list(). – Maarten Winkels Aug 06 '14 at 05:41

WITH Clause : Subquery Factoring using Hibernate

1 Answers1