1. Introduction

MyBatis-Plus (MP for short) is a MyBatis enhancement tool, on the basis of MyBatis only do enhancement do not change, to simplify the development and improve efficiency.

1.1. Characteristics

• No intrusion: only enhancements are made, no changes are made, and its introduction will not affect the existing project, as smooth as silk
• Low loss: Basic CURD will be injected automatically upon startup, with basically no loss in performance and direct object-oriented operation
• Powerful CRUD operations: built-in universal Mapper, universal Service, only through a small amount of configuration can achieve a single table most CRUD operations, more powerful condition constructor, to meet all types of use requirements
• Support Lambda form call: through Lambda expressions, it is convenient to write all kinds of query conditions, without worrying about field write errors
• Support automatic generation of primary keys: support up to four primary key policies (including distributed unique ID generator – Sequence), can be freely configured, perfect solution to the primary key problem
• Support for ActiveRecord mode: Support for ActiveRecord form calls, entity classes only need to inherit from Model classes to perform powerful CRUD operations
• Support custom global universal operations: support Write once (use anywhere)
• Built-in code generator: using code or Maven plug-in can quickly generate Mapper, Model, Service, Controller layer code, support template engine, more than a lot of custom configuration 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, write paging is equal to ordinary List query
• The paging plug-in supports a variety of databases: MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer, etc
• Built-in performance analysis plug-in: outputs 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: provides intelligent analysis and blocking of delete and UPDATE operations on all tables, and can customize interception rules to prevent misoperations

1.2. Database support

Any database that can use Mybatis for CRUD and supports standard SQL, the specific support is as follows.

• Mysql, Oracle, DB2, H2, HSQL, SQLite, PostgresQL, SQLServer, Phoenix, Gauss, Clickhouse, Sybase, OceanBase, Firebird, Cubrid, Goldilocks, csiidb
• Dameng Database, Virtual Valley database, DDA Jincang database, NTU General (Huaku) database, NDATong Database, Shentong database, Hangao database

1.3. Frame structure

2. Get started

2.1.pom.xml import MyBatis Plus dependency

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

2.2. Configure the data source in application.yml

# configure data source
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 logs to output more detailed log information
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
Copy the code

2.3. Create entity classes based on database tables

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

2.4. Create a Mapper interface

// Inherit the basic interface BaseMapper from the corresponding mapper
@ResponseBody // represents the persistence layer
public interface UserMapper extends BaseMapper<User> {
    // All CRUD operations have been written
    // You do not need to configure a lot of files as before
}
Copy the code

2.5. Test

To start the class, @mapperscan (“mapper package “) is required, otherwise the Mapper bean cannot be loaded

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

    @Test
    void contextLoads(a) {
        // The argument is a Wrapper, conditional constructor, we don't need null here
        // Query all users
        List<User> users = userMapper.selectList(null); users.forEach(System.out::println); }}Copy the code

So my SQL who wrote, method and where, in fact, are mybatis plus.

3. Configure logs

All of our SQL is now invisible, we want to know how it is executed, so we have to look at the log!

# config log
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
Copy the code

4.CRUD

4.1. Service CRUD interface

4.4.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);
Copy the code

4.1.2. SaveOrUpdate

// TableId annotation has update record, no insert record
boolean saveOrUpdate(T entity);
// Try updating according to updateWrapper, if not proceed with saveOrUpdate(T)
boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper);
// Batch modify inserts
boolean saveOrUpdateBatch(Collection<T> entityList);
// Batch modify inserts
boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize);
Copy the code

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 (delete by ID)
boolean removeByIds(Collection<? extends Serializable> idList);
Copy the code

4.1.4. The Update

// SqlSET is required to update records according to the UpdateWrapper condition
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);
Copy the code

4.1.5. Get

