如何通过Java JPA获取PostgreSQL存储过程返回的表数据？

以下是排查和解决的几个关键点：

1. 确认PostgreSQL函数的完整性 #

你提供的函数定义只有头部，必须确保函数体正确实现了表返回逻辑。完整的函数需要包含RETURN QUERY语句来输出结果集，示例如下：

CREATE OR REPLACE FUNCTION public.proc_name(_user_id integer)
 RETURNS TABLE(earned numeric, date_of_expiry timestamp with time zone)
LANGUAGE plpgsql
AS $$
BEGIN
  -- 替换为你的实际查询逻辑，确保能返回数据
  RETURN QUERY SELECT earned_col, expiry_col FROM your_table WHERE user_id = _user_id;
END;
$$;

先在数据库客户端（如psql、pgAdmin）执行SELECT * FROM public.proc_name(你的测试用户ID);，验证函数本身是否能返回数据。如果数据库端调用无结果，优先排查函数逻辑。

2. 修正JPA接口的映射匹配 #

AbcInterFace的方法名和类型必须与函数返回列严格对应：

• 列名earned对应方法getEarned()，类型用BigDecimal匹配PostgreSQL的numeric
• 列名date_of_expiry对应方法getDateOfExpiry()，类型用OffsetDateTime匹配timestamp with time zone
  示例接口定义：

public interface AbcInterFace {
    BigDecimal getEarned();
    OffsetDateTime getDateOfExpiry();
}

如果方法名和列名不匹配，JPA无法映射数据，会返回空值或空列表。

3. 检查参数类型兼容性 #

你的JPA方法参数是long userId，SQL中强制转换为int，如果传入的userId值超出PostgreSQLint的范围（-2147483648到2147483647），会导致参数转换失败，函数无数据返回。解决方式：

• 把函数的参数类型改为bigint，同时SQL中去掉强制转换或改为:userId::bigint
• 确保传入的userId值在int范围内，或把JPA参数改为Integer userId

4. 使用JPA标准存储过程调用方式 #

替代原生SQL，用JPA的@NamedStoredProcedureQuery和@Procedure注解更规范，减少出错概率：

步骤1：定义存储过程查询 #

在任意实体类或单独的@Entity类上添加：

@NamedStoredProcedureQuery(
    name = "ProcName.call",
    procedureName = "public.proc_name",
    resultSetMapping = "AbcResultMapping"
)
@SqlResultSetMapping(
    name = "AbcResultMapping",
    classes = @ConstructorResult(
        targetClass = AbcDto.class, // 可以用DTO替代接口
        columns = {
            @ColumnResult(name = "earned", type = BigDecimal.class),
            @ColumnResult(name = "date_of_expiry", type = OffsetDateTime.class)
        }
    )
)

步骤2：仓库接口调用 #

@Procedure(name = "ProcName.call")
List<AbcDto> callAbc(@Param("_user_id") Integer userId);

如果坚持用接口投影，也可以将resultSetMapping改为适配接口的映射规则。

5. 验证数据库权限 #

确保执行JPA的数据库用户拥有以下权限：

• 函数public.proc_name的执行权限：GRANT EXECUTE ON FUNCTION public.proc_name(integer) TO 你的数据库用户名;
• 函数内部查询的表的读取权限：GRANT SELECT ON your_table TO 你的数据库用户名;

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