[Long Java Road] mybatis-plus

mybatis-plus

Getting Started

build table

CREATE TABLE USER
(
    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)
);

INSERT INTO USER (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');

import 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>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.1</version>
    </dependency>
    <!--import mysql drive-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
    </dependency>
    <!--Simplified entity class,use@Data replace getset method-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.8</version>
    </dependency>

Data source configuration

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8
    username: root
    password: mzf200314
    type: com.zaxxer.hikari.HikariDataSource

pojo

package com.fate.demo1.pojo;

import lombok.Data;

/**
 * @author m
 */
@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;

    public User(Long id, String name, Integer age, String email) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
    }
}

mapper

Inherit directly

package com.fate.demo1.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fate.demo1.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

/**
 * @author m
 */
@Repository
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

Enable log information

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

Add to

@Test
void add(){
    User user = new User(null, "fate", 17, "asdhjfgah");
    System.out.println(mapper.insert(user));
    System.out.println(user);
}

The primary key will be obtained (the default snowflake algorithm generates id)

delete

@Test
void delete(){
    int i = mapper.deleteById(1521849298382331905L);
    System.out.println(i);
}
@Test
void deleteByMap(){
    HashMap<String, Object> stringObjectHashMap = new HashMap<>();
    //Will match the conditions set in the map to delete operations
    stringObjectHashMap.put("name","Zhang San");
    stringObjectHashMap.put("age",16);
    int i = mapper.deleteByMap(stringObjectHashMap);
    System.out.println(i);
}
@Test
void deleteB(){
    //batch deletion
    LinkedList<Integer> integers = new LinkedList<>();
    for (int i = 9; i < 100; i++) {
        integers.add(i);
    }
    int i = mapper.deleteBatchIds(integers);
    System.out.println(i);
}

Revise

@Test
void update(){
    //If the property is null, the property will not be updated
    User fate = new User(11L, "fate", null, null);
    int i = mapper.updateById(fate);
}

find

@Test
void select(){
    //query by id
    User user = mapper.selectById(1);
    //Query by id collection
    List<Long> asList = Arrays.asList(1L, 2L, 3L, 4L);
    List<User> users = mapper.selectBatchIds(asList);
    //Query according to the conditions in the map
    HashMap<String, Object> map = new HashMap<>();
    map.put("name","fate");
    List<User> userList = mapper.selectByMap(map);
    //Query by Condition Constructor
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    mapper.selectList(wrapper);
    //Custom query (how to write mybatis, how to write it)
    Map<String, Object> mapById = mapper.selectMapById(1L);
}

Service CRUD interface

  • General Service CRUD encapsulates the IService (opens new window) interface, and further encapsulates CRUD using get query single row remove delete list query collection page paging Prefix naming distinguishes Mapper layer to avoid confusion

  • The generic type T is any entity object

  • It is recommended that if there is a possibility to customize the general Service method, please create your own IBaseService to inherit the base class provided by Mybatis-Plus

  • Object Wrapper as Conditional Constructor

public interface UserService extends IService<User> {
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}

test

get records

@Test
void getCount(){
    System.out.println(userService.count());
}

Bulk add

@Test
void insert(){
    //Bulk add
    LinkedList<User> users = new LinkedList<User>();
    for (int i = 0; i < 10; i++) {
        User user = new User(null, "test" + i, i, i + "@fate.com");
        users.add(user);
    }
    userService.saveBatch(users);
}

The bottom layer adopts the method of adding each record separately

Common Notes

@TableName annotation

Used to identify the corresponding table name of the entity class in the database

@Data
@TableName("user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;

    public User(Long id, String name, Integer age, String email) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.email = email;
    }
}

Use global configuration, configure table name prefix

global-config:
  db-config:
    table-prefix: prefix

@TableId annotation

  • Used to identify the primary key (mp defaults to id as the primary key)
  • Through the value attribute, you can set the corresponding field name of the attribute in the table
  • The type attribute indicates the strategy for primary key generation
    • IdType.AUTO (commonly used): The database ID is automatically incremented
      This type needs to ensure that the database is set with ID auto-increment, otherwise it is invalid
    • IdType.NONE: The type is not set the primary key type
    • IdType.INPUT: The user enters an ID, which can be filled by registering an auto-fill plugin by itself
    • IdType.ASSIGN_ID (commonly used): Assign ID (primary key type is number or string), default implementation class com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator (snowflake algorithm)
    • IdType.ASSIGN_UUID: Assign UUID (primary key type is string) Default implementation class com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator(UUID.replace("-",""))

