[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()来获得.

 

参考:

1.JdbcTemplate执行存储过程的3种方式:https://blog.csdn.net/jiaweicheng77/article/details/84564092

2.Using Unbounded SELECT Statements:https://learning.oreilly.com/library/view/mysql-stored-procedure/0596100892/ch05s04.html