无法使用Hibernate调用PostgreSQL 11存储过程

Question

PostgreSQL 11现在支持存储过程，我正在尝试使用Hibernate 5.3.7.Final和Postgresql 42.2.5 JDBC驱动程序调用存储过程。在PostgreSQL 11之前，我们使用JPA的@NamedStoredProcedure调用函数。然而，这些函数是通过SELECT my_func();执行的，而新的存储过程必须通过CALL my_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注解如下所示：

@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方言中的一个错误吗？因为在Postgresql 11之前，您可以将FUNCTIONS建模为JPA中的存储过程，因此使用的是SELECT而不是CALL。

- rieckpil

---

你能否使用基本的JDBC CallableStatement之一？建议你尝试一下，因为这是JPA提供程序可能正在使用的所有内容，因此它可以将问题隔离到不同的地方。 - user3973283

由于pgJDBC 42.2.5是在2018年8月发布的，而PostgreSQL 11是在2018年10月发布的，因此我怀疑它是否支持CALL语法。您在日志中看到的{call ...}语法是JDBC特定的语法，应该由JDBC驱动程序转换为适当的语法。您可能希望将此问题发布在此处，因为我在那里没有看到任何相关问题。--暂时，您可以使用“旧”的方法（即使用RETURNS VOID定义FUNCTION）。 - pozs

Answer 1

由于pgJDBC 42.2.5版本发布时间早于PostgreSQL 11版本（2018年8月和2018年10月），我认为这是JDBC驱动程序本身的问题。我已经在GitHub存储库中创建了一个问题。

作为解决方法，您可以将STORED PROCEDURE重写为FUNCTION并使用@NamedStoredProcedureQuery或直接与JDBCCallableStatement交互。例如：

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维护者那里得到答案，我就会更新这个答案。

更新:

这个主题已经在Postgres邮件列表中讨论过了（https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us），目前还没有解决方案。唯一的方法是将本地SQL查询传递给数据库或将STORED PROCEDURE重写为FUNCTION。

- rieckpil

---

我在42.2.6版本中仍然遇到了这个问题，你得到了答案吗？ - Patrice Blanchardie

我还没有检查这个版本的发布说明。 - rieckpil

Answer 2

自 PostgreSQL 11 后，PostgreSQL JDBC 驱动程序团队在 PostgreSQL 驱动程序版本 42.2.16 中引入了一个 ENUM 名称为 EscapeSyntaxCallMode。因此，在创建数据库连接或创建 DataSource 对象时，我们可以使用这个枚举。这个 Enum 有三种类型的值:

1. "func" - 当我们始终想要调用函数时，设置为此项。
2. "call" - 当我们始终想要调用过程时，设置为此项。
3. "callIfNoReturn" - 它检查调用函数/过程的返回类型，如果返回类型存在，PostgreSQL 就会将其视为函数并按函数方式调用它。否则，它将按过程方式调用它。所以在我的项目中，我使用了 "callIfNoReturn", 因为我希望 PostgreSQL 自动检测我是调用函数还是过程。

我已经详细地给出了答案，并列出了正确的步骤: Postgresql 11: Stored Procedure call error - To call a procedure, use CALL, Java

- Abhishek Singh

---

非常感谢您抽出时间，还为这个问题提供了答案。 - rieckpil

Answer 3

同样的问题。这是我的解决方案。

try {
    Query query = entityManager.createNativeQuery("CALL procedure( :param1, :param2 )");
    query.setParameter("param1", intParam1);
    query.setParameter("param2", intParam2);
    
    log.info("Running...");
    try {
        query.getSingleResult();
    } catch (PersistenceException pe) {
        if (pe.getCause() != null && pe.getCause() instanceof GenericJDBCException) {
            GenericJDBCException gjdbce = (GenericJDBCException) pe.getCause();
            // NO_RESULTS_CODE = "02000" in PostgreSQL
            if (NO_RESULTS_CODE.equals(gjdbce.getSQLState())) {
                log.info("Success!");
                return;
            }
        }
        
        throw pe;
    }
} catch (Exception e) {
    log.error(e.getMessage(), e);
}

祝好运！

- Sergio Marsilli