java - 出现错误 : cursor "" does not exist

Question

我尝试使用 spring.jdbc 调用函数中的 SimpleJdbcCall 返回光标，但出现以下错误:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call dbo.api_config_select(?)}]; SQL state [34000]; error code [0]; ERROR: cursor "<unnamed portal 1>" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

这是PostGreSQL函数代码:

CREATE OR REPLACE FUNCTION "dbo"."api_config_select" (in "_id" integer) RETURNS refcursor AS
$$
    DECLARE
      ref refcursor;                                                     
    BEGIN
      OPEN ref FOR 

        SELECT
            1;

      RETURN ref;                                                       
    END;
    $$
LANGUAGE 'plpgsql' COST 100

这是 Java 代码

        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("api_config_select").withSchemaName("dbo")
              .declareParameters(

              new SqlOutParameter("_cursor",  Types.OTHER),
              new SqlParameter("_id", Types.INTEGER));

        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("_id", id);

        try {
            Map<String, Object> result = simpleJdbcCall.execute(10);
            for (String s : result.keySet()) {
                System.out.println("6.0  " + result.get(s));
            }
        }

        catch(UncategorizedSQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
                e.printStackTrace();
        }

一旦应用程序调用 simpleJdbcCall.execute()，我就会收到错误。我尝试传递引用名称，但出现相同的错误。

有人有使用 PostgreSql、Spring JDBC 和游标的示例代码吗？

Answer 1

在您的方法中使用此代码块:

Connection conn = jdbcTemplate.getDataSource().getConnection();
conn.setAutoCommit(false);
CallableStatement proc = conn.prepareCall("{? = call dbo.api_config_select() }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next())
{
    // do something with the results.
}
results.close();
proc.close();