MyBatisPlus - getting started notes

MyBatisPlus

MyBatisPlus

quick get start

Official website document address: https://baomidou.gitee.io/mybatis-plus-doc/#/install

To use a third-party component:

1. Import corresponding dependencies

2. Study how dependencies are configured

3. How to write code

4. Improve the ability to expand technology

step

1. Create database User table

id name age email
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 database Schema script is as follows:

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT 'Primary key ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT 'full name',
	age INT(11) NULL DEFAULT NULL COMMENT 'Age',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY (id)
);

The corresponding database Data script is as follows:

DELETE FROM user;

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');

Initialization project

Create a springboot project

have access to Spring Initializer Quickly initialize a Spring Boot project

Add dependency

Introduce spring boot starter parent project dependency:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.0.RELEASE</version>
    <relativePath/>
</parent>

Introduce spring boot starter, spring boot starter test, mybatis plus boot starter, lombok, mysql dependency:

<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>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.0.5</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

to configure

stay application.yml Add mysql basic configuration and project startup basic configuration to the configuration file

spring:
#Configure the thmeleaf template
  thymeleaf:
    cache: false #Open cache
    prefix: classpath:/templates/
    suffix: .html
    encoding: utf-8
  #Configure mysql data source
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://39.105.27.58/ssmarkert?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT
    username: root
    password: lovehxp521..

#Project start port
server:
  port: 8080

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

Add @ MapperScan annotation to Springboot startup class, and scan Mapper file:

@SpringBootApplication
@MapperScan("com.hxp.ssmkert.mapper")
public class SsmkertApplication {

    public static void main(String[] args) {
  SpringApplication.run(SsmkertApplication.class, args);
    }

}

code

Write entity class User.java (Lombok simplified code is used here)

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

Write Mapper interface UserMapper.java , all CRUD has been written for us

public interface UserMapper extends BaseMapper<User> {

}

Start using

Add test class and conduct integration test:

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void userMapperTest1(){
        List<User> users = userMapper.selectList(null);//Condition is null
        users.forEach(System.out::println);
    }
}

The parameter of the selectList() method in userMapper is the MP built-in condition Wrapper, so it is unconditional if it is not filled in

Console output:

User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)

Thinking?

1. Who wrote SQL for us? Mybatis plus is all written

2. Where did the method come from? Mybatis plus is all written

Configuration log

We don't know our mysql operation. We need to configure the log printout to help us develop tests.

stay appliaction.yml In file

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

Primary key policy of primary key

1. Use annotation to add primary key policy

Auto primary key

//Using annotations to add a primary key policy
@TableId(type = IdType.AUTO)//Primary key auto increment strategy
private Long id;

Note: Auto increment must be set for data table when using auto increment primary key

2. Configure the global primary key generation policy through the configuration file

Primary key policy list:

strategy explain
AUTO Database ID auto increment
NONE The type is not set as primary key type
INPUT The user enters the ID, which can be filled by registering the auto fill plug-in
ID_WORKER Global unique ID (idWorker)
UUID Global unique ID (UUID)
ID_WORKER_STR String globally unique ID (string representation of idworker)
AUTO(0),//Database ID auto increment
NONE(1),//This type is not set as the primary key type, and the new version update defaults to NONE
INPUT(2),//User input ID * this type can be filled in through self registration auto fill plug-in

/* The following three types are filled automatically only when the inserted object ID is empty. */
ID_WORKER(3),//Global unique ID (idWorker)
UUID(4),//Global unique ID (UUID)
ID_WORKER_STR(5);//String globally unique ID (string representation of idworker)

CRUD of mybatis plus

update

//Modify test
@Test
public void UpdateTest() {
    User user = new User();
    user.setId(6L);
    user.setName("A small potato");
    user.setAge(24);
    user.setEmail("2426712259@qq.com");
    int update = userMapper.updateById(user);//Modify according to the primary key id
    System.out.println(update);
}
/*Printout log
==>  Preparing: UPDATE user SET name=?, age=?, email=? WHERE id=? 
==> Parameters: A small potato (String), 24(Integer), 2426712259@qq.com(String), 6(Long)
<==    Updates: 1
*/


//Here, we only modify name, not other parameters
@Test
    public void UpdateTest2() {
        User user = new User();
        user.setId(6L);
        user.setName("A little pepper");
//        user.setAge(24);
//        user.setEmail("2426712259@qq.com");
        int update = userMapper.updateById(user);
        System.out.println(update);
    }
