Spring JdbcTemplate 调用 Oracle 存储过程 与 Oracle 驱动下载
目录
前 言
Oracle 驱动下载
Oracle 数据源配置
execute 调用无返回值存储过程
execute 调用单个返回值存储过程
execute 调用返回结果集存储过程
call 方法调用存储过程
前 言1、关于 JdbcTemplate 的介绍、pom 依赖、DI 注入可以参考《Spring JdbcTemplate 模板剖析 之 常用 增删改查》，本文继续介绍 JdbcTemplate 调用数据库的存储过程，虽然 Mysql 也有存储过程，但是为了尽可能的多覆盖一点，本文选择调用 Oracle 的存储过程，其它数据库也是同理。
1）execute 方法：能执行任何 SQL 语句，一般用于执行 DDL 语句；以及 建表、删表等等 SQL.
2）update、batchUpdate 方法：update 方法用于执行新增、修改、删除等语句；batchUpdate 方法用于执行批处理相关语句；
3）queryForObject、queryForList、query 方法：用于执行查询相关语句；queryForObject 查询的结果只能是1条，多余或少于都会抛异常；
4）queryForList 与 query 查询结果为空时，返回的 list 大小为0，不会引发空指针异常。
5）call 方法：用于执行存储过程、函数相关语句。
2、本文环境：Oracle 11g（驱动版本 ojdbc8-19.3.0.0） + Java JDK 1.8 + Spring Boot 2.1.5 + + IDEA 2018.
3、JdbcTemplate 本身就是对 JDBC 的轻量级封装，所以调用存储过程也类似 "JDBC 调用存储过程/函数"。
4、为了测试方便，先提前准备数据：准备员工表与部门表测试数据
Oracle 驱动下载1、不同于开源的 Mysql ，Oracle 是收费的，从 Maven 中央仓库上面通常无法成功下载 Oracle 依赖，只能直接去 Oracle 官网下载：
com.oracle.jdbc
ojdbc8
12.2.0.1
ojdbc6 匹配 jdk 1.6、ojdbc8 匹配 jdk 1.8，ojdbc10 匹配 jdk 10 依此类推，而 ojdbc14 匹配的是 jdk1.4.
2、解决方式一：网络上有好心人和组织共享了一些可供下载的、且与官网等价的 maven 依赖，比如下面这个(亲测有效)：
com.github.noraui
ojdbc8
12.2.0.1
3、解决方式二：虽然从 maven 中央仓库无法下载 ojdbc 驱动，但是直接从 Oracle 官网是可以下载 jar 包的。
3.1、先从官网下载 ojdbc8.jar 包：https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html
从 Oracle 官网下载它们家的东西都是需要先登陆的，所以如果没有账号，需要先注册，下载驱动不收费。
3.3、然后使用下面的 mvn install 命令进行项目部署，它会自动存放在本地仓库中：
mvn install:install-file -DgroupId=com.oracle.jdbc -DartifactId=ojdbc8 -Dversion=12.2.0.1 -Dpackaging=jar -DgeneratePom=true -Dfile=C:\Users\Think\Downloads\ojdbc8.jar
-DgroupId：指定 groupId 的值，可以自定义，建议与人家官网的一致即可
-DartifactId：指定 artifactId 的值，可以自定义，建议与人家官网的一致即可
-Dversion：指定 version 的值，可以自定义，建议与人家官网的一致即可，也可以从打开 ojdbc8.jar ，其中的 MANIFEST.MF 文件中也可以看到版本号
-Dpackaging：指定打包的类型，如 jar、war 包
-DgeneratePom：指定是否生成 pom.xml 文件，指定让它生成
-Dfile：需要部署的 jar 包文件路径
Oracle 数据源配置
1、在开始下面的代码编写之前，需要在全局配置文件中指定数据源配置：
#数据源配置
spring:
profiles: oracleDb
datasource:
username: hnbs_3
password: 1
driverClassName: oracle.jdbc.driver.OracleDriver
url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
更多配置项可以参考官网：https://docs.spring.io/spring-boot/docs/2.1.6.RELEASE/reference/htmlsingle/#common-application-properties
或者：org.springframework.boot.autoconfigure.jdbc.DataSourceProperties.java
execute 调用无返回值存储过程
1、Mysql 中有 "drop table if exists 表名"的操作，当表存在时才进行删除，Oracle 中并没有 if exists 的判断，所以直接删除时，如果表不存在，"drop table 表名 " 就会报错，
2、这里用一个存储过程来解决这个问题，Oracle 数据库中准备存储过程如下，功能就是传入表名作为参数，如果表已经存在，则删除它，否则不进行操作，存储过程不进行返回。
--表名作为参数，如果表已经存在，则删除它。
create or replace procedure pro_drop_table_by_name(tableName in user_tables.TABLE_NAME%type)
is
flag number := 0; --表是否存在的表示，大于0表示表已经存在
begin
--user_tables 是系统定义的视图，可以查看当前用户下的所有表信息，表中的表名区分大小写，而且是大写
select count(1) into flag from user_tables where table_name = upper(tableName) ;
if flag > 0 then
execute immediate 'drop table '|| tableName ;--如果表已经存在，则删除它
end if;
end;
-- 数据库中调用存储过程：call pro_drop_table_by_name('student');
注意：这里为了保证数据完整性，并没有做级联删除，也就是说当被删除的表中的数据如果被其它表引用，则删除时会报错
/**
* 删除表
* http://localhost:8080/emp/dropTable?tableName=emp
* http://localhost:8080/emp/dropTable?tableName=dept
* emp 员工表引用了 dept 部门表，如果先删除 dept 表，其中有数据被 emp 表引用，则删除时报错：
* oracle.jdbc.OracleDatabaseException: ORA-02449: 表中的唯一/主键被外键引用
*
* @param tableName
* @return
*/
@GetMapping("emp/dropTable")
public String dropTableByName(@RequestParam String tableName) {
JsonObject jsonObject = new JsonObject();
try {
//sql 和在数据库中完全一样
String sql = "call pro_drop_table_by_name('" + tableName + "')";
jdbcTemplate.execute(sql);
jsonObject.addProperty("code", 200);
jsonObject.addProperty("message", sql);
} catch (DataAccessException e) {
logger.error(e.getMessage(), e);
jsonObject.addProperty("code", 500);
jsonObject.addProperty("message", e.getMessage());
}
return jsonObject.toString();
}
execute 调用单个返回值存储过程
1、Oracle 数据库中准备存储过程如下：
--表名作为参数，同时指定返回参数，如果表名存在，则返回 1，不存在返回 0
create or replace procedure pro_check_table_by_name(tableName in user_tables.TABLE_NAME%type, ifExists out number) is
begin
--user_tables 是系统定义的视图，可以查看当前用户下的所有表信息，表中的表名区分大小写，而且是大写
select count(1) into ifExists from user_tables where table_name = upper(tableName) ;
end;
-- 数据库中调用存储过程：
declare
tableName varchar2(30) := 'demp'; //被检查的表名
ifExists number; //返回参数
begin
pro_check_table_by_name(tableName,ifExists);
dbms_output.put_line(ifExists);//打印返回值
end;
2、execute(CallableStatementCreator csc, CallableStatementCallback action) 调用存储过程底层就是"JDBC 调用存储过程/函数"，所以写起来完全一样，CallableStatementCreator 中创建 java.sql.CallableStatement，CallableStatementCallback 中进行调用以及获取返回值。
/**
* 检查某个表在数据库中是否已经存在，存在时返回1，否则返回0
* http://localhost:8080/emp/checkTableByName?tableName=emp
*
* @param tableName
* @return
*/
@GetMapping("emp/checkTableByName")
public Integer checkTableByName(@RequestParam String tableName) {
Integer execute = (Integer) jdbcTemplate.execute(new CallableStatementCreator() {
//创建可回调语句，方法里面就是纯 jdbc 创建调用存储的写法
@Override
public CallableStatement createCallableStatement(Connection connection) throws SQLException {
//存储过程调用 sql，通过 java.sql.Connection.prepareCall 获取回调语句
String sql = "call pro_check_table_by_name(?,?)";
CallableStatement callableStatement = connection.prepareCall(sql);
//设置第一个占位符参数值，传入参数。参数索引从1开始
callableStatement.setString(1, tableName);
//注册第二个参数（返回值）的数据类型
callableStatement.registerOutParameter(2, OracleTypes.INTEGER);
return callableStatement;
}
}, new CallableStatementCallback() {
//正式调用存储过程以及处理返回的值.
@Override
public Object doInCallableStatement(CallableStatement callableStatement) throws SQLException {
//执行调用存储过程
callableStatement.execute();
//参数索引从1开始，获取村存储过程的返回值.
return callableStatement.getInt(2);
}
});
return execute;
}
execute 调用返回结果集存储过程
1、数据中准备存储过程如下：
--创建存储过程，用于分页查询
--传入参数：pageNo 查询的页码，pageSize 每页的条数；输出参数：vrows 使用一个引用游标用于接收多条结果集。普通游标无法做到，只能使用引用游标
create or replace procedure pro_query_emp_limit(pageNo in number,pageSize in number,vrows out sys_refcursor) is
begin
--存储过程中只进行打开游标，将 select 查询出的所有数据放置到 vrows 游标中，让调用着进行获取
open vrows for select t.empno,t.ename,t.job,t.mgr,t.hiredate,t.sal,t.comm,t.deptno from (select rownum r,t1.* from emp t1) t
where t.r between ((pageNo-1) * pageSize+1) and pageNo * pageSize;
end;
--数据库中使用引用游标读取上面的存储过程返回的值。下面只是加深理解，和 java 调用无关
declare
vrows sys_refcursor ;--声明引用游标
vrow emp%rowtype; --定义变量接收遍历到的每一行数据
begin
pro_query_emp_limit(5,3,vrows);--调用存储过程
loop
fetch vrows into vrow; -- fetch into 获取游标的值
exit when vrows%notfound; -- 如果没有获取到值，则退出循环
dbms_output.put_line('姓名：'|| vrow.ename || ' 薪水：'|| vrow.sal);
end loop;
end;
2、调用和上面的单挑结果返回基本一致，区别就是将返回的结果改成 ResultSet 结果集：
/**
* 存储过程实现分页查询，传入页码和条数即可进行分页返回
* http://localhost:8080/emp/pageQuery?pageNo=2&pageSize=5
*
* @param pageNo 页码
* @param pageSize 每页显示的条数
* @return
*/
@GetMapping("emp/pageQuery")
public List pageQuery(@RequestParam Integer pageNo, @RequestParam Integer pageSize) {
List execute = (List) jdbcTemplate.execute(new CallableStatementCreator() {
//创建可回调语句，方法里面就是纯 jdbc 创建调用存储的写法
@Override
public CallableStatement createCallableStatement(Connection connection) throws SQLException {
//存储过程调用 sql，通过 java.sql.Connection.prepareCall 获取回调语句,sql 外围可以花括号括起来
String sql = "{call pro_query_emp_limit(?,?,?)}";
CallableStatement callableStatement = connection.prepareCall(sql);
//设置第占位符参数值
callableStatement.setInt(1, pageNo);
callableStatement.setInt(2, pageSize);
//输出参数类型设置为引用游标
callableStatement.registerOutParameter(3, OracleTypes.CURSOR);
return callableStatement;
}
}, new CallableStatementCallback() {
//正式调用存储过程以及处理返回的值.
@Override
public Object doInCallableStatement(CallableStatement callableStatement) throws SQLException {
//存储返回结果
List<Map> resultMapList = new ArrayList(8);
//遍历时临时对象
Map temp;
//执行调用存储过程，将结果转为 java.sql.ResultSet 结果集
callableStatement.execute();
ResultSet resultSet = (ResultSet) callableStatement.getObject(3);
//遍历结果集
while (resultSet.next()) {
temp = new HashMap(8);
//根据字段名称取值
temp.put("empno", resultSet.getInt("empno"));
temp.put("ename", resultSet.getString("ename"));
temp.put("job", resultSet.getString("job"));
temp.put("mgr", resultSet.getInt("mgr"));
temp.put("hiredate", resultSet.getDate("hiredate"));
temp.put("sal", resultSet.getFloat("sal"));
resultMapList.add(temp);
}
return resultMapList;
}
});
return execute;
}
call 方法调用存储过程
1、开篇就已经说过 call 方法专门用于执行存储过程、函数相关语句。call 方法在 execute 的基础上对返回结果进行进一步的封装，只需要创建 CallableStatement 即可，不用再关心结果转换。
2、exexute 的 CallableStatementCallback 回调改为使用 List，其中的每一个 SqlParameter 按顺序对应占位符参数。
SqlParameter 表示存储过程的传入参数，可以不指定参数名称，但是必须指定参数类型
SqlOutParameter 表示存储过程的输出参数，必须指定名称和类型，名称自定义即可，会被作为返回值存放在 map 中
3、下面改用 call 方法来实现上面的功能，使用存储过程检查某个表在数据库中是否已经存在，存在时返回1，否则返回0，使用 call 方法进行调用：
/**
* 存储过程检查某个表在数据库中是否已经存在，存在时返回1，否则返回0，使用 call 方法进行调用
* http://localhost:8080/emp/callCheckTableByName?tableName=emp
*
* @param tableName
* @return
*/
@GetMapping("emp/callCheckTableByName")
@SuppressWarnings("all")
public Map callCheckTableByName(@RequestParam String tableName) {
//SqlParameter 表示存储过程的传入参数，可以不指定参数名称，但是必须指定参数类型
//SqlOutParameter 表示存储过程的输出参数，必须指定名称和类型，名称自定义即可，会被作为返回值存放在 map 中
List sqlParameterList = new ArrayList(4);
sqlParameterList.add(new SqlParameter(OracleTypes.VARCHAR));
sqlParameterList.add(new SqlOutParameter(tableName, OracleTypes.NUMBER));
//call 方法在 execute 的基础上对返回结果进行进一步的封装，只需要创建 CallableStatement
//List 中的每一个 SqlParameter 按顺序对应占位符参数
//返回的 map 包含返回参数
Map call = jdbcTemplate.call(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection connection) throws SQLException {
//存储过程调用 sql，通过 java.sql.Connection.prepareCall 获取回调语句,sql 外围可以花括号括起来
String sql = "{call pro_check_table_by_name(?,?)}";
CallableStatement callableStatement = connection.prepareCall(sql);
//设置第一个占位符参数值，传入参数。参数索引从1开始
callableStatement.setString(1, tableName);
//注册第二个参数（返回值）的数据类型，oracle.jdbc.OracleTypes 中定义了全部的数据类型常量
callableStatement.registerOutParameter(2, OracleTypes.INTEGER);
return callableStatement;
}
}, sqlParameterList);
return call;
}
4、使用 call 方法调用存储过程进行分页查询，使用了 call 之后对于返回的游标就方便多了，不再需要自己一个一个取值了，它会自动进行转换，推荐方式：
/**
* 使用 call 方法调用存储过程进行分页查询，推荐方式
* http://localhost:8080/emp/callPageQuery?pageNo=2&pageSize=5
*
* @param pageNo
* @param pageSize
* @return
*/
@GetMapping("emp/callPageQuery")
@SuppressWarnings("all")
public List<Map> callPageQuery(@RequestParam Integer pageNo, @RequestParam Integer pageSize) {
//设置存储过程参数
//SqlParameter 表示存储过程的传入参数，可以不知道参数名称，但是必须指定参数类型
//SqlOutParameter 表示存储过程的输出参数，必须指定名称和类型，名称自定义即可，会被作为返回值存放在 map 中
List sqlParameterList = new ArrayList(4);
sqlParameterList.add(new SqlParameter(OracleTypes.NUMBER));
sqlParameterList.add(new SqlParameter(OracleTypes.NUMBER));
sqlParameterList.add(new SqlOutParameter("resultSet", OracleTypes.CURSOR));
//使用了 call 之后对于返回的游标就方便多了，不再需要自己一个一个取值了，它会自动进行转换
//call 的 key 会是 resultSet，然后它的值会是一个 List
源码：https://github.com/wangmaoxiong/jdbc_template_app
作者：蚩尤后裔
