1. Introduction to MyBatisPlus
1.1. Overview
Official website: https://baomidou.com/
MyBatis-Plus (referred to as MP) is a MyBatis The enhancement tool of MyBatis only enhances and does not change on the basis of MyBatis, and is born to simplify development and improve efficiency.
1.2. Features
- Non-invasive: only make enhancements without changing, introducing it will not affect existing projects, smooth as silk
- Low loss: Basic CURD is automatically injected at startup, performance is basically lossless, and direct object-oriented operation
- Powerful CRUD operations: Built-in general Mapper and general Service, most CRUD operations on a single table can be implemented with only a small amount of configuration, and more powerful conditional constructors to meet various usage needs
- Support Lambda form invocation: Through Lambda expressions, you can easily write various query conditions, and you don't need to worry about writing wrong fields.
- Support multiple databases: support MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer2005, SQLServer and other databases
- Supports automatic primary key generation: supports up to 4 primary key strategies (including a distributed unique ID generator - Sequence), which can be freely configured to perfectly solve the primary key problem
- Supports XML hot loading: The XML corresponding to Mapper supports hot loading. For simple CRUD operations, it can even be started without XML
- Support ActiveRecord mode: Support ActiveRecord form call, entity class can perform powerful CRUD operations only by inheriting Model class
- Support custom global general operations: support global general method injection ( Write once, use anywhere )
- Support keyword automatic escaping: Support database keywords (order, key...) automatic escaping, and can also customize keywords
- Built-in code generator: Use code or Maven plugin to quickly generate Mapper , Model , Service , Controller layer code, support template engine, and more custom configurations waiting for you to use
- Built-in paging plug-in: Based on MyBatis physical paging, developers do not need to care about specific operations. After configuring the plug-in, writing paging is equivalent to ordinary List query
- Built-in performance analysis plug-in: It can output Sql statements and their execution time. It is recommended to enable this function during development and testing, which can quickly identify slow queries
- Built-in global interception plug-in: provides intelligent analysis and blocking of delete and update operations on the entire table, and can also customize interception rules to prevent misoperation
- Built-in Sql injection stripper: supports Sql injection stripping, effectively preventing Sql injection attacks
2. Getting started with MyBatisPlus
2.1. Create test database and tables
mybatis_plus
Its table structure is as follows:
id | name | age | |
---|---|---|---|
1 | Jone | 18 | test1@baomidou.com |
2 | Jack | 20 | test2@baomidou.com |
3 | Tom | 28 | test3@baomidou.com |
4 | Sandy | 21 | test4@baomidou.com |
5 | Billie | 24 | test5@baomidou.com |
The corresponding table creation statement is as follows:
CREATE TABLE user2 ( id BIGINT(20) NOT NULL COMMENT 'primary key ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT 'Name', age INT(11) NULL DEFAULT NULL COMMENT 'age', email VARCHAR(50) NULL DEFAULT NULL COMMENT 'Mail', PRIMARY KEY (id) );
The corresponding database Data script is as follows:
INSERT INTO user2 (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'), (2, 'Jack', 20, 'test2@baomidou.com'), (3, 'Tom', 28, 'test3@baomidou.com'), (4, 'Sandy', 21, 'test4@baomidou.com'), (5, 'Billie', 24, 'test5@baomidou.com');
2.2. Create a project and add dependencies
(1) Use Spring Initializr to initialize the Spring Boot project
Group: com.example
Artifact: mybatis-plus
Version: 2.2.1.RELEASE
(2) The project introduces dependencies
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--mybatis-plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.1</version> </dependency> <!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--lombok Used to simplify entity classes--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies>
(3) Install the lombok plugin in idea
2.3, create a configuration file
mysql5
#mysql database connection spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus spring.datasource.username=root spring.datasource.password=123456
mysql8 and above (spring boot 2.1) Note: changes in driver and url
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=root
Notice:
1. The url here uses the ?serverTimezone=GMT%2B8 suffix, because Spring Boot 2.1 integrates the 8.0 version of the jdbc driver. This version of the jdbc driver needs to add this suffix, otherwise running the test case will report the following error:
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more
2, here driver-class-name uses com.mysql.cj.jdbc.Driver, recommended in jdbc 8, previously com.mysql.jdbc.Driver has been discarded or WARN information will be provided when the test case is run
2.4, write code
(1) Create a startup class
Add the @MapperScan annotation to the Spring Boot startup class to scan the Mapper folder
@SpringBootApplication @MapperScan("com.atguigu.mybatisplus.mapper") public class MybatisPlusApplication { ...... }
(2) Create an entity class
@Data public class UserGgkt { @TableId() private Long id; private String name; private Integer age; private String email; }
(3) Create Mapper
public interface UserMapper extends BaseMapper<UserGgkt> { }
(4) Functional test - query all records
public class MybatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test public void testSelectList() { System.out.println(("----- selectAll method test ------")); //The parameter of the selectList() method in UserMapper is MP's built-in conditional wrapper Wrapper //So if you don't fill it out, you don't have any conditions. List<User> users = userMapper.selectList(null); users.forEach(System.out::println); } }
Notice:
IDEA reports an error at userMapper because the injected object cannot be found, because the class is dynamically created, but the program can be executed correctly.
In order to avoid errors, you can add the @Repository annotation to the interface of the mapper layer
or replace @Autowired with @Resource
lombok exception:
class lombok.javac.apt.LombokProcessor (in unnamed module @0x7a8b9166) cannot access class com.sun.tools.javac.processing.JavacProcessingEnvironment (in module jdk.compiler) because module jdk.compiler does not export com.sun.tools.javac.processing to unnamed module @0x7a8b9166
The current lombok version is too low Change the version to 1.18.20
3. MyBatisPlus implements CRUD operations
3.1, insert operation
@SpringBootTest public class CrudTest { @Resource UserMapper userMapper; @Test public void testCrudInsert(){ UserGgkt userGgkt = new UserGgkt(); userGgkt.setAge(18); userGgkt.setName("Allen"); userGgkt.setEmail("test@qq.com"); // Returns the number of rows affected int insert = userMapper.insert(userGgkt); System.out.println(insert); // id is automatically backfilled System.out.println(userGgkt); } }
**Note: **Database insert id value defaults to: globally unique id
View sql output log
#mybatis log mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
3.2, primary key strategy
(1)ID_WORKER
The default primary key strategy of MyBatis-Plus is: ID_WORKER global unique ID
(2) Self-increment strategy
-
To auto-increment the primary key, you need to configure the following primary key strategy
-
- You need to set the primary key auto-increment when creating the data table
- Configure @TableId(type = IdType.AUTO) in the entity field
@TableId(type = IdType.AUTO) private Long id;
Other primary key strategies: analyze the IdType source code to know
public enum IdType { /** * Database ID auto increment */ AUTO(0), /** * The type is not set the primary key type */ NONE(1), /** * User input ID * This type can be filled by registering an autofill plugin by itself */ INPUT(2), /** * Globally Unique ID */ ASSIGN_ID(3), /** * Universally Unique ID (UUID) */ ASSIGN_UUID(4), /** @deprecated */ @Deprecated ID_WORKER(3), /** @deprecated */ @Deprecated ID_WORKER_STR(3), /** @deprecated */ @Deprecated UUID(4); private final int key; private IdType(int key) { this.key = key; } public int getKey() { return this.key; } }
3.3. Update operation according to Id
**Note: **The sql generated during update is automatically dynamic sql: UPDATE user SET age=? WHERE id=?
@Test public void testUpdateById(){ //1 Query records based on id User user = userMapper.selectById(1L); //2 Set the modified value user.setAge(50); //3 Call method modification int result = userMapper.updateById(user); System.out.println(result); }
3.4. Paging query
MyBatis Plus comes with a paging plug-in, and the paging function can be realized with a simple configuration
(1) Create a configuration class
/** * Pagination plugin */ @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }
(2) Test selectPage paging
**Test:**Finally get the relevant data through the page object
@Test public void testSelectPage() { Page<User> page = new Page<>(1,5); userMapper.selectPage(page, null); page.getRecords().forEach(System.out::println); System.out.println(page.getCurrent()); System.out.println(page.getPages()); System.out.println(page.getSize()); System.out.println(page.getTotal()); System.out.println(page.hasNext()); System.out.println(page.hasPrevious()); }
Console sql statement prints: SELECT id,name,age,email,create_time,update_time FROM user LIMIT 0,5
3.5. Delete records according to id
@Test public void testDeleteById(){ int result = userMapper.deleteById(1); System.out.println(result); }
3.6. Batch delete
@Test public void testDeleteBatchIds() { int result = userMapper.deleteBatchIds(Arrays.asList(8, 9, 10)); System.out.println(result); }
3.7. Logical deletion
- Physical deletion: real deletion, delete the corresponding data from the database, and then the deleted data cannot be queried
- Logical deletion: Fake deletion, change the status of the field in the corresponding data representing whether it has been deleted to "deleted status", and then you can still see this data record in the database
(1) Add the deleted field to the database
ALTER TABLE `user` ADD COLUMN `deleted` boolean
(2) Add deleted field to entity class
And add @TableLogic annotation
@TableLogic private Integer deleted;
(3) application.properties join the configuration
This is the default value, if your default value is the same as the mp default, this configuration can be omitted
mybatis-plus.global-config.db-config.logic-delete-value=1 mybatis-plus.global-config.db-config.logic-not-delete-value=0
(5) Test logical deletion
- After the test, it was found that the data was not deleted, and the value of the deleted field changed from 0 to 1
- After the test, analyze the printed sql statement, which is an update
- **Note:** The value of the deleted field of the deleted data must be 0 in order to be selected for tombstone deletion
/** * test tombstone */ @Test public void testLogicDelete() { int result = userMapper.deleteById(1L); System.out.println(result); }
(7) Test the query after logical deletion
The query operation in MyBatis Plus will also automatically add the judgment of the tombstone field
/** * Test the query after tombstone: * Does not include tombstoned records */ @Test public void testLogicDeleteSelect() { User user = new User(); List<User> users = userMapper.selectList(null); users.forEach(System.out::println); }
Analyze the printed sql statement after the test, including WHERE deleted=0
SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0
4. MyBatisPlus Conditional Constructor

Wrapper: Conditional construction abstract class, the top parent class
AbstractWrapper: used to encapsulate query conditions and generate where conditions of sql
QueryWrapper: The Entity object encapsulates the operation class, not using lambda syntax
UpdateWrapper : Update condition encapsulation for Entity object update operation
AbstractLambdaWrapper: Lambda syntax uses Wrapper to uniformly process parsing lambda to get column.
LambdaQueryWrapper: You can see from the name that it is the query Wrapper used for Lambda syntax
LambdaUpdateWrapper: Lambda update package Wrapper
Note: The column in the method input parameters of the following conditional constructors all represent database fields
4.1, QueryWrapper use
(1)ge,gt,le,lt
gt is greater than >
ge greater than or equal to >=
lt is less than <
le is less than or equal to <=
@Test public void testSelect() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 28); List<User> users = userMapper.selectList(queryWrapper); System.out.println(users); }
(2)eq,ne
eq equal
ne range
**Note: **seletOne returns an entity record, an error will be reported when there are multiple records
@Test public void testSelectOne() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("name", "Tom"); User user = userMapper.selectOne(queryWrapper); System.out.println(user); }
SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ?
(3)like,likeLeft,likeRight
selectMaps returns a list of Map collections
@Test public void testSelectMaps() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper .like("name", "e") .likeRight("email", "t"); List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//The return value is a list of Map s maps.forEach(System.out::println); }
SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 AND name LIKE ? AND email LIKE ?
(4)orderByDesc,orderByAsc
orderByDesc descending order
orderByAsc ascending order
@Test public void testSelectListOrderBy() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.orderByDesc("id"); List<User> users = userMapper.selectList(queryWrapper); users.forEach(System.out::println); }
SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 ORDER BY id DESC
4.2. Using LambdaQueryWrapper
@Test public void testLambdaQuery() { LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.eq(User::getAge,30); queryWrapper.like(User::getName,"open"); List<User> list = userMapper.selectList(queryWrapper); System.out.println(list); }
SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 AND age = ? AND name LIKE ?
5. MyBatisPlus encapsulates the Service layer
5.1. Create service
public interface UserService extends IService<User> { }
5.2, create a service implementation class
@Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService { }
The bottom layer encapsulates the process of injecting Mapper

5.3, method call test
@SpringBootTest class TestApplicationTests { //inject service @Autowired private UserService userService; //Query all data in the table @Test public void findAll() { List<User> userList = userService.list(); for (User user:userList) { System.out.println(user); } } }