# 光速上手 - 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;
}

总结

       上述步骤以经过实际开发测试，保证有效！

1. 高效性：直接通过原生 SQL 查询所需数据，减少不必要字段的查询和映射。
2. 灵活性：可以自由定义 DTO 结构，满足复杂查询的需求。
3. 可维护性：使用 @SqlResultSetMapping 将 SQL 与 Java 类关联，便于后续维护。

       该文章适用于需要自定义复杂查询且无需将查询结果绑定到实体类的场景。如果你也有类似需求，不妨以文章为参照上手试试！

end…

如果这篇文章帮到你, 帮忙点个关注呗, 不想那那那点赞或收藏也行鸭 (｡•̀ᴗ-)✧ ~

                                                                                                                                   '(இ﹏இ`｡)