java - 如何使用 JPA 中的规范连接多个列?
问题描述
我正在尝试将查询转换为 JPA 规范,该查询包含带有 OR 条件的 JOIN 操作。
这是查询:
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)
我试图编写一个规范,但我被阻止如何使用 OR 条件加入多个列。
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));
}
};
}
}
用户实体:
@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;
-----
}
运营实体
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;
.....
}
我希望有一个规范来替换上面的查询
解决方案
您可以尝试使用子查询而不是连接表达式
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 查询
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
)
from User u
是一个较短的替代品select u from User u
我写了这段代码没有运行,所以它需要修改
推荐阅读
- scala - 如何将错误消息动态传递给scala中的自定义异常
- salt-stack - 为什么我的 Salt Minion 没有显示任何支柱属性?
- vue.js - 道具至少应该定义它们的类型
- java - Java Graph Dijkstras 算法
- android - 构建 Linphone:错误:缺少以下二进制文件:ndk-build。请安装它们
- android - Android 立即在 ui 线程上运行
- c# - 接收完整数据
- postgresql - 致命:找不到所需的检查点记录
- c# - IP 日志记录为 2FA?'void' 到 'string' 和 'Form1.getMyIPAddress()' 的问题
- c++ - 输出没有临时的返回对