存储过程替代的必要性
在现代应用开发中，许多团队倾向于减少或消除数据库中复杂的存储过程，转而将业务逻辑迁移到应用层。这种做法有助于提高代码的可维护性、可测试性、可移植性，并简化数据库迁移过程。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的部分。
查询的逻辑如下：
- 创建一个临时表z，包含一个名为owner_num的列，其值来自传入的owner参数。
- 将z与owner_details表进行LEFT OUTER JOIN。连接条件是z.owner_num = ad.owner_num并且ad.op_type_num = optype。
- COALESCE(op_type_num, optype)用于处理LEFT OUTER JOIN可能导致的op_type_num为NULL的情况。如果ad.op_type_num为NULL，则使用传入的optype参数。
- 最终选择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<OwnerDetails, Integer> {
@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<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")
OwnerDetails fetchOwnerDetailsByOwnerNumAndOpType(
Integer owner,
Integer type);
}注意事项：
- ?1对应方法签名中的第一个参数owner。
- ?2对应方法签名中的第二个参数type。
- 使用位置参数时，方法参数不需要@Param注解。
- 如果方法参数的顺序改变，SQL查询中的?n也必须相应调整，否则会导致运行时错误或逻辑错误。
总结
通过上述两种方法，我们可以成功地将PostgreSQL存储过程中的复杂查询逻辑迁移到Spring Boot应用的原生SQL查询中。命名参数通常是更推荐的做法，因为它提高了代码的可读性和健壮性，减少了因参数顺序变化而引入错误的风险。在实际开发中，应根据项目的具体需求和团队规范选择合适的参数映射方式。同时，确保Java实体类与SQL查询返回的列结构匹配，是成功执行原生查询的关键。
