10.3 MyBatis调用存储过程 10.3.5删除数据

发表于 更新于 分类于 ， ， Waline： 本文字数： 1.7k 阅读时长 ≈ 2 分钟

10.3 MyBatis调用存储过程 10.3.5删除数据

在之前创建的mybatis数据库中创建一个删除tb_user表数据的存储过程。SQL脚本如下所示:

1
2
3
4
5
6
7
8
drop procedure if exists delete_user_by_id;
delimiter $$
create procedure delete_user_by_id(IN p_id integer)
begin
    delete from tb_user where id=p_id;
end
$$
delimiter ;

mapper.xml映射

1
2
3
4
5
<delete
    id="deleteUserById"
    parameterType="int"
    statementType="CALLABLE"> {call delete_user_by_id(#{id,mode=IN})}
</delete>

mapper接口方法

1
void deleteUserById(Integer id);

测试类

/MyProcedureTest/src/test/DeleteUserByIdTest.java
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
package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;

public class DeleteUserByIdTest {
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        // 1.获取SqlSession实例
        sqlSession = SqlSessionFratoryTools.getSqlSession();
        try
        {
            // 2.获取Mapper接口的代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int id = 1;
            User user = userMapper.selectUserById(id);
            if (user != null)
            {
                System.out.println("    删除:" + user);
                userMapper.deleteUserById(id);
            }
            // 5.提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 6.出错回滚
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 7.关闭会话
            if (sqlSession != null)
            {
                sqlSession.close();
            }
        }
    }
}

运行结果

DEBUG [main] ==>  Preparing: {call select_user_by_id(?)} 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
DEBUG [main] <==    Updates: 0
    删除:User [id=1, name=小明, sex=男, age=21]
DEBUG [main] ==>  Preparing: {call delete_user_by_id(?)} 
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==    Updates: 1

执行测试类时,先调用”select_user_by_id“存储过程查询id为1的User数据,如果找到该数据,则调用”delete_user_by_id“存储过程将该User数据删除。