1 Reply Latest reply on May 30, 2014 2:49 PM by Cdelahun-Oracle

    Criteria API - Avoiding a crossjoin when using a composite / embeddedId PK

    3509ff76-6a5c-4679-9005-8c962cb1fe22

      Hi, Im using Spring Data's specification framework and have the following toPredicate() method. Its almost working except the "selectedTargetSubquery.from(SelectedTarget)" part of the query is generating a "cross join" which is resulting in too many rows.

       

      Is there a way I can do an inner join (using Join<X,Y> xx = ) ?  it seems to be impossible with composite keys

       

       

       

       

      @Override

          Predicate toPredicate(Root<Target> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

       

       

              //we want to query SelectedTarget on  activationUUID to find targets to exclude

       

       

              Subquery<SelectedTarget> selectedTargetSubquery = query.subquery(SelectedTarget.class)

              Root<SelectedTarget> selectedTargetRoot = query.from(SelectedTarget.class)

              Path<SelectedTargetPK> selectedTargetPKPath = selectedTargetRoot.get("selectedTargetPK")

              Path<Target> targetPath = selectedTargetPKPath.get("target")

       

       

              selectedTargetSubquery.select(targetPath.get("targetId"))

              selectedTargetSubquery.from(SelectedTarget)

              selectedTargetSubquery.where(selectedTargetPKPath.get("activationUUID").in([activationUUID]))

       

       

              return root.get("targetId").in(selectedTargetSubquery).not()

       

       

          }

       

       

       

       

       

       

       

      Here is where I tried a Join: but get error :  java.lang.ClassCastException: org.hibernate.ejb.metamodel.SingularAttributeImpl$Identifier cannot be cast to javax.persistence.metamodel.ManagedType

       

          @Override

          Predicate toPredicate(Root<Target> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

       

       

              //we want to query SelectedTarget on  activationUUID to find targets to exclude

              Subquery<SelectedTarget> selectedTargetSubquery = query.subquery(SelectedTarget.class)

              Root<SelectedTarget> selectedTargetRoot = query.from(SelectedTarget.class)

              Join<SelectedTargetPK> selectedTargetPKPath = selectedTargetRoot.join("selectedTargetPK")       ///  TRYING JOIN HERE

              Path<Target> targetPath = selectedTargetPKPath.get("target")

       

       

              selectedTargetSubquery.select(targetPath.get("id"))

              selectedTargetSubquery.from(SelectedTarget)

              selectedTargetSubquery.where(selectedTargetPKPath.get("activationUUID").in([activationUUID]))

       

              return root.get("id").in(selectedTargetSubquery).not()

       

       

          }

        • 1. Re: Criteria API - Avoiding a crossjoin when using a composite / embeddedId PK
          Cdelahun-Oracle

          Two problems with this.  The first, you are using Hibernate on a Oracle TopLink forum

          The second, you are trying to join over what seems to be a composite pk class.  Your "selectedTargetPK" fields exist already in the selectedTargetRoot table, so there is or should be nothing to join.  I'm guessing it is an embeddedID class mapping in your SelectedTarget, not a separate entity/table.  There is no need to do a table join.

           

          Where you want the join is between the SelectedTarget and Target relationship.  I assume that would be the selectedTargetPKPath.get("target").  This is equivalent to using an inner join already though, and so probably not the problem.  The issue is that you are building the subquery on selectedTargetSubquery.select(targetPath.get("id")) based on the expression you've built up in the prior lines, but then specifying an entirely different root using selectedTargetSubquery.from(SelectedTarget).  So the sub query gets built using a new version of the table rather than the one you built the where clause and select clause from.

           

          What you instead might try is:

                  Subquery<<SelectedTargetIDType>> selectedTargetSubquery = query.subquery(<SelectedTargetIDType>);//replace <SelectedTargetIDType> with the type used for SelectedTarget.ID since that is what you are returning

                  Root<SelectedTarget> selectedTargetRoot = selectedTargetSubquery.from(SelectedTarget.class);//notice the expressions are being built off the sub query's root?

                  Path<SelectedTargetPK> selectedTargetPKPath = selectedTargetRoot.get("selectedTargetPK");

                  Path<Target> targetPath = selectedTargetPKPath.get("target");//this causes a join between the SelectedTarget and Target tables

           

                  selectedTargetSubquery.select(targetPath.get("id"))//

                  selectedTargetSubquery.where(selectedTargetPKPath.get("activationUUID").in([activationUUID]))

           

                 return root.get("id").in(selectedTargetSubquery).not();// this is where you associate the subquery into the main query.  It should result in 2 target tables and 1 SelectedTarget table being used