/*
==>  Preparing: UPDATE user SET name=? WHERE id=? 
==> Parameters: A string, 6 (long)
<==    Updates: 1
*/

Discovery:

Through two modification cases, we find that the way for us to implement mybatis plus is dynamic SQL

Auto fill

Creation time, modification time, and these operations are all automated. We do not want to update them manually.

Alibaba Development Manual: all database tables must be configured with these two fields: gmt_create,gmt_modified, and it needs to be automated.

Method 1: database level

1. Modify database

gmt_ create/create_ Time: datetime add default: CURRENT_TIMESTAMP
 gmt_ modified/update_ Time: datetime add default: CURRENT_TIMESTAMP check to update according to the current timestamp

2. Test again to synchronize entity class code

    private Date updateTime;
    private Date createTime;

Let's insert a new piece of data, create_time and update_time has been generated for us

We do not update the time manually when we perform the modification. Check the update_ Does time change

@Test
    public void UpdateTest() {
        //There is no change of time here
        User user = new User();
        user.setId(7L);
        user.setName("A little pepper");
        int update = userMapper.updateById(user);
        System.out.println(update);
    }

As a result of the execution, the time has changed.

Method 2: code level

Code auto fill

1. Delete the default value of the database

2. Use mybatis plus annotation

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

3. Create a custom listener

Before mybatis plus version 3.3.0:

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    //Fill in policy on insert
    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill");
        this.setFieldValByName("createTime",new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }

    //Populate policy on update
    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill");
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}

After mybatis plus version 3.3.0:

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill ....");
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now()); // From version 3.3.0 (recommended)
        this.fillStrategy(metaObject, "createTime", LocalDateTime.now()); // You can also use (3.3.0 if there is a bug in this method, please upgrade to a later version such as' 3.3.1.8-SNAPSHOT ')
        /* Select one of the above, and the following one is obsolete (note that strict insert fill has multiple methods, check the source code in detail) */
        //this.setFieldValByName("operator", "Jerry", metaObject);
        //this.setInsertFieldValByName("operator", "Jerry", metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill ....");
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); // From version 3.3.0 (recommended)
        this.fillStrategy(metaObject, "updateTime", LocalDateTime.now()); // You can also use (3.3.0 if there is a bug in this method, please upgrade to a later version such as' 3.3.1.8-SNAPSHOT ')
        /* Choose one of the above, and the following one is obsolete (note that strict update fill has multiple methods, check the source code in detail) */
        //this.setFieldValByName("operator", "Tom", metaObject);
        //this.setUpdateFieldValByName("operator", "Tom", metaObject);
    }
}

4. Insertion test

 //Test insert
    @Test
    public  void testInert(){
        User user = new User();
        user.setName("A cucumber");
        user.setAge(24);
        user.setEmail("15542256441@qq.com");
        int insert = userMapper.insert(user);
        System.out.println(insert);
    }

/*
==>  Preparing: INSERT INTO user ( name, age, email, create_time, update_time ) VALUES ( ?, ?, ?, ?, ? ) 
==> Parameters: A cucumber (String), 24(Integer), 15542256441@qq.com(String), 2020-05-28 15:24:06.774(Timestamp), 2020-05-28 15:24:06.774(Timestamp)
<==    Updates: 1

*/

Test result create_ time&update_ Time has been generated

5. Update test

 //Modify test
    @Test
    public void UpdateTest() {
        User user = new User();
        user.setId(8L);
        user.setName("A bunch of watermelon");
        int update = userMapper.updateById(user);
        System.out.println(update);
    }

/*
==>  Preparing: UPDATE user SET name=?, update_time=? WHERE id=? 
==> Parameters: String, 2020-05-28 15:28:17.975 (timestamp), 8 (long)
<==    Updates: 1
*/

Result: update_time changes

Official auto populated documents

Auto fill function

Example project:

👉 mybatis-plus-sample-auto-fill-metainfo

  • Implement the meta object processor interface: com.baomidou.mybatisplus.core.handlers.MetaObjectHandler
  • Annotation fill field @ tablefield (.. fill= FieldFill.INSERT )The generator policy section can also be configured!
public class User {

    // be careful! This needs to be marked as a fill in field
    @TableField(.. fill = FieldFill.INSERT)
    private String fillField;

    ....
}
  • Custom implementation class MyMetaObjectHandler
