An article takes you to master the MyBatis simplified framework - MyBatisPlus

An article takes you to master the MyBatis simplified framework - MyBatisPlus

We have learned the mainstream frameworks currently required for development in previous articles

Similar to the SpringBoot framework we learned to simplify Spring development, our Chinese people have also developed a MyBatisPlus framework to simplify MyBatis development

Let's master the content of MyBatisPlus step by step~

About MyBatisPlus

First, let's briefly introduce MyBatisPlus:

  • MyBatisPlus (MP) is an enhanced tool based on the MyBatis framework, designed to simplify development and improve efficiency

MyBatisPlus development has three development methods:

  • Using MyBatisPlus based on MyBatis
  • Using MyBatisPlus based on Spring
  • Using MyBatisPlus based on SpringBoot

MyBatisPlus Starter Case

We use MyBatisPlus based on SpringBoot as an example to demonstrate the convenience of MyBatisPlus development

SpringBoot uses MyBatis

First, let's recall the steps related to SpringBoot's development using MyBatis:

  1. Create a SpringBoot project
  2. Tick ​​Configure to use technology
  3. Set DataSource related properties (JDBC parameters)
  4. Defining the Data Layer Interface Mapping Configuration

The most cumbersome is the configuration of the data layer interface, which requires writing a large number of @ annotations to query the database

package com.itheima.dao;

import com.itheima.domain.Book;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface BookDao {

    @Insert("insert into tbl_book (type,name,description) values(#{type},#{name},#{description})")
    public int save(Book book);

    @Update("update tbl_book set type = #{type}, name = #{name}, description = #{description} where id = #{id}")
    public int update(Book book);

    @Delete("delete from tbl_book where id = #{id}")
    public int delete(Integer id);

    @Select("select * from tbl_book where id = #{id}")
    public Book getById(Integer id);

    @Select("select * from tbl_book")
    public List<Book> getAll();
}
copy

SpringBoot uses MyBatisPlus

Our SpringBoot greatly simplifies data layer code writing using MyBatisPlus

We describe the overall steps in sequence below:

  1. Create a project (SpringBoot project, check the corresponding technology stack)
  1. Import related dependent coordinates
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.itheima</groupId>
    <artifactId>mybatisplus_01_quickstart</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <!--because SpringBoot not integrated MyBatisPlus，So we need to manually add MyBatisPlus dependent coordinates-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <!--Druid coordinates-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
copy
  1. Configure the environment yaml
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatisplus_db?serverTimezone=UTC
    username: root
    password: root
copy
  1. Writing database entity classes
package com.itheima.domain;

import lombok.*;

@Data
public class User {
    private Long id;
    private String name;
    private String password;
    private Integer age;
    private String tel;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }


    @java.lang.Override
    public java.lang.String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                ", tel='" + tel + '\'' +
                '}';
    }
}
copy
  1. data layer writing
package com.itheima.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.domain.User;
import org.apache.ibatis.annotations.Mapper;

// @Mapper in order to make the scan to this data layer package (mentioned in SpringBoot)
@Mapper
// Note: MyBatisPlus does not need to write methods, we directly inherit the BaseMapper class and indicate the entity class we use
public interface UserDao extends BaseMapper<User> {
    // No need to write methods, configure a large number of database methods for us in the BaseMapper class to query, add, modify, delete
}
copy
  1. test
package com.itheima;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {

    @Autowired
    private UserDao userDao;

    // We just need to get the UserDao class and call its methods (MyBatisPlus provides methods)
    // Here we just call the selectById method as a test
    @Test
    void testGetById(){
        User user = userDao.selectById(2L);
        System.out.println(user);
    }
}
copy

At this point, our first MyBatisPlus case is over

MyBatisPlus overview

After using MyBatisPlus, we can re-introduce MyBatisPlus:

  • MyBatisPlus (MP) is an enhanced tool based on the MyBatis framework, designed to simplify development and improve efficiency
  • Official website: MyBatis-Plus (baomidou.com)

