1,Insert

1) Insert test

@Test
void testInsert(){
    User user = new User();
    user.setName("tuwer");
    user.setAge(8);
    user.setEmail("abc@qq.com");
    // id will be generated automatically
    int res = userMapper.insert(user);    
    System.out.println(res);
    System.out.println(user);
}

2) Primary key policy

• @TableId

  • Description: primary key annotation
  • Usage location: entity class primary key field

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface TableId {

    /**
     * Field name (this value can be null)
     */
    String value() default "";

    /**
     * Primary key type
     * {@link IdType}
     */
    IdType type() default IdType.NONE;
}

• IdType enumeration class

public enum IdType {
    /**
     * Self increment of database ID
     * <p>For this type, please ensure that the database is set with ID self increment, otherwise it is invalid</p>
     */
    AUTO(0),
    /**
     * This type is not set with primary key (in the annotation, it is equal to follow the global, and the global value is equal to INPUT)
     */
    NONE(1),
    /**
     * User input ID
     * <p>This type can be filled by registering the auto fill plug-in</p>
     */
    INPUT(2),

    /* The following three types are automatically filled only when the inserted object ID is empty. */
    /**
     * Assign ID (the primary key type is number or string),
     * Default implementation class {@ link com. Baomidou. Mybatisplus. Core. Increment. Defaultidentifier generator} (snowflake algorithm)
     *
     * @since 3.3.0
     */
    ASSIGN_ID(3),
    /**
     * Assign UUID (the primary key type is string)
     * Default implementation class {@ link com. Baomidou. Mybatisplus. Core. Increment. Defaultidentifiergenerator} (UUID. Replace ("-", ""))
     */
    ASSIGN_UUID(4);

    private final int key;

    IdType(int key) {
        this.key = key;
    }
}

• Snow flake algorithm

snowflake is Twitter's open source distributed ID generation algorithm, and the result is a long ID. Its core idea is to use 41bit as the number of milliseconds, 10bit as the machine ID (five bits are the machine ID of the data center (Beijing and Hong Kong), and 12bit as the serial number within milliseconds (meaning that each node can generate 4096 IDS per millisecond), and finally there is a symbol bit, which is always 0.

2,Update

@Test
void testUpdate(){
    User user = new User();
    user.setId(8L);
    user.setName("tuwer test");
    user.setAge(28);
    user.setEmail("abc@qq.com");
    // Although the method is ById, the parameter is the class object user
    int i = userMapper.updateById(user);
    System.out.println(i);
}

3. Auto fill

Creation time and change time! These operations are generally completed automatically and do not need to be updated manually

Alibaba Development Manual: almost all tables should be configured with gmt_create,gmt_modified ! And automation is needed

1) Database mode: Level I

It is not allowed to modify the database level in actual development

• Modifying databases: adding gmt_create and gmt_modified field

alter table user add gmt_create datetime default CURRENT_TIMESTAMP null comment 'Creation time';
alter table user add gmt_modified datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment 'Update time';

• Modify entity class

  Hump naming: gmtCreate corresponds to GMT in the database_ create

  Date and LocalDateTime can be used for date type, and LocalDateTime is recommended

private Date gmtCreate;
private LocalDateTime gmtModified;

2) Method 2: code level

• Delete operations at the database level: default, update

• Add annotation on the field attribute of entity class

  // Fill on insert
  @TableField(fill = FieldFill.INSERT)
  private Date gmtCreate;
  
  // Fill in when inserting and updating
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private LocalDateTime gmtModified;
  

• Fill processor

  • The population processor MyMetaObjectHandler needs to declare @ Component or @ Bean injection in Spring Boot

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("Fill at start of insertion...");
        this.strictInsertFill(metaObject, "gmtCreate", Date.class, new Date());
        this.strictInsertFill(metaObject, "gmtModified", LocalDateTime.class, LocalDateTime.now());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("Fill at start of insert update...");
        this.strictUpdateFill(metaObject, "gmtModified", LocalDateTime.class, LocalDateTime.now());
    }
}

• test

Insert test

Update test

4. Optimistic lock

Optimistic lock: very optimistic, always think that there will be no problem; No matter what you do, don't lock it! If there is a problem, update the test value again

