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

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$;

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)
);

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 参数

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
}

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);
}

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对应
}