代码之家 › 专栏 › 技术社区 › Mike

使用JPA CriteriaBuilder的多级子查询

jpa

Mike · 技术社区 · 7 年前

我有以下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
}

我的目标是检索包含上次写入时间为列中最大值的用户的所有人口统计信息。我非常想使用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规范是否允许链接子查询(嵌套子查询)?我是否找到了语法正确但实际上不允许的东西?

1 回复 | 直到 7 年前

Mike 7 年前

我知道如何让子查询工作。首先是我更新的实用程序方法

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();