如何在JPA中使用规范连接多个列？ [英] How to join multiple columns using Specification in JPA?

问题描述

我正在尝试将查询转换为JPA规范，该查询包含具有OR条件的JOIN操作。

I'm trying to convert a query into a JPA Specification, the query contains JOIN operation with OR condition.

这里是查询：

select u from User u inner join Operation o on (u.id = o.verificateur1 or u.id = o.verificateur2) where o.id not in (:ids)

我试图编写规范，但在如何加入方面受阻

I tried to write a specification but I'm blocked on how to join multiple column with OR condition.

public class UserSpecification {

    public static Specification<User> UsersNotInSelectedOperations(final List<Long> operationId ){

        return new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Join<User, Operation> userJoinOp = root.join("fk_user1_id");
                final Path<User> users = userJoinOp.get("id");
                return criteriaBuilder.not(users.in(operationId));
            }
        };
    }

}

用户实体：

the User entity :

@Entity
@Table(name = "aigle_user")
public class User extends AbstractEntity implements UserDetails {

    private static final long serialVersionUID = 2840226091237599675L;

    @Column(name = "mail", nullable = true)
    private String mail;

    @Column(name = "password")
    private String password;

    @Column(name = "is_activated")
    private boolean isActivated;

    @Column(name = "is_admin")
    private boolean isAdmin;

    @ManyToMany(cascade = { CascadeType.PERSIST })
    @LazyCollection(LazyCollectionOption.FALSE)
    @JoinTable(name = "aigle_group_user", joinColumns = @JoinColumn(name = "fk_user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "fk_group_id", referencedColumnName = "id"))
    private Set<Group> groups;

    @ManyToMany(cascade = { CascadeType.PERSIST })
    @LazyCollection(LazyCollectionOption.FALSE)
    @JoinTable(name = "aigle_role_user", joinColumns = @JoinColumn(name = "fk_user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "fk_role_id", referencedColumnName = "id"))
    private Set<Role> roles;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "destinataire", orphanRemoval = true)
    private Set<Tache> taches;

    @Column(name = "last_name", unique = false)
    private String lastName;

    @Column(name = "first_name", unique = false)
    private String firstName;

    @Column(name = "telephone")
    private String telephone;

    private String salesforceId;

-----
}

操作实体

The Operation Entity

public class Operation extends OperationField {

...
    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_user1_id")
    private User verificateur1;

    @ManyToOne(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_user2_id")
    private User verificateur2;
.....
}

I期望替换上面查询的规范

I expect a Specification that replace the query above

推荐答案

您可以尝试使用子查询而不是join

You can try to use subquery instead of join expression

    public static Specification<User> UsersNotInSelectedOperations(final List<Long> operationIds) {
            return (root, query, builder) -> {
                    Subquery<Operation> subquery = query.subquery(Operation.class);
                    Root<Operation> operation = subquery.from(Operation.class);

                    Predicate predicate1_1 = builder.equal(operation.get("verificateur1").get("id"), root.get("id"));
                    Predicate predicate1_2 = builder.equal(operation.get("verificateur2").get("id"), root.get("id"));

                    Predicate predicate1 = builder.or(predicate1_1, predicate1_2); 
                    Predicate predicate2 = operation.get("id").in(operationIds).not();

                    subquery.select(operation).where(predicate1, predicate2);

                    return builder.exists(subquery);
            };
   }

使用此规范，您将获得像这样的HQL查询

Using this specification you get HQL query like this

from User u
where exists( // subquery ->
  from Operation o
  where (o.verificateur1.id = u.id or o.verificateur2.id = u.id) // predicate 1
  and o.id not in (:ids) // predicate 2
  // <- subquery
)

来自用户u 较短替换从用户u中选择u

我编写的这段代码没有运行，因此需要修订

I wrote this code without running, so it needs revision

这篇关于如何在JPA中使用规范连接多个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！