jpa - 使用 JPA CriteriaBuilder 的多级子查询
问题描述
我有以下 JPA 实体
@Entity
@Table(name="application_user")
public class ApplicationUser {
@Id
@Column(name="user_id")
private String userid;
@Column(name="last_write_time")
private Instant lastWrite;
//other fields omitted
}
@Entity
@Table(name="demographic")
public class Demographic {
@Id
@Column(name="user_id")
private String userid;
//primary key is a foreign key link
@OneToOne
@PrimaryKeyJoinColumn(name="user_id", referencedColumnName="user_id")
private ApplicationUser user;
//other fields omitted
}
我的目标是检索包含上次写入时间是列中最大值的用户的所有 Demographics。我非常想使用 JPA CriteriaBUilder 编写以下 SQL
select * from demographic where
userid in (
select userid from application_user where
last_write in (
select max(last_write) from application_user
)
)
我尝试编写以下 CriteriaBuilder 代码来实现此目标并成功编译。注意我正在使用生成的元模型类。
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Demographic> c = cb.createQuery(Demographic.class);
Root<Demographic> root = c.from(Demographic.class);
root.fetch(Demographic_.user, JoinType.INNER);
Subquery<Instant> sqLatestUsers = c.subquery(Instant.class);
Root<ApplicationUser> subRootLatestUsers = sqLatestUsers.from(ApplicationUser.class);
sqLatestUsers.select(cb.greatest(subRootLatestUsers.<Instant>get(ApplicationUser_.LAST_WRITE)));
Predicate predicateLatestUsers = subRootLatestUsers.get(ApplicationUser_.LAST_WRITE).in(sqLatestUsers);
Subquery<ApplicationUser> sq = c.subquery(ApplicationUser.class);
Root<Demographic> subRoot = sq.from(Demographic.class);
sq.select(subRoot.<ApplicationUser>get(Demographic_.USER)).where(predicateLatestUsers);
Predicate containsUsers = subRoot.get(Demographic_.USER).in(sq);
c.select(root).where(containsUsers);
代码在 Wildfly 14 中编译并成功部署,但是当我执行代码时,出现以下错误(带有空格以提高可读性):
Invalid path: 'generatedAlias2.user' : Invalid path: 'generatedAlias2.user'
...
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias2.user' [
select generatedAlias0 from com.company.model.Demographic as generatedAlias0
inner join fetch generatedAlias0.user as generatedAlias1
where generatedAlias2.user in (
select generatedAlias2.user from com.company.model.Demographic as generatedAlias2 where generatedAlias3.lastWrite in (
select max(generatedAlias3.lastWrite) from com.company.model.StarfishUser as generatedAlias3
)
)
]
JPA 规范是否允许链接子查询(嵌套子查询)?我是否找到了一些语法正确但实际上不允许的东西?
解决方案
我弄清楚如何让子查询工作。首先是我更新的 Utility 方法
public static <R, T> Subquery<T> getLatestSubelement(CriteriaBuilder cb, CriteriaQuery<R> c, Class<T> clazz, SingularAttribute<T, Instant> attribute) {
//Get latest timestamp
Subquery<Instant> sq = c.subquery(Instant.class);
Root<T> subRoot = sq.from(clazz);
sq.select(cb.greatest(subRoot.<Instant>get(attribute)));
//Get object with the latest timestamp
Subquery<T> sq2 = c.subquery(clazz);
Root<T> subRoot2 = sq2.from(clazz);
sq2.where(subRoot2.get(attribute).in(sq));
return sq2;
}
这是使用实用程序方法的代码
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Demographic> c = cb.createQuery(Demographic.class);
Root<Demographic> root = c.from(Demographic.class);
joinType = JoinType.INNER;
//use fetch instead of join to prevent duplicates in Lists
root.fetch(Demographic_.user, joinType);
Subquery<ApplicationUser> sq = JpaUtil.getLatestSubelement(cb, c, ApplicationUser.class, ApplicationUser_.lastWrite);
c.where(root.get(Demographic_.user).in(sq));
TypedQuery<Demographic> q = em.createQuery(c);
Stream<Demographic> stream = q.getResultStream();
推荐阅读
- android - 获取所有类型的账户 Android API 29
- r - Reading from CSV file in R and formatting dates and times with strptime()
- excel - 过滤表中具有多个范围的 SUMIF 公式
- java - Does Gson’s fromJSON have a parsing limit on the string parameter?
- r - 使用 2 个不同大小的数据帧 R 的子集
- sockets - TServerSocket:如何在发送消息之前检查 ListView 上的特定客户端是否仍然连接?
- angular6 - How to show/hide the agm circle when user clicks on each marker pin - Angular 8
- r - R function writing - getting error: NaNs producedError in tsort[U + 1]only 0's may be mixed with negative subscripts
- amazon-web-services - 阻止公共访问设置的哪些组合使我的 s3 存储桶对所有人可见?
- javascript - Rest parameters produces an error in my react-bootstrap component?