We have the requirement to support hierarchical queries within dynamically built queries. So we need to implement an "Is Under" Predicate to be used with the JPA Criteria API. For example, we would like to build a query like this dynamically:
SELECT i from ItemEO i WHERE i.hierarchy.hierarchyId IN (SQL('SELECT h.HIERARCHY_ID FROM HIERARCHY h START WITH ? CONNECT BY PRIOR h.HIERARCHY_ID = h.PARENT_ID', :ancestor));
I have come up with several approaches, but some of them require Eclipselink to be patched. Namely, the implementation of the In expression assumes that the Expressions given to it are either SubQuery expressions or literal expressions. Yet, my converted expression is a FunctionExpression.
@Override
public Predicate isUnder(Root<ItemEO> root, CriteriaBuilder cb, IsUnderClause data) {
JpaCriteriaBuilder jcb = (JpaCriteriaBuilder) cb;
org.eclipse.persistence.expressions.Expression eb = new ExpressionBuilder();
eb = eb.value(data.getIsUnder());
org.eclipse.persistence.expressions.Expression processIsUnderFilter = eb
.sql("SELECT HIERARCHY_ID FROM HIERARCHY START WITH HIERARCHY_ID = ? CONNECT BY PRIOR HIERARCHY_ID = PARENT_ID", new ArrayList<>(0));
Expression<Long> elExp = pathToHierarchyId(root, data);
Expression<Long> values = jcb.fromExpression(processIsUnderFilter, Long.class);
return jcb.in(elExp).value(values);
}
I have also tried to use the Expression API to create the In predicate entirely (using a ReportQuery). However, this cannot be converted back into a Predicate because the "CriteriaBuilder#and" implementation assumes it is given CompoundExpressionImpl's.
@Override
public Predicate isUnder(Root<ItemEO> root, CriteriaBuilder cb, IsUnderClause data) {
JpaCriteriaBuilder jcb = (JpaCriteriaBuilder) cb;
org.eclipse.persistence.expressions.Expression eb = new ExpressionBuilder();
ReportQuery rq = new ReportQuery(HierarchyEO.class, new ExpressionBuilder());
rq.setSQLString("SELECT HIERARCHY_ID FROM HIERARCHY START WITH HIERARCHY_ID = " + data.getIsUnder() + " CONNECT BY PRIOR HIERARCHY_ID = PARENT_ID");
eb = eb.subQuery(rq);
Expression<Long> elExp = pathToHierarchyId(root, data);
org.eclipse.persistence.expressions.Expression path = jcb.toExpression(elExp);
path = path.in(rq);
return cb.and(cb.conjunction(), asPredicate(jcb.fromExpression(path, Boolean.class)));
}
public Expression<Boolean> asPredicate(Expression<Boolean> e) {
List list = new ArrayList();
EntityManager em = BaseTestCase.getEM();
return new CompoundExpressionImpl(em.getMetamodel(), ((InternalSelection) e).getCurrentNode(), list);
}
Is there an alternative approach that we can use to generate this query dynamically? Or can Eclipselink be patched/tested in either situation to take FunctionExpressions?
I've attached a standalone project containing 5 approaches along with JUnit tests for correctness. In it, I've patched InImpl to do type-checking.
] 
Search
Help
Register
Login
Home
