PostgreSQL存储过程到Spring Boot原生SQL查询的迁移指南-小浪学习网

PostgreSQL存储过程到Spring Boot原生SQL查询的迁移指南

本文详细介绍了如何将postgresql数据库中的存储过程（SP）逻辑转换为spring Boot应用中可直接使用的原生SQL查询。通过分析原始SP的结构和功能，文章提供了两种在Spring Data JPA的@Query注解中集成SQL查询的方法：使用命名参数和使用序号参数，并附带示例代码，旨在帮助开发者实现数据库逻辑的解耦和Java应用的简化。

迁移背景与动机

在现代应用开发中，尤其是在采用微服务架构和spring boot等框架时，将业务逻辑从数据库存储过程迁移到应用层是一种常见的实践。这种迁移有助于提高代码的可维护性、可测试性、可移植性，并允许开发者使用统一的编程语言（如java）来管理业务逻辑。本教程将以一个具体的postgresql存储过程为例，展示如何将其核心查询逻辑转换为spring data jpa可调用的原生sql查询。

原始PostgreSQL存储过程分析

我们首先分析原始的PostgreSQL存储过程spfetchowner：

CREATE OR REPLACE PROCEDURE public.spfetchowner(     owner Integer,     optype integer,     INOUT p_refcur refcursor) LANGUAGE 'plpgsql' AS $BODY$ BEGIN     OPEN p_refcur for     select         z.owner_num,         COALESCE(op_type_num, optype) AS op_type_num,         ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday         FROM (SELECT owner AS owner_num) AS z         LEFT OUTER JOIN owner_details AS ad             ON z.owner_num = ad.owner_num AND op_type_num = optype; END; $BODY$;

该存储过程接收两个输入参数：owner (整数类型) 和 optype (整数类型)，以及一个INOUT类型的refcursor参数p_refcur，用于返回查询结果集。其核心逻辑是一个SELECT语句，执行以下操作：

参数引入: (SELECT owner AS owner_num) AS z 创建了一个临时表z，将输入参数owner作为owner_num列引入到查询中。
左外连接: 将z表与owner_details表进行左外连接，连接条件是z.owner_num = ad.owner_num并且ad.op_type_num = optype（这里的optype是存储过程的另一个输入参数）。
COALESCE函数: COALESCE(op_type_num, optype) AS op_type_num 用于处理owner_details表中op_type_num可能为NULL的情况。如果op_type_num为NULL，则使用输入参数optype的值。
结果集: 返回owner_num、处理后的op_type_num以及owner_details表中的星期几（sunday到saturday）字段。

数据库表结构

查询涉及的owner_details表结构如下：

CREATE TABLE owner_details (     owner_num integer NOT NULL,     op_type_num integer NOT NULL,     sunday numeric(5,3),     monday numeric(5,3),     tuesday numeric(5,3),     wednesday numeric(5,3),     thursday numeric(5,3),     friday numeric(5,3),     saturday numeric(5,3),     CONSTRAINT pk_owner_details PRIMARY KEY (owner_num, op_type_num) );

转换为原生SQL查询

存储过程的核心是OPEN p_refcur FOR SELECT …中的SELECT语句。我们可以直接提取并修改这个SELECT语句，使其能够接收来自Java应用的参数。

原始SQL中的owner和optype是存储过程的输入参数。在转换为原生SQL时，它们将由Java方法传入的参数替代。

转换后的SQL查询结构如下：

SELECT     z.owner_num,     COALESCE(ad.op_type_num, :typeNum) AS op_type_num, -- :typeNum 替换了存储过程的 optype 参数     ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday FROM (SELECT :ownerNum AS owner_num) AS z -- :ownerNum 替换了存储过程的 owner 参数 LEFT OUTER JOIN owner_details AS ad     ON z.owner_num = ad.owner_num AND ad.op_type_num = :typeNum; -- :typeNum 替换了存储过程的 optype 参数

请注意，COALESCE(op_type_num, optype)中的optype和ON z.owner_num = ad.owner_num AND op_type_num = optype中的op_type_num在LEFT OUTER JOIN的ON子句中，如果owner_details表中不存在匹配的行，ad.op_type_num将为NULL。然而，原始SP的意图是如果owner_details中找不到op_type_num，就使用传入的optype参数。在ON子句中，我们应该使用传入的参数typeNum来匹配。COALESCE的第二个参数也应是传入的typeNum。

在Spring Data JPA中集成原生查询

Spring Data JPA允许通过@Query注解执行原生SQL查询。这里我们提供两种参数绑定方式。

1. 使用命名参数

命名参数通过@Param注解将Java方法参数映射到SQL查询中的命名占位符（例如:ownerNum）。这种方式提高了查询的可读性和可维护性，尤其是在参数较多时。

实体类 (OwnerDetails): 为了接收查询结果，需要一个与查询结果列对应的Java实体类。

