[Java]通過Jdbc調用存儲過程

1.sql語句:call storeProcedure(a,b,c)

2.存儲過程有傳回結果集或結果做法:

Connection conn = null;
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
      conn = jdbcTemplate.getDataSource().getConnection();
      cs = conn.prepareCall(sqlLibrary.EventLog_listEventLogByAppIdNStatus);
      conn.setAutoCommit(false);
      cs.setString(1, app_flag);
      cs.setString(2, status);
      cs.setString(3, eventMaxCount);
      cs.registerOutParameter(4, OracleTypes.CURSOR);
      cs.execute();
      rs = (ResultSet) cs.getObject(4);
      while (rs.next()) {
        event_list.add(new Event(rs));
      }
      rs.close();
      conn.commit();
      conn.setAutoCommit(true);
    } catch (SQLException e) {
      logger.error("sql exception: " + e.getMessage()); ///
    } catch (DataAccessException e) {
      dbLogger.error("One PNS DB is down. Exception: " + e.getMessage());
    } finally {
      try {
        if (rs != null)
          rs.close();
        if (cs != null)
          cs.close();
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }      

 可以看到這是存儲過程(Oracle)有傳回結果集的,output是一個cursor

ResultSet:

public Event(ResultSet rs) throws SQLException{
    this.event_id = rs.getString("event_id");
    this.user_id = rs.getString("user_id");
    this.msisdn = rs.getString("msisdn");
    this.service_name  = rs.getString("service_name");
    this.quota_status = rs.getString("quota_status");
    this.quota_name = rs.getString("quota_name");
    this.event_code = rs.getString("event_code");
    this.app_flag = rs.getInt("app_flag");
    this.status = rs.getInt("status");
    this.event_date =  getOracleTimestamp(rs.getObject("event_date"));
    this.last_update_date =  getOracleTimestamp(rs.getObject("last_update_date"));
    this.event_content = rs.getString("event_content");
  }      

3.沒有傳回結果集的

Connection conn = null;
    CallableStatement cs = null;
    ResultSet rs = null;
    int rs_code = 99;
    String rs_msg = null;
    List<CsvModel> searchResultList = new ArrayList<CsvModel>();
    try{
      conn = jdbcTemplate.getDataSource().getConnection();
      cs = conn.prepareCall(Constant.UMGCsvStoreProcedure_call);
      logger.debug("=======param in:{}", csvSP.toString());
      cs.setString(1, csvSP.getSubmitTimeFro());
      cs.setString(2, csvSP.getSubmitTimeTo());
      /*cs.setTimestamp(1, Timestamp.valueOf(csvSP.getSubmitTimeFro()));
      cs.setTimestamp(2, Timestamp.valueOf(csvSP.getSubmitTimeTo()));*/
      cs.setString(3, csvSP.getCorpId());
      cs.setString(4, csvSP.getSenderList());
      cs.setString(5, csvSP.getRecipientList());
      cs.setString(6, csvSP.getChannel());
      cs.setString(7, csvSP.getDirection());
      cs.setString(8, null);
      cs.setString(9, null);
      cs.registerOutParameter(10, Types.INTEGER);
      cs.registerOutParameter(11, Types.VARCHAR);
      cs.execute();
      rs = cs.getResultSet();
      
      rs_code = (int) cs.getObject(10);
      rs_msg = (String) cs.getObject(11);
      if (rs_code == 0) {
        while (rs.next()) {
          searchResultList.add(new CsvModel(rs));
        }
      }
      rs.close();
    }catch (DataAccessException e) {
      dbLogger.error("DB is down. Exception is [{}]", e.getMessage());
      throw e;
    } catch (SQLException e) {
      logger.error("-- Error occur, exception is [{}]", e.getMessage());
      e.printStackTrace();
    }catch (Exception e) {
      logger.error("-- Error occur, exception is [{}]", e.getMessage());
      throw e;
    }finally {
      try {
        if (rs != null)
          rs.close();
        if (cs != null)
          cs.close();
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }      

可以看出,基本是和有結果集的操作差不多,但由于我的select statement不是以結果集遊标的形式傳回,是以要用上getResultSet()來獲得.