@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill ....");
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now()); // From version 3.3.0 (recommended)
        this.fillStrategy(metaObject, "createTime", LocalDateTime.now()); // You can also use (3.3.0 if there is a bug in this method, please upgrade to a later version such as' 3.3.1.8-SNAPSHOT ')
        /* Select one of the above, and the following one is obsolete (note that strict insert fill has multiple methods, check the source code in detail) */
        //this.setFieldValByName("operator", "Jerry", metaObject);
        //this.setInsertFieldValByName("operator", "Jerry", metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill ....");
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now()); // From version 3.3.0 (recommended)
        this.fillStrategy(metaObject, "updateTime", LocalDateTime.now()); // You can also use (3.3.0 if there is a bug in this method, please upgrade to a later version such as' 3.3.1.8-SNAPSHOT ')
        /* Choose one of the above, and the following one is obsolete (note that strict update fill has multiple methods, check the source code in detail) */
        //this.setFieldValByName("operator", "Tom", metaObject);
        //this.setUpdateFieldValByName("operator", "Tom", metaObject);
    }
}

matters needing attention:

  • The field must declare the TableField annotation, and the attribute fill selects the corresponding policy. The declaration tells mybatis plus that it needs to reserve the SQL fields to be injected
  • The filler processor MyMetaObjectHandler needs to declare @ Component or @ Bean injection in Spring Boot
  • According to the notes FieldFill.xxx And field name and field type to distinguish the strictInsertFill or strictUpdateFill methods that must use the parent class
  • There is no need to distinguish the fillStrategy method that can use the parent class according to anything
public enum FieldFill {
    /**
     * Do not process by default
     */
    DEFAULT,
    /**
     * Insert filled field
     */
    INSERT,
    /**
     * Update fill fields
     */
    UPDATE,
    /**
     * Insert and update filled fields
     */
    INSERT_UPDATE
}

Optimistic lock

In the process of re interview, we are often asked about pessimistic lock and optimistic lock!

Optimistic lock: as the name implies, he is optimistic. He always thinks that there will be no problem. No matter what he does, he will not lock! If there is a problem, update the value test again

Optimistic lock: as the name implies, it is very pessimistic. He always thinks there will be problems. Lock whatever you do! Do it again!

Main applicable scenarios

Intention:

When a record is to be updated, I hope it has not been updated by others

Optimistic lock implementation:

  • When fetching records, get the current version
  • When updating, bring this version
  • When performing an update, set version = newVersion where version = oldVersion
  • If the version is wrong, the update fails
Optimistic lock: 1. Query first to obtain version number version = 1
 --A thread modify user name
 update user set name = "a small potato", version = version + 1
where id = 2 and version = 1

--B thread finishes the modification first. At this time, version = 2, which will cause A thread to fail to modify
 update user set name = "a pile of small potatoes", version = version + 1
where id = 2 and version = 1

Test MP optimistic lock plug-in

1. Add code entity class and data table field version

    /*Add comment to open objective lock*/
	@Version
    private Integer version;

2. Create mybatis plus configuration file and open optimistic lock

@Configuration
public class MybatisPlusConfig {
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

3. Test

Test optimistic lock successful operation

    /*Test optimistic lock successful operation*/
    @Test
    public void testOptimsticLocker(){
        //1. Query user information
        User user = userMapper.selectById(8L);
        //2. Modify user information
        user.setName("A bunch of little watermelons");
        //3. Perform update operation
        userMapper.updateById(user);
    }
/*
==>  Preparing: UPDATE user SET name=?, age=?, email=?, create_time=?, update_time=?, version=? WHERE id=? AND version=? 
==> Parameters: A pile of small watermelons 222222(String), 24(Integer), 15542256441@qq.com(String), 2020-05-28 15:24:07.0(Timestamp), 2020-05-28 16:29:41.583(Timestamp), 3(Integer), 8(Long), 2(Integer)
<==    Updates: 1
*/

Test optimistic lock failed operation

