前言
最近在研究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);
----------------------------------------------------------------------------------------------------------------------------------------
注：本篇文章很大程度上借鉴了 JDBC执行存储过程的四种情况 这篇博文。
欢迎读者关注我的 老调重弹：JDBC系列 的其他文章～～～