[eclipselink-users] CONNECT BY functionality in EclipseLink 2.5

["Reynolds, Richard" <richard.reynolds@xxxxxxxx>]

Using this blog post as reference. 

 

I am having trouble using the CONNECT BY feature that was introduced in the 2.5 version.

 

Here is my setup:

 

public class RelationTest extends BaseEntity {

 

       @ManyToOne

       @JoinColumn(name = "PARENT_ID")

       private RelationTest parent;

 

       private String name;

 

       @Audited(targetAuditMode = RelationTargetAuditMode.NOT_AUDITED)

       @OneToMany(cascade = CascadeType.ALL, mappedBy = "parent")

       private Set<RelationTest> children = new HashSet<>();

}

 

Here is the first test that I tried to do and I am unsuccessful with this attempt. 

@Test

       public void testSQLQuery() {

              EntityManager entityManager = BaseEntity.entityManager();

 

              Query query = entityManager.createQuery("SELECT rt FROM " + RelationTest.class.getCanonicalName()

                           + " as rt START WITH rt.parent IS NULL CONNECT BY rt.children");

 

              @SuppressWarnings("unchecked")

              List<RelationTest> results = query.getResultList();

 

              Assert.notEmpty(results);

       }

 

This is the SQL that it generates.

 

SELECT t0.ID,

  t0.NAME,

  t0.version,

  t0.PARENT_ID

FROM RELATIONTEST t0

  START WITH

  (

     IS NULL

  )

  CONNECT BY t0.PARENT_ID = PRIOR t0.ID;

 

On a following test I tried changing it to be = instead of IS and that didn’t make a difference.  The code appears to have a problem with referencing a @ManyToOne when a CONNECT BY is performed.  I understand that typically when you reference a @ManyToOne in JPQL/HQL that it needs to perform a JOIN on that table in order to reference it.  However, in my case, I don’t want the join to happen.  I just want to be able to directly reference the column of that property. 

 

The end result that I am expecting is this:

SELECT *

FROM RELATIONTEST

  START WITH PARENT_ID IS NULL

  CONNECT BY PRIOR ID   = PARENT_ID;

 

I tried stepping through the API and see where it was going wrong, but it’s pretty hard to follow.  I got lost after it generated the SQLSelectStatement object in org.eclipse.persistence.internal.queries.ExpressionQueryMechanism. buildBaseSelectStatement(boolean, Map, boolean).

 

The following attempt does generate valid SQL, but it doesn’t work quite right. 

JPQL:      SELECT rt FROM RelationTest as rt START WITH rt.parent.id IS NULL CONNECT BY rt.children;

 

SQL:        SELECT t0.ID,

  t0.NAME,

  t0.version,

  t0.PARENT_ID

FROM RELATIONTEST t0,

  RELATIONTEST t1

WHERE (t1.ID = t0.PARENT_ID)

  START WITH

  (

    t1.ID IS NULL

  )

  CONNECT BY t0.PARENT_ID = PRIOR t0.ID;

                An inner join is performed on the parent which hoses the results.

 

Am I doing something wrong or is this a limitation of the API?  Any suggestions would be welcome. 

 

Thank you.