    /*Test optimistic lock failed operation*/
    @Test
    public void testOptimsticLocker2(){
        //Simulate simultaneous operation of two threads
        //Thread one failed to submit, and thread two took the lead
        User user = userMapper.selectById(8L);
        user.setName("A pile of small watermelons 1111111");

        //Thread two,
        User user2 = userMapper.selectById(8L);
        user2.setName("A pile of small watermelons 222222");
        userMapper.updateById(user2);

        userMapper.updateById(user);
    }
/*results of enforcement
==>  Preparing: UPDATE user SET name=?, age=?, email=?, create_time=?, update_time=?, version=? WHERE id=? AND version=? 
==> Parameters: A pile of small watermelons 222222(String), 24(Integer), 15542256441@qq.com(String), 2020-05-28 15:24:07.0(Timestamp), 2020-05-28 16:29:41.583(Timestamp), 3(Integer), 8(Long), 2(Integer)
<==    Updates: 1 Thread 2 executed successfully

==>  Preparing: UPDATE user SET name=?, age=?, email=?, create_time=?, update_time=?, version=? WHERE id=? AND version=? 
==> Parameters: A pile of small watermelons 1111111(String), 24(Integer), 15542256441@qq.com(String), 2020-05-28 15:24:07.0(Timestamp), 2020-05-28 16:29:41.669(Timestamp), 3(Integer), 8(Long), 2(Integer)
<==    Updates: 0 Thread one failed to execute
*/

Query operation

1. According to all

    @Test
    public void userMapperTest1(){
        List<User> users = userMapper.selectList(null);//Condition is null
        users.forEach(System.out::println);
    }

2. Query by id

    @Test
    public  void testSelect(){
        User user = userMapper.selectById(7);
        System.out.println(user);
    }

2. Query with multiple IDS

    @Test
    public void selectByIds(){
        List<User> userList = userMapper.selectBatchIds(Arrays.asList(7L, 8L, 9L));
        userList.forEach(System.out::println);
    }

/*
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? , ? ) 
==> Parameters: 7(Long), 8(Long), 9(Long)
<==    Columns: id, name, age, email, create_time, update_time, version
<==        Row: 7, A little pepper, 24, 15542256441@qq.com, 2020-05-28 07:00:32, 2020-05-28 06:55:47, 1
<==        Row: 8, A pile of small watermelons 222222, 24, 15542256441@qq.com, 2020-05-28 07:24:07, 2020-05-28 08:29:42, 3
<==        Row: 9, A little potato, 25, 242642258@qq.com, 2020-05-28 08:04:42, 2020-05-28 08:04:42, 1
<==      Total: 3
*/

4. Use map multi condition query

   @Test
    public void selectByMap(){
        HashMap<String, Object> map = new HashMap<>();
        map.put("name","A small potato");
        map.put("age",23);
        List<User> userList = userMapper.selectByMap(map);
        userList.forEach(System.out::println);
    }

/*
==>  Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE name = ? AND age = ? 
==> Parameters: A small potato (String), 23(Integer)
<==    Columns: id, name, age, email, create_time, update_time, version
<==        Row: 8, A little potato, 23, 15542256441@qq.com, 2020-05-28 07:24:07, 2020-05-28 08:29:42, 3
<==      Total: 1
*/

Paging query

Pagination is used in a lot of websites!

1. Original limit page

2. pageHelper paging plug-in

3. Mybatis plus internal paging plug-in

How to use the inbuilt paging plug-in of mybatis plus!

1. Just configure the interceptor component

//Spring boot mode
@Configuration
@MapperScan("com.hxp.ssmkert.mapper")//Scan mapper
@EnableTransactionManagement//Open transaction manager
public class MybatisPlusConfig {
    /**
     * Paging plug in
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
    
     /**
     * Optimistic lock plug-in
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

2. Test code

    //Page by page query the first page, displaying 5 pieces of data
    @Test
    public void selectByPage(){
        Page<User> page = new Page<>(1,5);//pageNum,PageSize
        userMapper.selectPage(page,null);
        page.getRecords().forEach(System.out::println);
    }

/*
==> Parameters: 
<==    Columns: id, name, age, email, create_time, update_time, version
<==        Row: 1, Jone, 18, test1@baomidou.com, 2020-05-28 06:55:03, 2020-05-28 06:55:03, 1
<==        Row: 2, Jack, 20, test2@baomidou.com, 2020-05-28 06:55:03, 2020-05-28 06:55:03, 1
<==        Row: 3, Tom, 28, test3@baomidou.com, 2020-05-28 06:55:03, 2020-05-28 06:55:03, 1
<==        Row: 4, Sandy, 21, test4@baomidou.com, 2020-05-28 06:55:03, 2020-05-28 06:55:03, 1
<==        Row: 5, Billie, 24, test5@baomidou.com, 2020-05-28 06:55:03, 2020-05-28 06:55:03, 1
<==      Total: 5
*/

Delete operation

Physical delete

1. Delete by id

