PostgreSQL存储过程转Spring Boot原生查询指南

存储过程替代的必要性

在现代应用开发中，许多团队倾向于减少或消除数据库中复杂的存储过程，转而将业务逻辑迁移到应用层。这种做法有助于提高代码的可维护性、可测试性、可移植性，并简化数据库迁移过程。Spring Boot结合Spring Data JPA提供了强大的能力，允许开发者在Java应用中直接执行原生SQL查询，从而有效地替代存储过程。

原始存储过程分析

我们首先分析一个PostgreSQL存储过程spfetchowner，它用于根据所有者ID和操作类型获取所有者详细信息：

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 (所有者ID), optype (操作类型), 以及一个INOUT类型的p_refcur游标。其核心逻辑在于OPEN p_refcur FOR SELECT ...语句，这正是我们需要转换为原生SQL的部分。

查询的逻辑如下：

1. 创建一个临时表z，包含一个名为owner_num的列，其值来自传入的owner参数。
2. 将z与owner_details表进行LEFT OUTER JOIN。连接条件是z.owner_num = ad.owner_num并且ad.op_type_num = optype。
3. COALESCE(op_type_num, optype)用于处理LEFT OUTER JOIN可能导致的op_type_num为NULL的情况。如果ad.op_type_num为NULL，则使用传入的optype参数。
4. 最终选择owner_num, op_type_num以及owner_details表中的星期几字段。

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查询转换

根据存储过程的核心SELECT语句，我们可以直接构建等效的原生SQL查询。关键在于将存储过程的输入参数（owner和optype）在SQL查询中进行引用。

SELECT
    z.owner_num, 
    COALESCE(op_type_num, :typeNum) AS op_type_num, 
    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;

在这个转换后的SQL中，我们使用了占位符:ownerNum和:typeNum来表示将从Java方法传入的参数。

Spring Data JPA @Query 参数映射

在Spring Data JPA的Repository接口中，我们可以使用@Query注解来执行上述原生SQL查询。@Query注解的nativeQuery = true属性表示这是一个原生SQL查询。对于查询中的参数，Spring Data JPA提供了两种主要的映射方式。

假设我们有一个OwnerDetails实体类，其字段与上述查询结果的列相匹配：

public class OwnerDetails {
    private Integer ownerNum;
    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
}

1. 命名参数映射

命名参数通过@Param注解将Java方法参数与SQL查询中的命名占位符关联起来。这种方式提高了代码的可读性，尤其是在参数较多时。

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.math.BigDecimal;

@Repository
public interface OwnerDetailsRepository extends JpaRepository {

    @Query(nativeQuery = true,
            value = "SELECT " +
                    "z.owner_num, " +
                    "COALESCE(ad.op_type_num, :typeNum) AS op_type_num, " + // 注意这里COALESCE的第一个参数是ad.op_type_num
                    "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")
    OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
            @Param("ownerNum") Integer owner,
            @Param("typeNum") Integer type);
}

注意事项：

• @Param("ownerNum")将Java方法的owner参数映射到SQL中的:ownerNum。
• @Param("typeNum")将Java方法的type参数映射到SQL中的:typeNum。
• COALESCE函数中的op_type_num应明确指定为ad.op_type_num以避免歧义，因为z表中没有此列。

2. 位置参数映射

位置参数通过?后跟数字（从1开始）来引用方法参数在签名中的位置。这种方式在参数较少时可能更简洁，但在参数顺序发生变化时容易出错。

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;

@Repository
public interface OwnerDetailsRepository extends JpaRepository {

    @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")
    OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
            Integer owner,
            Integer type);
}

注意事项：

• ?1对应方法签名中的第一个参数owner。
• ?2对应方法签名中的第二个参数type。
• 使用位置参数时，方法参数不需要@Param注解。
• 如果方法参数的顺序改变，SQL查询中的?n也必须相应调整，否则会导致运行时错误或逻辑错误。

总结

通过上述两种方法，我们可以成功地将PostgreSQL存储过程中的复杂查询逻辑迁移到Spring Boot应用的原生SQL查询中。命名参数通常是更推荐的做法，因为它提高了代码的可读性和健壮性，减少了因参数顺序变化而引入错误的风险。在实际开发中，应根据项目的具体需求和团队规范选择合适的参数映射方式。同时，确保Java实体类与SQL查询返回的列结构匹配，是成功执行原生查询的关键。