这篇“spring中JdbcTemplate操作oracle的存储过程是什么”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“s
这篇“spring中JdbcTemplate操作oracle的存储过程是什么”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“spring中JdbcTemplate操作oracle的存储过程是什么”文章吧。
使用java代码调用oracle的存储过程,本例使用JdbcTemplate模板类操作.
方便后续查阅.
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS4(TASK_ID IN NUMBER) ISBEGIN INSERT INTO F_LOG_INFO (TASK_ID, BEGIN_TIME, END_TIME, FLAG, FaiL_INFO, DATA_COUNT, TABLE_NAME) VALUES (TASK_ID, SYSDATE - 1, SYSDATE, '999', '999', 999, 'TABLE_NAME2019'); COMMIT;EXCEPTioN WHEN OTHERS THEN ROLLBACK;END PRO_QUERY_INFO_ARGS4;
public static void doProcedures() {String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";jdbcTemplate.execute(procedures);}
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS3(ARGS IN VARCHAR2, RTNINFO OUT VARCHAR2, ERRORMSG OUT VARCHAR2, FAILINFO OUT VARCHAR2) ISBEGIN ERRORMSG := ''; RTNINFO := '你输入的ARGS=' || ARGS; SELECT FAIL_INFO INTO FAILINFO FROM F_LOG_INFO where TASK_ID = 1; COMMIT;EXCEPTION WHEN OTHERS THEN ERRORMSG := 'PRO_QUERY_INFO_ARG抛出异常: ' || sqlERRM;END PRO_QUERY_INFO_ARGS3;
public static void getProceduresResult() { String tt2 = (String) jdbcTemplate.execute( new CallableStatementCreator() { public CallableStatement createCallableStatement( Connection con) throws SQLException { String procedures = "{call PRO_QUERY_INFO_ARGS3 (?,?,?,?)}"; CallableStatement cs = con.prepareCall(procedures); cs.setString(1, "代码调用"); cs.reGISterOutParameter(2, OracleTypes.VARCHAR); cs.registerOutParameter(3, OracleTypes.VARCHAR); cs.registerOutParameter(4, OracleTypes.VARCHAR); return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement st) throws SQLException, DataAccessException { st.execute(); Object tt2 = st.getObject(2); Object tt3 = st.getObject(3); Object tt4 = st.getObject(4); return tt2; }});}
CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS2(ERRORMSG OUT VARCHAR2, CURINFO OUT SYS_REFCURSOR) ISBEGIN ERRORMSG := ''; OPEN CURINFO FOR SELECT FAIL_INFO, TABLE_NAME FROM F_LOG_INFO; COMMIT;EXCEPTION WHEN OTHERS THEN ERRORMSG := 'PRO_QUERY_INFO_ARG2抛出异常: ' || SQLERRM;END PRO_QUERY_INFO_ARGS2;
public static List getProceduresResultList() { List resultList = (List) jdbcTemplate.execute( new CallableStatementCreator() { public CallableStatement createCallableStatement( Connection conn) throws SQLException { String procedures = "{ CALL PRO_QUERY_INFO_ARGS2(?,?) }"; CallableStatement statement = conn .prepareCall(procedures); statement.registerOutParameter(1, OracleTypes.VARCHAR); statement.registerOutParameter(2, OracleTypes.CURSOR); return statement; } }, new CallableStatementCallback() { public Object doInCallableStatement( CallableStatement statement) throws SQLException, DataAccessException { List resultsMap = new ArrayList(); statement.execute(); ResultSet resultSet = (ResultSet) statement .getObject(2); while (resultSet.next()) { Map rowMap = new HashMap(); rowMap.put("FAIL_INFO", resultSet.getObject("FAIL_INFO")); rowMap.put("TABLE_NAME", resultSet.getObject("TABLE_NAME")); resultsMap.add(rowMap); } resultSet.close(); return resultsMap; } }); return resultList;}
create table F_LOG_INFO( task_id NUMBER(16) not null, begin_time DATE, end_time DATE, flag VARCHAR2(8), fail_info VARCHAR2(512), data_count NUMBER(16), table_name VARCHAR2(256));alter table F_LOG_INFO add constraint PK_F_LOG_INFO primary key (TASK_ID);
public class TestProcedures {public static JdbcTemplate jdbcTemplate = getJdbcTemplate();public static void main(String[] args) { System.out.println("测试开始......"); // getProceduresResult(); doProcedures(); List result = getProceduresResultList(); for (int i = 0; i < result.size(); i++) { Map rowMap = (Map) result.get(i); String id = rowMap.get("FAIL_INFO").toString(); String name = rowMap.get("TABLE_NAME").toString(); System.out.println("FAIL_INFO=" + id + ";TABLE_NAME=" + name); } System.out.println("测试结束......");}public static void doProcedures() {String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";jdbcTemplate.execute(procedures);}public static void getProceduresResult() { String tt2 = (String) jdbcTemplate.execute( new CallableStatementCreator() { public CallableStatement createCallableStatement( Connection con) throws SQLException { String procedures = "{call PRO_QUERY_INFO_ARGS3 (?,?,?,?)}"; CallableStatement cs = con.prepareCall(procedures); cs.setString(1, "代码调用"); cs.registerOutParameter(2, OracleTypes.VARCHAR); cs.registerOutParameter(3, OracleTypes.VARCHAR); cs.registerOutParameter(4, OracleTypes.VARCHAR); return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement st) throws SQLException, DataAccessException { st.execute(); Object tt2 = st.getObject(2); Object tt3 = st.getObject(3); Object tt4 = st.getObject(4); return tt2; } });}public static List getProceduresResultList() { List resultList = (List) jdbcTemplate.execute( new CallableStatementCreator() { public CallableStatement createCallableStatement( Connection conn) throws SQLException { String procedures = "{ CALL PRO_QUERY_INFO_ARGS2(?,?) }"; CallableStatement statement = conn .prepareCall(procedures); statement.registerOutParameter(1, OracleTypes.VARCHAR); statement.registerOutParameter(2, OracleTypes.CURSOR); return statement; } }, new CallableStatementCallback() { public Object doInCallableStatement( CallableStatement statement) throws SQLException, DataAccessException { List resultsMap = new ArrayList(); statement.execute(); ResultSet resultSet = (ResultSet) statement .getObject(2); while (resultSet.next()) { Map rowMap = new HashMap(); rowMap.put("FAIL_INFO", resultSet.getObject("FAIL_INFO")); rowMap.put("TABLE_NAME", resultSet.getObject("TABLE_NAME")); resultsMap.add(rowMap); } resultSet.close(); return resultsMap; } }); return resultList;}public static JdbcTemplate getJdbcTemplate() { DruidDataSource dataSource = new DruidDataSource(); String username = "demodb"; String passWord = "123456"; String jdbcUrl = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; String driverName = "oracle.jdbc.OracleDriver"; dataSource.setPassword(password); dataSource.setUrl(jdbcUrl); dataSource.setUsername(username); dataSource.setDriverClassName(driverName); JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource); return jdbcTemplate;}}
以上就是关于“spring中JdbcTemplate操作oracle的存储过程是什么”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注编程网精选频道。
--结束END--
本文标题: spring中JdbcTemplate操作oracle的存储过程是什么
本文链接: https://www.lsjlt.com/news/357627.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
2024-05-09
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0