06. jdbc CallableStatement 调用存储过程与函数

jdbc 系列文章列表, 请查看目录: 《jdbc学习笔记》

1. jdbc 调用存储过程

jdbc 调用存储过程与函数, 均是通过CallableStatement 类执行sql的. 通常而已, 存储过程是不允许有返回值的, 而函数是允许有返回值的.

1.1 CallableStatement sql

jdbc调用存储过程与函数的sql写法有区别, 需要额外注意:

存储过程: { call $produceName([?,…]) }函数: { ?= call $functionName([?,…]) }

1.2 相关API

setXXX(int index, Object value): 设置存储过程入参或函数入参execute(): 执行sqlregisterOutParameter(int index, int type): 设置函数返回值参数, type 为java.sql.Type 中定义的常量值getObject(int index, Class clz): 获取函数返回值

2. 创建存储过程与函数

由于本篇文章重点不在与如何创建存储过程与函数, 所以笔者就通过创建简单的存储过程与函数, 来实现通过jdbc调用存储过程与函数的功能.

2.1 创建存储过程

创建一个用于创建动态表的存储过程, 传入表后缀(年份, 整型), 然后动态创建表t_user_{year}创建一个将用户名转为大写的函数 -- 切换数据库 use `learn-jdbc`; -- 创建存储过程 create procedure newUser(IN year INT) BEGIN SET @tname = CONCAT('t_user_',year); SET @field_id = '(id int NOT NULL AUTO_INCREMENT ,'; SET @field_name = ' name varchar(120) ,'; SET @field_age = ' password varchar(120) ,'; SET @pkey = ' PRIMARY KEY (`id`))'; SET @encod = ' ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;'; SET @sql_text =CONCAT('create table ', @tname, @field_id, @field_name, @field_age, @pkey, @encod); PREPARE stmt FROM@sql_text; EXECUTE stmt; END; -- 创建函数 create function queryUpperName(id INT) returns varchar(20) return (select upper(name) from t_user where id = id);

2.2 查询存储过程列表

-- 查询存储过程列表 select t.name, t.db from mysql.proc t where t.type = 'PROCEDURE' and t.db = 'learn-jdbc'; -- 查询函数列表 select `name` from mysql.proc where `type` = 'FUNCTION' and t.db = 'learn-jdbc';

2.3 调用存储过程与函数

调用存储过程和函数的方式不一致, 需要注意.

-- 调用存储过程 call newUser(2019); -- 调用函数 select queryUpperName(1);

3. jdbc 调用存储过程与函数

3.1 调用存储过程

@Test public void test_producer() throws SQLException { //1. 获取数据库连接 Connection connection = DbConnUtil.getConnection(); //2. 拼接调用存储过程sql String sql = "{call newUser(?)}"; //3. 获取CallableStatement 对象 CallableStatement callableStatement = connection.prepareCall(sql); //4. 设置参数 callableStatement.setInt(1, 2020); //5. 执行调用存储过程sql callableStatement.execute(); //6. 关闭资源 DbConnUtil.release(callableStatement, connection); }

3.2 调用函数

调用函数过程相关复杂, 需要设置返回值参数和解析函数返回值结果.

@Test public void test_function() throws Exception{ //1. 获取数据库连接 Connection connection = DbConnUtil.getConnection(); //2. 拼接调用存储过程sql String sql = "{?= call queryUpperName(?)}"; //3. 获取CallableStatement 对象 CallableStatement callableStatement = connection.prepareCall(sql); //4. 设置返回值参数 callableStatement.registerOutParameter(1, Types.VARCHAR); //5. 设置函数入参 callableStatement.setInt(2, 1); //6. 执行调用存储过程sql callableStatement.execute(); //7. 通过getXXX 获取函数返回值 String name = callableStatement.getObject(1, String.class); System.out.println(name); //8. 关闭资源 DbConnUtil.release(callableStatement, connection); }