1. 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.

1.1. Characteristics

• No invasion: it is only enhanced without change, and its introduction will not affect the existing project, which is as smooth as silk
• Low loss: the basic CURD will be injected automatically upon startup, with basically no loss of performance and direct object-oriented operation
• Powerful crud operation: built in general Mapper and general Service, most CRUD operations of a single table can be realized only through a small number of configurations, and there is a powerful condition constructor to meet various use requirements
• Support Lambda formal call: it is convenient to write various query conditions through Lambda expression, and there is no need to worry about wrong fields
• Support automatic generation of primary key: support up to 4 primary key strategies (including distributed unique ID generator - Sequence), which can be configured freely to perfectly solve the primary key problem
• Support ActiveRecord mode: support ActiveRecord formal calls. Entity classes only need to inherit Model classes to perform powerful CRUD operations
• Support custom global general operations: support global general method injection (Write once, use anywhere)
• Built in code generator: code or Maven plug-in can be used to quickly generate Mapper, Model, Service and Controller layer code, support template engine, and more custom configurations 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
• The paging plug-in supports multiple databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer and other databases
• 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 to quickly find out slow queries
• Built in global interception plug-in: it provides intelligent analysis and blocking of full table delete and update operations, and can also customize interception rules to prevent misoperation

1.2. Support database

Any database that can use mybatis for CRUD and supports standard SQL. The specific support is as follows. If you don't check the pagination tutorial PR in the following list, your support.

• mysql，oracle，db2，h2，hsql，sqlite，postgresql，sqlserver，Phoenix，Gauss ，clickhouse，Sybase，OceanBase，Firebird，cubrid，goldilocks，csiidb
• Dameng database, virtual Valley database, NPC Jincang database, NTU general (Huaku) database, NTU general database, Shentong database, Hangao database

1.3. Frame structure

2. Get started quickly

2.1.pom.xml import MyBatis Plus dependency

<dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>Latest Version</version>
</dependency>

2.2. Configure data source in application.yml

# Configure data sources
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/wyl? useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8 
    username: root
    password: 123456

# Configure log and output more detailed log information
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2.3. Create entity class according to database table

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

2.4. Create Mapper interface

//Inherit the basic interface BaseMapper on the corresponding mapper
@ResponseBody //Represents the persistence layer
public interface UserMapper extends BaseMapper<User> {
    //All CRUD operations have been written
    //You don't need a lot of configuration files like before
}

2.5. Test

The startup class needs to add @ mapperscan ("the package where the mapper is located"), otherwise the mapper bean cannot be loaded

@SpringBootTest
class MybatisPlusApplicationTests {
    //It inherits BaseMapper, and all methods come from the parent class
    //We can also write our own extension methods
    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        //The parameter is a Wrapper and a condition constructor. We don't need null here
        //Query all users
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }
}

Who wrote my sql and where did my methods go? In fact, they are all mybatis plus.

3. Configure log

All our sql is invisible now. We want to know how it is executed, so we must look at the log!

# Configuration log
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

4.CRUD

4.1.Service CRUD interface

4.1.1.Save

// Insert a record (select field, policy insert)
boolean save(T entity);
// Insert (batch)
boolean saveBatch(Collection<T> entityList);
// Insert (batch)
boolean saveBatch(Collection<T> entityList, int batchSize);

4.1.2.SaveOrUpdate

// Update record exists in TableId annotation. Insert a record
boolean saveOrUpdate(T entity);
// If the updateWrapper attempts to update, do you want to continue with the saveOrUpdate(T) method
boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper);
// Batch modify insert
boolean saveOrUpdateBatch(Collection<T> entityList);
// Batch modify insert
boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize);

4.1.3.Remove

// Delete the record according to the entity condition
boolean remove(Wrapper<T> queryWrapper);
// Delete by ID
boolean removeById(Serializable id);
// Delete the record according to the columnMap condition
boolean removeByMap(Map<String, Object> columnMap);
// Delete (batch delete according to ID)
boolean removeByIds(Collection<? extends Serializable> idList);

4.1.4.Update

// According to the UpdateWrapper condition, sqlset needs to be set for updating records
boolean update(Wrapper<T> updateWrapper);
// Update the record according to the whereWrapper condition
boolean update(T updateEntity, Wrapper<T> whereWrapper);
// Select modify according to ID
boolean updateById(T entity);
// Batch update by ID
boolean updateBatchById(Collection<T> entityList);
// Batch update by ID
boolean updateBatchById(Collection<T> entityList, int batchSize);

4.1.5.Get

