It will be the top of the mountain

| @Author: TTODS

MyBatisPlus framework series article directory:

• Spring boot integrates MybatisPlus

• CRUD of MyBatisPlus Mapper layer

• CRUD of MyBatisPlus Service layer

• Paging function provided by MyBatisPlus

• Field filling of MyBatisPlus

• Logical deletion of MyBatisPlus (current)

• Optimistic lock of MyBatisPlus

• [code generator for MyBatisPlus (recently released)]

preface

Logical deletion refers to the use of a special field to indicate whether a record in the database table has been deleted (or exists)

For example, add an is for each record in the database table_ Delete field, when is_ When delete is 1, it indicates that the record has been deleted_ When delete is 0, it indicates that the field is still valid

The opposite of logical deletion is physical deletion, which refers to deleting records directly from the database

This paper introduces how to use MybatisPlus to realize logical deletion function

Create a simple test table tag

CREATE TABLE `tag`  (
  `id` int(11) AUTO_INCREMENT NOT NULL,
  `name` varchar(255),
  `is_delete` tinyint(1) default 0,
    PRIMARY KEY (`id`)
);

The table consists of three fields:

1. id number
2. Name name
3. is_delete flag

Then insert some data for testing in the table in advance

INSERT INTO `tag` VALUES (1, 'Label 1', 0);
INSERT INTO `tag` VALUES (2, 'Label 2', 0);
INSERT INTO `tag` VALUES (3, 'Label 3', 0);
INSERT INTO `tag` VALUES (4, 'Label 4', 0);
INSERT INTO `tag` VALUES (5, 'Label 5', 0);
INSERT INTO `tag` VALUES (6, 'Label 6', 0);
INSERT INTO `tag` VALUES (7, 'Label 7', 0);
INSERT INTO `tag` VALUES (8, 'Label 8', 0);
INSERT INTO `tag` VALUES (9, 'Label 9', 0);
INSERT INTO `tag` VALUES (10, 'Label 10', 0);
INSERT INTO `tag` VALUES (11, 'Label 11', 0);
INSERT INTO `tag` VALUES (12, 'Label 12', 0);
INSERT INTO `tag` VALUES (13, 'Label 13', 0);
INSERT INTO `tag` VALUES (14, 'Label 14', 0);
INSERT INTO `tag` VALUES (15, 'Label 15', 0);
INSERT INTO `tag` VALUES (16, 'Label 16', 0);
INSERT INTO `tag` VALUES (17, 'Label 17', 0);
INSERT INTO `tag` VALUES (18, 'Label 18', 0);
INSERT INTO `tag` VALUES (19, 'Label 19', 0);
INSERT INTO `tag` VALUES (20, 'Label 20', 0);

After the operation, the tag table is as follows:

Write entity class Tag

package com.example.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import lombok.Data;

@Data
public class Tag {
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String name;
    // @TableLogic is used to label the field indicating logical deletion. Value is the value indicating undeleted, and delval is the value indicating deleted
    @TableLogic(value = "0",delval = "1")
    private Integer isDelete;
}

@TableLogic is used to label the fields indicating logical deletion. Value is the value not deleted, and delval is the value deleted

Write TagMapper

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.pojo.Tag;

public interface TagMapper extends BaseMapper<Tag> {
}

Write a test class to test

package com.example;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.UpdateChainWrapper;
import com.example.mapper.TagMapper;
import com.example.pojo.Tag;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Arrays;
import java.util.List;

@SpringBootTest
@RunWith(SpringRunner.class)
public class LogicDeleteTests {
    @Resource
    TagMapper mapper;
    
}

Test delete

@Test
public void testDelete() {
    // Delete records with id 1, 2 and 5
    mapper.deleteBatchIds(Arrays.asList(1,2,5));
}

Generated sql code:

==>  Preparing: UPDATE tag SET is_delete=1 WHERE id IN ( ? , ? , ? ) AND is_delete=0
==> Parameters: 1(Integer), 2(Integer), 5(Integer)
<==    Updates: 3

Mybatis plus does not execute the delete statement, but uses the update statement to update the is of the corresponding record_ Change the delete field to 1

Test query

@Test
public void testSelect(){
    // Query records with id less than 10
    QueryWrapper<Tag> wrapper = new QueryWrapper<Tag>().lt("id", 10);
    List<Tag> tags = mapper.selectList(wrapper);
    tags.forEach(System.out::println);
}

Generated sql code and program output:

==>  Preparing: SELECT id,name,is_delete FROM tag WHERE is_delete=0 AND (id < ?)
==> Parameters: 10(Integer)
<==      Total: 6
Tag(id=3, name=Label 3, isDelete=0)
Tag(id=4, name=Label 4, isDelete=0)
Tag(id=6, name=Label 6, isDelete=0)
Tag(id=7, name=Label 7, isDelete=0)
Tag(id=8, name=Label 8, isDelete=0)
Tag(id=9, name=Label 9, isDelete=0)

Mybatis plus automatically splices where is after the select statement_ Delete = 0 to avoid finding deleted records

Test update

@Test
public void testUpdate(){
    // Change the tag name with id less than 11 to the original name and splice '< update >'
    UpdateChainWrapper<Tag> updateChain = new UpdateChainWrapper<>(mapper);
    updateChain.setSql("`name`=concat(`name`,'<update>')").lt("id",11).update();
}

Generated sql code:

==>  Preparing: UPDATE tag SET `name`=concat(`name`,'<update>') WHERE is_delete=0 AND (id < ?)
==> Parameters: 11(Integer)
<==    Updates: 7

Similar to query statements, mybatis plus automatically splices where is after the update statement_ Delete = 0 to avoid updating deleted records

Test insertion

@Test
public void testInsert(){
    Tag tag = new Tag();
    tag.setName("Label 21");
    mapper.insert(tag);
}

Generated sql statement

==>  Preparing: INSERT INTO tag ( name ) VALUES ( ? )
==> Parameters: Label 21(String)
<==    Updates: 1

Mybatis plus does not insert is in the insert statement_ Delete is set to 0, but we can set the default value for this field in the database

Configure logical deletion in mybatis plus configuration item

If you configure logical deletion in the application.yml configuration file, you don't need to use @ TableLogic annotation in each entity class

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: isDelete
      logic-delete-value: 1
      logic-not-delete-value: 0

Previous: Field filling of MyBatisPlus
Next: Optimistic lock of MyBatisPlus

- THE END -