reference resources: MyBatis-Plus

1, Quick start

1.0 preparation

reference resources MyBatis-Plus Create table statement in!

1.1 add dependency & profile

During springboot consolidation:

<!-- springboot Unit test dependencies for -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<!-- mybatis-plus Start dependence -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3.4</version>
</dependency>
<!-- mysql rely on -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

Configuration file: application yml

#Mybatis plus configuration
mybatis-plus:
  global-config:
    db-config:
      #Global primary key generation policy
      id-type: assign_id #This value is the default value (the snowflake algorithm generates the primary key id value)
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #Log output

[note]

1. Don't forget to add the @ MapperScan annotation in the Spring Boot startup class and scan the Mapper folder;
2. Add an annotation on the user entity class: @ TableName("t_user") maps t in the database_ User table;

1.3 UserMapper interface

UserMapper interface shall implement BaseMapper interface;

public interface UserMapper extends BaseMapper<User> {
	//After Mapper inherits this interface, there is no need to write Mapper XML file, you can get CRUD function
}

1.4 Test Category & test effect

@SpringBootTest
class MybatisPlusDemoApplicationTests {

	//The injected Mapper interface custom implements the BaseMapper interface
    @Autowired
    private UserMapper userMapper;

    @Test
    void baseMapperTest1() {
        int i = userMapper.insert(new User("zhangsan", 22, "123@qq.com"));
        System.out.println(i);
    }

}

When self increment is not set for the primary key in the database, and the primary key id is not manually set when adding records, mybatis plus will use the snowflake algorithm to generate the primary key id for records by default; as follows

2, Common notes

2.1 notes

reference resources: Mybatis plus annotation

2.2 expansion (global configuration)

Global configuration: to affect the configuration of all entities, you can set global primary key configuration;

#Mybatis plus configuration
mybatis-plus:
  global-config:
    db-config:
      #Global primary key generation policy
      id-type: assign_id #This value is the default value (the snowflake algorithm generates the primary key id value)

3, CRUD interface

3.1 Service CRUD interface

reference resources: Service CRUD interface

1) Create UserService interface

public interface UserService extends IService<User> {

	// IService: a top-level Service that internally calls methods in BaseMapper to implement CRUD and other operations

}

2) Create an implementation class for UserService

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    // ServiceImpl: IService implementation class (generic: M is mapper object, T is entity)

}

3) Perform unit tests

@SpringBootTest
class MybatisPlusDemoApplicationTests {

	//Bean of implementation class injected into IService
    @Autowired
    private UserService userService;

    @Test
    void iServiceTest1(){
        User user = userService.getById(2L);
        System.out.println(user);
    }

}

Console operation effect:

1. If we extend the method in our own XXXMapper, we should also extend a method in our own XxxService to call the method in Mapper. When calling, we can call it directly with the basemapper object. (UserMapper, the basemapper implementation class, is injected into the ServiceImpl implementation class)

//The source code of ServiceImpl is as follows
public class ServiceImpl<M extends BaseMapper<T>, T> implements IService<T> {
	//Omit some code
    @Autowired
    protected M baseMapper;
    //Omit some code
}

3.2 Mapper CRUD interface

reference resources: Mapper CRUD interface

4, Expand function

4.1 automatic filling (FieldFill)

1) Add two attributes to the database table;

2) Add the corresponding attribute value to the entity class;

@TableName("t_user")
public class User {

    @TableId
    private Long id;
    private String name;
    private Integer age;
    private String email;
    
    //Fill in the field when inserting (if the attribute value does not correspond to the field value, add the value attribute for mapping; see the bottom prompt!)
    @TableField(value = "create_time",fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    
    //Fill in fields when inserting and updating
    @TableField(value = "update_time",fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;

	//Omit the constructor, get and set methods
}

3) Implement meta object processor interface - > create handler package and create MyMetaObjectHandler class; (don't forget the @ Component annotation)

/**
 * @Description The meta object field fills the implementation class of the controller abstract class to realize the automatic writing of public fields
 * @Author cb
 * @Date 2021-12-23 21:31
 **/
@Component	//Don't forget to inject the Handler into the container!!!
public class MyMetaObjectHandler implements MetaObjectHandler {
	//log4j log
    Logger log = LoggerFactory.getLogger(MyMetaObjectHandler.class);

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill ...");
        //Fill in the value at the time of insertion according to the attribute marked FieldFill
        this.strictInsertFill(metaObject,"createTime", LocalDateTime.class,LocalDateTime.now());
        this.strictInsertFill(metaObject,"updateTime",LocalDateTime.class,LocalDateTime.now());
        log.info("end insert fill ...");
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill ...");
        //Fill in the value when modifying according to the attribute marked FieldFill
        this.strictUpdateFill(metaObject,"updateTime",LocalDateTime.class,LocalDateTime.now());
        log.info("end update fill ...");
    }

}

4) Add test & modify test;

//New test
@Test
void baseMapperTest1() {
    int i = userMapper.insert(new User("chief counsellor of Liu Bang", 26, "14bx6@qq.com"));
    System.out.println(i);
}

//Modify test
@Test
void baseMapperTest2() {
    User user = new User("Liang Liang Zhang",23,"cc@qq.com");
    user.setId(1474013526022393858L);
    int i = userMapper.updateById(user);
    System.out.println(i);
}

Note: mybatis plus will automatically convert the underline naming style in the database into the hump naming style in the entity class; Therefore, if the naming is standard, the value attribute of the @ TableField annotation may not be written.

4.2 logical deletion (@ TableLogic)

Usage scenario: data recovery can be performed;

