不能用Hibernate调用PostgreSQL的11个存储过程

2022-01-18 00:00:00 postgresql java Hibernate jpa spring-data

PostgreSQL 11 现在支持存储过程，我正在尝试使用 Hibernate 5.3.7.Final 和 Postgresql 42.2.5 JDBC 驱动程序 调用一个.在 PostgreSQL 11 之前，我们有可以使用 JPA 的 @NamedStoredProcedure 调用的函数.但是，函数是用 SELECT my_func(); 执行的，新的存储过程必须用 CALL my_procedure();

PostgreSQL 11 now supports stored procedures and I am trying to call one with Hibernate 5.3.7.Final and Postgresql 42.2.5 JDBC driver. Previous to PostgreSQL 11 we had functions that could be called with JPA's @NamedStoredProcedure. However, the functions were executed with SELECT my_func(); and the new stored procedures have to be executed with CALL my_procedure();

我正在尝试执行以下简单的存储过程:

I am trying to execute the following simple stored procedure:

CREATE OR REPLACE PROCEDURE p_raise_wage_employee_older_than(operating_years 
int, raise int)
AS $$
    BEGIN
       UPDATE employees
       SET wage = wage + raise 
       WHERE EXTRACT(year FROM age(entrance_date)) >= operating_years;
    END $$
LANGUAGE plpgsql;

JPA 注释如下所示:

The JPA annotation looks like the following:

@NamedStoredProcedureQuery(name = "raiseWage", 
   procedureName = "p_raise_wage_employee_older_than", 
   parameters = {
        @StoredProcedureParameter(name = "operating_years", type = Integer.class, 
           mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "raise", type = Integer.class, 
            mode = ParameterMode.IN)
})

我正在调用存储过程:

StoredProcedureQuery storedProcedureQuery = this.em.createNamedStoredProcedureQuery("raiseWage"); 
storedProcedureQuery.setParameter("operating_years", 20);
storedProcedureQuery.setParameter("raise", 1000);
storedProcedureQuery.execute();

我的日志如下所示:

Hibernate: {call p_raise_wage_employee_older_than(?,?)}
2019-02-17 11:07:41.290  WARN 11168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42809
2019-02-17 11:07:41.291 ERROR 11168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: p_raise_wage_employee_older_than(integer, integer) is a procedure
  Hinweis: To call a procedure, use CALL.
  Position: 15

第一个 Hibernate 日志表明 Hibernate 使用 call 来执行存储过程，但我得到一个 SQL 异常，即未使用 CALL.这是 Postgresql 11 之前的 Postgresql 方言中的错误吗?您可以将 FUNCTIONS 建模为 JPA 中的存储过程，因此使用了 SELECT 而不是 CALL?

The first Hibernate log is indicating that Hibernate uses call to execute the stored procedure but I am getting a SQL exception that CALL is not used. Is this a bug in the Postgresql dialect as previous to Postgresql 11 you were able to model the FUNCTIONS as stored procedures within JPA and therefore SELECT was used and not CALL?

推荐答案

由于 pgJDBC 42.2.5 是在 PostgreSQL 11 版本(2018 年 10 月)之前(2018 年 8 月)发布的，我认为这是目前内部的一个问题PostgreSQL 本身的 JDBC 驱动程序.我在 GitHub 存储库中创建了一个 问题.

As the pgJDBC 42.2.5 was released prior (Aug, 2018) to the PostgreSQL 11 release (Oct, 2018), I think this is currently a issue within the JDBC driver for PostgreSQL itself. I've created a issue within the GitHub repository.

对于解决方法，您可以将 STORED PROCEDURE 重写为 FUNCTION 并使用 @NamedStoredProcedureQuery 或直接与 JDBC 交互CallableStatement 例如:

For a workaround, you could rewrite the STORED PROCEDURE as a FUNCTION and use @NamedStoredProcedureQuery or directly interact with the JDBC CallableStatement e.g.:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "postgres", "postgres");

CallableStatement callableStatement = conn.prepareCall("{call f_raise_wage_employee_older_than(?,?)}");
callableStatement.setInt(1, 20);
callableStatement.setInt(2, 500);
callableStatement.executeUpdate();

或使用 EntityManager 执行本机查询:

Or execute a native query with the EntityManager: 

this.em.createNativeQuery("CALL p_raise_wage_employee_older_than(1, 20)");

我会在收到 pgJDBC 维护者的答复后立即更新此答案.

I'll update this answer as soon as I get an answer from the pgJDBC maintainer.

更新:

Postgres 邮件列表(https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us)目前没有解决方案.唯一的方法是将本地 SQL 查询传递给数据库或将 STORED PROCEDURE 重写为 FUNCTION

This topic is already discussed in the Postgres Mailing list (https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us) and there is currently no solution. The only way is to pass native SQL queries to the database or rewrite the STORED PROCEDURE as a FUNCTION

相关文章