如何使用 jpa 规范向子查询添加和构建动态谓词？

Question

我正在尝试使用 JPA 规范构建动态子查询。如何将谓词添加到子查询并构建它？

例如，我将有 2 个表：User 和 Usercard：

@Entity

    @Table(name = "users", schema = "someschema")

    public class User {

    @Id

    @GeneratedValue(strategy = GenerationType.AUTO)

    private Long id;

    private String name;

    private String surName;

    private String email;

    @OneToMany

    private List<Usercard> usercardList;

//other methods...

}

和

@Entity

@Table(name = "usercard", schema = "someschema")

public class Usercard {

    @Id

    private Long id;

    private String account;

    private String value;

    @ManyToOne

    private User user;

//other methods...

}

我有我的回购协议：

@Repository

public interface UserRepository extends JpaRepository<User, Long>, 

JpaSpecificationExecutor<User> {}

并尝试构建类似的东西：

public List<User> findByPredicate(String email) {

    return userRepository.findAll((Specification<User>) (root, 

        criteriaQuery, criteriaBuilder) -> {

        List<Predicate> predicates = new ArrayList<>();

        if (email != null) {

        predicates.add(criteriaBuilder.and(criteriaBuilder.equal( 

                                       root.get("email"), email)));

        }

        return criteriaBuilder.and(predicates.toArray(new 

                Predicate[predicates.size()]));

    });

}

但是对于带有谓词的子查询。

我试过这样的方法：

public List<User> findByUsercardAccount(String email, String account) {

    return userRepository.findAll(new Specification<User>() {

        @Override

        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> 

            criteriaQuery, CriteriaBuilder criteriaBuilder) {

            Subquery<Usercard> subquery = 

                criteriaQuery.subquery(Usercard.class);

            Root<Usercard> subRoot = subquery.from(Usercard.class);

            List<Predicate> predicates = new ArrayList<>();

            //predicates for Users table

            predicates.add(criteriaBuilder.and(criteriaBuilder.equal( 

                       root.get("email"), email)));

            //predicates for Usercard table

因此，我需要一种在几个表中进行动态搜索的方法，我可以在其中为动态查询和动态子查询传递参数。如果有任何帮助，我将不胜感激。

Answer 1

找到了这个解决方案：public List<User> findByUsercardAccount(String account, String email) {&nbsp; &nbsp; return userRepository.findAll((Specification<User>) (root, criteriaQuery, criteriaBuilder) -> {&nbsp; &nbsp; &nbsp; &nbsp; Subquery<User> subquery = criteriaQuery.subquery(User.class);&nbsp; &nbsp; &nbsp; &nbsp; Root<Usercard> subRoot = subquery.from(Usercard.class);&nbsp; &nbsp; &nbsp; &nbsp; List<Predicate> predicates = new ArrayList<>();&nbsp; &nbsp; &nbsp; &nbsp; List<Predicate> subPredicates = new ArrayList<>();&nbsp; &nbsp; &nbsp; &nbsp; if (account != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; subPredicates.add(criteriaBuilder.equal(subRoot.get("account"), account));&nbsp; &nbsp; &nbsp; &nbsp; if (email != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("email"), email)));&nbsp; &nbsp; &nbsp; &nbsp; subquery.select(subRoot.get("id")).where(subPredicates.toArray(new Predicate[predicates.size()]));&nbsp; &nbsp; &nbsp; &nbsp; predicates.add(criteriaBuilder.exists(subquery));&nbsp; &nbsp; &nbsp; &nbsp; return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));&nbsp; &nbsp; });}}因此，我创建了子根和子查询，添加了一些限制（subPredicates）并将它们作为谓词传递给主根。