MyBatisPlus Features:

  • Non-invasive: make enhancements, no changes, no impact on existing projects
  • Powerful CRUD operations: built-in general-purpose Mapper, single-table CRUD operations can be implemented with a small amount of configuration
  • Lambda support: write query conditions without worrying about field errors
  • Support gradual automatic generation
  • Built-in pagination plugin

The last thing to mention: MyBatisPlus is developed by Chinese people, and the official website is also written in Chinese, with Chinese style~

Standard Data Layer Development

We have roughly grasped the specific operation process of MyBatisPlus before, let us analyze the data layer development in more detail.

Lombok dependent coordinates

Before we officially start explaining the development of the data layer, we will provide you with a simple and reliable dependency:

  • Lombok dependency

So what does this coordinate do?

  • Lombok dependencies can be used to simplify the development of entity classes
  • Lombok, a Java class library, provides a set of annotations to simplify the development of POJO entity classes

We use a simple case to demonstrate:

  1. First we need to import the coordinates
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>
copy
  1. Generate multiple new annotations in our entity class
package com.itheima.domain;

import lombok.*;

//lombok

@Data
public class User {
    private Long id;
    private String name;
    private String password;
    private Integer age;
    private String tel;
}
/*
lombok Provides us with multiple annotations:

@Setter: Provides all set methods
@Getter: Provides all Get methods
@ToString: Provides a ToString refactoring method
@NoArgsConstructor: No-argument constructor
@AllArgsConstructor: parameterized construction

Among them, our most commonly used annotations are:
@Data: Includes all methods except constructors (Set, Get, ToString, hashCode, equals)
*/
copy

Standard Data Layer Development (Simple Edition)

First, let's list some data layer statements that we usually use in development:

Function

MP interface

new

int insert(T t)

delete

int deleteById(Serializable id)

Revise

int updateById(T t)

query by id

T selectById(Serializable id)

Inquire all

List selectList()

The above methods do not need to be defined in the data layer, but can be tested directly:

package com.itheima;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {

    // Autowire data layer
    @Autowired
    private UserDao userDao;

    // Note: The data type of id is LONG, and L needs to be added after the value
    
    // Note: Some of the following methods need to return id, entity class, null
    
    // new
    @Test
    void testSave(){
        User user = new User();
        user.setName("dark horse programmer");
        user.setPassword("itheima");
        user.setAge(12);
        user.setTel("4006184000");
        userDao.insert(user);
    }

    // delete
    @Test
    void testDelete(){
        userDao.deleteById(1L);
    }

    // Update (note: update according to id, update the value different from the original data, null value will not be updated)
    @Test
    void testUpdate(){
        User user = new User();
        user.setId(1L);
        user.setName("Tom888");
        user.setPassword("tom888");
        userDao.updateById(user);
    }

    // query by id
    @Test
    void testGetById(){
        User user = userDao.selectById(2L);
        System.out.println(user);
    }

	// Inquire all
    @Test
    void testGetAll() {
        List<User> userList = userDao.selectList(null);
        System.out.println(userList);
    }

}
copy

Standard Data Layer Development (Pagination Query)

We list paging queries as a separate section for explanation:

Function

MP interface

Paging query

IPage<T> selectPage(IPage<T> page)

The paging query of MyBatisPlus requires some preconditions, we will describe them one by one below:

  1. Add an interceptor to perform paging operations:
// The paging operation of MyBatisPlus needs to add an interceptor

// We create the Config folder under the Java folder and create the Java class of MPConfig as the configuration class

package com.itheima.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

// As a configuration class, so that spring can scan directly
@Configuration
public class MpConfig {
    