  //Delete by id
    @Test
    public void deleteById(){
        int i = userMapper.deleteById(9L);
    }

2. Delete multiple by id

//Delete multiple by id
@Test
public void deleteByIds(){
    userMapper.deleteBatchIds(Arrays.asList(7L,8L,9L));
}

Logical deletion

1. Add fields and use notes (add corresponding fields in database)

@TableLogic//Logical deletion
private Integer deleted;

2. Enable the logical deletion component in the configuration file

@Configuration
@MapperScan("com.hxp.ssmkert.mapper")//Scan mapper
@EnableTransactionManagement//Open transaction manager
public class MybatisPlusConfig {

    /**
     *Logical deletion component
     */
    @Bean
    public ISqlInjector sqlInjector(){
        return new LogicSqlInjector();
    }
}

3. Logical deletion

    //Delete by id
    @Test
    public void deleteById2(){
        int i = userMapper.deleteById(8L);
    }

/*
==>  Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0 
==> Parameters: 8(Long)
<==    Updates: 1  We can find that what he left now was modification, not deletion
*/

result:

Performance analysis plug-in

In normal development, we will encounter some slow sql. Test! druid

Function: performance analysis interceptor, used to output each SQL statement and its execution time

MP also provides performance analysis plug-ins. If it exceeds this time, it will stop running!

1. Import plug-ins

    /**
     * SQL Execution efficiency plug-in
     */
    @Bean
    @Profile({"dev","test"})// Set the dev test environment on
    public PerformanceInterceptor performanceInterceptor() {
        PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
        performanceInterceptor.setMaxTime(500);//Set the maximum time of sql execution 500 ms
        performanceInterceptor.setFormat(true);//Open sql format
        return performanceInterceptor;
    }

2. Query test

    @Test
    public void selectByPage(){
        Page<User> page = new Page<>(1,5);
        userMapper.selectPage(page,null);
        page.getRecords().forEach(System.out::println);
    }
/*
==> Parameters: 
<==    Columns: id, name, age, email, version, deleted, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
<==        Row: 2, Jack, 20, test2@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
<==        Row: 3, Tom, 28, test3@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
<==        Row: 4, Sandy, 21, test4@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
<==        Row: 5, Billie, 24, test5@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
<==      Total: 5
 Time: 50 ms  Show that if the execution time exceeds the time specified by us, an error will be reported
			  Show formatted SQL statements
Execute SQL: 
    SELECT
        id,
        name,
        age,
        email,
        version,
        deleted,
        create_time,
        update_time 
    FROM
        user 
    WHERE
        deleted=0 LIMIT 0,5

*/

Conditional constructor (very important)

1,allEq

Test 1: when null2IsNull is true

 @Test
   public void allEqTest1(){

        /**
         * allEq The second parameter boolean null2IsNull is set to true, which is true by default. It can be left blank
         */
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        HashMap<String, Object> map = new HashMap<>();
        map.put("age",18);
        map.put("name",null);
        wrapper.allEq(map);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        /**
         * Execute SQL: 
         *     SELECT
         *         *
         *     FROM
         *         user
         *     WHERE
         *         deleted=0
         *         AND name IS NULL   Here we find that when our parameter is null, the query parameter is null. In general, we develop multiple conditions. When the parameter is null, it should not be queried
         *         AND age = 18
         */
    }

Test 2: when null2IsNull is false

@Test
    public void allEqTest2(){
        /**
         * allEq The second parameter boolean null2IsNull is set to false
         */
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        HashMap<String, Object> map = new HashMap<>();
        map.put("age",18);
        map.put("name",null);
        wrapper.allEq(map,false);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
        /*
        Execute SQL: 
        SELECT
           *
        FROM
            user
        WHERE
            deleted=0
            AND age = 18  Looking at the query conditions here, we will find that when the parameter is null, it will filter out the conditions
        */
    }

Official document:

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)

Description of individual parameters:

Params: key is the database field name and value is the field value
null2IsNull: true is called when the value of the map is null isNull Method, if it is false, null value will be ignored

  • Example 1: allEq({id:1,name: "Lao Wang", age:null }) - > id = 1 and name = 'Lao Wang' and age is null
  • Example 2: allEq({id:1,name: "Lao Wang", age:null}, false) - > id = 1 and name = 'Lao Wang'
allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 

Description of individual parameters:

Filter: filter function, whether to allow the field to pass into the comparison condition
params and null2IsNull: same as above

  • Example 1: alleq ((k, V) - > k.indexof ("a") > = 0, {ID: 1, name: "Lao Wang", age:null }) - > name = 'Lao Wang' and age is null
  • Example 2: alleq ((k, V) - > k.indexof ("a") > = 0, {ID: 1, name: "Lao Wang", age:null}, false) - > name = 'Lao Wang'

2. The comparison of eq characters is equal to=

eq(R column, Object val)
eq(boolean condition, R column, Object val)
  • Example: eq("name", "Lao Wang") - > name = 'Lao Wang'

Test:

  //Test equals=
    @Test
    public void eqTest(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("name","A small potato");
        User user = userMapper.selectOne(wrapper);//selectOne() queries a
        System.out.println(user);
        /**
         * ==>  Preparing: SELECT * FROM user WHERE deleted=0 AND name = ?   A simple string comparison`=`
         * ==> Parameters: A small potato (String)
         */
    }

3. ne is not equal to

  • E.g.: ne("name", "Lao Wang") - > name < > Lao Wang

Test:

 @Test
    public void eqTest2(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.ne("name","A small potato");//Not equal to name! = 'a little potato'
        List<Object> objects = userMapper.selectObjs(wrapper);//Query object collection
        objects.forEach(System.out::println);
        /*
            SELECT * FROM user WHERE deleted=0 AND name <> ?
            ==> Parameters: A small potato (String)
            <==    Columns: id, name, age, email, version, deleted, create_time, update_time
            <==        Row: 1, Jone, 18, test1@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
            <==        Row: 2, Jack, 20, test2@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
            <==        Row: 3, Tom, 28, test3@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
            <==        Row: 4, Sandy, 18, test4@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
            <==        Row: 5, Billie, 24, test5@baomidou.com, 1, 0, 2020-05-28 06:55:03, 2020-05-28 06:55:03
            <==        Row: 7, A little pepper, 24, 15542256441@qq.com, 1, 0, 2020-05-28 07:00:32, 2020-05-28 06:55:47
            <==      Total: 6
         */
    }

4. Greater than gt, less than lt, greater than or equal to ge, less than or equal to le

test

 /* gt greater than */
    @Test
    public void geTest(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age",24);
        List<Map<String, Object>> mapList = userMapper.selectMaps(wrapper);
        mapList.forEach(System.out::println);
        /*
         * SELECT * FROM user WHERE deleted=0 AND age > ?
        */
    }
/* Less than the same */

5. Between between between value 1 AND value 2