// Query by ID
T getById(Serializable id);
// Query a record according to Wrapper. The result set, if multiple, will throw an exception, randomly taking one wrapper with a constraint. Last ("LIMIT 1")
T getOne(Wrapper<T> queryWrapper);
// Query a record according to Wrapper
T getOne(Wrapper<T> queryWrapper, boolean throwEx);
// Query a record according to Wrapper
Map<String, Object> getMap(Wrapper<T> queryWrapper);
// Query a record according to Wrapper
<V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);
Copy the code

4.1.6. The List

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

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);
Copy the code

4.1.8. Count

// Query the total number of records
int count(a);
// Query the total number of records according to the Wrapper condition
int count(Wrapper<T> queryWrapper);
Copy the code

4.1.9. Chain

query

// the chain query is normal
QueryChainWrapper<T> query(a);
// query lambdas. Note: Kotlin is not supported
LambdaQueryChainWrapper<T> lambdaQuery(a); 

/ / sample:
query().eq("column", value).one();
lambdaQuery().eq(Entity::getId, value).list();
Copy the code

update

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

/ / sample:
update().eq("column", value).remove();
lambdaUpdate().eq(Entity::getId, value).update(entity);
Copy the code

4.2. The Mapper CRUD interface

2. Insert

// Insert a record
int insert(T entity);
Copy the code

4.2.2. Delete

// Delete the record according to the entity condition
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
// Delete (delete by 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);
Copy the code

4.2.3 the Update

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

4.2.4. Select

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

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

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

case

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

paging

Select * from user where name = 'wyl'
List<User> userList = user.selectPage(
        new Page<User>(1.10),
        new EntityWrapper<User>().eq("name"."wyl")
).getRecords();
Copy the code

In combination with

Select * from user where username = 'wyl', gender = male, age 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"));Copy the code

4.3 optional components of MAPper layer

AlwaysUpdateSomeColumnById

int alwaysUpdateSomeColumnById(T entity);
Copy the code

insertBatchSomeColumn

int insertBatchSomeColumn(List<T> entityList);
Copy the code

logicDeleteByIdWithFill

int logicDeleteByIdWithFill(T entity);
Copy the code

4.4. Conditional constructors

Very important: WappperWe write some complex SQL can use it instead! 1, test 1, remember to view the output of SQL for analysis

@Test
void contextLoads(a) {
    // Query the user 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 this to the map we just studied
}
Copy the code

2, test two, remember to view the output of SQL for analysis

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

Test 3

@Test
void test3(a){
    Select * from users aged between 19 and 30
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.between("age".19.30); / / range
    Integer count = userMapper.selectCount(wrapper);
    System.out.println(count);
}
Copy the code

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

// fuzzy query
@Test
void test4(a){
    Select * from users aged between 19 and 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);
}
Copy the code

Test 5

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

Test 6

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

5. Code generators

Dao, POJO, service and Controller are all written by me!

AutoGenerator is the code generator of MyBatis-Plus, through which the code of Entity, Mapper, Mapper XML, Service, Controller and other modules can be generated quickly. Greatly improved the development efficiency.

5.1. Importing dependencies

<! -- Code generator dependencies -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.3.1. TMP</version>
</dependency>
<! -- The generator needs to generate various components from the template, so the template also needs to be imported -->
<! Freemarker = Freemarker = Freemarker = Freemarker
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity</artifactId>
    <version>1.7</version>
</dependency>
Copy the code

5.2. Start the class by using either main or @test methods

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) {
        // A code automatic generator object needs to be built
        // Code generator
        AutoGenerator mpg = new AutoGenerator();
        // Configure the policy

        //1
        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);  // Whether to override
        gc.setServiceName("%sService"); // Go to the I prefix of Service
        gc.setIdType(IdType.ID_WORKER);
        gc.setDateType(DateType.ONLY_DATE);
        gc.setSwagger2(true);
        mpg.setGlobalConfig(gc);

        //2. Set the 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);

        // 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
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("user");    // Set the name of the table to be mapped
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true);    / / lombok automatically
        strategy.setLogicDeleteFieldName("deleted");
        // Automatically populate the 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 locking
        strategy.setVersionFieldName("version");
        strategy.setRestControllerStyle(true);
        strategy.setControllerMappingHyphenStyle(true);     //localhost:8080/hello_id_2
        mpg.setStrategy(strategy);

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

