java - 使用具有一对多关系的 CriteriaBuilder 的 Spring Data JPA 规范

Question

我有一个 User实体，一个 UserToApplication实体和 Application实体。

单个 User可以访问多个 Application .还有一个Application可供多人使用User .

这里是 User实体。

@Entity
@Table(name = "USER", schema = "UDB")
public class User {
    private Long userId;
    private Collection<Application> applications;
    private String firstNm;
    private String lastNm;
    private String email;

    @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_SEQ", initialValue = 1, allocationSize = 1)
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator")
    @Column(name = "USER_ID", unique = true, nullable = false)
    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    public Collection<Application> getApplications() {
        return applications;
    }

    public void setApplications(Collection<Application> applications) {
        this.applications = applications;
    }

    /* Other getters and setters omitted for brevity */
}

这里是 UserToApplication实体。

@Entity
@Table(name = "USER_TO_APPLICATION", schema = "UDB")
public class Application {
    private Long userToApplicationId;
    private User user;
    private Application application;

    @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_TO_APP_SEQ", initialValue = 0, allocationSize = 1)
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator")
    @Column(name = "USER_TO_APPLICATION_ID", unique = true, nullable = false)
    public Long getUserToApplicationId() {
        return userToApplicationId;
    }

    public void setUserToApplicationId(Long userToApplicationId) {
        this.userToApplicationId = userToApplicationId;
    }

    @ManyToOne
    @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID", nullable = false)
    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @ManyToOne
    @JoinColumn(name = "APPLICATION_ID", nullable = false)
    public Application getApplication() {
        return application;
    }
}

这里是 Application实体。

@Entity
@Table(name = "APPLICATION", schema = "UDB")
public class Application {
    private Long applicationId;
    private String name;
    private String code;

    /* Getters and setters omitted for brevity */
}

我有以下 Specification我用来搜索 User由 firstNm , lastNm , 和 email .

public class UserSpecification {

    public static Specification<User> findByFirstNmLastNmEmail(String firstNm, String lastNm, String email) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                final Predicate firstNmPredicate = null;
                final Predicate lastNmPredicate = null;
                final Predicate emailPredicate = null;

                if (!StringUtils.isEmpty(firstNm)) {
                    firstNmPredicate = cb.like(cb.lower(root.get(User_.firstNm), firstNm));
                }
                if (!StringUtils.isEmpty(lastNm)) {
                    lastNmPredicate = cb.like(cb.lower(root.get(User_.lastNm), lastNm));
                }
                if (!StringUtils.isEmpty(email)) {
                    emailPredicate = cb.like(cb.lower(root.get(User_.email), email));
                }
                return cb.and(firstNmPredicate, lastNmPredicate, emailPredicate);
            }
        };
    }

}

这里是 User_我目前拥有的元模型。

@StaticMetamodel(User.class)
public class User_ {
    public static volatile SingularAttribute<User, String> firstNm;
    public static volatile SingularAttribute<User, String> lastNm;
    public static volatile SingularAttribute<User, String> email;
}

现在，我还想将应用程序 ID 列表传递给 Specification , 这样它的方法签名将是:

public static Specification<User> findByFirstNmLastNmEmailApp(String firstNm, String lastNm, String email, Collection<Long> appIds)

所以，我的问题是，如果我添加 @OneToMany映射到 User_ Collection<Application> applications 的元模型我的领域 User实体，那么我将如何在 Specification 中引用它?

我的当前Specification将类似于以下 SQL 查询:

select * from user u
where lower(first_nm) like '%firstNm%'
and lower(last_nm) like '%lastNm%'
and lower(email) like '%email%';

以及我希望在新的 Specification 中实现的目标会是这样的:

select * from user u
join user_to_application uta on uta.user_id = u.user_id
where lower(u.first_nm) like '%firstNm%'
and lower(u.last_nm) like '%lastNm%'
and lower(u.email) like '%email%'
and uta.application_id in (appIds);

是否可以在元模型中进行这种映射，如何在我的 Specification 中实现这种结果？ ?

Answer 1

我找到了解决方案。为了映射一对多属性，我在元模型中添加了以下内容:

public static volatile CollectionAttribute<User, Application> applications;

我还需要为 Application 添加元模型实体。

@StaticMetamodel(Application.class)
public class Application_ {
    public static volatile SingularAttribute<Application, Long> applicationId;
}

然后在我的Specification ，我可以访问 applications对于用户，使用 .join() Root<User> 上的方法实例。这里是 Predicate我形成了。

final Predicate appPredicate = root.join(User_.applications).get(Application_.applicationId).in(appIds);

另外，值得注意的是我的Specification如问题中所写，如果任何输入值为空，则将不起作用。一个空值 Predicate传递给 .and() CriteriaBuilder的方法|将导致 NullPointerException .所以，我创建了一个 ArrayList类型 Predicate ，然后添加每个 Predicate如果相应的参数不为空，则添加到列表中。最后，我将 ArrayList到一个数组以将其传递给 .and() CriteriaBuilder 的功能.这是最终的Specification :

public class UserSpecification {

    public static Specification<User> findByFirstNmLastNmEmailApp(String firstNm, String lastNm, String email, Collection<Long> appIds) {
        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                final Collection<Predicate> predicates = new ArrayList<>();
                if (!StringUtils.isEmpty(firstNm)) {
                    final Predicate firstNmPredicate = cb.like(cb.lower(root.get(User_.firstNm), firstNm));
                    predicates.add(firstNmPredicate);
                }
                if (!StringUtils.isEmpty(lastNm)) {
                    final Predicate lastNmPredicate = cb.like(cb.lower(root.get(User_.lastNm), lastNm));
                    predicates.add(lastNmPredicate);
                }
                if (!StringUtils.isEmpty(email)) {
                    final Predicate emailPredicate = cb.like(cb.lower(root.get(User_.email), email));
                    predicates.add(emailPredicate);
                }
                if (!appIds.isEmpty()) {
                    final Predicate appPredicate = root.join(User_.applications).get(Application_.applicationId).in(appIds);
                    predicates.add(appPredicate);
                }

                return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
    }

}