The primary key generation strategy can be modified using the global configuration

[External link image transfer failed, the source site may have anti-leech mechanism, it is recommended to save the image and upload it directly (img-xpvfyJC3-1651797024023)(https://gitee.com/fatem/markdown_imgs/raw/master/img/202205051101322 .png)]

Snowflake algorithm

  1. background
    It is necessary to choose an appropriate solution to cope with the growth of data scale, in order to cope with the gradually increasing access pressure and data volume
    The expansion methods of the database mainly include: business sub-database, master-slave replication, database sub-table
  2. . Database sub-table
    Distributed storage of different business data in different database servers can support businesses with a scale of millions or even tens of millions of users, but if the business continues to develop, the single-table data of the same business will also reach the processing bottleneck of a single database server. For example, if Taobao's hundreds of millions of user data are all stored in one table of a database server, it will definitely not meet the performance requirements. At this time, it is necessary to split the single-table data.
    There are two ways to split single-table data: vertical splitting and horizontal splitting. The schematic diagram is as follows: [The external link image transfer failed, the source site may have an anti-leech mechanism, it is recommended to save the image and upload it directly (img-XPz9g0RH-1651797024024)(https://gitee.com/fatem/markdown_imgs/raw/master/ img/202205051109112.png)]
  3. vertical table
    Vertical split table is suitable for splitting out some columns in the table that are not commonly used and take up a lot of space
    For example, the nickname and description fields in the previous schematic diagram, assuming we are a dating website, when users filter other users, they mainly use the two fields of age and sex to query, while the two fields of nickname and description are mainly used for display, Generally not used in business queries. The description itself is relatively long, so we can separate these two fields into another table, so that when querying age and sex, it can bring a certain performance improvement
  4. horizontal sub-table
    Horizontal sub-tables are suitable for tables with a particularly large number of rows. Some companies require table sub-tables if the number of rows in a single table exceeds 50 million. This number can be used as a reference, but it is not an absolute standard. The key is to look at the access performance of the table. For some more complex tables, it may be more than 10 million to be divided into tables. For some simple tables, even if the stored data exceeds 100 million rows, it is not necessary to divide the table.
    But in any case, when the data volume of the table reaches tens of millions, you should be alert as an architect, because this is likely to be a performance bottleneck or hidden danger of the architecture.
    Compared with vertical sub-tables, horizontal sub-tables will introduce more complexity, such as how to deal with globally unique data id s
  5. Primary key auto increment
    Taking the most common user ID as an example, it can be segmented according to the range size of 1000000, 1 ~ 999999 is placed in Table 1, 1000000 ~ 1999999 is placed in Table 2, and so on
    Complex point: the selection of segment size. If the segment is too small, there will be too many sub-tables after the segmentation, which will increase the maintenance complexity; if the segment is too large, there may still be performance problems in a single table. Generally, it is recommended that the segment size be between 1 million and 20 million. Select the appropriate segment size according to the business.
    Advantages: New tables can be expanded smoothly as data increases. For example, the current number of users is 1 million. If it increases to 10 million, it is only necessary to add a new table, and the original data does not need to be moved.
    Disadvantage: uneven distribution. If the table is divided according to 10 million, it is possible that a certain segment actually stores only one piece of data, while another segment actually stores 10 million pieces of data.
  6. modulo
    Also taking user ID as an example, if we planned 10 database tables from the beginning, we can simply use the value of user_id%10 to represent the database table number to which the data belongs, and the user with ID 985 is placed in the sub-table numbered 5. , the user whose ID is 10086 is placed in the sub-table numbered 6.
    Complex point: the determination of the number of initial tables. Too many tables are more troublesome to maintain, and too few tables may cause performance problems for a single table.
    Advantages: The table distribution is relatively uniform.
    Disadvantages: Expanding new tables is cumbersome, and all data must be redistributed.
  7. Snowflake Algorithm: It is a distributed primary key generation algorithm published by Twitter, which can ensure the non-repetition of the primary keys of different tables and the ordering of the primary keys of the same table
    A total of 64 bits in length (a long type).
    The first is a sign bit, 1bit identification, because the long basic type is signed in Java, the highest bit is the sign bit, positive numbers are 0, and negative numbers are 1, so id is generally positive, and the highest bit is 0.
    41bit time cutoff (millisecond level), which stores the difference of time cutoff (current time cutoff - start time cutoff), and the result is approximately equal to 69.73 years.
    10 bits are used as the machine ID(5 bits are the data center, 5 bits are the machine ID, which can be deployed on 1024 nodes).
    12bit as the serial number in milliseconds (meaning that each node can generate 4096 IDs per millisecond)

@TableField("name")

The field name of the attribute in the database represented by the @TableField annotation

@TableLogic (logical deletion)

When setting this annotation, calling the delete method will only set the corresponding property to 1, and will not actually delete it

wrapper

Know your name

package com.fate.demo1.wapper;

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.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.fate.demo1.mapper.UserMapper;
import com.fate.demo1.pojo.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;
import java.util.Map;

@SpringBootTest
public class WapperTest {
    @Autowired
    private UserMapper userMapper;
    @Test
    void select(){
        //Conditional query
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name","1")
                .between("age",1,10)
                .isNotNull("email");

        userMapper.selectList(wrapper).forEach(System.out::println);
    }
    @Test
    void select1(){
//        SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 ORDER BY age ASC,id ASC
//        Sort query
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("age").orderByAsc("id");
        userMapper.selectList(wrapper).forEach(System.out::println);
    }
    @Test
    void delete(){
//        UPDATE user SET is_deleted=1 WHERE is_deleted=0 AND (age BETWEEN ? AND ?)
//        Because the logical deletion is set, the delete statement becomes an update
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between("age",0,1);
        userMapper.delete(wrapper);
    }
    @Test
    void update(){
//        UPDATE user SET age=? WHERE is_deleted=0 AND (age > ? AND email IS NOT NULL)
//        Condition modification, modify the required parameters, passed in by the entity class
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",20).isNotNull("email");
        User user = new User(null,null,100,null);
        userMapper.update(user,queryWrapper);
    }
    @Test
    void update1(){
        //Condition in lambda expression takes precedence
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.and(i->i.gt("age",99));
    }
    @Test
    void selectForSome(){
//        SELECT name,age FROM user WHERE is_deleted=0
//        Only some of the specified fields were queried
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name","age");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }
    @Test
    void selectSon(){
//        subquery
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id","SELECT id FROM user");
        userMapper.selectList(queryWrapper).forEach(System.out::println);
    }
    @Test
    void updateByWrapper(){
//        UPDATE user SET email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR age BETWEEN ? AND ?))
//        Modify the properties directly in the wrapper
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.like("name","test").
                and(i->i.gt("age",5).or().between("age",10,100));
        updateWrapper.set("email","12345@test.com");
        userMapper.update(null,updateWrapper);
    }
    @Test
    void condition(){
//        SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0
        String name=null;
//        SELECT id,name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (name LIKE ?)
        name="test";
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(name),"name",name);
        userMapper.selectList(queryWrapper).forEach(System.out::println);
    }
    @Test
    void lambdaQueryWrapper(){
        //The basic use is almost the same (change the field name to User::getName format) LambdaUpdateWrapper is the same
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.like(User::getName,"test");
        userMapper.selectList(wrapper).forEach(System.out::println);
    }
}

plugin

You need to create a configuration class before adding a plugin

@Configuration
@MapperScan("com.fate.demo1.mapper")
public class MPConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //Add paging plugin (set database type)
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return mybatisPlusInterceptor;
    }
}

Pagination plugin

@Test
void page(){
    Page<User> userPage = new Page<>(1,5);
    mapper.selectPage(userPage,null).getRecords().forEach(System.out::println);
    System.out.println(userPage);
}

Intercept SQL and add pagination statement

Pagination for custom queries

IPage<User> getUserToPage(@Param("page") IPage<User> page);
<select id="getUserToPage" resultType="com.fate.demo1.pojo.User">
    select id,name,age,email from user
</select>
@Test
void page2(){
    Page<User> userPage = new Page<>(1,5);
    mapper.getUserToPage(userPage).getRecords().forEach(System.out::println);
    System.out.println(userPage);
}

mp will automatically identify whether your method needs to return paginated data

optimistic locking

Scenes

A commodity, the cost price is 80 yuan, the price is 100 yuan. The boss first notified Xiao Li that you should increase the price of the product by 50 yuan. Xiao Li was playing a game and was delayed for an hour. Exactly an hour later, the boss felt that the price of the product had increased to 150 yuan, which was too high and might affect sales. He also informed Xiao Wang that you reduced the price of the product by 30 yuan.
At this time, Xiao Li and Xiao Wang operate the commodity back-end system at the same time. When Xiao Li is operating, the system first takes out the commodity price of 100 yuan; Xiao Wang is also operating, and the price of the commodity he takes out is also 100 yuan. Xiao Li added 50 yuan to the price and saved 100+50=150 yuan into the database; Xiao Wang reduced the product by 30 yuan and saved 100-30=70 yuan into the database. Yes, if there is no lock, Xiao Li's operation is completely covered by Xiao Wang's.
Now the commodity price is 70 yuan, 10 yuan lower than the cost price. A few minutes later, this item quickly sold more than 1,000 items, and the boss lost more than 10,000.

Optimistic locking and pessimistic locking

In the above story, if it is an optimistic lock, Xiao Wang will check whether the price has been modified before saving the price. If it has been modified, the revised price will be taken out again, 150 yuan, so that he will store 120 yuan in the database.
If it is a pessimistic lock, after Xiao Li takes out the data, Xiao Wang can only operate the price after Xiao Li has completed the operation, and the final price will also be guaranteed to be 120 yuan.

Simulate Modification Conflicts

CREATE TABLE t_product
(
id BIGINT(20)NOT NULL COMMENT'primary key ID',
NAME VARCHAR(30)NULL DEFAULT NULL COMMENT'product name',
price INT(11) DEFAULT 0 COMMENT'price',
VERSION INT(11)DEFAULT 0 COMMENT'optimistic lock version number',PRIMARY KEY(id)
);

pojo,Mapper,service three-piece set

@TableName("t_product")
@Data
public class Product {
    private Long id;
    private String name;
    private Integer price;
    private Integer version;

    public Product() {
    }

    public Product(Long id, String name, Integer price, Integer version) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.version = version;
    }
}

@Version

Represents the version number (representing the identification field of optimistic locking)

Add plugin

@Configuration
@MapperScan("com.fate.demo1.mapper")
public class MPConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //Add paging plugin (set database type)
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        //Add optimistic locking plugin
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

Generic enumeration

@EnumValue

Add the corresponding value of the enumeration to the database

#Scan packages for generic enumeration
  type-enums-package:  com.fate.demo1.enums

Code generator

rely

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.31</version>
</dependency>

run the code

public class FastAutoGeneratorTest {
public static void main(String[] args) {
    FastAutoGenerator.create("jdbc:mysql://127.0.0.1:3306/mybatis_plus´╝čcharacterEncoding=utf-8&userSSL=false", "root", "123456").globalConfig(builder -> {
                    builder.author("atguigu") // set author
                    //.enableSwagger() // Enable swagger mode
                    .fileOverride() // Overwrite generated files
                    .outputDir("D://mybatis_plus"); // specify the output directory
                    })
                    .packageConfig(builder -> {
                    builder.parent("com.atguigu") // set parent package name
                    .moduleName("mybatisplus") // Set parent package module name
                    .pathInfo(Collections.singletonMap(OutputFile.mapperXml,                  
                               "D://mybatis_plus"));
                    // Set mapperXml generation path
                    })
                    .strategyConfig(builder -> {
                    builder.addInclude("t_user") // Set the name of the table to be generated
                    .addTablePrefix("t_", "c_"); // set filter table prefix
                    })
                    .templateEngine(new FreemarkerTemplateEngine()) // Use Freemarker
                    Engine template, the default is Velocity engine template
                    .execute();
        }
}

Multiple data sources

Create database mybatis_plus_1 and table product

CREATE DATABASE mybatis_plus_1;

USE mybatis_plus_1;


CREATE TABLE product
(
    id BIGINT(20) NOT NULL COMMENT 'primary key id',
    NAME VARCHAR(30) NULL DEFAULT NULL COMMENT 'product name',
    price INT(11) DEFAULT 0 COMMENT 'price',
    VERSION INT(11) DEFAULT 0 COMMENT 'optimistic lock version number',
    PRIMARY KEY (id)
);


rely

<dependency>
	<groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.0</version>
</dependency>

configure

spring:
  # Configure data source information
  datasource:
    dynamic:
      # Set the default data source or data source group, the default value is master
      primary: master
      # Strictly match the data source, the default is false, true throws an exception if it does not match the specified data source, false uses the default data source
      strict: false
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&serverTimezone=GMT%2B8
          driver-class-name: com.mysql.cj.jdbc.Driver
          username: root
          password: root
        save_1:
          url: jdbc:mysql://localhost:3306/mybatis_plus_1?useSSL=false&serverTimezone=GMT%2B8
          driver-class-name: com.mysql.cj.jdbc.Driver
          username: root
          password: root

service

@DS("master")
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}

@DS("slave_1")
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements ProductService {
}

Tags: Java MySQL intellij-idea

Posted by loureiro on Fri, 06 May 2022 04:42:39 +0300