如何在JPA标准查询中使用Oracle的Connect by子句？

Question

我想在使用jpa条件的查询中显示父子关系。但是我一直在学习如何设置通过表达式连接

ReadAllQuery raQuery = JpaHelper.getReadAllQuery(transquery);
raQuery.setHierarchicalQueryClause(startswith, connectBy, order by);

其中，transquery是由具有多个选择和多个联接的criteriaQuery生成的

我想如下所示显示父事务及其子事务，但在条件查询中使用

    select t0.state,t4.name,t7.money from transaction t0 
    LEFT OUTER JOIN transactionAgents t4 ON (t4.TRANSID = t0.ID)
    LEFT OUTER JOIN AgentReference t7 on  t7.id=t4.agentid
    where t0.transdate>'10-july-2014' and t0.circle=10
    connect by prior t0.id=t0.parent ORDER BY t0.LAST_MODIFIED ASC, t0.ID DESC

Answer 1

ReadAllQuery。setHierarchicalQueryClause（）是Eclispelink特有的功能，该方法的javadoc对要传递的所有方法参数都有很好的描述：

Expression startWith = builder.get("id").equal(new Integer(100));
//can be any expression which identifies a set of employees
Expression connectBy = builder.get("managedEmployees");
//indicated the relationship that the hierarchy is based on, must be self-referential
Vector orderBy = new Vector();
orderBy.addElement(builder.get("startDate"));
readAllQuery.setHierarchicalQueryClause(startWith, connectBy, orderBy);
//This query would generate SQL like this:
//SELECT * FROM EMPLOYEE START WITH ID=100 CONNECT BY PRIOR ID = MANAGER_ID ORDER SIBLINGS BY START_DATE