    // Need to be set to Bean
    @Bean
    public MybatisPlusInterceptor mpInterceptor(){
        //1. Define the Mp interceptor MybatisPlusInterceptor (equivalent to the outer interceptor)
        MybatisPlusInterceptor mpInterceptor = new MybatisPlusInterceptor();
        //2. Add a specific interceptor PaginationInnerInterceptor (equivalent to adding a small interceptor to a large interceptor)
        mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mpInterceptor;
    }
}
copy
  1. Just test it directly
package com.itheima;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class Mybatisplus01QuickstartApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetByPage(){
       // selectPage requires two parameters, IPage and Wrapper. IPage has the implementation class of Page. Wrapper will be mentioned later. It is temporarily set to null here.
        
        //The IPage object encapsulates the data related to the paging operation. The first parameter is the start number, and the second number is the number of data that can be displayed on the page.
        IPage page  = new Page(2,3);
        
        userDao.selectPage(page,null);
        
        // The following are some methods carried by the page itself to view related data
        System.out.println("Current page number value:"+page.getCurrent());
        System.out.println("Displays per page:"+page.getSize());
        System.out.println("How many pages in total:"+page.getPages());
        System.out.println("How many pieces of data in total:"+page.getTotal());
        System.out.println("data:"+page.getRecords());
    }

}
copy
  1. View logs
# If we want to view the actual development of the database statement, we can choose to open the log to view

# The view log method is set in the yaml configuration file

# Enable mp log (output to console)
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
copy

DQL programming control

We have explained the simple data layer development before, and we will further explain the programming content of DQL.

Clear version map

Before the official start, let's talk about some simple and easy knowledge points:

  • Every time we start the server, the version map of SpringBoot and MyBatisPlus will appear, which sometimes affects the reading of our code

So how do we clear it

  1. Set up logback.xml file
<!--we are at sources Create a new one spring config document-->

<!--set one configuration double label,No need to fill in the content-->
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
</configuration>
copy
  1. do cleanup in yaml config file
# We only need to set the banner of spring and MyBatisPlus to false to close the version map

# dataSource
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatisplus_db?serverTimezone=UTC
    username: root
    password: root
  main:
    banner-mode: off
# mp log
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    banner: false
copy

Basic condition query

Before introducing conditional query, we need to introduce Wrapper first:

  • Wrapper is an interface parameter
  • Wrapper is the parameter for conditional judgment carried in our conditional query
  • In the parameters of the interface method, various Wrappers will appear, such as queryWrapper, updateWrapper, etc.

Next, we will introduce three basic conditional queries:

  1. Query by condition
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {
        //Method 1: Query by condition
        
        // Our conditional queries generally use QueryWrapper type classes to create Wrapper conditional judgment parameters
        QueryWrapper qw = new QueryWrapper();
        // We use some methods of QueryWrapper to give judgment conditions, which we will introduce later
        // The lt is less than method is used here, followed by a String type string to represent the database column name, followed by a numeric value to represent the value
        qw.lt("age",18);
        // We use userDao's selectList method to obtain data according to the qw conditional judgment mechanism
        // Get the List of User values ​​and print them
        List<User> userList = userDao.selectList(qw);
        System.out.println(userList);

    }

}
copy
  1. Query by condition in lambda format (method version)
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        //Method 2: Query by condition in lambda format
        
        QueryWrapper<User> qw = new QueryWrapper<User>();
        // MyBatisPlus can support Lambda expressions, we use the lambda method to make subsequent operations become Lambda expressions
        // Our String database column names can be written in the form of Lambda expressions
        qw.lambda().lt(User::getAge, 10);
        List<User> userList = userDao.selectList(qw);
        System.out.println(userList);

    }

}
copy
  1. Query by condition in lambda format (inherited class version)
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {
        
        //Method 3: Query by condition in lambda format
        
        // Here, LambdaQueryWrapper is directly inherited, and subsequent operations can directly use Lambda expressions without carrying the method lambda
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        lqw.lt(User::getAge, 10);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

In addition to the above three basic conditional queries, we can find that conditional queries can be superimposed, mainly divided into two superposition methods:

  1. normal stacking
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        // Between 10 and 30 years old
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        // write separately
        lqw.lt(User::getAge, 30);
        lqw.gt(User::getAge, 10);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy
  1. chain stack
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        // Between 10 and 30 years old
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        // chain overlay writing
        lqw.lt(User::getAge, 30).gt(User::getAge, 10);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

Finally, we also introduce two forms of combined query conditions:

  1. and (and)
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        //and relationship
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //And relationship: between 10 and 30 years old (direct chain writing is OK)
        lqw.lt(User::getAge, 30).gt(User::getAge, 10);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy
  1. or (or)
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        //or relationship
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //Or relationship: less than 10 years old or more than 30 years old (use or method to isolate between conditions)
        lqw.lt(User::getAge, 10).or().gt(User::getAge, 30);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

Null handling

When we do actual project processing, various query frameworks will appear

For example, price query searches for products, but we may not give both the lowest price limit and the highest price limit

Let's do a simple example with code:

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll() {
		// Simulate the query data passed from the page
        UserQuery uq = new UserQuery();
        // If we have one of the following data that is not set, it will cause the query statement to have a ? Not populated, causing the search to fail
        uq.setAge(10);
        uq.setAge2(null);

        // null judgment
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        lqw.lt(User::getAge, uq.getAge2());
        lqw.gt(User::getAge, uq.getAge());
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList)

    }

}
copy

