如何在Java中从PostgreSQL存储过程的refcursor结果集获取数据

原代码的核心问题 #

1. 参数位置错误：存储过程的p_refcur是第三个INOUT参数，但你错误地将第一个参数注册为输出参数，导致无法正确获取游标
2. 硬编码游标名称：调用时直接写死'refcursorresult'，没有通过驱动绑定参数，不符合JDBC调用规范
3. ResultSet处理逻辑混淆：无需额外操作，驱动会直接将refcursor转换为可遍历的ResultSet

修正后的完整代码 #

public List<ResponseDTO> callMySP(String someVal, String anotherVal) {      
    Connection conn = null;
    CallableStatement cstmt = null;     
    List<ResponseDTO> respList = new ArrayList<>();
    
    try {           
        conn = DatabaseConnection.connect();
        if (conn != null) {
            conn.setAutoCommit(false); // refcursor必须在事务上下文内使用，禁用自动提交
        }           
        
        // 使用?占位第三个INOUT参数，交给驱动处理游标名称
        cstmt = conn.prepareCall("call public.my_sp(?, ?, ?);");
        // 设置前两个输入参数
        cstmt.setString(1, someVal);
        cstmt.setString(2, anotherVal);
        // 注册第三个INOUT参数：Types.OTHER对应PostgreSQL的refcursor类型
        cstmt.registerOutParameter(3, Types.OTHER);
        
        // 执行存储过程调用
        cstmt.execute();
        
        // 直接从第三个参数获取游标对应的ResultSet
        ResultSet rs = (ResultSet) cstmt.getObject(3);
        
        if (rs != null) {    
            while (rs.next()) {
                // 根据实际表字段映射DTO
                ResponseDTO dto = new ResponseDTO();
                dto.setId(rs.getInt("id")); // 替换为你的实际列名
                dto.setName(rs.getString("name")); // 示例字段，按需修改
                // 其他字段赋值逻辑
                respList.add(dto);                      
            }
            rs.close(); // 及时关闭ResultSet
        }
        
        conn.commit(); // 事务提交，释放游标资源

    } catch (SQLException sqlEx) {          
        LOGGER.error("数据库操作异常", sqlEx);
        // 异常时必须回滚事务
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                LOGGER.error("事务回滚失败", e);
            }
        }
    } catch (Exception e) {
        LOGGER.error("系统异常", e);            
    } finally {
        // 按顺序关闭资源：ResultSet -> CallableStatement -> Connection
        try {
            if (cstmt != null) cstmt.close();
        } catch (SQLException e) {
            LOGGER.error("关闭CallableStatement失败", e);
        }
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            LOGGER.error("关闭Connection失败", e);
        }
    }

    return respList;
}

关键注意事项 #

• 事务强制要求：PostgreSQL的refcursor依赖活跃事务，必须关闭自动提交，操作完成后手动提交或异常时回滚
• 参数绑定规范：不要硬编码游标名称，使用?占位INOUT参数，让驱动自动管理游标生命周期
• 资源及时释放：ResultSet、CallableStatement、Connection必须在finally块中依次关闭，避免资源泄漏

内容的提问来源于stack exchange，提问作者Som