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.

Answer 1

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