So I am trying to replace the following native SQL with JPA Criteria Api:

select CAT.* from CAT
join OWNER.ID = CAT.OWNER_ID
where OWNER.NAME = :ownerName

or

select CAT.* from CAT, OWNER
where OWNER.ID = CAT.OWNER_ID 
and OWNER.NAME = :ownerName

The entities look a bit like this:

class Owner {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", unique = true, nullable = false)
    private Long id;

    @Column(name = "NAME", length = 15)
    private String name;

    ...
}

class Cat {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", unique = true, nullable = false)
    private Long id;

    @Column(name = "OWNER_ID", nullable = false)
    private Long ownerId;

    @Column(name = "NAME", length = 15)
    private String name;

    ...
}

I implemented a org.springframework.data.jpa.domain.Specification I can use together with a JpaSpecificationExecutor like:

@Primary
@Repository
public interface CatRepository
    extends JpaRepository<Cat, Long>, JpaSpecificationExecutor<Cat> {}

and

interface Function3<ARG1, ARG2, ARG3, RETURN> {
   RETURN apply(ARG1 arg1, ARG2 arg2, ARG3 arg3);
}

public static <TYPE> Specification<TYPE> create(
      final Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> predicate) {
    return (root, query, criteriaBuilder) -> {
      return predicate.apply(root, (AbstractQuery<TYPE>) query, criteriaBuilder);
    };
}

 public static <TYPE, JOINTYPE>
      Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> join(
          Class<JOINTYPE> joinClass,
          String joinColumn,
          String joiningColumn,
          final Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> predicate,
          final Function3<Root<JOINTYPE>, AbstractQuery<JOINTYPE>, CriteriaBuilder, Predicate>
              joinPredicate) {
    return (root, criteriaQuery, criteriaBuilder) -> {
      CriteriaQuery<JOINTYPE> joinQuery = criteriaBuilder.createQuery(joinClass);
      Root<JOINTYPE> joinRoot = joinQuery.from(joinClass);
      //TODO add a filter here for Owner.name here
      // joinQuery.where(joinPredicate.apply(joinRoot, joinQuery, criteriaBuilder));

      return criteriaBuilder.equal(root.get(joinColumn), joinRoot.get(joiningColumn));
    };
}

But I only get one side of the join/select like:

select 
   generatedAlias0 
from 
   com.some.pckg.Cat as generatedAlias0 
where 
   generatedAlias0.ownerId=generatedAlias1.id

when I do:

catRepo.find(create(join(Owner.class,"ownerId","id", null, null)));

How do I add the table to the select here? So the final generated value looks like:

select 
   generatedAlias0 
from 
   com.some.pckg.Cat as generatedAlias0,
   com.some.pckg.Owner as generatedAlias1 
where 
   generatedAlias0.ownerId=generatedAlias1.id

CodePudding user response：

The problem is that you are creating a new query in the specification.
You need to extend an existing one, which passed into specification.
1. Relation between entities is not specified

    static <TYPE, JOINTYPE> Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> join(Class<JOINTYPE> joinClass, String joinColumn, String joiningColumn) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            Root<JOINTYPE> joinRoot = criteriaQuery.from(joinClass);
            return criteriaBuilder.equal(root.get(joinColumn), joinRoot.get(joiningColumn));
        };

2. @ManyToOne relation defined

@Entity
public class Cat {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", unique = true, nullable = false)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "OWNER_ID", nullable = false)
    private Owner owner;

    @Column(name = "NAME", length = 15)
    private String name;
}

    static <TYPE, JOINTYPE> Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> join(Class<JOINTYPE> joinClass, String relationPropery) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            root.join(relationPropery);
            return criteriaBuilder.conjunction();
        };
    }

Full implementation:

@Repository
public interface CatSpecificationRepository extends  JpaRepository<Cat, Long>, JpaSpecificationExecutor<Cat> {

    static <TYPE> Specification<TYPE> create(final Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> predicate) {
        return (root, query, criteriaBuilder) -> predicate.apply(root, (AbstractQuery<TYPE>) query, criteriaBuilder);
    }

    static <TYPE, JOINTYPE> Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> join(
            Class<JOINTYPE> joinClass,
            String joinColumn,
            String joiningColumn,
            final Function3<Root<TYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> predicate,
            final Function3<Root<JOINTYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate> joinPredicate) {
        return (root, criteriaQuery, criteriaBuilder) -> {
            Root<JOINTYPE> joinRoot = criteriaQuery.from(joinClass);
            List<Predicate> predicates = new LinkedList<>();
            predicates.add(criteriaBuilder.equal(root.get(joinColumn), joinRoot.get(joiningColumn)));

            if (joinPredicate != null) {
                predicates.add(joinPredicate.apply(joinRoot, criteriaQuery, criteriaBuilder));
            }

            if (predicate != null) {
                predicates.add(predicate.apply(root, criteriaQuery, criteriaBuilder));
            }

            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        };
    }

    static <TYPE, JOINTYPE> Function3<Root<JOINTYPE>, AbstractQuery<TYPE>, CriteriaBuilder, Predicate>isNameEquals(String value) {
        return value != null ? (root, query, builder) -> builder.equal(root.get("name"), value) : null;
    }
}

Example of usage

catSpecificationRepository.findAll(create(join(Owner.class,"ownerId","id", null, isNameEquals(value))));

Generated query

    select
        cat0_.id as id1_2_,
        cat0_.name as name2_2_,
        cat0_.owner_id as owner_id3_2_ 
    from
        cat cat0_ cross 
    join
        owner owner1_ 
    where
        cat0_.owner_id=owner1_.id 
        and owner1_.name=?

Please note, current SQL query construction:

from 
   cat cross join owner
where 
   cat.owner_id=owner.id 

is equivalent to

from 
   cat inner join owner on (cat.owner_id=owner.id )

or

from 
   cat, owner
where 
   cat.owner_id=owner.id