Springboot2->存储过程调用实战

• oracle存储过程实例代码

create or replace procedure proc_test(v_startTime TIMESTAMP,
                                              v_endTime   TIMESTAMP,
                                              type_cur out sys_refcursor) AS
c_start_time date ;
c_endTime   date ;
begin
c_start_time := v_startTime;
c_endTime     := v_endTime;
open type_cur for
  with lai_dian_shu as
    (select count(1) count1
      from test1 t
      where call_type = '1'  
        and start_time between
          c_start_time and
          c_endTime),
  jie_ting_shu as
    (select count(1) count2
      from test2      where call_type = '1'
        and call_state = '1' 
        and start_time between
            c_start_time and
            c_endTime),
  hu_sun_shu as
    (select count(1) count3
      from test3
      where call_type = '1'
        and call_state = '2' 
        and start_time between
            c_start_time and
            c_endTime),
  thirty_jie_ting as
    (select count(1) count4
      from test4
      where call_type = '1'
        and call_state = '1' 
        and ring_time<30 
        and start_time between
          c_start_time and
          c_endTime)
  select to_char(count2 / count1*100,'00.00') answerProportion, 
  to_char(count3 / count1*100,'00.00') callLossProportion,
  to_char(count4 / count2*100,'00.00') efficiency
  from lai_dian_shu,jie_ting_shu, hu_sun_shu,thirty_jie_ting;
end;

• 方式一：oracle存储过程通用方法静态方式以及调用

public static List getOracleProcedureQueryList(EntityManager entityManager, String ProcedureName, Object... params) {
      try {
          StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery(ProcedureName);
          for (int i = 0; i < params.length; i++) {
              storedProcedureQuery.registerStoredProcedureParameter(i, params[i].getClass(), ParameterMode.IN).setParameter(i, params[i]);
          }
          storedProcedureQuery.registerStoredProcedureParameter(params.length + 1, Class.class, ParameterMode.REF_CURSOR);
          storedProcedureQuery.execute();
          List result = storedProcedureQuery.getResultList();
          if (result.isEmpty())
              return Collections.emptyList();
          return result;
      } catch (Exception ex) {
          logger.error(ex.toString());
          throw ex;
      }
  }

public List procedureCall(Date startTime, Date endTime) {
      return ProcedureQueryServiceUtil.getOracleProcedureQueryList(entityManager, PROC_SIGT_CALL_RATIO, startTime, endTime);
  }

• 方式二：oracle存储过程通用方法注入形式以及调用

@Configuration
public class ProcedureQueryServiceUtil {

  private Logger logger = LoggerFactory.getLogger(ProcedureQueryServiceUtil.class);

  /**
    * oracle 存储过程获取数据 存储过程返回REF_CURSOR类型
    *
    * @param entityManager 实体管理上下文
    * @param ProcedureName 存储过程名称
    * @param params       参数集合
    * @return 集合信息
    */
  public List getOracleProcedureQueryList(EntityManager entityManager, String ProcedureName, Object... params) {
      try {
          StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery(ProcedureName);
          for (int i = 0; i < params.length; i++) {
              storedProcedureQuery.registerStoredProcedureParameter(i, params[i].getClass(), ParameterMode.IN).setParameter(i, params[i]);
          }
          storedProcedureQuery.registerStoredProcedureParameter(params.length + 1, Class.class, ParameterMode.REF_CURSOR);
          storedProcedureQuery.execute();
          List result = storedProcedureQuery.getResultList();
          if (result.isEmpty())
              return Collections.emptyList();
          return result;
      } catch (Exception ex) {
          logger.error(ex.toString());
          throw ex;
      }
  }

  /**
    * oracle 函数调用（Function） 函数返回REF_CURSOR类型
    *
    * @param entityManager 实体管理上下文
    * @param functionName 方法名称
    * @param params       参数集合
    * @return 集合信息
    */
  public List getOracleFunctionQueryList(EntityManager entityManager, String functionName, Object... params) {
      try {
          Query query = entityManager.createNamedQuery(functionName);
          for (int i = 0; i < params.length; i++) {
              query.setParameter(i, params[i]);
          }
          List resultList = query.getResultList();
          if (resultList.isEmpty()) return Collections.emptyList();
          return resultList;
      } catch (Exception ex) {
          logger.error(ex.toString());
          throw ex;
      }
  }


  /**
    * SQLserver 存储过程调用 存储过程返回REF_CURSOR类型
    *
    * @param entityManager 实体管理上下文
    * @param spQueryAdapter 实体查询适配器上下文
    * @param ProcedureName 存储过程名称
    * @param params         参数集合
    * @return 集合信息
    */
  public List getSqlserverProcedureQueryList(EntityManager entityManager, SPQueryAdapter spQueryAdapter, String ProcedureName, Object... params) {
      try {
          StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery(ProcedureName);
          Class[] classes = new Class[]{};
          Object[] objects = new Object[]{};
          for (int i = 0; i < params.length; i++) {
              classes[i] = params[i].getClass();
              objects[i] = params[i];
          }
          spQueryAdapter.setParamsByPosition(storedProcedureQuery, classes, objects, Object.class, ParameterMode.REF_CURSOR);
          storedProcedureQuery.execute();
          List result = storedProcedureQuery.getResultList();
          if (result.isEmpty())
              return Collections.emptyList();
          return result;
      } catch (Exception ex) {
          logger.error(ex.toString());
          throw ex;
      }
  }
}

@Autowired
private ProcedureQueryServiceUtil procedureQueryServiceUtil;

@Override
public List procedureCall(Date startTime, Date endTime) {
    return procedureQueryServiceUtil.getOracleProcedureQueryList(entityManager, PROC_SIGT_CALL_RATIO, startTime, endTime);
}