Spring JdbcTemplate 调用MySQL存储过程

在MySQL数据库中创建数据表和存储过程：

#创建数据表
#用户表
CREATE TABLE user (
uid int(11) NOT NULL AUTO_INCREMENT,
job_id int(11) DEFAULT NULL,
dept_id int(11) DEFAULT NULL,
PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#部门表
CREATE TABLE dept (
id int(11) NOT NULL AUTO_INCREMENT,
dept_name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#工作表
CREATE TABLE job (
id int(11) NOT NULL AUTO_INCREMENT,
job_name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#创建储存过程
CREATE PROCEDURE getDeptAndJobById (
IN uid INT,
OUT dept_name VARCHAR(255),
OUT job_name VARCHAR(255)
)
BEGIN
SELECT
d.dept_name
FROM
USER u
LEFT JOIN dept d ON d.id = u.dept_id
WHERE
u.uid = uid INTO dept_name;
SELECT
j.job_name
FROM
USER u
LEFT JOIN job j ON j.id = u.job_id
WHERE
u.uid = uid INTO job_name;
END;
#查看所有已创建的储存过程
SHOW PROCEDURE STATUS;
#调用
call getDeptAndJobById(9,@dept_name,@job_name);
SELECT @dept_name,@job_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Spring JdbcTemplate代码，功能是获取指定UID用户所在的部门名和工作岗位名

@Override
public List<String> getDeptAndJobByUid(int uid) {
    return this.getJdbcTemplate().execute(new CallableStatementCreator() {
        @Override
        public CallableStatement createCallableStatement(Connection connection) throws SQLException {
            String storedProc = "{call getDeptAndJobById(?,?,?)}";// 调用SQL存储过程
            CallableStatement cs = connection.prepareCall(storedProc);
            cs.setInt(1, uid);// 设置输入参数的值
            cs.registerOutParameter(2, Types.VARCHAR);//注册输出值类型
            cs.registerOutParameter(3, Types.VARCHAR);//注册输出值类型
            return cs;
        }
    }, new CallableStatementCallback<List<String>>() {
        @Override
        public List<String> doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
            callableStatement.execute();//执行
            List<String> list = new ArrayList<>();
            list.add(callableStatement.getString(2));//根据注册的位置获取到返回值
            list.add(callableStatement.getString(3));//根据注册的位置获取到返回值
            return list;// 获取输出参数的值
        }
    });
}

---

作者：景向后山
来源：CSDN
原文：https://blog.csdn.net/cx118118/article/details/77571575
版权声明：本文为博主原创文章，转载请附上博文链接！