// Query by ID
T getById(Serializable id);
// Query a record according to the Wrapper. If there are multiple result sets, exceptions will be thrown. Take one at random and add the limiting condition wrapper.last("LIMIT 1")
T getOne(Wrapper<T> queryWrapper);
// Query a record according to the Wrapper
T getOne(Wrapper<T> queryWrapper, boolean throwEx);
// Query a record according to the Wrapper
Map<String, Object> getMap(Wrapper<T> queryWrapper);
// Query a record according to the Wrapper
<V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

4.1.6.List

// Query all
List<T> list();
// Query list
List<T> list(Wrapper<T> queryWrapper);
// Query (batch query by ID)
Collection<T> listByIds(Collection<? extends Serializable> idList);
// Query (based on columnMap criteria)
Collection<T> listByMap(Map<String, Object> columnMap);
// Query all lists
List<Map<String, Object>> listMaps();
// Query list
List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper);
// Query all records
List<Object> listObjs();
// Query all records
<V> List<V> listObjs(Function<? super Object, V> mapper);
// Query all records according to Wrapper conditions
List<Object> listObjs(Wrapper<T> queryWrapper);
// Query all records according to Wrapper conditions
<V> List<V> listObjs(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);

4.1.7.Page

// Unconditional paging query
IPage<T> page(IPage<T> page);
// Conditional paging query
IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper);
// Unconditional paging query
IPage<Map<String, Object>> pageMaps(IPage<T> page);
// Conditional paging query
IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);

4.1.8.Count

// Total records queried
int count();
// Query the total number of records according to Wrapper conditions
int count(Wrapper<T> queryWrapper);

4.1.9.Chain

query

// Chain query general
QueryChainWrapper<T> query();
// Chained query lambda. Note: Kotlin is not supported
LambdaQueryChainWrapper<T> lambdaQuery(); 

// Example:
query().eq("column", value).one();
lambdaQuery().eq(Entity::getId, value).list();

update

// Chain change normal
UpdateChainWrapper<T> update();
// Chain change lambda. Note: Kotlin is not supported 
LambdaUpdateChainWrapper<T> lambdaUpdate();

// Example:
update().eq("column", value).remove();
lambdaUpdate().eq(Entity::getId, value).update(entity);

4.2 mapper crud interface

4.2.1.Insert

// Insert a record
int insert(T entity);

4.2.2.Delete

// Delete the record according to the entity condition
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
// Delete (batch delete according to ID)
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// Delete by ID
int deleteById(Serializable id);
// Delete the record according to the columnMap condition
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

4.2.3.Update

// Update the record according to the whereWrapper condition
int update(@Param(Constants.ENTITY) T updateEntity, @Param(Constants.WRAPPER) Wrapper<T> whereWrapper);
// Modify according to ID
int updateById(@Param(Constants.ENTITY) T entity);

4.2.4.Select

// Query by ID
T selectById(Serializable id);
// Query a record according to the entity condition
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// Query (batch query by ID)
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// Query all records according to the entity condition
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// Query (based on columnMap criteria)
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
// Query all records according to Wrapper conditions
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// Query all records according to Wrapper conditions. Note: only the value of the first field is returned
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// Query all records (and turn the page) according to the entity condition
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// Query all records (and turn pages) according to Wrapper conditions
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// Query the total number of records according to Wrapper conditions
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

case

List<User> userList1 = user.selectList(
        new EntityWrapper<User>().eq("name", "Wang Yanling")
);

paging

// Query 10 user records named 'wyl' in pages
List<User> userList = user.selectPage(
        new Page<User>(1, 10),
        new EntityWrapper<User>().eq("name", "wyl")
).getRecords();

combination

// Query 10 user records whose name is' wyl ', gender is male, and age is between 18 and 50
List<User> userList = userMapper.selectPage(
        new Page<User>(1, 10),
        new EntityWrapper<User>().eq("name", "wyl")
                .eq("sex", 0)
                .between("age", "18", "50")
);

4.3.mapper layer options

AlwaysUpdateSomeColumnById

int alwaysUpdateSomeColumnById(T entity);

insertBatchSomeColumn

int insertBatchSomeColumn(List<T> entityList);

logicDeleteByIdWithFill

int logicDeleteByIdWithFill(T entity);

4.4. Conditional constructor

Very important: wapper

We can write some complex SQL and use it instead!

1. Test one, remember to view the output SQL for analysis

@Test
void contextLoads() {
    //Query users whose name is not empty and whose mailbox is not empty, and whose age is greater than 12
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.isNotNull("name")
            .isNotNull("email")
            .ge("age", 12);
    userMapper.selectList(wrapper).forEach(System.out::println); //Compare it with the map we just learned
}

