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.

Oracle JDBC Connection KeepAlive

So, you’ve got a spoiled bloated app or a third party library and it’s fond of manopolizing JDBC connections. There are couple of options with Oracle.

1) Server side option: Oracle Database comes with a Dead Client Detection feature where Oracle Server sends a probe. It can be configured by specifying SQLNET.EXPIRE_TIME parameter in sqlnet.ora. The value specified in in minutes.

2) If you control the connection, then there are plenty of option. If you don’t (in case of third party libraries), then TCP KeepAlive may help you. However, Oracle JDBC driver doesn’t provide a good way to customize TCP level parameters. The only way to specify KeepAlive setting is to use TNSEntry. It works with Thin driver. However, on the downside, you need to have connect string in tnsnames.ora. (ENABLE=broken) option will set KeepAlive on TCP socket.
orcl =
(DESCRIPTION =(ENABLE=broken)
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

System.setProperty("oracle.net.tns_admin", "oracle/NETWORK/ADMIN");
OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("orcl");
ods.setDriverType("thin");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setLoginTimeout(2);
Properties cp = new Properties();
cp.setProperty("SetBigStringTryClob", "true");
ods.setConnectionProperties(cp);

A typical error when a connection is not used for extended period is,
java.sql.SQLException: Io exception: Connection reset
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException( DatabaseError.java:255)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows( T4CPreparedStatement.java:974 )
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe( OracleStatement.java:1054 )
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe( T4CPreparedStatement.java:836 )
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout( OracleStatement.java:1124 )
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal( OraclePreparedStatement.java:3285 )

You can keep the JDBC connection alive this way even if its not used for long time. However, if you are in such situation, get out before you employ this trick. You will be happier. Long time back, we ran into such a library and we found out the root cause and realized that we should pull the rip cord.

Blog at WordPress.com.