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:
- Create a SpringBoot project
- Tick Configure to use technology
- Set DataSource related properties (JDBC parameters)
- 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
copypackage 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(); }
SpringBoot uses MyBatisPlus
Our SpringBoot greatly simplifies data layer code writing using MyBatisPlus
We describe the overall steps in sequence below:
- Create a project (SpringBoot project, check the corresponding technology stack)

- Import related dependent coordinates
copy<?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>
- Configure the environment yaml
copyspring: 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
- Writing database entity classes
copypackage 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 + '\'' + '}'; } }
- data layer writing
copypackage 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 }
- test
copypackage 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); } }
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:
- First we need to import the coordinates
copy<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency>
- Generate multiple new annotations in our entity class
copypackage 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) */
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:
copypackage 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); } }
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:
- Add an interceptor to perform paging operations:
copy// 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; } }
- Just test it directly
copypackage 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()); } }
- View logs
copy# 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
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
- Set up logback.xml file
copy<!--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>
- do cleanup in yaml config file
copy# 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
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:
- Query by condition
copypackage 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); } }
- Query by condition in lambda format (method version)
copypackage 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); } }
- Query by condition in lambda format (inherited class version)
copypackage 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); } }
In addition to the above three basic conditional queries, we can find that conditional queries can be superimposed, mainly divided into two superposition methods:
- normal stacking
copypackage 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); } }
- chain stack
copypackage 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); } }
Finally, we also introduce two forms of combined query conditions:
- and (and)
copypackage 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); } }
- or (or)
copypackage 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); } }
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:
copypackage 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) } }
In the past, most of our null value processing used if statement to judge:
copypackage 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); } }
MyBatisPlus gives a new predicate method to decide whether to load the statement:
copypackage 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); } }
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:
- The query result contains some properties in the model class
copypackage 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); } }
- The query result contains an undefined property in the model class
copypackage 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); } }
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:
copypackage 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); } }
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:
- The table name is out of sync with the coding development design
copy// 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; }
- Column names are out of sync with coding development design
copy// 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; }
- when the column should not be printed
copy// 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; }
- When a column that does not exist in the database appears in the entity class
copy// 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; }
We separate the above content and mainly explain two annotations:
- @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
- @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:
copypackage 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; }
We can also set the type attribute of @TableId to make @TableId of the entire project execute in one form:
copy# 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
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:
copypackage 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); } }
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:
- Add deleted column to database
copyALTER TABLE tb_user ADD deleted int(1) DEFAULT 0;
- Add the deleted attribute to the entity library and annotate it as a tombstone attribute
copypackage 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; */
Similarly, our tombstone values can also be set uniformly:
copy# 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
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:
- Add a new version attribute to the database
copyALTER TABLE tb_user ADD version int(11) DEFAULT 1;
- Add a new version attribute to the entity class and add an annotation
copypackage 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; }
- use interceptor
copy/* 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; } }
- test
copypackage 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); } }
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