java - JPA 2.1 StoredProcedureQuery 与 PostgreSQL 和 REF_CURSORs

Question

我在我的 PostgreSQL 数据库中创建了一个函数，我想使用 JPA 2.1 的 StoredProcedureQuery 方法调用它。

这是我的 PostgreSQL 查询:

CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor 
AS $$ 
    DECLARE tuples refcursor; 
    BEGIN OPEN tuples FOR 
        SELECT user, COUNT(*) 
        FROM my_table 
        WHERE date_ = date
        GROUP BY user; 
    return tuples; 
    END; 
$$ 
LANGUAGE plpgsql

这只是一个计算特定日期用户数的简单查询。这只是一个演示查询，用于测试 StoredProcedureQueries 的工作方式。事实上，仅通过 postgreSQL 使用时它工作得很好。

现在，让我们尝试在 Javaland 中使用 JPA 2.1 调用它:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.setParameter(2, "2015-02-01");
storedProcedure.execute();

当我这样做时，我得到了以下异常:

org.hibernate.HibernateException: PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered

只声明了一个引用游标!事实上，如果我只是为 WHERE date_ = date 注册单个 REF_CURSOR 参数并硬编码到我的 Postgresql 函数的值中，这个调用就可以正常工作。

因此，向带有 ref_cursor 的存储过程查询添加任何附加参数似乎会破坏功能。单独使用 ref_cursor 参数可以正常工作。

有人知道为什么会这样吗？？为什么向我的 PostgreSQL 函数的 StoredProcedureQuery 添加参数会破坏它？

何时起作用的示例:

 CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor 
AS $$ 
    DECLARE tuples refcursor; 
    BEGIN OPEN tuples FOR 
        SELECT user, COUNT(*) 
        FROM my_table 
        WHERE date_ = '2015-02-01'
        GROUP BY user; 
    return tuples; 
    END; 
$$ 
LANGUAGE plpgsql

在 java :

StoredProcedureQuery storedProcedure =    em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.execute();

Answer 1

简短回答:颠倒对 registerStoredProcedureParameter() 的两次调用的顺序:

storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);

长答案:我在 Hibernate source code for Postgress callable statement support 中做了一些挖掘，并发现每个 registerStoredProcedureParameter() 调用都会创建一个 ParameterRegistrationImplementor被添加到列表中并传递的实例。您会注意到此类存储参数的位置，这与其在列表中的位置无关。

后来这个列表是analyzed并假定 REF_CURSOR 参数排在第一位，如果 REF_CURSOR 参数不在第一位，则抛出错误消息，无论参数编号是多少.

这不是一个非常聪明的做事方式(恕我直言)，但至少解决方法很简单:如果你交换调用顺序，你应该没问题。