mybatis存储过程实现三、四，插入、删除用户信息和角色关联信息

1. 首先看一下数据库要完成的存储过程（我用的数据库是DBeaver，这里也给出Navicat for Mysql中的存储过程实现，两个不同软件代码实现不太一样）

Navicat for Mysql中存储过程代码：

插入用户信息和角色关联信息：

drop procedure if exists `insert_user_and_roles`;

create procedure `insert_user_and_roles`(
    OUT userId BIGINT,
    IN userName VARCHAR(50),
    IN userPassword VARCHAR(50),
    IN userEmail VARCHAR(50),
    IN userInfo TEXT,
    IN headImg BLOB,
    OUT createTime DATETIME,
    IN roleIds VARCHAR(200)
)
BEGIN
-- 设置当前时间
SET createTime = NOW();
-- 插入时间
INSERT INTO sys_user 
(user_name,user_password,user_email,user_info,head_img,create_time)
VALUES
(userName,userPassword,userEmail,userInfo,headImg,createTime);
-- 获取自增主键
SELECT LAST_INSERT_ID() INTO userId;
-- 保存用户和角色关系数据
SET roleIds = CONCAT(',',roleIds,',');
INSERT INTO sys_user_role (user_id,role_id)
SELECT userId ,id from sys_role 
where INSTR(roleIds,CONCAT(',',id,','))>0;
END

删除用户信息和角色关联信息： 

-- 第四个存储过程
-- 删除用户信息和角色关联信息
drop procedure if exists `delete_user_by_id`;

create procedure `delete_user_by_id`(IN userId BIGINT)
BEGIN
DELETE FROM sys_user_role where user_id = userId;
DELETE FROM sys_user  where id = userId;
END

DBeaver中存储过程代码：

插入用户信息和角色关联信息：

create procedure `insert_user_and_roles`(
    OUT userId BIGINT,
    IN userName VARCHAR(50),
    IN userPassword VARCHAR(50),
    IN userEmail VARCHAR(50),
    IN userInfo TEXT,
    IN headImg BLOB,
    OUT createTime DATETIME,
    IN roleIds VARCHAR(200)
)
BEGIN
-- 设置当前时间
SET createTime = NOW();
-- 插入时间
INSERT INTO sys_user 
(user_name,user_password,user_email,user_info,head_img,create_time)
VALUES
(userName,userPassword,userEmail,userInfo,headImg,createTime);
-- 获取自增主键
SELECT LAST_INSERT_ID() INTO userId;
-- 保存用户和角色关系数据
SET roleIds = CONCAT(',',roleIds,',');
INSERT INTO sys_user_role (user_id,role_id)
SELECT userId ,id from sys_role 
where INSTR(roleIds,CONCAT(',',id,','))>0;
END

drop procedure if exists `insert_user_and_roles`;

删除用户信息和角色关联信息：

create procedure `delete_user_by_id`(IN userId BIGINT)
BEGIN
DELETE FROM sys_user_role where user_id = userId;
DELETE FROM sys_user  where id = userId;
END

-- 第四个存储过程
-- 删除用户信息和角色关联信息
drop procedure if exists `delete_user_by_id`;

注意：这里务必注意，先执行第一部分代码在执行第二部分的代码，不然执行过程会出如下错误：

执行完毕后会出现相应的存储过程：

              

 这样，第一步基础就打好了。（千万注意，在实现mybatis之前必须要实现此存储过程，不然就会出现如下错误：### Cause: java.sql.SQLException: Parameter number 2 is not an OUT parameter，这个问题折磨了我两天，头秃，难过到窒息，最后实现了此存储过程后报错才消失）

第一步基础打好了之后，接下来进行java代码配置。

2. UserMapper.xml中代码：

<insert id="insertUserAndRoles" statementType="CALLABLE">
        {
        call insert_user_and_roles
        (
        #{user.id, mode = OUT, jdbcType = BIGINT},
        #{user.userName , mode = IN},
        #{user.userPassword , mode = IN},
        #{user.userEmail , mode = IN},
        #{user.userInfo , mode = IN},
        #{user.headImg , mode = IN,jdbcType = BLOB},
        #{user.createTime , mode = OUT,jdbcType = TIMESTAMP},
        #{roleIds,mode = IN}
        )
        }
    </insert>

<delete id="deleteUserById" statementType="CALLABLE">
        {
        call delete_user_by_id
        (
        #{userId, mode = IN}
        )
        }
    </delete>

注意：这里insert标签中参数要和存储过程中参数顺序保持一致，不然出现报错的情况。；例如我修改代码如下：

<insert id="insertUserAndRoles" statementType="CALLABLE">
        {
        call insert_user_and_roles
        (
        #{user.id, mode = OUT, jdbcType = BIGINT},
        #{user.createTime , mode = OUT,jdbcType = TIMESTAMP},
        #{user.userName , mode = IN},
        #{user.userPassword , mode = IN},
        #{user.userEmail , mode = IN},
        #{user.userInfo , mode = IN},
        #{user.headImg , mode = IN,jdbcType = BLOB},
        #{roleIds,mode = IN}
        )
        }
    </insert>

那么执行后会出现下面报错：

 

 

 3. 接口如下：

/*
    * 保存用户信息和角色关联信息
    * */
    int insertUserAndRoles(@Param("user")SysUser user,@Param("roleIds")String roleIds);

    /*
    * 根据用户 id 删除用户和用户的角色信息
    * */
    int deleteUserById(Long id);

4. 测试代码如下：

@Test
    public  void testInsertUserAndRoles(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser user = new SysUser();
            user.setUserName("test2");
            user.setUserPassword("123456");
            user.setUserEmail("ccb.com");
            user.setUserInfo("test2 info");
            user.setHeadImg(new byte[]{1,2,3});
            //插入用户信息和角色关联信息
            userMapper.insertUserAndRoles(user,"1,2");
            sqlSession.commit();
            userMapper.deleteUserById(user.getId());
            Assert.assertNotNull(user.getId());
            Assert.assertNotNull(user.getCreateTime());
            sqlSession.commit();
        }finally {
            sqlSession.close();
        }
    }

测试结果如下：

 

  至此，第s三、四个存储过程结束。