Spring Data JPA调用PostgreSQL 16存储过程/函数失败求助

问题背景 #

复习Spring Boot+Spring Data JPA时，调用PostgreSQL 16的存储过程和函数遇到异常：仅返回void的存储过程调用成功，其余场景均报错（提示找不到存储过程/函数、无返回结果等），但数据库客户端直接调用存储过程和函数完全正常。

一、存储过程调用问题解决 #

原存储过程定义 #

CREATE OR REPLACE PROCEDURE misc.createmisc(
    IN _name VARCHAR, 
    IN _mkey VARCHAR,
    IN _mvalue integer,
    OUT newid integer
)
LANGUAGE sql
AS $$
    INSERT INTO misc.misc (name, mkey, mvalue) VALUES (_name, _mkey, _mvalue) RETURNING id;
$$

核心报错原因 #

1. @Procedure参数绑定错误：Spring Data JPA的@Procedure处理PostgreSQL OUT参数时，未明确指定参数模式（IN/OUT），且参数名未与存储过程定义严格匹配（PostgreSQL默认将未加引号的标识符转为小写）。
2. 原生Query类型不匹配：传入参数的隐式转换导致与存储过程定义的参数类型不匹配，且OUT参数的传递方式错误。
3. 执行方法错误：调用存储过程时使用了executeQuery()，但存储过程本身不返回结果集，需用execute()。

修正方案 #

方案1：正确使用@Procedure获取返回值

修改仓库接口方法，明确指定存储过程的schema、参数名与模式：

@Procedure(procedureName = "misc.createmisc")
Integer callCreateMiscWithReturn(
    @Param("_name") String name,
    @Param("_mkey") String mkey,
    @Param("_mvalue") Integer mvalue,
    @Param("newid") Out<Integer> newid
);

调用示例：

Out<Integer> newid = new Out<>();
miscStoredProcedureCrudRepository.callCreateMiscWithReturn("Test SP", "mk_test", 123, newid);
Integer generatedId = newid.getValue();

方案2：原生Query正确调用并获取结果

利用PostgreSQL存储过程的结果集返回特性，调整原生Query写法：

@Query(value = "SELECT * FROM misc.createmisc(:_name, :_mkey, :_mvalue)", nativeQuery = true)
@Transactional
Integer callCreateMiscNative(
    @Param("_name") String name,
    @Param("_mkey") String mkey,
    @Param("_mvalue") Integer mvalue
);

注：PostgreSQL 11+支持存储过程返回结果集，此写法可直接获取INSERT...RETURNING的结果。

二、函数调用问题解决 #

原函数定义 #

CREATE OR REPLACE FUNCTION misc.createmiscasfunc(_name character varying, _mkey character varying, _mvalue integer)
 RETURNS integer
 LANGUAGE sql
AS $$
INSERT INTO misc (name, mkey, mvalue) VALUES (_name, _mkey, _mvalue) RETURNING id;
$$

核心报错原因 #

JDBC不支持PostgreSQL的函数命名参数语法（_name => :name），导致参数类型匹配失败，数据库无法识别对应函数。

修正方案 #

改用位置参数调用函数：

@Query(value = "SELECT misc.createmiscasfunc(:name, :mkey, :mvalue)", nativeQuery = true)
@Transactional
Integer createMiscAsFunc(
    @Param("name") String name,
    @Param("mkey") String mkey,
    @Param("mvalue") Integer mvalue
);

或直接使用位置占位符简化写法：

@Query(value = "SELECT misc.createmiscasfunc(?, ?, ?)", nativeQuery = true)
@Transactional
Integer createMiscAsFunc(String name, String mkey, Integer mvalue);

通用注意事项 #

• 标识符大小写：PostgreSQL默认将未加引号的标识符转为小写，确保Java代码中的参数名与数据库定义（如_name而非name）严格一致。
• 事务与修改注解：调用修改数据的存储过程/函数时，必须添加@Transactional和@Modifying注解。
• 驱动版本：使用最新版PostgreSQL JDBC驱动（如postgresql:42.7.3），避免版本兼容问题。

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