# 光速上手 - JPA 原生 sql DTO 投影

前言

使用 JPA 时，我们一般通过 @Entity 进行实体类映射，从数据库中查询出对象。然而，在实际开发中，有时需要自定义查询结果并将其直接映射到 DTO，而不是实体类。这种需求可以通过 JPA 原生 SQL 查询和 DTO 投影 来实现。博主将以实际开发场景为例，快速摘要如何在 JPA 中实现基于原生 SQL 的 DTO 投影

开始 - 实现步骤

以下是实现 DTO 投影的完整步骤，包括实体类、SQL 映射配置、接口调用和 DTO 设计。

一、配置实体类及映射

首先在实体类中定义 @SqlResultSetMapping，用于将原生 SQL 查询结果映射到 DTO 类。在这个例子中，我们定义了 IssueVideo 实体，并通过 @SqlResultSetMapping 和 @NamedNativeQuery 配置了一个纯sql查询

两个注解详解 (已理解可以跳过)

1. @NamedNativeQuery

核心作用
- @NamedNativeQuery 是用来定义 原生 SQL 查询 的。
- 尽管JPA 中已经为sql 提供了许多方便的解决方式，但是某些场景下，我们还是需要直接使用原生 SQL , 例如：
  1. 数据查询逻辑复杂，无法用 JPQL 表达
  2. 涉及数据库特定的功能（如窗口函数、分区排序等）
  3. 查询结果无法直接映射到实体类（如 DTO、聚合结果）

通过 @NamedNativeQuery，我们可以直接在实体类中绑定一个原生 SQL 查询，并为这个查询命名。在调用时，可以通过指定这个命名的查询名称直接执行该 SQL

2. @SqlResultSetMapping
核心作用
- @SqlResultSetMapping 是用来定义 查询结果的映射 规则的。
- 当我们使用原生 SQL 查询时，返回的结果是数据库的行列数据，与实体类的属性或 DTO 的结构未必完全匹配。
```
@SqlResultSetMapping(name = "", ...) -> 它长这样
```
  它用来告诉 JPA：
  - 查询返回的列和 DTO 的字段如何一一对应
  - 如何将原生 SQL 查询的结果映射为自定义的类（DTO）
  没有 @SqlResultSetMapping 时，JPA 会尝试将查询结果映射到实体类，但如果结果不是直接对应实体类那么映射就会失败。这时，我们就需要 @SqlResultSetMapping 来自定义映射规则。

3. 为什么需要将它们写在实体类上？
- 实体类是 SQL 映射的入口
  在 JPA 中，实体类是我们与数据库表交互的核心对象。因此：
  - 将 @NamedNativeQuery 和 @SqlResultSetMapping 写在实体类上，可以明确这段查询与该实体相关，方便维护和查阅。
  - JPA 的原生查询和结果映射机制依赖于实体类的原数据，通过注解绑定的方式，可以让这些查询和映射规则作为实体类的一部分，便于复用。
- 关联性强
  - @NamedNativeQuery
    定义了原生 SQL 查询，而 @SqlResultSetMapping 定义了如何映射这个查询的结果，它们是 成对使用的。二者写在同一个实体类上，能清晰地表达“该查询和该实体类相关”的逻辑。
  - 如果你把它们分散到其他地方，可能会增加代码复杂性和维护成本。

4. 总结

简单来说，@NamedNativeQuery 和 @SqlResultSetMapping 分别解决了两个不同的问题：

@NamedNativeQuery 负责“如何查询”
它定义了 SQL 的逻辑以及参数。
@SqlResultSetMapping 负责“如何处理查询结果”
它定义了如何将 SQL 返回的数据映射到 DTO中

二者通过查询名称（name 属性）联系在一起。例如：

@NamedNativeQuery(name = "IssueRecommendRespDTOQuery", // 查询名称resultSetMapping = "IssueRecommendRespDTOResult", // 映射↓query = """SELECT ... -- 原生 SQL 查询"""
)
@SqlResultSetMapping(name = "IssueRecommendRespDTOResult", // 映射名称 对应↑classes = @ConstructorResult(targetClass = IssueRecommendRespDTO.class, // 映射到的 DTOcolumns = {@ColumnResult(name = "isdId", type = Integer.class),...})
)

两个注解解释 - end