Pessimistic lock: very pessimistic, always think there will be problems; No matter what you do, it will be locked! Operate again!

Optimistic lock implementation method:

When you want to update a record, you want it not to be updated by others

• When the record is fetched, the current version is obtained
• When updating, bring this version
• When updating, set version = newVersion where version = oldVersion
• If the version is incorrect, the update fails

1) Modify database

Add version field

2) Modify entity class

Add the Version attribute and add the @ Version annotation

@Version
private Integer version;

3) Registration lock assembly

@Configuration
@MapperScan("com.tuwer.mapper")
public class MybatisPlusConfig {
    /**
     * Registration lock assembly
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

4) Testing

• Single thread

@Test
public void testOptimisticLocker1(){
    //1. Query user information
    User user = userMapper.selectById(8L);
    //2. Modify user information
    user.setAge(18);
    user.setEmail("111@qq.com");
    //3. Perform update operation
    userMapper.updateById(user);
}

• Simulated multithreading

@Test
public void testOptimisticLocker2(){
    // Thread 1
    User user1 = userMapper.selectById(8L);
    user1.setAge(1);
    user1.setEmail("222@qq.com");
    
    // Simulate another thread to perform queue jumping
    User user2 = userMapper.selectById(8L);
    user2.setAge(2);
    user2.setEmail("333@qq.com");
    userMapper.updateById(user2);
    
    // Spin lock to multiple attempts to submit!
    userMapper.updateById(user1);//If there is no optimistic lock, it will override the value of the queue jumping thread
}

5,Select

1) Query individual users by id

@Test
public void testSelectById(){
    User user = userMapper.selectById(8L);
    System.out.println(user);
}

2) Query multiple users by id

@Test
public void testSelectBatchIds(){
    List<User> users = userMapper.selectBatchIds(Arrays.asList(1L, 2L, 5L));
    users.forEach(System.out::println);
}

3) Conditional queries are encapsulated by map

@Test
public void testSelectByMap() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("name", "tuwer");
    map.put("age", 2);

    List<User> users = userMapper.selectByMap(map);
}

4) Paging query

• selectPage()

// Parameter 1: IPage object
// Parameter 2: Wrapper condition query
// Return: IPage object; Encapsulate the result into the IPage object of parameter 1
<P extends IPage<T>> P selectPage(P page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

• Page class

  • The Page class implements the IPage interface
  • Default value: 10 items are displayed on each page, and the current page is 1

public class Page<T> implements IPage<T> {

    // ...
    
    /**
     * The number of items displayed on each page is 10 by default
     */
    protected long size = 10;

    /**
     * Current page
     */
    protected long current = 1;
    
    // ...
}    

constructor

query

@Test
public void testSelectPage(){
    // Query by Page default
    Page<User> page = new Page<>();
    userMapper.selectPage(page, null);

    page.getRecords().forEach(System.out::println);
}

@Test
public void testSelectPage(){
    // Check page 2, 3 records
    Page<User> page = new Page<>(2,3);
    userMapper.selectPage(page, null);

    page.getRecords().forEach(System.out::println);
}

6,Delete

1) Physical deletion

Delete directly from database

2) Logical deletion

• It is not deleted in the database, but invalidated by a variable! deleted=0 ==> deleted=1
• Logical deletion is a scheme to facilitate data recovery and protect the value of data itself, but it is actually deletion
• Delete: convert to update

• Modify database: add deleted field

alter table user add deleted tinyint default 0 not null comment 'Logical deletion';

• to configure

mybatis-plus:
  global-config:
    db-config:
      #logic-delete-field: deleted # Logically deleted entity field name (entity class can be ignored after configuration)
      logic-delete-value: 1 # Logical deleted value (default = 1)
      logic-not-delete-value: 0 # Logical undeleted value (default is 0)

• Modify entity class: add deleted attribute and add @ TableLogic annotation

@TableLogic // If logic delete field: deleted is configured, @ TableLogic can be omitted here
private Integer deleted;

• delete

@Test
public void testDelete(){
    userMapper.deleteById(8L);
}

Configure logic delete field: deleted, omit @ TableLogic

• Query after deletion