Mybatis plus Getting Started tutorial

Posted by tomfmason on Thu, 13 Jan 2022 05:49:22 +0100

brief introduction

MyBatis plus (MP for short) is an enhancement tool for MyBatis. On the basis of MyBatis, it only makes enhancements and does not change. It is born to simplify development and improve efficiency.
Moisten things silently
Only enhancement without change, and its introduction will not have an impact on the existing project, which is as smooth as silk.
Efficiency first
CRUD operations can be performed quickly with simple configuration, saving a lot of time.
Rich functions
Hot loading, code generation, paging, performance analysis and other functions are available.

1. Create database

    id BIGINT(20)NOT NULL COMMENT 'Primary key ID',
    PRIMARY KEY (id)

INSERT INTO user (id, name, age, email)VALUES
(1, 'Jone', 18, ''),
(2, 'Jack', 20, ''),
(3, 'Tom', 28, ''),
(4, 'Sandy', 21, ''),
(5, 'Billie', 24, '');

2. Create project

Initialization project: use Spring Initializr to quickly initialize a Spring Boot project
Import dependency:


<!--mysql rely on-->
<!--lombok Used to simplify entity classes-->

3. Write code

3.1 configuration file

In application Add the relevant configuration of MySQL database in the properties configuration file


be careful
1. What is the url used here? serverTimezone=GMT%2B8 suffix, because the jdbc driver of version 8.0 needs to add this suffix, otherwise run the test case and report the following error:
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more
2. The driver class name here uses com mysql. cj. jdbc. Driver, it is recommended to use this driver in jdbc 8, otherwise there will be WARN information when running the test case

3.2 startup

Add the @ MapperScan annotation in the Spring Boot startup class and scan the Mapper folder

package com.example.demomptest;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

public class DemomptestApplication {

    public static void main(String[] args) {, args);

3.3 entity class

public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;

3.4 adding Mapper

Before writing Mapper, we need to write interfaces and SQL statements. We can use mybatis plus to directly inherit the BaseMapper interface.

package com.example.demomptest.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demomptest.entity.User;
import org.springframework.stereotype.Repository;

public interface Usermapper extends BaseMapper<User> {

3.5 writing test classes

package com.example.demomptest;

import com.example.demomptest.entity.User;
import com.example.demomptest.mapper.Usermapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

class DemomptestApplicationTests {
    private Usermapper usermapper;

    //Test find all users
    public void findall() {
        List<User> user = usermapper.selectList(null);

3.6 operation results

View SQL output log
In application Properties, so that you can see the specific SQL and operations on the console.

#mybatis log

4. Primary key policy

When we tested the insert operation, we found that we do not define User_ID, the system will help us create a 19 bit id value. Why? For example:

The primary key policy of mybatis plus is used here

The default primary key policy of mybatis plus is ASSIGN_ID, this strategy means that the ID is a random nineteen digit number

 @TableId(type = IdType.ASSIGN_ID)
    private Long id;

Other key configurations:
AUTO: database primary key AUTO increment

#Global setting primary key generation policy

INPUT: user defined id
NONE: the primary key type is not set

Common notes
@TableName("table name")
When the table name is inconsistent with the entity class name, you can add @ TableName() Declaration on the entity class
@TableId declares that the attribute is the primary key in the table (if the attribute name is not the default id)
@TableFieId("field") can be used to declare when the entity class attribute is inconsistent with the table field

5. Auto fill and lock

What is auto fill?
Some tables have update times_ Time, creation timecreate_ Time, updater, or creator.
These fields need to be set every time you add, delete or modify data. The traditional approach is to set the fields of the entity before these operations. This approach is not only easy to forget and lead to errors, but also the code will appear particularly redundant.
Although the database time can be used for adding and modifying time, such functions cannot be used by adding and modifying people.
Therefore, mybatis plus provides the function of automatic filling to help you set the values of these fields, improve development efficiency, and the code will be particularly elegant.
There are two ways to realize automatic filling, one at the database level and the other by programming
1. Database level implementation
Add a new field create of type datetime to the User table_ time,update_time

2. Entity class level implementation

1, Entity class modification

Add fields on entities and add auto fill annotations

@TableField(fill = FieldFill.INSERT)
    private Date createTime;

@TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

2, Implement meta object processor interface

package com.example.demomptest.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

public class MyMetaObjectHandler implements MetaObjectHandler {

    // mp performs the add operation. This method performs
    public void insertFill(MetaObject metaObject){
        this.setFieldValByName("createTime",new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);

    // mp performs the modification operation. This method performs
    public void updateFill(MetaObject metaObject){
        this.setFieldValByName("updateTime",new Date(),metaObject);

Optimistic lock

Scenario: when a record is to be updated, it is hoped that the record has not been updated by others, that is, thread safe data update is realized
Optimistic lock implementation
1. When fetching records, get the current version
2. When updating, bring this version
3. When updating, set version = newVersion where version = oldVersion
4. If the version is incorrect, the update fails
Special note
The only supported data types are: int, Integer, long, long, Date, Timestamp, LocalDateTime

newVersion = oldVersion + 1 under integer type

Optimistic lock implementation process

1. Modify entity class

    private Integer version;

2. Create a profile

Create the package config and create the file mybatisplusconfig java

3. Register beans in MybatisPlusConfig

public class MpConfig {
     * Optimistic lock plug-in
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();

Conditional constructor (Wrapper)

Scenario: Wrapper is required to write some complex SQL statements

1, Test ge, gt, le, lt, isNull, isNotNull

Find the user with age > = 22

   // Wrapper test
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();"age",22);
        List<User> users = usermapper.selectList(queryWrapper);

2, Test eq, ne

Find the user with name = Lucy

	// Test eq
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        User user = usermapper.selectOne(queryWrapper);

3, Test between, not between

Test users aged 18 to 22

// Test between
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        List<User> users = usermapper.selectList(queryWrapper);

4, Test like, notLike, likeLeft, likereight

Test users with Zhang's name

 	// Test like
    public void testWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();"name","Zhang");
        List<User> users = usermapper.selectList(queryWrapper);

5, Test orderBy, orderByDesc, orderByAsc

Test age in descending order

	// Age in reverse order
    public void testSelectListOrderBy() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age", "id");
        List<User> users = usermapper.selectList(queryWrapper);

Query mode

Query modeexplain
setSqlSelectSet SELECT query field
whereWHERE statement, splice + WHERE condition
neNot equal to < >
gtGreater than >
geGreater than or equal to >=
ltLess than<
leLess than or equal to<=
likeFuzzy query LIKE
notLikeFuzzy query NOT LIKE
inIN query
notInNOT IN query
isNullNULL value query
havingHAVING keyword
orderBySort ORDER BY
orderByAscAscending sort
orderByDescDescending sort
betweenBETWEEN conditional statement
notBetweenNot between conditional statement

Topics: Java