In the past, most of our null value processing used if statement to judge:

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll() {
		// Simulate the query data passed from the page
        UserQuery uq = new UserQuery();
		// There may be any number that is not assigned a value
        uq.setAge(10);
        uq.setAge2(null);

        // null judgment
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        lqw.lt(User::getAge, uq.getAge2());
        
        // Normally, we use if to judge whether there is a value, and if there is a value, add the operation
        // But when there are too many if statements, the code is verbose
        if( null != uq.getAge()) {
            lqw.gt(User::getAge, uq.getAge());
        }
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

MyBatisPlus gives a new predicate method to decide whether to load the statement:

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll() {
		// Simulate the query data passed from the page
        UserQuery uq = new UserQuery();
        uq.setAge(10);
        uq.setAge2(30);

		// Various methods of LambdaQueryWrapper carry a judgment condition in the first parameter. When it is established, the subsequent operation is executed. If it is not established, it will be skipped directly.
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        // First determine whether the first parameter is true, if it is true, connect the current condition
        lqw.lt(null != uq.getAge2(),User::getAge, uq.getAge2());
        lqw.gt(null != uq.getAge(),User::getAge, uq.getAge());
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

query projection

Our selective query database column is called query projection. Next, let us introduce the implementation method of query projection

The implementation of query projection is roughly divided into two types:

  1. The query result contains some properties in the model class
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll() {
        // query projection
    
    	// When the content we query belongs to the attributes contained in the entity class, we can use QW or LambdaQW to achieve
    	// Most of us use LambdaQW to implement, because it has automatic identification and is not easy to make mistakes
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
    	// We use the select method to add query types, and LambdaQueryWrapper uses Lambda expressions to add
        lqw.select(User::getId,User::getName,User::getAge);
        QueryWrapper<User> lqw = new QueryWrapper<User>();
        // We use the select method to add query types, and QueryWrapper uses String to add
        lqw.select("id","name","age","tel");
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy
  1. The query result contains an undefined property in the model class
package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;
    
    @Test
    void testGetAll() {
        // query projection
    
        // Undefined properties we can only use the String string of QueryWrapper to write, such as count(*) and grouping conditions
        QueryWrapper<User> lqw = new QueryWrapper<User>();
        lqw.select("count(*) as count, tel");
        // QueryWrapper provides the grouping method groupBy, the parameter is the database column name of String type
        lqw.groupBy("tel");
        List<Map<String, Object>> userList = userDao.selectMaps(lqw);
        System.out.println(userList);

    }

}
copy

Display of query conditions

First of all, we give the official website links for all query conditions: Conditional Constructor | MyBatis-Plus (baomidou.com)

If necessary, you can check the relevant construction methods online

Below we only show some common query conditions:

package com.itheima;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import com.itheima.domain.query.UserQuery;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

@SpringBootTest
class Mybatisplus02DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testGetAll() {

        //Conditional query
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        // eq is equivalent to =
        lqw.eq(User::getName,"Jerry").eq(User::getPassword,"jerry");
        User loginUser = userDao.selectOne(lqw);
        System.out.println(loginUser);

        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //Range query lt le gt ge eq between
        lqw.between(User::getAge,10,30);
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        //fuzzy match like
        lqw.likeLeft(User::getName,"J");
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

		// Inquire all
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>();
        List<User> userList = userDao.selectList(lqw);
        System.out.println(userList);

    }

}
copy

Mapping processing

The last introduction is still the old problem that we also have in MyBatis:

  • What should we do when our implementation class and database table are different

We divide it into several situations as follows:

  1. The table name is out of sync with the coding development design
// Suppose our database table is named tbl_user, but our entity class is designed as User

package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

// Use the annotation @TableName followed by the corresponding database table name
@TableName("tbl_user")
public class User {
    private Long id;
    private String name;
    private String password;
    private Integer age;
    private String tel;
}
copy
  1. Column names are out of sync with coding development design
// Suppose our database password is designed as pwd, but our entity class password attribute is designed as password

package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

//lombok
@Data
@TableName("tbl_user")
public class User {
    private Long id;
    private String name;
    
    // Use @TableField annotation, followed by attribute value=database column name
    @TableField(value = "pwd")
    private String password;
    
    private Integer age;
    private String tel;
}
copy
  1. when the column should not be printed
// For example, our pwd password should not be queried when querying, but we use select * from tbl_user to query, how to block

package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

//lombok
@Data
@TableName("tbl_user")
public class User {
    private Long id;
    private String name;
    
    // Use the @TableField annotation, and then use the attribute select to set it to false, that is, it cannot be queried
    @TableField(value = "pwd"，select = false)
    private String password;
    
    private Integer age;
    private String tel;
}
copy
  1. When a column that does not exist in the database appears in the entity class
// For example, we have designed an attribute online to judge whether it is online or not. It does not need to be placed in the database. How can we remove this attribute in select *

package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

//lombok
@Data
@TableName("tbl_user")
public class User {
    private Long id;
    private String name;
    @TableField(value = "pwd",select = false)
    private String password;
    private Integer age;
    private String tel;
    
    // With @TableField annotation, followed by the attribute exist set to false, it is judged that the attribute does not exist in the database
    @TableField(exist = false)
    private Integer online;
}
copy

We separate the above content and mainly explain two annotations:

  1. @TableName
  • Name: @TableName
  • Type: class annotation
  • Location: Above the model class definition
  • Function: Set the database table relationship corresponding to the current class
  • Related properties: value sets the database table name
  1. @TableField
  • Name: @TableField
  • Type: Property Annotation
  • Location: Above the model class property definition
  • Role: Set the current attributes and many relationships
  • Related attributes: value sets the database field name, exists sets whether the attribute exists in the database field, select sets whether the attribute participates in the query

DML programming control

In this chapter, we will talk about some frequently used operations in MyBatisPlus. Let's introduce them one by one.

ID Generation Policy Control

In actual development, we will need ID generation requirements, and we need to take different ID generation methods for different situations:

  • Employee ID: Incremental ID generation
  • Courier number: area ID generation
  • Network ID: Random ID generation

Therefore MyBatisPlus provides a new annotation to achieve such requirements:

  • Name: @TableId
  • Type: Property Annotation
  • Location: Above the property definition in the model class that represents the primary key
  • Role: Set the generation strategy of the primary key attribute in the current class
  • Related attributes: value sets the database primary key name, type sets the primary key generation strategy, refer to the IdType enumeration value for details

ID generation strategy enumeration value:

  • AUTO (0): Use the database id auto-increment strategy to control id generation
  • NONE (1): Do not set ID generation strategy
  • INPUT(2): The user manually enters the ID
  • ASSIGN_ID (3): ID generated by the snowflake algorithm (compatible with numeric and string types)
  • ASSIGN_UUID (4): Use the UUID generation algorithm as the ID generation strategy

We give an example demonstration:

package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

@Data
//Set the table name mapping relationship
@TableName("tbl_user")
public class User {
    //Set the primary key generation strategy (set here to auto increment)
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    @TableField(value = "pwd",select = false)
    private String password;
    private Integer age;
    private String tel;
    @TableField(exist = false)
    private Integer online;
    private Integer deleted;
    private Integer version;
}
copy

We can also set the type attribute of @TableId to make @TableId of the entire project execute in one form:

# dataSource
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatisplus_db?serverTimezone=UTC
    username: root
    password: root
  main:
    banner-mode: off
# mp log
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    banner: false
    db-config:
      id-type: assign_id # Set to set ID for snowflake algorithm
copy

Multi-record operation

We often perform multiple record operations in actual development:

  • Delete multiple operations based on primary key
  • Query multiple operations based on primary key

MyBatisPlus also provides us with corresponding methods:

package com.itheima;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class Mybatisplus03DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testDelete(){
        //Delete specified multiple data
        //The deleteBatchIds parameter is in the form of an array, we can provide an array of ids
        List<Long> list = new ArrayList<>();
        list.add(1402551342481838081L);
        list.add(1402553134049501186L);
        list.add(1402553619611430913L);
        userDao.deleteBatchIds(list);
    }
    
    @Test
    void testSelect()}{
        //Query the specified multiple pieces of data
    	//The selectBatchIds parameter is in the form of an array, we can provide an array of ids
        List<Long> list = new ArrayList<>();
        list.add(1L);
        list.add(3L);
        list.add(4L);
        userDao.selectBatchIds(list);
	}

}
copy