2. In test 2, remember to view the output SQL for analysis

@Test
void test2(){
    //Query name Chanv
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("name", "Chanv");
    User user = userMapper.selectOne(wrapper);
    System.out.println(user);
}

3. Test three

@Test
void test3(){
    //Query users aged 19 to 30
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.between("age", 19, 30); //section
    Integer count = userMapper.selectCount(wrapper);
    System.out.println(count);
}

4. Test 4, remember to view the output SQL for analysis

//Fuzzy query
@Test
void test4(){
    //Query users aged 19 to 30
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //left and right
    wrapper.notLike("name", "b")
            .likeRight("email", "t");
    List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
    maps.forEach(System.out::println);
}

5. Test five

@Test
void test5(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //id is found in the sub query
    wrapper.inSql("id", "select id from user where id < 3");
    List<Object> objects = userMapper.selectObjs(wrapper);
    objects.forEach(System.out::println);
}

6. Test six

@Test
void test6(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //Sort by id
    wrapper.orderByDesc("id");
    List<User> users = userMapper.selectList(wrapper);
    users.forEach(System.out::println);
}

5. Code generator

dao, pojo, service and controller are all written by myself!

AutoGenerator is the code generator of mybatis plus. Through AutoGenerator, you can quickly generate the code of Entity, Mapper, Mapper XML, Service, Controller and other modules, which greatly improves the development efficiency.

5.1. Import dependency

<!-- Code generator dependency -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.3.1.tmp</version>
</dependency>
<!-- The generator needs to generate various components according to the template, so the template also needs to be imported -->
<!-- velocity It is the default template. In addition to it, common templates include: Freemarker,Beetl -->
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity</artifactId>
    <version>1.7</version>
</dependency>

5.2. Start class. Any main or @ Test method is OK

package com.wyl.mybatisplus;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
public class Code {
    public static void main(String[] args) {
        //You need to build an automatic code generator object
        // Code generator 
        AutoGenerator mpg = new AutoGenerator();
        //Configuration policy

        //1. Global configuration
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("ChanV");
        gc.setOpen(false);
        gc.setFileOverride(false);  //Overwrite
        gc.setServiceName("%sService"); //I prefix to Service
        gc.setIdType(IdType.ID_WORKER);
        gc.setDateType(DateType.ONLY_DATE);
        gc.setSwagger2(true);
        mpg.setGlobalConfig(gc);

        //2. Set data source
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/mybatis-plus?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8");
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("root");
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        //3. Package configuration
        PackageConfig pc = new PackageConfig();
        pc.setModuleName("blog");
        pc.setParent("com.chanv");
        pc.setEntity("pojo");
        pc.setMapper("mapper");
        pc.setService("service");
        pc.setController("controller");
        mpg.setPackageInfo(pc);

        //4. Policy configuration
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("user");    //Set the table name to map
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true);    //Automatic lombok
        strategy.setLogicDeleteFieldName("deleted");
        //Auto fill configuration
        TableFill createTime = new TableFill("create_time", FieldFill.INSERT);
        TableFill updateTime = new TableFill("update_time", FieldFill.UPDATE);
        ArrayList<TableFill> tableFills = new ArrayList<>();
        tableFills.add(createTime);
        tableFills.add(updateTime);
        strategy.setTableFillList(tableFills);
        //Optimistic lock
        strategy.setVersionFieldName("version");
        strategy.setRestControllerStyle(true);
        strategy.setControllerMappingHyphenStyle(true);     //localhost:8080/hello_id_2
        mpg.setStrategy(strategy);

        mpg.execute();  //Execute code constructor
    }
}

The above two steps can complete the function of generating code!

Start scan on class

@SpringBootApplication  // Startup class
@MapperScan(value = {"com.wyl.mybatisplus.generator.mapper"})  // Scan mapper
public class MybatisplusApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisplusApplication.class, args);
    }
}

Scan on test class

@SpringBootTest
@MapperScan(value = {"com.wyl.mybatisplus.generator.mapper"})  // @Mapperscan ("package location of mapper")
class UserServiceTest {

    @Autowired
    private UserMapper mapper;

    @Test
    public void test(){
        mapper.selectList(null).forEach(System.out::println);
    }
}

5.3. Test MVC

5.3.1. Back end: controller.java

