前言
最近在研究Mybatis框架,由于该框架基于JDBC,想要很好地理解和学习Mybatis,必须要对JDBC有较深入的了解。所以便把JDBC 这个东东翻出来,老调重弹,好好总结一番,作为自己的笔记,也是给读者一个参考~~~
本文主要通过 使用JDBC创建存储过程 和使用JDBC调用存储过程两部分 阐述JDBC 对存储过程的支持。本文将在Oracle数据库下创建一个可以表示岗位信息的基本表Jobs为例, 然后通过存储过程对这个Jobs表进行各种操作。表JOBS的建表语句如下:
-
- create table JOBS
- (
- job_id VARCHAR2(10) not null,
- job_title VARCHAR2(35),
- min_salary NUMBER(6),
- max_salary NUMBER(6)
- );
-
-
- comment on table JOBS
- is '岗位信息表';
-
- 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 '最大薪酬';
-
- alter table JOBS
- add constraint PK_JOB_ID primary key (JOB_ID);
JDBC创建存储过程
使用数据库操作数据库需要三个步骤: 执行 创建存储过程语句 --> 编译存储过程---> 调用存储过程。
比如我们创建一个向表Jobs添加记录的存储过程,并且调用它,在数据库上要执行下列代码:
-
-
- 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;
-
-
- COMPILE;
-
-
- 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);
-
-
- 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;";
-
- statement.execute(procedureString);
-
- connection.commit();
-
- CallableStatement callableStatement = connection.prepareCall("CALL insert_jobs_proc(?,?,?,?)");
-
- 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{
-
-
- 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中调用如下:
-
-
-
- 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);
-
-
- 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;";
-
- statement.execute(procedureString);
-
- connection.commit();
-
- CallableStatement callableStatement = connection.prepareCall("CALL get_job_min_salary_proc(?,?)");
-
- callableStatement.setString(1, "AD_PRES");
-
- callableStatement.registerOutParameter(2, Types.NUMERIC);
-
- 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{
-
-
- try {
- statement.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
-
- try {
- connection.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
-
既有输入IN参数,也有输出OUT参数,输出是列表
创建一个存储过程 get_min_greater_proc,输入参数 最小薪酬,返回jobs表里最小薪酬不小于此参数的岗位集合。
对应的SQL语句如下:
-
- CREATE OR REPLACE PACKAGE my_package_cursor IS
- TYPE my_cursor IS REF CURSOR;
- END my_package_cursor;
-
-
- 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调用代码如下:
-
-
-
- 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);
-
- CallableStatement callableStatement = connection.prepareCall("CALL get_min_greater_proc(?,?)");
-
- callableStatement.setBigDecimal(1, new BigDecimal(20000));
-
- callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
-
- 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{
-
-
- 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 拼接而成。
-
- 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代码如下:
-
- CallableStatement callableStatement = connection.prepareCall("CALL get_job_info(?)");
-
- callableStatement.setString(1, "AD_PRES");
-
- callableStatement.registerOutParameter(1, Types.VARCHAR);
-
- callableStatement.execute();
-
- String jobId = callableStatement.getString(1);
- System.out.println(jobId);
----------------------------------------------------------------------------------------------------------------------------------------
注:本篇文章很大程度上借鉴了 JDBC执行存储过程的四种情况 这篇博文。
欢迎读者关注我的 老调重弹:JDBC系列 的其他文章~~~
原文地址:http://blog.csdn.net/luanlouis/article/details/31376041