java - 如何在子查询(hql)中选择多列

Question

有没有办法在hql的子查询中选择多列？

我想将以下 mysql 查询转换为 hql

select u.name, sub.cnt from user u
  inner join (select user_id, count(user_id) cnt from user_log group by user_id order by cnt desc limit 5) sub 
    on u.id = sub.user_id;

但是在hql中，我知道只能在where子句中使用子查询

Answer 1

我不知道有纯粹的 Hibernate 解决方案。我在我的博客文章中谈到了这一点。

但是您可以考虑一个 FluentJPA 解决方案:

FluentQuery query = FluentJPA.SQL((User u) -> {

    UserIdCount sub = subQuery((UserLog log) -> {
        int count = alias(COUNT(log.getUserId()), UserIdCount::getCount);
        SELECT(log.getUserId(), count);
        FROM(log);
        ORDER(BY(count).DESC());
        LIMIT(5);
    });

    SELECT(u.getName(), sub.getCount());
    FROM(u).JOIN(sub).ON(u.getId() == sub.getUserId());
});

return query.createQuery(em, UserNameCount.class).getSingleResult();

生成以下 SQL:

SELECT t0.name, q0.count 
FROM USER t0  INNER JOIN (SELECT t1.user_id, COUNT(t1.user_id) AS count 
FROM USER_LOG t1 
ORDER BY  COUNT(t1.user_id)  DESC   
LIMIT 5 ) q0  ON (t0.id = q0.user_id)

我使用的实体(用 lombok 声明):

@Entity
@Getter
@Table(name = "USER")
class User {
    private Long id;
    private String name;
}

@Entity
@Getter
@Table(name = "USER_LOG")
class UserLog {
    private Long userId;
}

@Tuple
@Getter
class UserIdCount {
    private Long userId;
    private int count;
}

@Tuple
@Data
class UserNameCount {
    private int count;
    private String name;
}