[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
上一篇: 远程操作建立存储过程