Oracle Hierarchical Query in Hibernate

When you need them, there is no replacement of Oracle hierarchical queries. They scale linearly with each level while a join would scale exponentially. 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.

About these ads

2 thoughts on “Oracle Hierarchical Query in Hibernate

  1. My programmer is trying to convince me to move to .
    net from PHP. I have always disliked the idea because of the expenses.

    But he’s tryiong none the less. I’ve been using
    WordPress on several websites for about a year and am anxious about
    switching to another platform. I have heard excellent things about blogengine.
    net. Is there a way I can import all my wordpress content into it?

    Any help would be really appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
The Esquire Theme.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: