Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Support for dynamic SQL from Expression API in predicates(Support for dynamic SQL from Expression API in predicates)
Support for dynamic SQL from Expression API in predicates [message #1730904] Fri, 29 April 2016 15:47
Matthew Marron is currently offline Matthew MarronFriend
Messages: 2
Registered: December 2013
Junior Member
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.
Previous Topic:Uni-directional OneToMany problems with JPA2.0 -> JPA2.1 migration
Next Topic:CacheCoordination with synchronization type INVALIDATE_CHANGED_OBJECTS issues
Goto Forum:
  


Current Time: Fri Apr 03 18:37:14 GMT 2020

Powered by FUDForum. Page generated in 0.04322 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top