使用PostgreSQL和REF_CURSORs的JPA 2.1 StoredProcedureQuery [英] JPA 2.1 StoredProcedureQuery with PostgreSQL and REF_CURSORs

问题描述

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

这是我的PostgreSQL查询：

$ p $ CREATE OR REPLACE FUNCTION get_values （日期文本）返回refcursor
AS $$
DECLARE元组refcursor;
BEGIN OPEN元组FOR
SELECT用户，COUNT（*）
FROM my_table
WHERE date_ =日期
GROUP BY用户;
返回元组;
END;
$$
LANGUAGE plpgsql


这只是一个简单的查询来统计用户在特定的一天。这只是一个演示查询来测试StoredProcedureQueries的工作方式。实际上，当通过postgreSQL单独使用时它就可以正常工作。

现在，让我们尝试使用JPA 2.1和Javaland调用它：

  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仅支持一个REF_CURSOR参数，但多个参数已被注册
  

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

因此，似乎在存储过程查询中添加了任何其他参数，并且ref_cursor会中断功能。一个人，ref_cursor参数工作正常。

有人看到为什么会发生这种情况？为什么向我的PostgreSQL函数的StoredProcedureQuery添加参数会打破它？

它的工作原理示例：

  CREATE OR REPLACE FUNCTION get_values（日期文本）返回refcursor 
 AS $$ 
 DECLARE元组refcursor; 
 BEGIN OPEN元组FOR 
 SELECT用户，COUNT（*）
 FROM my_table 
 WHERE date_ ='2015-02-01'
 GROUP BY用户; 
返回元组; 
 END; 
 $$ 
 LANGUAGE plpgsql 
  

和javaland：

  StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery（get_values）; 
 storedProcedure.registerStoredProcedureParameter（1，Object.class，ParameterMode.REF_CURSOR）; 
 storedProcedure.execute（）; 
  

解决方案

简短回答：将两次通话的顺序颠倒 registerStoredProcedureParameter（）：

  storedProcedure.registerStoredProcedureParameter（1，Object.class ，ParameterMode.REF_CURSOR）; 
 storedProcedure.registerStoredProcedureParameter（2，String.class，ParameterMode.IN）; 
  

长答案：我在 Postgress可调用语句的Hibernate源代码支持，并发现每个 registerStoredProcedureParameter（）调用都会创建一个 ParameterRegistrationImplementor

稍后，这个列表是> 并假定 REF_CURSOR 参数将首先排队，如果 REF_CURSOR 参数不是第一个，则会抛出错误消息，而不管参数号是。

不是一种非常明智的做事方式（恕我直言），但至少这个解决方法很简单：如果您交换了电话，你应该没问题。

I have a function I created in my PostgreSQL DB that I want to call using JPA 2.1's StoredProcedureQuery method.

Here is my PostgreSQL query:

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

This is just a simple query to count users on a particular day. This is just a demo query to test how the StoredProcedureQueries work. And in fact, it works just fine when used via postgreSQL alone.

Now, let's try and call this using JPA 2.1 and in Javaland:

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();

When I do this, I get back the following exception:

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

There is only a single ref cursor declared! In fact, if I just register the single REF_CURSOR parameter and hardcode in a value for my Postgresql function for the WHERE date_ = date, this call works just fine.

So it would seem adding any additional parameters to a storedprocedurequery with a ref_cursor breaks the functionality. Alone, the ref_cursor parameters works fine.

Anybody see why this would happen?? Why is it that adding parameters to the StoredProcedureQuery for my PostgreSQL function breaks it?

Example of when it works:

 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

and in javaland:

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

解决方案

Short answer: Reverse the order of your two calls to registerStoredProcedureParameter():

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

Long answer: I did some digging in the Hibernate source code for Postgress callable statement support, and found that each registerStoredProcedureParameter() call creates a ParameterRegistrationImplementor instance that gets tacked into a list and passed around. You'll note that this class stores the position of the parameter, which is independent of its position within the list.

Later, this list is analyzed and assumes that the REF_CURSOR parameter will be first in line, and throws your error message if a REF_CURSOR parameter is not first, regardless of what the parameter number is.

Not a very bright way of doing things (IMHO), but at least the workaround is easy: if you swap the order of your calls, you should be fine.

这篇关于使用PostgreSQL和REF_CURSORs的JPA 2.1 StoredProcedureQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！