java - 如何在 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