   /* between  between and */
    @Test
    public void betweenTest(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.between("age",18,24);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

6. Not between

    /* notBetween */
    @Test
    public void notBetween(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.notBetween("age",18,24);
        userMapper.selectList(wrapper).forEach(System.out::println);
        /**
         * SELECT * FROM user WHERE deleted=0 AND age NOT BETWEEN ? AND ?
         */
    }

7. like statement

 /**
     * like Normal like looks up on both sides
     */
    @Test
    public void likeTest(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name","Small");
        userMapper.selectList(wrapper).forEach(System.out::println);
        /**
         * SELECT * FROM ser WHERE deleted=0 AND name LIKE '%Small% '
         */
    }

8. Not like statement

 /**
     * notLike
     */
    @Test
    public void notLike(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.notLike("name","Small");
        userMapper.selectList(wrapper).forEach(System.out::println);
        /**
         * SELECT * FROM user WHERE deleted=0 AND name NOT LIKE '%Small% '
         */
    }

9. likeLeft statement

 /**
     * liveLeft
     */
    @Test
    public void likeLeft(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeLeft("name","Soil");
        userMapper.selectList(wrapper).forEach(System.out::println);
        /**
         * SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE deleted=0 AND name LIKE  '%Soil '
         */
    }

10. Likereight statement

    /**
     * liveLeft
     */
    @Test
    public void likeRight(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name","T");
        userMapper.selectList(wrapper).forEach(System.out::println);
        /**
         * SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE deleted=0 AND name LIKE 'T%'
         */
    }

Code generator

AutoGenerator is a mybatis plus code generator. Through AutoGenerator, you can quickly generate Entity, Mapper, Mapper XML, service, Controller and other module codes, greatly improving the development efficiency.

package com.hxp.ssmkert.test;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
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.po.TableFill;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;


import java.util.ArrayList;

/**
 * \* Created with IntelliJ IDEA.
 * \* User: A small potato
 * \* Date: 2020/5/30
 * \* Time: 11:25
 * \* mybatis-plus Code generator 
 */
public class MyAutoGeneratorTest {
    public static void main(String[] args) {
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://39.105.27.58/ssmarkert?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT";
        String username = "root";
        String password = "lovehxp521..";

        //1. First, you need to create a code generator object
        AutoGenerator generator = new AutoGenerator();
        //2. Configuration strategy
        //(1) Global configuration
        GlobalConfig gconfig = new GlobalConfig();
        gconfig.setAuthor("A small potato");//Name of configuration developer
        gconfig.setSwagger2(true);//Open Swagger2 document
        gconfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");//Set the path of code output
        gconfig.setOpen(false);//Open output directory or not
        gconfig.setFileOverride(true);//Overwrite or not
        gconfig.setServiceName("%sService");//Remove the prefix I of service
        gconfig.setIdType(IdType.AUTO);//Set primary key policy
        gconfig.setDateType(DateType.ONLY_DATE);//The policy corresponding to time type can also be defaulted
        //Add configuration
        generator.setGlobalConfig(gconfig);

        //(2) Set data source
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setDriverName(driverClassName);
        dsc.setDbType(DbType.MYSQL);
        dsc.setUrl(url);
        dsc.setUsername(username);
        dsc.setPassword(password);
        generator.setDataSource(dsc);

        //(3) Package configuration
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setModuleName("test");
        packageConfig.setParent("com.hxp.ssmkert");
        packageConfig.setMapper("mapper");
        packageConfig.setEntity("entity");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setController("controller");
        //Add configuration for package
        generator.setPackageInfo(packageConfig);
        //(4) Policy configuration
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setInclude("user");//Table names to map, multiple can be passed
        strategyConfig.setNaming(NamingStrategy.underline_to_camel);//Set hump name
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);//Database table field mapping to entity naming policy setting hump naming
        strategyConfig.setEntityLombokModel(true);//Enable lombok
        strategyConfig.setLogicDeleteFieldName("deleted");//Logical deletion
        //Auto fill
        TableFill createTime = new TableFill("create_time", FieldFill.INSERT);
        TableFill updateTime = new TableFill("update_time", FieldFill.INSERT_UPDATE);
        ArrayList<TableFill> tableFields = new ArrayList<>();
        tableFields.add(createTime);
        tableFields.add(updateTime);
        //Set auto fill
        strategyConfig.setTableFillList(tableFields);
        //Set optimistic lock
        strategyConfig.setVersionFieldName("version");
        //Generate < code > @ restcontroller < / code > controller
        strategyConfig.setRestControllerStyle(true);
        //Camel to hyphen localhost:8080/hello_id_2
        strategyConfig.setControllerMappingHyphenStyle(true);

        //Database table configuration
        generator.setStrategy(strategyConfig);


        //3. Execute code generator
        generator.execute();
    }
}

Snowflake algorithm

Twitter's snowflake algorithm of distributed self increasing ID snowflake:https / / github.com/souyunku/SnowFlake

summary

In the distributed system, there are some scenarios that need to use a globally unique ID. in order to prevent ID conflicts, a 36 bit UUID can be used. However, there are some disadvantages of the UUID. First, it is relatively long, and the UUID is generally unordered.

Sometimes we want to use a simpler ID, and we want the ID to be generated in order according to time.

Twitter's snowflake solves this demand. At first, twitter migrated the storage system from MySQL to Cassandra. Because Cassandra has no sequential ID generation mechanism, it developed such a set of globally unique ID generation services.

structure

The structure of snowflake is as follows (each part is separated with -)

0 - 0000000000 0000000000 0000000000 0000000000 0 - 00000 - 00000 - 000000000000

The first bit is unused, the next 41 bits are millisecond time (the length of 41 bits can be used for 69 years), then 5-bit datacenterId and 5-bit workerid (the length of 10 bits can support the deployment of up to 1024 nodes), and the last 12 bits are the count within milliseconds (the 12-bit count sequence number supports 4096 ID sequence numbers per node per millisecond)

All together, it's just 64 bits. It's a Long type. (maximum length after conversion to string 19)

The IDs generated by snowflake are sorted according to the increasing time on the whole, and there is no ID collision in the whole distributed system (differentiated by datacenter and workerId), and the efficiency is high. Snowflake has been tested to generate 260000 IDS per second.

/**
 * Description: Twitter's snowflake algorithm for distributed ID increment (Java version)
 *
 * @author yanpenglei
 * @create 2018-03-13 12:37
 **/
public class SnowFlake {

    /**
     * Start timestamp
     */
    private final static long START_STMP = 1480166465631L;