Tombstone

The deletion operations we face in actual development are sometimes not real deletion operations:

  • For example, for the storage of some data, we need to calculate the total output in the year-end summary, so all benefit income cannot be deleted
  • However, for some departing employees, data about them needs to be temporarily deleted, thus creating a contradiction

Therefore, we propose the concept of logical deletion:

  • We add an attribute deleted to the total list to set whether it is deleted
  • When deleted is 1, it is regarded as data deletion; when deleted is 1, it is regarded as data existing
  • We replace real data deletion with a virtual deletion idea (logical deletion) in actual development

Let's explain how to implement this idea in MyBatisPlus:

  1. Add deleted column to database
ALTER TABLE tb_user ADD deleted int(1) DEFAULT 0;
copy
  1. Add the deleted attribute to the entity library and annotate it as a tombstone attribute
package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

@Data
//Set the table name mapping relationship
@TableName("tbl_user")
public class User {
    //Set the primary key generation strategy
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    @TableField(value = "pwd",select = false)
    private String password;
    private Integer age;
    private String tel;
    @TableField(exist = false)
    private Integer online;
    //Tombstone field, marking whether the current record is deleted (original value of value, modified value of delval)
    @TableLogic(value = "0" ,delval = "1")
    private Integer deleted;
}

/*
@TableLogic
 After setting, when we use the delete method, the data will not be deleted directly, but the data value will be changed from value to delval
 Something like: UPDATE tbl_user SET deleted = 1 WHERE id = ? AND deleted = 0;
*/
copy