实体类代码示例

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "issue_video")
// region jpa 投影主页查询目标dto (可以通过 "region <> endregion" 将其折叠起来)
@SqlResultSetMapping(name = "IssueRecommendRespDTOResult",classes = @ConstructorResult(targetClass = IssueRecommendRespDTO.class,columns = {@ColumnResult(name = "isdId", type = Integer.class),@ColumnResult(name = "videoUrl", type = String.class),@ColumnResult(name = "duration", type = Integer.class),@ColumnResult(name = "issId", type = Integer.class),@ColumnResult(name = "title", type = String.class),@ColumnResult(name = "cover", type = String.class),@ColumnResult(name = "watchNum", type = BigInteger.class),@ColumnResult(name = "commentNum", type = Integer.class),@ColumnResult(name = "creTime", type = LocalDateTime.class),@ColumnResult(name = "authorId", type = Integer.class),@ColumnResult(name = "authorName", type = String.class)})
)
// endregion// region jpa 投影主页推荐查询sql
@NamedNativeQuery(name = "IssueRecommendRespDTOQuery",resultSetMapping = "IssueRecommendRespDTOResult",query = """
SELECTsub.isd_id AS isdId,sub.video_url AS videoUrl,sub.duration AS duration,sub.iss_id AS issId,sub.title AS title,sub.cover AS cover,sub.watch_num AS watchNum,sub.comment_num AS commentNum,sub.cre_time AS creTime,sub.u_id AS authorId,sub.name AS authorNameFROM (SELECTi.score,v.isd_id,v.video_url,v.duration,i.iss_id,i.title,i.cover,i.watch_num,i.comment_num,i.cre_time,author.u_id,author.name,casewhen ROW_NUMBER() OVER (PARTITION BY i.su_idORDER BYCASEWHEN sp.su_id IS NOT NULL THEN (i.score + sp.score)ELSE i.scoreEND DESC) <= 3 then 1else 2end AS rank_within_partition,RANK() OVER (PARTITION BY i.su_idORDER BYCASEWHEN sp.su_id IS NOT NULL THEN (i.score + sp.score)ELSE i.scoreEND DESC) as global_rankFROM issue_video vJOIN issue i ON i.iss_id = v.iss_idJOIN user author ON author.u_id = i.u_idLEFT JOIN subarea_preference spON sp.su_id = i.su_idand sp.u_id = :uIdorder by (i.score + sp.score) desc) suborder by rank_within_partition, global_rank
"""
)
// endregion
public class IssueVideo {@GeneratedValue(strategy = GenerationType.IDENTITY)@Idprivate Integer isdId;private String videoUrl;private int duration;private int size;private LocalDateTime updTime;private String remark;private LocalDateTime issueTime;private String permission;private Boolean isDeclare;private Boolean offDanmu;private Boolean offComm;private Boolean onGretestComm;private Boolean isDel;@ManyToOne@JoinColumn(name = "vd_id")private VideoDeclare videoDeclare;private BigInteger danmuNum;@OneToOne@JoinColumn(name = "iss_id")private Issue issue;
}

二、配置对应 JPA 接口

在 JPA 接口中，直接通过 @Query 注解调用刚刚定义的原生 SQL 查询，并将结果映射成期望的 DTO 返回类型

Repository 代码示例

public interface IssueVideoRepo extends JpaRepository<IssueVideo, Integer> {/*** 获取主页推荐视频* @param uId 用户 ID* @return DTO 列表*/@Query(name = "IssueRecommendRespDTOQuery", nativeQuery = true)List<IssueRecommendRespDTO> getRecommendVideos(@Param("uId") Integer uId);
}

三、事务层调用接口

事务层负责调用 Repository 接口，并将返回的结果处理为最终服务层需要的数据。以下为服务实现代码：

Service 代码示例

@Service
public class IssueVideoServiceImpl implements IssueVideoService {@Autowiredprivate IssueVideoRepo issueVideoRepo;@Overridepublic List<IssueRecommendRespDTO> getRecommendVideos(Integer uId) {List<IssueRecommendRespDTO> recommendVideos = null;try {recommendVideos = issueVideoRepo.getRecommendVideos(uId);} catch (Exception exception) {exception.printStackTrace();}return recommendVideos;}
}

四、定义 DTO 类

最后，定义与查询结果对应的 DTO 类。DTO 结构需要与 @SqlResultSetMapping 中的字段一一对应。

DTO 代码示例

@Data // 主dto 不需要lombok 全参数注解 @AllArgsConstructor, 因为要额外配置
public class IssueRecommendRespDTO {private Integer isdId;private String videoUrl;private Integer duration;private IssueRecommendInDTO issue;public IssueRecommendRespDTO() {}public IssueRecommendRespDTO(Integer isdId, String videoUrl, Integer duration,Integer issId, String title, String cover,BigInteger watchNum, Integer commentNum, LocalDateTime creTime,Integer authorId, String authorName) {this.isdId = isdId;this.videoUrl = videoUrl;this.duration = duration;this.issue = new IssueRecommendInDTO(issId, title, cover,watchNum, commentNum, creTime,new AuthorInDTO(authorId, authorName));}
}@AllArgsConstructor
@NoArgsConstructor
@Data
public class IssueRecommendInDTO { // In命名表示 该dto 很可能处于内部使用 > internal : 内部的private Integer issId;private String title;private String cover;private BigInteger watchNum;private Integer commentNum;private LocalDateTime creTime;private AuthorInDTO author;
}@AllArgsConstructor
@NoArgsConstructor
@Data
public class AuthorInDTO {private Integer authorId;private String authorName;
}