package com.wyl.mybatisplus.generator.controller;
import com.wyl.mybatisplus.generator.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.stereotype.Controller;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/generator/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/success")
    public ModelAndView index(){
        ModelAndView mav = new ModelAndView();
        mav.setViewName("success");
        mav.addObject("list",userService.list());
        return mav;
    }
}

5.3.2. Front end: html

Remember to put it under templates

index.html

<h1>Index Page...</h1>
<a href="/generator/user/success">Display data</a>

succuss.html

<!DOCTYPE html>
<html lang="en">
<!-- to configure thymeleaf Template label Library -->
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Success Page</title>
</head>
<body>

<h1>Success Page...</h1>

<table border="1" cellspacing="0" cellpadding="1">
    <tr>
        <th>number</th>
        <th>user name</th>
        <th>Age</th>
    </tr>
    <tr th:each="user:${list}">
        <td th:text="${user.id}"></td>
        <td th:text="${user.userName}"></td>
        <td th:text="${user.userAge}"></td>
    </tr>
</table>

</body>
</html>

The thymeleaf template engine is used in the front end, and application.yml needs to be configured

spring:
# View resolution
  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html

6.MybatisX rapid development plug-in

MybatisX is a rapid development plug-in based on IDEA, which is born for efficiency.

function

XML jump

Generate code (you need to configure the Database data source in idea first)

Reset template

JPA tips

Generate new

Generate query

Generate modification

Generate delete

8. Optimistic lock

During the interview, we are often asked about optimistic lock and pessimistic lock! This is actually very simple!

Atomic reference!

Optimistic lock: as the name suggests, he is very optimistic. He always thinks there will be no problem. No matter what he does, he doesn't lock it! If there is a problem, update the value test again!

Pessimistic lock: as the name suggests, he is very pessimistic. He always has problems with his tasks. No matter what he does, he will lock it! Do it again!

Here we mainly explain the optimistic lock mechanism!

Optimistic lock implementation method:

• Take out the record and get the current version
• When updating, bring this version
• When updating, set version = new version where version = oldversion
• If the version is incorrect, the update fails

Optimistic lock: 1. Query first to obtain the version number version = 1
-- A
update user set name = "wyl", version = version + 1
where id = 2 and version = 1

-- B The thread finishes first. At this time version = 2，Will cause A Modification failed!
update user set name = "wjm", version = version + 1
where id = 2 and version = 1

Test MP's optimistic lock plug-in

1. Add version field to the database!

//Successful test!
@Test
public void testOptimisticLocker(){
    //1. Query user information
    User user = userMapper.selectById(1330080433207046145L);
    //2. Modify user information
    user.setName("ChanV");
    user.setEmail("1277077741@qq.com");
    //3. Perform update operation
    userMapper.updateById(user);
}

//Failed to test optimistic lock! Multithreading
@Test
public void testOptimisticLocker2(){
    //Thread 1
    User user = userMapper.selectById(5L);
    user.setName("ChanV111");
    user.setEmail("1277077741@qq.com");
    //Simulate another thread to perform queue jumping
    User user2 = userMapper.selectById(5L);
    user2.setName("ChanV222");
    user2.setEmail("1277077741@qq.com");
    userMapper.updateById(user2);

    //Spin lock multiple attempts to commit
    userMapper.updateById(user);    //If there is no optimistic lock, the value of the queue thread will be overwritten
}

9. Global policy configuration:

Through the above small case, we can find that the entity class needs to add the @ TableName annotation to specify the database table name and the growth strategy of id through the @ TableId annotation. It doesn't matter if there are fewer entity classes. It's troublesome if there are more entity classes. Therefore, the global policy can be configured in the spring-dao.xml file.

<!-- 5,mybatisplus Global policy configuration for -->
<bean id="globalConfiguration" class="com.baomidou.mybatisplus.entity.GlobalConfiguration">
        <!-- 2.3 After version, the default value of hump naming is true，Therefore, it can not be configured -->
        <!--<property name="dbColumnUnderline" value="true"/>-->
        <!-- Global primary key auto increment policy, 0 indicates auto -->
        <property name="idType" value="0"/>
        <!-- Global table prefix configuration -->
        <property name="tablePrefix" value="tb_"/>
</bean>

The configuration here is useless. The configuration needs to be injected into sqlSessionFactory before it takes effect. As follows:

<!-- 3,to configure mybatisplus of sqlSessionFactory -->
<bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <property name="typeAliasesPackage" value="com.zhu.mybatisplus.entity"/>
        <!-- Inject global configuration -->
        <property name="globalConfig" ref="globalConfiguration"/>
</bean>

In this way, the @ TableName annotation and @ TableId annotation in the entity class can be removed.