Similarly, our tombstone values ​​can also be set uniformly:

# dataSource
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatisplus_db?serverTimezone=UTC
    username: root
    password: root
  main:
    banner-mode: off
# mp log
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    banner: false
    db-config:
      id-type: assign_id
      table-prefix: tbl_
      # Tombstone field name
      logic-delete-field: deleted
      # Tombstone literal: 0 if not deleted
      logic-not-delete-value: 0
      # Tombstone literal: delete is 1
      logic-delete-value: 1
copy

optimistic locking

We also encounter multi-threading problems in business development:

  • For example, our seckill activity, there are also problems mentioned in the previous multi-threading
  • If we don't want our remaining number of items to become negative, then we need to set Lock to solve this problem

First, let's explain the basic concept of optimistic locking:

  • Optimistic locking first reads the current state of the item
  • Then use the status of the current commodity as a condition to modify the status of the commodity, and modify the status of the commodity
  • If user A and user B read the product status at the same time, and modify the product after user A's WHERE condition is established, then user B's WHERE condition is no longer established and cannot be modified

In MyBatisPlus we use the concept of optimistic locking to solve:

  1. Add a new version attribute to the database
ALTER TABLE tb_user ADD version int(11) DEFAULT 1;
copy
  1. Add a new version attribute to the entity class and add an annotation
