Sunday, March 9, 2008

Oracle Hierarchical Query in Hibernate

When you need then there is no replacement of Oracle hierarchical queries. At the same time we would like Hibernate to do the hard work of mapping result set to entities. Here is an example of Hibernate Native Query which would fetch the organization chart from famous scott.emp table.


Session s = (Session) em.getDelegate();
String orgChartQuery = "select {emp.*} from EMP {emp} \n" +
"start with emp.mgr is null \n" +
"connect by prior emp.empno = emp.mgr\n" +
"order siblings by ename";
@SuppressWarnings("unchecked")
List list = s.createSQLQuery(orgChartQuery)
.addEntity("emp", Employee.class)
.list();


Now suppose we want to render this in a tree control with breadcurms. We can use the following query.


Session s = (Session) em.getDelegate();
String orgChartQuery = "select sys_connect_by_path(emp.empno, '/') breadcrum,\n"+
"level, {emp.*} from EMP {emp} \n" +
"start with emp.mgr is null \n" +
"connect by prior emp.empno = emp.mgr\n" +
"order siblings by ename";
@SuppressWarnings("unchecked")
List list = s.createSQLQuery(orgChartQuery)
.addScalar("breadcrum", Hibernate.STRING)
.addScalar("level", Hibernate.INTEGER)
.addEntity("emp", Employee.class)
.list();
for (Object[] objects : list) {
String breadcrum = (String) objects[0];
Integer level = (Integer) objects[1];
Employee emp = (Employee) objects[2];
System.out.println(breadcrum +" "+ level + " "+emp.getName()
+ " "+emp.getDepartment().getName());
}

Breadcrums willl give you path to the object from the root and level gives the 1 based level in the tree.

2 Comments:

At November 18, 2009 at 5:36 AM , Blogger Sander De Vos said...

Thanks for the info. It is very useful, especially the Scalar and Entity adding of the results.

 
At October 8, 2012 at 9:18 AM , Blogger Ondrej Medek said...

Thanks, very helpful.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home