Spring Boot中如何映射PostgreSQL存储过程的返回结果？

问题背景 #

定义了PostgreSQL自定义类型package_dto和带OUT参数的存储过程update_package，在Spring Boot Repository调用时无法直接映射到同字段的PackageDto（POJO/Record），只能手动解析数组，同时需要判断存储过程执行是否成功、返回值是否为空。

核心解决方案：通过结果集映射实现自动转换 #

1. 定义匹配的PackageDto #

确保DTO的字段类型、名称与PostgreSQL自定义类型完全对应，推荐使用Record简化代码：

public record PackageDto(
    Long id,
    String name,
    Integer duration,
    BigDecimal price,
    String currency
) {}

如果使用POJO，需提供对应构造方法或Setter方法。

2. 配置SQL结果集映射 #

在实体类（如Package实体）上添加@SqlResultSetMapping，明确字段与DTO的映射关系：

import jakarta.persistence.*;

@SqlResultSetMapping(
    name = "PackageDtoMapping",
    classes = @ConstructorResult(
        targetClass = PackageDto.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "duration", type = Integer.class),
            @ColumnResult(name = "price", type = BigDecimal.class),
            @ColumnResult(name = "currency", type = String.class)
        }
    )
)
@Entity
public class Package {
    // 实体类原有字段与映射逻辑
    @Id
    private Long id;
    private String name;
    private Integer duration;
    private BigDecimal price;
    private String currency;
    // Getter、Setter等
}

3. 修改Repository的存储过程调用 #

调整@Query语句，将自定义类型的OUT参数展开为独立列，并指定结果集映射：

@Query(
    value = "SELECT (o_package).* FROM update_package(:package_id, :name, :duration, :price, :currency, null)",
    nativeQuery = true,
    resultSetMapping = "PackageDtoMapping"
)
PackageDto updateProcedure(
    @Param("package_id") Long id,
    @Param("name") String name,
    @Param("duration") Integer duration,
    @Param("price") BigDecimal price,
    @Param("currency") String currency
);

关键说明：(o_package).*会将自定义类型的字段拆分为单独列，让Spring能通过配置的映射规则自动赋值到PackageDto。

判断执行成功与返回值是否为空 #

1. 基于现有存储过程的判断 #

当UPDATE语句未匹配到任何行时，存储过程的o_package会返回null，直接判断返回的PackageDto即可：

PackageDto result = packageRepository.updateProcedure(
    request.id(),
    request.name(),
    request.duration(),
    request.price(),
    Optional.ofNullable(request.currency()).orElse(ApplicationConstants.DEFAULT_CURRENCY)
);

if (result == null) {
    throw new RuntimeException("未找到目标套餐或更新失败");
}
return result;

2. 增强存储过程：添加执行状态输出 #

如果需要更明确的执行状态，可以修改存储过程增加OUT参数：

CREATE OR REPLACE PROCEDURE update_package(
    p_package_id BIGINT,
    p_name VARCHAR(255),
    p_duration INT,
    p_price NUMERIC,
    p_currency VARCHAR(32),
    OUT o_package package_dto,
    OUT o_success BOOLEAN
)
LANGUAGE plpgsql
AS $$
BEGIN
    o_success := FALSE;
    UPDATE package
    SET name     = p_name,
        duration = p_duration,
        price    = p_price,
        currency = p_currency
    WHERE id = p_package_id
    RETURNING id, name, duration, price, currency
        INTO o_package.id, o_package.name, o_package.duration, o_package.price, o_package.currency;
    
    -- 判断是否有行被更新
    IF FOUND THEN
        o_success := TRUE;
    END IF;
END;
$$;

然后新增包含状态的DTO和对应的结果集映射：

public record PackageWithStatusDto(PackageDto packageDto, Boolean success) {}

// 在Package实体上添加新的结果集映射
@SqlResultSetMapping(
    name = "PackageWithStatusMapping",
    classes = @ConstructorResult(
        targetClass = PackageWithStatusDto.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "duration", type = Integer.class),
            @ColumnResult(name = "price", type = BigDecimal.class),
            @ColumnResult(name = "currency", type = String.class),
            @ColumnResult(name = "o_success", type = Boolean.class)
        }
    )
)

最后修改Repository方法：

@Query(
    value = "SELECT (o_package).*, o_success FROM update_package(:package_id, :name, :duration, :price, :currency, null, null)",
    nativeQuery = true,
    resultSetMapping = "PackageWithStatusMapping"
)
PackageWithStatusDto updateProcedure(
    @Param("package_id") Long id,
    @Param("name") String name,
    @Param("duration") Integer duration,
    @Param("price") BigDecimal price,
    @Param("currency") String currency
);

调用时即可直接获取执行状态和结果：

PackageWithStatusDto result = packageRepository.updateProcedure(...);
if (!result.success()) {
    throw new RuntimeException("更新操作未生效");
}
return result.packageDto();

替代方案：改用PostgreSQL函数（FUNCTION） #

如果不需要存储过程的多DML事务特性，可以改用FUNCTION简化映射：

CREATE OR REPLACE FUNCTION update_package(
    p_package_id BIGINT,
    p_name VARCHAR(255),
    p_duration INT,
    p_price NUMERIC,
    p_currency VARCHAR(32)
) RETURNS package_dto
LANGUAGE plpgsql
AS $$
DECLARE
    result package_dto;
BEGIN
    UPDATE package
    SET name     = p_name,
        duration = p_duration,
        price    = p_price,
        currency = p_currency
    WHERE id = p_package_id
    RETURNING id, name, duration, price, currency INTO result;
    
    RETURN result;
END;
$$;

Repository调用方式：

@Query(
    value = "SELECT * FROM update_package(:package_id, :name, :duration, :price, :currency)",
    nativeQuery = true,
    resultSetMapping = "PackageDtoMapping"
)
PackageDto updateFunction(...);

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