Spring Boot中如何将带count关联子查询的SQL转换为JPQL

前置映射要求 #

JPQL是面向JPA实体对象的查询语言，改写前请确认实体映射符合规范：

• 话题实体类名为Topic，映射数据库表topics，包含属性id（主键）、title（话题标题）
• 点赞实体类名为TopicLike，映射数据库表topic_like，包含属性id（主键），可通过关联属性topic（多对一映射到Topic实体，对应外键列topic_id）关联到所属话题；如果未做实体关联，也可以直接用topicId属性映射topic_id列

可直接运行的JPQL语句 #

如果TopicLike配置了和Topic的多对一关联，使用以下JPQL：

SELECT t.id, t.title,
       (SELECT COUNT(l.id) FROM TopicLike l WHERE l.topic.id = t.id) AS countt
FROM Topic t

如果TopicLike没有配置实体关联，直接用topicId字段映射外键列，将子查询条件调整为对应属性即可：

SELECT t.id, t.title,
       (SELECT COUNT(l.id) FROM TopicLike l WHERE l.topicId = t.id) AS countt
FROM Topic t

Spring Data JPA 集成示例 #

在自定义的Topic Repository接口中，通过@Query注解写入上述JPQL即可，不要配置nativeQuery = true，可通过接口投影接收返回结果：

1. Repository层代码

public interface TopicRepository extends JpaRepository<Topic, Long> {

    @Query("SELECT t.id, t.title, " +
            "(SELECT COUNT(l.id) FROM TopicLike l WHERE l.topic.id = t.id) AS countt " +
            "FROM Topic t")
    List<TopicLikeCountProjection> listTopicWithLikeCount();
}

2. 投影接口（用于接收查询结果，方法名和JPQL中返回的别名、属性名对应即可）

public interface TopicLikeCountProjection {
    Long getId();
    String getTitle();
    Long getCountt();
}

注意事项 #

• JPQL中实体类名、实体属性名严格区分大小写，SQL关键字（SELECT、FROM、COUNT等）不区分大小写
• 不要在JPQL中直接写原生表名、物理列名，所有引用必须对应到托管实体的类名和属性名
• 如果需要返回实体对象+点赞数的结构，也可以构造对应VO类接收结果，在JPQL中通过new关键字调用VO全参构造即可

内容的提问来源于stack exchange，提问作者S3D