java - JPA Criteria API - 查询代码在单独执行时有效,但在用作子查询时无效
问题描述
我有实体调用Issue
和实体调用UserIssue
。UserIssue
延伸Issue
。
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Where(clause = "DELETED_AT IS NULL")
@Entity(name = "ISSUE")
public class Issue extends VersionedSequenceIdEntity {
... all fields
}
@Where(clause = "DELETED_AT IS NULL")
@Entity(name = "USER_ISSUE")
public class UserIssue extends Issue {
...
@Column(name = "IS_PRIVATE", nullable = false)
private Boolean isPrivate;
...
}
我正在做一个类似这篇文章的子查询,以通过 sublcas 属性进行一些过滤。我的确切测试用例是我创建了三个 UserIssues。其中两个具有 false 的“isPrivate”属性。第三个有 isPrivate on true。
当我执行这个实际上只是子查询的代码时,结果包含两个基于条件的“问题”并且它是正确的
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<UserIssue> issueQuerySimple = cb.createQuery(UserIssue.class);
Root<UserIssue> issueRootSimple = issueQuerySimple.from(UserIssue.class);
issueQuerySimple.select(issueRootSimple).where(cb.isFalse(issueRootSimple.get("isPrivate")));
List<UserIssue> resultListSimple = entityManager.createQuery(issueQuerySimple).getResultList();
但是,当我构建将上面的代码用作子查询的整个查询时,会返回所有三个“问题”并且出现问题。我希望代码只返回两个“isPrivate”属性设置为 false 的“问题”
CriteriaQuery<Issue> issueQuery = cb.createQuery(Issue.class);
Root<Issue> issueRoot = issueQuery.from(Issue.class);
Subquery<UserIssue> subQuery = issueQuery.subquery(UserIssue.class);
Root<UserIssue> userIssueRoot = subQuery.from(UserIssue.class);
subQuery.select(userIssueRoot).where(cb.isFalse(userIssueRoot.get("isPrivate")));
issueQuery.select(issueRoot).where(cb.exists(subQuery));
List<Issue> resultList = entityManager.createQuery(issueQuery).getResultList();
在这里,您还可以看到来自 hibernate 创建的日志的查询。对我来说似乎是正确的
select
issue0_.id as id2_2_,
issue0_.deleted_at as deleted_3_2_,
issue0_.created_when as created_4_2_,
issue0_.created_by as created_5_2_,
issue0_.updated_when as updated_6_2_,
issue0_.version as version7_2_,
issue0_.application_id as applicat8_2_,
issue0_.version_id as version_9_2_,
issue0_.organization_id as organiz10_2_,
issue0_.severity as severit11_2_,
issue0_.state as state12_2_,
issue0_.title as title13_2_,
issue0_.type_id as type_id18_2_,
issue0_.updated_by_customer_at as updated14_2_,
issue0_.assigned_to as assigne15_2_,
issue0_.description as descrip16_2_,
issue0_.is_private as is_priv17_2_,
issue0_.dtype as dtype1_2_
from
issue issue0_
where
(
issue0_.DELETED_AT IS NULL
)
and (
exists (
select
userissue1_.id
from
issue userissue1_
where
userissue1_.dtype='UserIssue'
and (
userissue1_.DELETED_AT IS NULL
)
and userissue1_.is_private=0
)
)
那么为什么基于子类字段的过滤不起作用并且它返回所有三个实例而不是两个?有什么问题或我看不到什么?
非常感谢
解决方案
代替
issueQuery.select(issueRoot).where(cb.exists(subQuery));
和
issueQuery.select(issueRoot).where(issueRoot.get("id").in(subQuery));
解释
- 从sql查询我们可以看出,只要有一个
userissue
withis_private=0
存在,就会选择所有的issue
s - 这不是你想要的行为。您仍然必须使用子查询,但想要替换
exists
withissue0_.id in
并且这个代码片段可以做到这一点
推荐阅读
- c# - PUT Ajax 请求返回 404 not found asp.net MVC
- python - Python3 pickle:AttributeError:无法获取属性'CHJ_tiny_obj'
- dvc - 如何为不同的文件夹使用不同的遥控器?
- wordpress - 为什么我登出后看不到我的 WordPress 网站?
- python - 对于 Pandas 数据框中的每一行,检查行是否包含列表中的字符串
- java - GAE - 找不到类:原因:java.lang.ClassNotFoundExceptionAppIdentityCredential$AppEngineCredentialWrapper
- excel - VBA过滤两列中的值并删除行
- javascript - 如何在单击按钮时动态更改图形?
- sql - 如何检索月份数据?
- plugins - JDK11失败的Liquibase maven插件