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

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1432283

前言

最近在研究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);undefined