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;

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;// 获取输出参数的值
            }
        });
    }