求助：将PostgreSQL存储过程转换为Spring Boot Java查询语句

1. 匹配存储过程逻辑的原生SQL #

直接提取原存储过程的核心查询逻辑，替换为Spring Data JPA支持的参数占位符：

SELECT
    z.owner_num, 
    COALESCE(ad.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

2. 修改后的Repository方法 #

将上述SQL填入@Query注解，确保参数名与@Param完全对应：

@Query(nativeQuery = true,
        value = """
            SELECT
                z.owner_num, 
                COALESCE(ad.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
            """)
Optional<OwnerDetails> fetchOwnerDetailsByOwnerNumAndOpType(
        @Param("ownerNum") Integer ownerNum, 
        @Param("typeNum") Integer typeNum );

3. 关键逻辑说明 #

• 左连接特性：原存储过程用左连接保证，即使owner_details中无匹配owner_num和op_type_num的记录，也会返回一条以传入参数为owner_num和op_type_num、星期字段全为null的结果，避免返回空结果集。
• COALESCE函数作用：当左连接无匹配时，ad.op_type_num为null，此时用传入的typeNum填充，确保op_type_num字段始终有值。
• Optional返回值：符合Spring Data JPA最佳实践，明确表示结果可能存在无有效业务数据的情况（若不需要也可直接返回OwnerDetails，但需保证实体类字段允许null）。

4. 实体类适配注意事项 #

确保OwnerDetails实体类字段支持接收null值，且对应数据库字段类型：

@Entity
@Table(name = "owner_details")
public class OwnerDetails {
    @Id
    @Column(name = "owner_num")
    private Integer ownerNum;
    
    @Id
    @Column(name = "op_type_num")
    private Integer opTypeNum;
    
    @Column(name = "sunday")
    private BigDecimal sunday; // 对应numeric(5,3)，用BigDecimal避免精度丢失
    // 其余星期字段同理，均定义为BigDecimal并允许null
    // 构造方法、getter/setter省略
}

注：若不需要将查询结果作为持久化实体，也可自定义DTO类接收返回值，通过@SqlResultSetMapping或直接字段映射实现。

内容的提问来源于stack exchange，提问作者Som