专栏首页Spring Cloud设计原理老调重弹:JDBC系列 之 存储过程 CallableStatement(创建和使用)

老调重弹:JDBC系列 之 存储过程 CallableStatement(创建和使用)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://louluan.blog.csdn.net/article/details/31376041

前言

最近在研究Mybatis框架,由于该框架基于JDBC,想要很好地理解和学习Mybatis,必须要对JDBC有较深入的了解。所以便把JDBC 这个东东翻出来,老调重弹,好好总结一番,作为自己的笔记,也是给读者一个参考~~~       本文主要通过 使用JDBC创建存储过程使用JDBC调用存储过程两部分 阐述JDBC 对存储过程的支持。本文将在Oracle数据库下创建一个可以表示岗位信息的基本表Jobs为例, 然后通过存储过程对这个Jobs表进行各种操作。表JOBS的建表语句如下: -- Create table create table JOBS ( job_id VARCHAR2(10) not null, job_title VARCHAR2(35), min_salary NUMBER(6), max_salary NUMBER(6) ); -- Add comments to the table comment on table JOBS is '岗位信息表'; -- Add comments to the columns comment on column JOBS.job_id is 'Job Id'; comment on column JOBS.job_title is '岗位名称'; comment on column JOBS.min_salary is '最小薪酬'; comment on column JOBS.max_salary is '最大薪酬'; -- Create/Recreate primary, unique and foreign key constraints alter table JOBS add constraint PK_JOB_ID primary key (JOB_ID);

JDBC创建存储过程

使用数据库操作数据库需要三个步骤: 执行 创建存储过程语句 --> 编译存储过程---> 调用存储过程。 比如我们创建一个向表Jobs添加记录的存储过程,并且调用它,在数据库上要执行下列代码:

