欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-04-23 08:37:03
...

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