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 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>() {
            @Override
            public List doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
                callableStatement.execute();//  
                List list = new ArrayList<>();
                list.add(callableStatement.getString(2));//             
                list.add(callableStatement.getString(3));//             
                return list;//         
            }
        });
    }