[Java]通过Jdbc调用存储过程

最新推荐文章于 2023-04-14 08:56:29 发布

原创最新推荐文章于 2023-04-14 08:56:29 发布 · 314 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jdbcTemplate #存储过程

这篇博客介绍了如何使用Java的JdbcTemplate调用Oracle存储过程，包括处理有返回结果集和无返回结果集的情况。通过示例展示了存储过程的调用方式以及如何获取和操作返回的结果集。

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.youkuaiyun.com/jiaweicheng77/article/details/84564092

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