Mybatis-Plus的删除和逻辑删除

阳光穿透心脏的1/2处 2021-07-24 21:31 686阅读 0赞

**目录**

一 删除

1 根据id删除记录

2 批量删除

3 简单条件删除

二 逻辑删除

1 物理删除和逻辑删除

2 逻辑删除实现流程

2.1 数据库修改

2.2 实体类修改

2.3 配置（可选）

2.4 测试

2.5 测试逻辑删除后的查询

--------------------

# 一 删除 #

## 1 根据id删除记录   ##

/**
         * 功能描述：根据Id进行删除
         *
         * @author cakin
         * @date 2020/11/14
         */
        @Test
        public void testDeleteById() {
            int result = userMapper.deleteById(4L);
            System.out.println("删除了" + result + "行");
        }

删除结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a2b2322] was not registered for synchronization because synchronization is not active
    2020-11-14 20:25:03.851  INFO 30440 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 20:25:04.189  INFO 30440 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@987805552 wrapping com.mysql.cj.jdbc.ConnectionImpl@55e3d6c3] will not be managed by Spring
    ==>  Preparing: DELETE FROM user WHERE id=? 
    ==> Parameters: 4(Long)
    <==    Updates: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a2b2322]
    删除了1行

## 2 批量删除 ##

/**
         * 功能描述：根据Id列表进行批量删除
         *
         * @author cakin
         * @date 2020/11/14
         */
        @Test
        public void testDeleteBatchIds() {
            int result = userMapper.deleteBatchIds(Arrays.asList(1, 2, 3));
            System.out.println("删除了" + result + "行");
        }

测试结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3047254d] was not registered for synchronization because synchronization is not active
    2020-11-14 20:26:51.139  INFO 31680 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 20:26:51.494  INFO 31680 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@477463877 wrapping com.mysql.cj.jdbc.ConnectionImpl@117bcfdc] will not be managed by Spring
    ==>  Preparing: DELETE FROM user WHERE id IN ( ? , ? , ? ) 
    ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
    <==    Updates: 3
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3047254d]
    删除了3行

## 3 简单条件删除 ##

/**
         * 功能描述：通过map进行简单条件删除
         *
         * @author cakin
         * @date 2020/11/14
         */
        @Test
        public void testDeleteByMap() {
            // 以map的形式封装删除条件
            HashMap<String, Object> map = new HashMap<>();
            map.put("name", "cakin");
            map.put("age", 18);
            int result = userMapper.deleteByMap(map);
            System.out.println("删除了" + result + "行");
        }

删除结果

# 二 逻辑删除 #

## 1 物理删除和逻辑删除 ##

*  物理删除：真实删除，将对应数据从数据库中删除，之后查询不到此条被删除数据。
 *  逻辑删除：假删除，将对应数据中代表是否被删除字段状态修改为"被删除状态"，之后在数据库中仍旧能看到此条数据记录。

逻辑删除的使用场景：

*  可以进行数据恢复。
 *  有关联数据，不便删除。

## 2 逻辑删除实现流程 ##

### 2.1 数据库修改 ###

# 添加 deleted字段
    ALTER TABLE `user` ADD COLUMN `deleted` boolean DEFAULT false

### 2.2 实体类修改 ###

添加deleted 字段，并加上 @TableLogic 注解

// 该注解用于逻辑删除
        @TableLogic
        private Integer deleted;

### 2.3 配置（可选） ###

application.properties 加入以下配置，此为默认值，如果你的默认值和mp默认的一样,该配置可无。

mybatis-plus.global-config.db-config.logic-delete-value=1
    mybatis-plus.global-config.db-config.logic-not-delete-value=0

### 2.4 测试 ###

/**
         * 功能描述：逻辑删除
         *
         * @author cakin
         * @date 2020/11/14
         */
        @Test
        public void testLogicDeleteById() {
            int result = userMapper.deleteById(1L);
            System.out.println("删除了" + result + "行");
        }

测试结果

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1b29d52b] was not registered for synchronization because synchronization is not active
    2020-11-14 20:34:49.906  INFO 20396 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 20:34:50.309  INFO 20396 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@1653153736 wrapping com.mysql.cj.jdbc.ConnectionImpl@367b22e5] will not be managed by Spring
    ==>  Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0 
    ==> Parameters: 1(Long)
    <==    Updates: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1b29d52b]
    删除了1行

*  测试后发现，数据并没有被删除，deleted字段的值由0变成了1
 *  测试后分析打印的sql语句，是一条update

> 注意：被删除前，数据的deleted 字段的值必须是 0，才能被选取出来执行逻辑删除的操作

### 2.5 测试逻辑删除后的查询 ###

MyBatis Plus中查询操作也会自动添加逻辑删除字段的判断

/**
         * 功能描述：测试逻辑删除后的查询
         *
         * @author cakin
         * @date 2020/11/14
         */
        @Test
        void testLogicSelectList() {
            List<User> users = userMapper.selectList(null);
            users.forEach(System.out::println);
        }

测试结果如下

Creating a new SqlSession
    SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47547132] was not registered for synchronization because synchronization is not active
    2020-11-14 20:36:50.699  INFO 33656 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
    2020-11-14 20:36:50.998  INFO 33656 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
    JDBC Connection [HikariProxyConnection@52354732 wrapping com.mysql.cj.jdbc.ConnectionImpl@67d86804] will not be managed by Spring
    ==>  Preparing: SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0 
    ==> Parameters: 
    <==    Columns: id, name, age, email, create_time, update_time, deleted
    <==        Row: 2, Jack, 20, test2@baomidou.com, null, null, 0
    <==        Row: 3, Tom, 28, test3@baomidou.com, null, null, 0
    <==        Row: 4, Sandy, 21, test4@baomidou.com, null, null, 0
    <==        Row: 5, Billie, 24, test5@baomidou.com, null, null, 0
    <==        Row: 1244892079889334273, Annie, 28, 55317332@qq.com, 2020-03-31 15:39:35, 2020-11-14 16:38:00, 0
    <==        Row: 1327500339715899394, Helen, 18, 55317332@qq.com, 2020-11-14 14:35:19, 2020-11-14 14:35:19, 0
    <==        Row: 1327500521547415554, cakin24, 18, 798103175@qq.com, 2020-11-14 14:36:03, 2020-11-14 14:36:03, 0
    <==      Total: 7
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@47547132]
    User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null, deleted=0)
    User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null, deleted=0)
    User(id=4, name=Sandy, age=21, email=test4@baomidou.com, createTime=null, updateTime=null, deleted=0)
    User(id=5, name=Billie, age=24, email=test5@baomidou.com, createTime=null, updateTime=null, deleted=0)
    User(id=1244892079889334273, name=Annie, age=28, email=55317332@qq.com, createTime=Tue Mar 31 15:39:35 CST 2020, updateTime=Sat Nov 14 16:38:00 CST 2020, deleted=0)
    User(id=1327500339715899394, name=Helen, age=18, email=55317332@qq.com, createTime=Sat Nov 14 14:35:19 CST 2020, updateTime=Sat Nov 14 14:35:19 CST 2020, deleted=0)
    User(id=1327500521547415554, name=cakin24, age=18, email=798103175@qq.com, createTime=Sat Nov 14 14:36:03 CST 2020, updateTime=Sat Nov 14 14:36:03 CST 2020, deleted=0)
    
    2020-11-14 20:36:51.175  INFO 33656 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
    2020-11-14 20:36:51.194  INFO 33656 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
    
    Process finished with exit code 0