无法使用Hibernate调用PostgreSQL的11存储过程 [英] Can't call PostgreSQL's 11 Stored Procedure with Hibernate

问题描述

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

然后我用以下命令调用存储过程：

And I am calling the stored procedure with:

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使用调用执行存储过程，但是我收到一个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.

要解决此问题，您可以将存储过程重写为功能并使用 @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 执行本机查询：

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 Mailing列表中已经讨论了该主题（ 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

这篇关于无法使用Hibernate调用PostgreSQL的11存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！