    /**
     * Number of bits occupied by each part
     */
    private final static long SEQUENCE_BIT = 12; //Number of digits occupied by serial number
    private final static long MACHINE_BIT = 5;   //Number of digits occupied by machine identification
    private final static long DATACENTER_BIT = 5;//Number of bits occupied by data center

    /**
     * Maximum value of each part
     */
    private final static long MAX_DATACENTER_NUM = -1L ^ (-1L << DATACENTER_BIT);
    private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT);
    private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);

    /**
     * Displacement of each part to the left
     */
    private final static long MACHINE_LEFT = SEQUENCE_BIT;
    private final static long DATACENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT;
    private final static long TIMESTMP_LEFT = DATACENTER_LEFT + DATACENTER_BIT;

    private long datacenterId;  //Data center
    private long machineId;     //Machine identification
    private long sequence = 0L; //serial number
    private long lastStmp = -1L;//Last timestamp

    public SnowFlake(long datacenterId, long machineId) {
        if (datacenterId > MAX_DATACENTER_NUM || datacenterId < 0) {
            throw new IllegalArgumentException("datacenterId can't be greater than MAX_DATACENTER_NUM or less than 0");
        }
        if (machineId > MAX_MACHINE_NUM || machineId < 0) {
            throw new IllegalArgumentException("machineId can't be greater than MAX_MACHINE_NUM or less than 0");
        }
        this.datacenterId = datacenterId;
        this.machineId = machineId;
    }

    /**
     * Generate next ID
     *
     * @return
     */
    public synchronized long nextId() {
        long currStmp = getNewstmp();
        if (currStmp < lastStmp) {
            throw new RuntimeException("Clock moved backwards.  Refusing to generate id");
        }

        if (currStmp == lastStmp) {
            //In the same millisecond, the serial number increases automatically
            sequence = (sequence + 1) & MAX_SEQUENCE;
            //The number of sequences in the same millisecond has reached the maximum
            if (sequence == 0L) {
                currStmp = getNextMill();
            }
        } else {
            //Serial number is set to 0 in different milliseconds
            sequence = 0L;
        }

        lastStmp = currStmp;

        return (currStmp - START_STMP) << TIMESTMP_LEFT //Time stamp section
                | datacenterId << DATACENTER_LEFT       //Data center part
                | machineId << MACHINE_LEFT             //Machine identification part
                | sequence;                             //Serial number section
    }

    private long getNextMill() {
        long mill = getNewstmp();
        while (mill <= lastStmp) {
            mill = getNewstmp();
        }
        return mill;
    }

    private long getNewstmp() {
        return System.currentTimeMillis();
    }

    public static void main(String[] args) {
        SnowFlake snowFlake = new SnowFlake(2, 3);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 1000000; i++) {
            System.out.println(snowFlake.nextId());
        }

        System.out.println(System.currentTimeMillis() - start);


    }
}
   this.datacenterId = datacenterId;
    this.machineId = machineId;
}

/**
 * Generate next ID
 *
 * @return
 */
public synchronized long nextId() {
    long currStmp = getNewstmp();
    if (currStmp < lastStmp) {
        throw new RuntimeException("Clock moved backwards.  Refusing to generate id");
    }

    if (currStmp == lastStmp) {
        //In the same millisecond, the serial number increases automatically
        sequence = (sequence + 1) & MAX_SEQUENCE;
        //The number of sequences in the same millisecond has reached the maximum
        if (sequence == 0L) {
            currStmp = getNextMill();
        }
    } else {
        //Serial number is set to 0 in different milliseconds
        sequence = 0L;
    }

    lastStmp = currStmp;

    return (currStmp - START_STMP) << TIMESTMP_LEFT //Time stamp section
            | datacenterId << DATACENTER_LEFT       //Data center part
            | machineId << MACHINE_LEFT             //Machine identification part
            | sequence;                             //Serial number section
}

private long getNextMill() {
    long mill = getNewstmp();
    while (mill <= lastStmp) {
        mill = getNewstmp();
    }
    return mill;
}

private long getNewstmp() {
    return System.currentTimeMillis();
}

public static void main(String[] args) {
    SnowFlake snowFlake = new SnowFlake(2, 3);

    long start = System.currentTimeMillis();
    for (int i = 0; i < 1000000; i++) {
        System.out.println(snowFlake.nextId());
    }

    System.out.println(System.currentTimeMillis() - start);


}

}

Tags: Mybatis Database MySQL SQL

Posted on Sun, 14 Jun 2020 22:51:17 -0400 by nadman123