--1.创建存储过程 CREATE OR REPLACE PROCEDURE insert_jobs_proc( input_job_id IN VARCHAR2, input_job_title IN VARCHAR2, input_min_salary IN NUMBER, input_max_salary IN NUMBER) AS BEGIN INSERT INTO jobs(job_id,job_title,Min_Salary,max_salary)VALUES(input_job_id,input_job_title,input_min_salary,input_max_salary); END insert_jobs_proc; --2.编译存储过程 COMPILE; --3.使用存储过程 CALL insert_jobs_proc('AD_PRES','President',20080,40000); 由于上述的代码本质上来说就是SQL代码,可以使用JDBC逐步执行上述的SQL代码即可(不过使用JDBC创建不需要调用compile进行编译,JDBC会自动让数据库编译): public static void inTest(){ Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Driver driver = DriverManager.getDriver(URL); Properties props = new Properties(); props.put("user", USER_NAME); props.put("password", PASSWORD); connection = driver.connect(URL, props); //获得Statement对象,这里使用了事务机制,如果创建存储过程语句失败或者是执行compile失败,回退 connection.setAutoCommit(false); statement = connection.createStatement(); String procedureString = "CREATE OR REPLACE PROCEDURE insert_jobs_proc(" +"input_job_id IN VARCHAR2," +"input_job_title IN VARCHAR2," +"input_min_salary IN NUMBER," +"input_max_salary IN NUMBER) AS " +"BEGIN " +"INSERT INTO jobs(job_id,job_title,Min_Salary,max_salary)VALUES(input_job_id,input_job_title,input_min_salary,input_max_salary); " +"END insert_jobs_proc;"; //1 创建存储过程,JDBC 数据库会编译存储过程 statement.execute(procedureString); //成功则提交 connection.commit(); //2.调用 CallableStatement callableStatement = connection.prepareCall("CALL insert_jobs_proc(?,?,?,?)"); //设置IN参数 callableStatement.setString(1, "AD_PRESS"); callableStatement.setString(2, "President"); callableStatement.setBigDecimal(3, new BigDecimal(20080)); callableStatement.setBigDecimal(4, new BigDecimal(40000)); callableStatement.execute(); connection.commit(); } catch (ClassNotFoundException e) { System.out.println("加载Oracle类失败!"); e.printStackTrace(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); }finally{ //使用完成后管理链接,释放资源,释放顺序应该是: ResultSet ->Statement ->Connection try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }

JDBC调用存储过程

使用JDBC调用存储过程的基本格式为:

CALL PROCEDURE_NAME(parameter1,parameter2,paramter3.....) 这里参数有三种不同的形式 :in 类型、out类型还有 in 和out的混合类型: IN 类型:此类型是用于参数从外部传递给存储过程使用; OUT类型:此类型是存储过程执行过程中的返回值; IN、OUT混合类型:此类型是参数传入,然后返回。 以下分四种参数类型创建不同的存储过程,然后通过JDBC调用: 只有输入IN参数,没有输出OUT参数 上面演示的存储过程  insert_jobs_proc 就是只有IN 参数传入的例子,请读者看上述的 例子。 既有输入IN参数,也有输出OUT参数,输出是简单值(非列表) 创建一个存储过程  get_job_min_salary_proc,传入特定岗位的job_id,返回输出此岗位的最小薪酬min_salary,对应的SQL语句如下: CREATE OR REPLACE PROCEDURE get_job_min_salary_proc( input_job_id IN VARCHAR2, output_salary OUT number) AS BEGIN SELECT min_salary INTO output_salary FROM jobs WHERE job_id = input_job_id; END get_job_min_salary_proc; 在JDBC中调用如下: /* * 有IN 类型的参数输入 和Out类型的参数输出 */ public static void inOutTest(){ Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Driver driver = DriverManager.getDriver(URL); Properties props = new Properties(); props.put("user", USER_NAME); props.put("password", PASSWORD); connection = driver.connect(URL, props); //获得Statement对象,这里使用了事务机制,如果创建存储过程语句失败或者是执行compile失败,回退 connection.setAutoCommit(false); statement = connection.createStatement(); String procedureString = "CREATE OR REPLACE PROCEDURE get_job_min_salary_proc(" +"input_job_id IN VARCHAR2," +"output_salary OUT number) AS " +"BEGIN " +"SELECT min_salary INTO output_salary FROM jobs WHERE job_id = input_job_id; " +"END get_job_min_salary_proc;"; //1 创建存储过程,JDBC 数据库会编译存储过程 statement.execute(procedureString); //成功则提交 connection.commit(); //2.创建callableStatement CallableStatement callableStatement = connection.prepareCall("CALL get_job_min_salary_proc(?,?)"); //3,设置in参数 callableStatement.setString(1, "AD_PRES"); //4.注册输出参数 callableStatement.registerOutParameter(2, Types.NUMERIC); //5.执行语句 callableStatement.execute(); BigDecimal salary = callableStatement.getBigDecimal(2); System.out.println(salary); } catch (ClassNotFoundException e) { System.out.println("加载Oracle类失败!"); e.printStackTrace(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); }finally{ //使用完成后管理链接,释放资源,释放顺序应该是: ResultSet ->Statement ->Connection try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } 既有输入IN参数,也有输出OUT参数,输出是列表 创建一个存储过程 get_min_greater_proc,输入参数 最小薪酬,返回jobs表里最小薪酬不小于此参数的岗位集合。 对应的SQL语句如下: --创建一个包,自定义一个数据类型 my_cursor CREATE OR REPLACE PACKAGE my_package_cursor IS TYPE my_cursor IS REF CURSOR; END my_package_cursor; --创建 存储过程,通过传入最小薪酬,返回JOBs表内不小于最小薪酬的岗位集合 CREATE OR REPLACE PROCEDURE get_min_greater_proc( input_min_salary IN NUMBER, setResult OUT my_package_cursor.my_cursor) AS BEGIN OPEN setResult FOR SELECT * FROM jobs WHERE min_salary >= input_min_salary; END get_min_greater_proc; JDBC调用代码如下: /* * 有IN 类型的参数输入 和Out类型的集合输出 */ public static void inOutResultSetTest(){ Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); Driver driver = DriverManager.getDriver(URL); Properties props = new Properties(); props.put("user", USER_NAME); props.put("password", PASSWORD); connection = driver.connect(URL, props); //1.创建callableStatement CallableStatement callableStatement = connection.prepareCall("CALL get_min_greater_proc(?,?)"); //2,设置in参数 callableStatement.setBigDecimal(1, new BigDecimal(20000)); //3.注册输出参数 callableStatement.registerOutParameter(2, OracleTypes.CURSOR); //4.执行语句 callableStatement.execute(); //返回的是结果集 resultSet = (ResultSet)callableStatement.getObject(2); } catch (ClassNotFoundException e) { System.out.println("加载Oracle类失败!"); e.printStackTrace(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); }finally{ //使用完成后管理链接,释放资源,释放顺序应该是: ResultSet ->Statement ->Connection try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } 输入输出参数是同一个(IN OUT) 创建一个存储过程 get_job_info ,传入job_id 返回 job_id ,返回的job_id 是输入的job_id 和对应的job_title 拼接而成。 --创建存储过程 传入job_id 返回job_id CREATE OR REPLACE PROCEDURE get_job_info( io_job_id IN OUT VARCHAR2) AS BEGIN SELECT job_id ||job_title INTO io_job_id FROM jobs WHERE job_id =io_job_id ; END get_job_info; 对应的JDBC代码如下: //1.创建callableStatement CallableStatement callableStatement = connection.prepareCall("CALL get_job_info(?)"); //2,设置in参数 callableStatement.setString(1, "AD_PRES"); //3.注册输出参数 callableStatement.registerOutParameter(1, Types.VARCHAR); //4.执行语句 callableStatement.execute(); //返回结果 String jobId = callableStatement.getString(1); System.out.println(jobId); ----------------------------------------------------------------------------------------------------------------------------------------

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 老调重弹:JDBC系列 之

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://louluan.blog.c...

    亦山
  • 《深入理解mybatis原理》 MyBatis缓存机制的设计与实现

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://louluan.blo...

    亦山
  • 《深入理解mybatis原理》 MyBatis的一级缓存实现详解 及使用注意事项

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://louluan.blog.c...

    亦山
  • 【优秀题解】一道题目的递归与非递归两种解法

    下面分享大牛一道题的两种(递归+非递归)的解法,供大家学习!也欢迎贡献你的题解! 原题链接:发工资咯 http://www.dotcpp.com/oj/pro...

    编程范 源代码公司
  • python 购物车小程序(列表、循环、

    py3study
  • 为什么 Spark Streaming + Kafka 无法保证 exactly once?

    结合文章 揭开Spark Streaming神秘面纱④ - job 的提交与执行我们画出了如下 job 调度执行流程图:

    codingforfun
  • Wget 大法wget 指定路径,指定文件名下载

    今天用到了Wget,突然一时间想不起来wget的下载到指定目录是哪个参数了,特地把所有参数都弄来,以防又忘记了。毕竟脚本是写了之后,半年都不用改,坑!

    用户2353021
  • Go语言interface详解

    interface Go语言里面设计最精妙的应该算interface,它让面向对象,内容组织实现非常的方便,当你看完这一章,你就会被interface的巧妙设计...

    李海彬
  • linux应用之wget命令详解

    wget是linux最常用的下载命令, 一般的使用方法是: wget + 空格 + 要下载文件的url路径

    用户5640963
  • python wget下载文件

    -P PREFIX 将文件保存在目录(--directory-prefix=PREFIX)

    py3study

扫码关注云+社区

领取腾讯云代金券