import java.math.BigDecimal; import jakarta.persistence.Entity; import jakarta.persistence.Id; import jakarta.persistence.Table; import jakarta.persistence.Column;  // 假设 OwnerDetails 是一个JPA实体，或者是一个POJO，用于映射查询结果 // 如果是POJO，则不需要@Entity和@Id，但需要确保字段名与查询结果的别名匹配 @Entity // 如果OwnerDetails对应数据库表，并且是JPA实体 @Table(name = "owner_details") // 示例，实际可能不需要对应整个表 public class OwnerDetails {      @Id // 假设owner_num和op_type_num共同构成主键     @Column(name = "owner_num")     private Integer ownerNum;      @Id     @Column(name = "op_type_num")     private Integer opTypeNum;      private BigDecimal sunday;     private BigDecimal monday;     private BigDecimal tuesday;     private BigDecimal wednesday;     private BigDecimal thursday;     private BigDecimal friday;     private BigDecimal saturday;      // Getters and Setters     public Integer getOwnerNum() { return ownerNum; }     public void setOwnerNum(Integer ownerNum) { this.ownerNum = ownerNum; }     public Integer getOpTypeNum() { return opTypeNum; }     public void setOpTypeNum(Integer opTypeNum) { this.opTypeNum = opTypeNum; }     public BigDecimal getSunday() { return sunday; }     public void setSunday(BigDecimal sunday) { this.sunday = sunday; }     public BigDecimal getMonday() { return monday; }     public void setMonday(BigDecimal monday) { this.monday = monday; }     public BigDecimal getTuesday() { return tuesday; }     public void setTuesday(BigDecimal tuesday) { this.tuesday = tuesday; }     public BigDecimal getWednesday() { return wednesday; }     public void setWednesday(BigDecimal wednesday) { this.wednesday = wednesday; }     public BigDecimal getThursday() { return thursday; }     public void setThursday(BigDecimal thursday) { this.thursday = thursday; }     public BigDecimal getFriday() { return friday; }     public void setFriday(BigDecimal friday) { this.friday = friday; }     public BigDecimal getSaturday() { return saturday; }     public void setSaturday(BigDecimal saturday) { this.saturday = saturday; }      // Constructors, toString, equals, hashCode if needed }

Repository 接口:

import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository;  import java.util.Optional; // 推荐使用Optional处理可能无结果的情况  @Repository public interface OwnerDetailsRepository extends JpaRepository<OwnerDetails, Integer> {      @Query(nativeQuery = true,             value = "SELECT " +                     "z.owner_num, " +                     "COALESCE(ad.op_type_num, :typeNum) AS op_type_num, " + // COALESCE的第二个参数使用传入的typeNum                     "ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday " +                     "FROM (SELECT :ownerNum AS owner_num) AS z " +                     "LEFT OUTER JOIN owner_details AS ad " +                     "ON z.owner_num = ad.owner_num AND ad.op_type_num = :typeNum") // ON子句也使用传入的typeNum     Optional<OwnerDetails> fetchOwnerDetailsByOwnerNumAndOpType(             @Param("ownerNum") Integer owner,             @Param("typeNum") Integer type); }

注意事项:

nativeQuery = true 明确指示Spring Data JPA执行原生SQL。
@Param(“ownerNum”) Integer owner 将Java方法参数owner绑定到SQL中的:ownerNum占位符。
返回类型Optional<OwnerDetails>是处理查询可能不返回任何结果的推荐方式。如果查询保证总会返回一行（例如，通过左连接和COALESCE确保了部分字段），也可以直接使用OwnerDetails。

2. 使用序号参数

序号参数通过?1, ?2等占位符，按照方法参数的顺序进行绑定。

Repository 接口:

import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.stereotype.Repository;  import java.util.Optional;  @Repository public interface OwnerDetailsRepository extends JpaRepository<OwnerDetails, Integer> {      @Query(nativeQuery = true,             value = "SELECT " +                     "z.owner_num, " +                     "COALESCE(ad.op_type_num, ?2) AS op_type_num, " + // ?2 对应方法第二个参数                     "ad.sunday, ad.monday, ad.tuesday, ad.wednesday, ad.thursday, ad.friday, ad.saturday " +                     "FROM (SELECT ?1 AS owner_num) AS z " + // ?1 对应方法第一个参数                     "LEFT OUTER JOIN owner_details AS ad " +                     "ON z.owner_num = ad.owner_num AND ad.op_type_num = ?2") // ?2 对应方法第二个参数     Optional<OwnerDetails> fetchOwnerDetailsByOwnerNumAndOpType(             Integer owner,             Integer type); // 参数顺序必须与SQL中的?1, ?2对应 }

注意事项:

方法参数的顺序至关重要，?1对应方法签名中的第一个参数，?2对应第二个，以此类推。
相较于命名参数，序号参数在SQL查询复杂或参数较多时，可读性可能下降，且修改参数顺序容易出错。因此，通常推荐使用命名参数。

总结与最佳实践

通过上述方法，我们成功地将PostgreSQL存储过程的核心查询逻辑迁移到了Spring Boot应用中的原生SQL查询。这种做法带来了以下好处：

逻辑集中: 业务逻辑主要集中在Java应用层，便于统一管理和维护。
可测试性增强: Java代码更容易进行单元测试和集成测试。
解耦: 降低了应用对特定数据库存储过程的依赖。
开发效率: 开发者可以使用熟悉的Java语言和spring框架特性进行开发。

最佳实践建议:

选择合适的参数绑定方式: 对于简单的查询，序号参数可能足够；但对于复杂查询或团队协作，命名参数通常是更优的选择，因为它提供了更好的可读性和健壮性。
返回类型匹配: 确保Java方法的返回类型（例如OwnerDetails）与SQL查询返回的列精确匹配。如果查询返回多个列，可以创建一个POJO来封装这些列，或者使用List<map<String, Object>>等通用类型。
错误处理: 考虑查询无结果或数据库异常情况，使用Optional或其他适当的异常处理机制。
性能考量: 原生SQL查询虽然灵活，但其性能优化通常需要直接在SQL层面进行，不像JPQL那样可以利用JPA提供的一些优化。
避免过度使用: 对于简单的CRUD操作，Spring Data JPA的派生查询方法和JPQL通常是更好的选择。原生SQL查询应保留给那些无法通过JPQL表达的复杂逻辑或需要直接操作数据库特定功能的场景。