• Physical delete: real delete, delete the corresponding data from the database, and then query the deleted data;
• Logical deletion: false deletion. Change the status of the deleted field in the data table to "deleted status", and then you can still see this data record in the database;

Steps:
1) Create a logical deletion status column in the database;

2) Add and delete logical attributes in the entity class;
Method 1: add @ TableLogic annotation; (from version 3.3.0 onwards: choose between mode 1 and mode 2)

//If the global configuration is deleted logically in the configuration file, this annotation may not be written;
//Logical deleted value (default is 1), logical undeleted value (default is 0)
@TableLogic
@TableField(value = "is_deleted")
private Integer deleted;

Mode 2: global configuration;

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted # Global logically deleted entity field name
      logic-delete-value: 1 # Logical deleted value (default is 1)
      logic-not-delete-value: 0 # Logical undeleted value (0 by default)

3) Delete test: the delete function is changed to the update function;

-- Actually implemented SQL
UPDATE t_user SET is_deleted=1 WHERE id=? AND is_deleted=0

4) Query test: logically deleted data will not be queried by default;

//Query test
@Test
void selectAll(){
	//QueryWrapper: an abstract class that encapsulates query conditions
    List<User> userList = userService.list(new QueryWrapper<User>().select("id","name","age"));
    for (User user : userList) {
        System.out.println(user);
    }
}

-- Actually implemented SQL
SELECT id,name,age FROM t_user WHERE is_deleted=0

5, Plug in

5.1 paging plug-in

5.1. 1 add configuration

/**
 * @Description MyBatis-Plus Configuration class for
 * @Author cb
 * @Date 2021-12-23 23:16
 **/
@SpringBootConfiguration
@MapperScan("com.ccbx.mapper") //Annotations in the main class can be moved here
public class MyBatisPlusConfig {

    //MyBatisPlus interceptor
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        //Add an internal interceptor for paging
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

}

5.1. 2 test

Encapsulating a paging parameter object of type Page, then calling the selectPage method in the BaseMapepr interface in the Service class, or directly calling the page method of the IService interface.

@Test
void testPage1(){
    //Current page and number of entries per page
    long current = 2,size = 5;
    Page<User> page = new Page<>(current,size);
    //Packaging query criteria
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //Query three fields and age greater than 22
    wrapper.select("id","name","age").gt("age",22);
    //As like as two peas, the Page object returned here is exactly the same as the Page above, but it only improves the value of the attribute in the Page above.
    userService.page(page, wrapper);
    System.out.println("Total records:"+page.getTotal());
    System.out.println("Data collection of the current page:");
    List<User> userList = page.getRecords();
    for (User user : userList) {
        System.out.println(user);
    }
}

SQL statements printed in the log:

-- page.getTotal()Method called SQL sentence
SELECT COUNT(*) AS total FROM t_user WHERE is_deleted = 0 AND (age > ?)

-- page.getRecords()Method called SQL sentence
SELECT id,name,age FROM t_user WHERE is_deleted=0 AND (age > ?) LIMIT ?,?

5.1. 3 user defined paging query with conditions

Method 1 (simple query): without writing SQL statements, use QueryWrapper to encapsulate query conditions; for example:

//Packaging query criteria
QueryWrapper<User> wrapper = new QueryWrapper<>();
//Query three fields and age greater than 22
wrapper.select("id","name","age").gt("age",22);

Method 2 (complex query): write the SQL into the SQL mapping file and splice the query conditions in the SQL;
Steps:
1) Add global configuration

mybatis-plus:
  # Load the MyBatis mapping file under the classpath
  mapper-locations: classpath:mappers/*Mapper.xml

2) UserMapper interface

public interface UserMapper extends BaseMapper<User> {

    //User defined paging query (entity class, VO class, Map set, etc. can be used when encapsulating query criteria here)
    Page<User> queryByPage(Page<User> page, @Param("conditionMap") Map<String,Object> cmap);

}

3)UserMapper.xml Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ccbx.mapper.UserMapper">

	<!--Custom query criteria SQL-->
    <select id="queryByPage" resultType="com.ccbx.pojo.User">
        select id,name,age from t_user
        <where>
            <if test="null != conditionMap.name and '' != conditionMap.name">
                name like #{conditionMap.name}
            </if>
            <if test="null != conditionMap.age">
                and age &gt; #{conditionMap.age}
            </if>
        </where>
    </select>

</mapper>    

4) UserService interface

public interface UserService extends IService<User> {
	//Custom paging condition query method
    public Page<User> myQueryByPage();
}

5) UserServiceImpl implementation class

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    
    //Custom paging condition query method
    public Page<User> myQueryByPage(){
        long current = 1, size = 5;
        Page<User> page = new Page<>(current, size);
        Map<String,Object> map = new HashMap<>();
        map.put("name","%J%");
        map.put("age",18);
        baseMapper.queryByPage(page,map);
        return page;
    }
    
}

6) Test method:

@Test
void myPageTest(){
    Page<User> page = userService.myQueryByPage();
    List<User> userList = page.getRecords();
    for (User user : userList) {
        System.out.println(user);
    }
}

5.1. 4 Summary: how to write paging query?

1. Without the query condition, the page (Page object, null) in Service is directly invoked in Controller.

2. With query conditions, but conditional encapsulation is not complex, you can call the selectPage method in the BaseMapepr interface in the Service class, or the page method that calls the IService interface directly.

3. With complex query conditions, add a new method in Mapper interface. The method must contain Page object parameters and map set of query condition parameters to be encapsulated (entity class and Vo class are OK); for example, the following code; then define the corresponding sql in Mapper.xml;

Page<User> queryByPage(Page<User> page, @Param("conditionMap") Map<String,Object> cmap);

5.2 code generator

reference resources: Code generator (New)