The above two steps can complete the code generation function!

Start scanning on class

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

Scan on test class

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

    @Autowired
    private UserMapper mapper;

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

5.3. Test the 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(a){
        ModelAndView mav = new ModelAndView();
        mav.setViewName("success");
        mav.addObject("list",userService.list());
        returnmav; }}Copy the code

5.3.2. Front end: HTML

Put it in templates

index.html

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

succuss.html

<! DOCTYPEhtml>
<html lang="en">
<! Thymeleaf template tag 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>Serial number</th>
        <th>The 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>
Copy the code

Thymeleaf template engine is used in the front end, and application. Yml needs to be configured

spring:
# view parsing
  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html
Copy the code

6.MybatisX fast development plug-in

MybatisX is a quick development plugin based on IDEA, designed for efficiency.

function

XML jump

Generate code (need to configure Database in IDEA first configure data source)

Reset the template

JPA prompt

Generate new

To generate the query

Generate change

Generate the delete

8. Optimistic locking

In the interview process, we are often asked about optimism lock, pessimism lock! This is actually very simple!

Atomic references!

Optimistic lock: as the name implies very optimistic, he always thinks there will be no problem, no matter what do not lock! If something goes wrong, update the value test again!

Pessimistic lock: as the name implies very pessimistic, he always tasks always appear problems, no matter what will be locked! Operate again!

We mainly explain here, optimistic locking mechanism!

Optimistic lock implementation:

• Retrieve the record to get the current version
• When you update, take this version with you
• When performing an update, set version = new Version where version = oldVersion
• If the version is incorrect, the update fails

Optimistic locking:1, obtain the version number version= 1
-- A
update user set name = "wyl", version = version + 1
where id = 2 and version = 1

If version = 2, it will cause A change failure.
update user set name = "wjm", version = version + 1
where id = 2 and version = 1
Copy the code

Test MP’s optimistic lock plugin

Add version to database!

// Test optimistic lock successfully!
@Test
public void testOptimisticLocker(a){
    //1. Query user information
    User user = userMapper.selectById(1330080433207046145L);
    //2. Modify user information
    user.setName("ChanV");
    user.setEmail("[email protected]");
    //3. Perform the update
    userMapper.updateById(user);
}

// Test optimistic lock failed! Under the multithreading
@Test
public void testOptimisticLocker2(a){
    / / thread 1
    User user = userMapper.selectById(5L);
    user.setName("ChanV111");
    user.setEmail("[email protected]");
    // Another thread performs queue jumping
    User user2 = userMapper.selectById(5L);
    user2.setName("ChanV222");
    user2.setEmail("[email protected]");
    userMapper.updateById(user2);

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

Copy the code

9. Global policy configuration:

As you can see from the example above, the entity class needs the @tablename annotation to specify the database TableName and the @tableid annotation to specify the id growth strategy. It doesn’t matter if there are fewer entity classes, but if there are more entity classes, there is trouble. So global policy configuration can be done in the spring-dao.xml file.

<! Mybatisplus global policy configuration -->
<bean id="globalConfiguration" class="com.baomidou.mybatisplus.entity.GlobalConfiguration">
        <! After version 2.3, the default value of camel name is true.
        <! --<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>
Copy the code

The configuration doesn’t work here, it needs to be injected into sqlSessionFactory to make it work. As follows:

<! Mybatisplus 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"/>
        <! Insert global configuration -->
        <property name="globalConfig" ref="globalConfiguration"/>
</bean>
Copy the code

This way, the @Tablename and @TableId annotations in the entity class can be removed.