package com.itheima.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

@Data
//Set the table name mapping relationship
@TableName("tbl_user")
public class User {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    @TableField(value = "pwd",select = false)
    private String password;
    private Integer age;
    private String tel;
    @TableField(exist = false)
    private Integer online;
    @TableLogic(value = "0" ,delval = "1")
    private Integer deleted;
    
    // version annotation (optimistic locking)
    @Version
    private Integer version;
}
copy
  1. use interceptor
/*
Let's explain why you need to use interceptors
 We convert the optimistic locking concept we introduced earlier into the statement:

SELETE version FROM tbl_user

UPDATE tbl_user SET ...(User modified) version=version+1 WHERE id = ? AND version = version (version we read earlier)

If there are other user operations before the user operation, the version will change, so that the user cannot find the corresponding data and cannot operate

Because we need to modify the previous version, we need to intercept the statement and make certain modifications, so the interceptor is used here.
*/

package com.itheima.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MpConfig {
    @Bean
    public MybatisPlusInterceptor mpInterceptor() {
        //1. Define the Mp interceptor
        MybatisPlusInterceptor mpInterceptor = new MybatisPlusInterceptor();
        //2. Add specific interceptors
        mpInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        //3. Add optimistic lock interceptor
        mpInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mpInterceptor;
    }
}
copy
  1. test
package com.itheima;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.itheima.dao.UserDao;
import com.itheima.domain.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class Mybatisplus03DqlApplicationTests {

    @Autowired
    private UserDao userDao;

    @Test
    void testUpdate(){
        
        // We set an instance User, id is 1, version is 1
        User user = new User();
        user.setId(1L);
        user.setName("Jock666");
        user.setVersion(1);
        userDao.updateById(user);

        // We use user, user2 as two users
        
        // Suppose two users read data at the same time
        User user = userDao.selectById(3L);     //version=1

        User user2 = userDao.selectById(3L);    //version=1

        // The user user first performs the operation (at this time, the instance version is 1, the operation is established, and the version becomes 2 after the operation is completed)
        // UPDATE tbl_user SET ... (modified by user) version=2 WHERE id = 1 AND version = 1
        user.setName("Jock aaa");
        userDao.updateById(user2);              

		// user2 starts the operation (at this time, the instance version is 2 but the previously read version is 1, the data cannot be read, and the operation cannot be performed)
        // UPDATE tbl_user SET ... (user modification) version=2 WHERE id = 1 AND version = 1 (no data can be read)
        user2.setName("Jock bbb");
        userDao.updateById(user);               

    }

}
copy

concluding remarks

Ok, this is the introduction to MyBatisPlus, I hope it can help you!

appendix

This article belongs to the learning content. For details, please refer to the SSM framework course of Mr. Li, the dark horse programmer of station B.

Here is the link: MyBatisPlus-01-MybatisPlus entry case

Posted by simplyi on Tue, 01 Nov 2022 14:53:38 +0530

Hot Tags

©2023 Programs Team