標準API加入過濾器

所以我試圖用 JPA Criteria Api 替換以下原生 SQL：

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

或者

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

物體看起來有點像這樣：

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;

    ...
}

我實作了一個org.springframework.data.jpa.domain.Specification可以與 JpaSpecificationExecutor 一起使用的方法，例如：

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

和

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));
    };
}

但我只得到加入/選擇的一側，例如：

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

當我做：

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

如何將表格添加到此處的選擇中？所以最終生成的值如下所示：

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

uj5u.com熱心網友回復：

問題是您正在規范中創建一個新查詢。
您需要擴展現有的，它已傳遞到規范中。
1.物體之間的關系沒有明確

    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關系定義

@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();
        };
    }

全面實施：

@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;
    }
}

使用示例

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

生成的查詢

    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=?

請注意，當前的 SQL 查詢構造：

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

相當于

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

或者

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

標籤：爪哇 jpa 休眠标准 标准-api

上一篇：如何將Map映射到SpringDataJpa中的Repository中的查詢？

下一篇：JPA-Eclipselink快取嵌套表（物體）