java调用存储过程-》游标解析返回的集合

//存储过程名称
        String procedureName = PNAME_SINGLE_INDEX_DETAIL_LIST;
        
        //初始化参数集合
        List<String> params = Lists.newArrayList(singleIndexDetailRequestVo.getCurOrgId(),
            singleIndexDetailRequestVo.getCurOrgType(), singleIndexDetailRequestVo.getTimeDim(),
            singleIndexDetailRequestVo.getIndexId(),singleIndexDetailRequestVo.getPageNo(),singleIndexDetailRequestVo
            .getPageSize());

        //调用BI得到结果集
        List<Map<String, Object>> resultSet = getResultSet(procedureName, params);

2.处理参数转换占位符

 public List<Map<String, Object>> getResultSet(String procedureName, List<String> params) {
    List<Map<String, Object>> resultSet = null;
    try {
      String placeHolder = Joiner.on(",").join(params).replaceAll("\\w+", "?");
      resultSet = getCallProcedureResult(String.format("{call %s(%s,?)}", procedureName, placeHolder), params);
    } catch (Exception e) {
      resultSet = Lists.newArrayList();
      logger.error("call procedure:" + procedureName + " happened an exception:" + e.getMessage());
    }
    return resultSet;
  }

3.调用存储过程核心方法

/**
     *
     * @param procedureName 存储过程名称包含预编译参数 {call testpro(?,?)} 最后一个参数为输出参数为游标类型
     * @param params 对应存储过程执行参数是有顺序的，参数类型为字符类型 不包含存储的输出参数
     * @return
     */
    @Override
    @SuppressWarnings("unchecked")
    public List<Map<String,Object>> getCallProcedureResult(final String procedureName, final List<String> params) {
        final long startTime = System.currentTimeMillis();
        logger.info("开始调用存储过程【"+procedureName+"】，入参【" + JSON.toJSONString(params) + "】");
        List<Map<String,Object>> resultList = null;
        try {
            resultList = (List<Map<String,Object>>) biJdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        String storedProc = procedureName;// 调用的sql
                        CallableStatement cs = con.prepareCall(storedProc);
                        for (int i=0; i< params.size();i++) {
                            cs.setString((i+1), params.get(i));// 设置输入参数的值
                        }
                        cs.registerOutParameter((params.size()+1),OracleTypes.CURSOR);// 注册输出参数的类型
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
                        List<Map<String,Object>> resultsMap = new ArrayList<Map<String,Object>>();
                        cs.execute();
                        ResultSet rs = (ResultSet) cs.getObject((params.size()+1));;// 此处值必须跟游标返回的值下标是统一个下标
                        if (rs!=null) {
                            ResultSetMetaData rsmd = rs.getMetaData();
                            List<String> columNames = new ArrayList<String>();
                            for(int i=1; i<= rsmd.getColumnCount(); i++){
                                columNames.add(rsmd.getColumnName(i)); //将字段名放在List中
                            }
                            if (!CollectionUtils.isEmpty(columNames)) {
                                while (rs.next()) {// 转换每行的返回值到Map中
                                    Map<String,Object> rowMap = new HashMap<String,Object>();
                                    for (String columName : columNames) {
                                        rowMap.put(columName, rs.getObject(columName));
                                    }
                                    resultsMap.add(rowMap);
                                }
                            }
                            rs.close();
                        }
                        return resultsMap;
                    }
                });
            final long endTime = System.currentTimeMillis();
            logger.info("结束调用存储过程【"+procedureName+"】，入参【"+ JSON.toJSONString(params) + "】，查询存储过程返回数据条数【"+resultList.size()+"】总耗时：" + (endTime-startTime) + "毫秒");
            logger.info("本次调用存储过程返回数据："+JSON.toJSONString(resultList));
            final List<Map<String, Object>> finalResultList = resultList;
            jdbcTemplate.update("INSERT INTO PROCEDURE_LOGS VALUES(?,?,?,?,?,?,?)",
                new PreparedStatementSetter() {
                    public void setValues(PreparedStatement ps) throws SQLException {
                        ps.setString(1, JugHelper.generalUUID());
                        ps.setString(2, procedureName);
                        ps.setString(3, JSON.toJSONString(params));
                        ps.setString(4, JSON.toJSONString(finalResultList));
                        ps.setTimestamp(5, new Timestamp(new Date().getTime()));
                        ps.setLong(6, (endTime-startTime));
                        ps.setString(7, "1");// 正常
                    }
                }
            );
        } catch (Exception e) {
            final long endTime = System.currentTimeMillis();
            final String errorMsg = getStackTrace(e);
            jdbcTemplate.update("INSERT INTO PROCEDURE_LOGS VALUES(?,?,?,?,?,?,?)",
                    new PreparedStatementSetter() {
                        public void setValues(PreparedStatement ps) throws SQLException {
                            ps.setString(1, JugHelper.generalUUID());
                            ps.setString(2, procedureName);
                            ps.setString(3, JSON.toJSONString(params));
                            ps.setString(4, errorMsg);
                            ps.setTimestamp(5, new Timestamp(new Date().getTime()));
                            ps.setLong(6, (endTime-startTime));
                            ps.setString(7, "0");// 异常
                        }
                    }
            );
        }
        return resultList;
    }

测试游标

CREATE OR REPLACE PROCEDURE TEST_SAULE_DETAIL_BING(
CUR_ORG_ID       VARCHAR2, --当前组织Id
CUR_ORG_TYPE     VARCHAR2, --组织类型 1：城市6：片区4：大区2：区域99：店组 9：成员
INDEX_DIM         varchar2,
INDEX_ID         varchar2,
test_cursor out sys_refcursor
)
AS
BEGIN
OPEN test_cursor for SELECT * FROM SAULSE_BING_TEST;
END TEST_SAULE_DETAIL_BING ;

 

转载于:https://www.cnblogs.com/pypua/articles/9622576.html