开发者

spring中JdbcTemplate操作oracle的存储过程实例代码

目录
  • 场景:
  • 功能:
  • 1.JdbcTemplate调用存储过程(Procedure)不带返回值
    • 1.1存储过程
    • 1.2代码
  • 2.JdbcTemplate调用存储过程(Procedure)带返回值但值不是集合类型
    • 2.1存储过程
    • 2.2代码
  • 3.JdbcTemplate调用存储过程(Procedure)带返回值且值集合类型
    • 3.1存储过程
    • 3.2代码
  • 4.附本例使用建表语句
    • 5.附本例使用完整测试代码
      • 总结

        场景:

        使用Java代码调用oracle的存储过程,本例使用JdbcTemplate模板类操作.

        功能:

        方便后续查阅.

        1.JdbcTemplate调用存储过程(Procedure)不带返回值

        1.1存储过程

        CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS4(TASK_ID IN NUMBER) IS
        BEGIN
          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;

        1.2代码

        public static void doProcedures() {
        	String procedures = "{call PRO_QUERY_INFO_ARGS4 ('888')}";
        	jdbcTemplate.execute(procedures);
        }

        2.JdbcTemplate调用存储过程(Procedure)带返回值但值不是集合类型

        2.1存储过程

        CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS3(ARGS     IN VARCHAR2,
                                                         RTNINFO  OUT VARCHAR2,
                                                         ERRORMSG OUT VARCHAR2,
                                                         FAILINFO OUT VARCHAR2) IS
        BEGIN
          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;

        2.2代码

        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, "代码调用");
          	  /** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
          	  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;
          	}
        	});
        }

        3.JdbcTemplate调用存储过程(Procedure)带返回值且值集合类型

        3.1存储过程

        CREATE OR REPLACE PROCEDURE PRO_QUERY_INFO_ARGS2(ERRORMSG OUT VARCHAR2,
                                                         CURINFO  OUT SYS_REFCURSOR) IS
        BEGIN
          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;

        3.2代码

        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);
          	  /** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
          	  statement.registerOutParameter(1, OracleTypes.VARCHAR);
          	  /** 注册输出参数的类型-此处集合为oracle的游标类型 */
          	  statement.registerOutParameter(2, OracleTypes.CURSOR);
          	  return statement;
          	}
          }, new CallableStatementCallback() {
          	public Object doInCallableStatement(
          	  	CallableStatement statement) throws SQLException,
          	  	DataAccessException {
          	  List resultsMap = new ArrayList();
          	  statement.execute();
          	  /** 获取游标结果集-此处2是存储过程参数顺序 */
          	  ResultSet resultSet = (ResultSet) statement
          	  		.getObject(2);
          	  /** 转换每行的返回值到Map中 */
          	  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;
        }

        4.附本例使用建表语句

        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);

        5.附本例使用完整测试代码

        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")js.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 开发者_Go学习('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, "代码调用");
          	 /** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
          	 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;
          	}
          });
        }
        /**
         * 调用存储过程-返回值是List集合
         * */
        public static List getProceduresResultList() {
          List resultList = (List) jdbcTemplate.execute(
           new CallableStatementCreawww.devze.comtor() {
           	public CallableStatement createCallableStatement(
           	 	Connection conn) throws SQLException {
           	 /** 调用指定存储过程 */
           	 Strin编程客栈g procedures = "{ CALL PRO_QUERY_INFO_ARGS2(?,?) }";
           	 CallableStatement statement = conn
           	 		.prepareCall(procedures);
           	 /** 注册输出参数的类型-此处集合为oracle的VARCHAR2 */
           	 statement.registerOutParameter(1, OracleTypes.VARCHAR);
           	 /** 注册输出参数的类型-此处集合为oracle的游标类型 */
           	 statement.registerOutParameter(2, OracleTypes.CURSOR);
           	 return statement;
           	}
           }, new CallableStatementCallback() {
           	public Object doInCallableStatement(
           	 	CallableStatement statement) throws SQLException,
           	 	DataAccessException {
           	 List resultsMap = new ArrayList();
           	 statement.execute();
           	 /** 获取游标结果集-此处2是存储过程参数顺序 */
           	 ResultSet resultSet = (ResultSet) statement
           	 		.getObject(2);
           	 /** 转换每行的返回值到Map中 */
           	 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;
        }
        /** 获取JdbcTemplate数据源 */
        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);
          /** 获取spring的JdbcTemplate*/
          JdbcTemplate jdbcTemplate = new JdbcTemawNpxplate();
          /** 设置数据源 */
          jdbcTemplate.setDataSource(dataSource);
          return jdbcTemplate;
        }
        }

        以上,感谢.

        总结

        到此这篇关于spring中JdbcTemplate操作oracle的存储过程的文章就介绍到这了,更多相关JdbcTemplate操作oracle存储过程内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

        0

        上一篇:

        下一篇:

        精彩评论

        暂无评论...
        验证码 换一张
        取 消

        最新开发

        开发排行榜