SpringBoot integration MyBatisPlus

In the spare time, record what you have learned.

Powerful? No feeling! Is it convenient? It does have a bit, but the multi table operation is poor, may be their own skill is not fine condition constructor, I later in the update

www.bookstack.cn/read/mybati…

1.Spring Boot integrates MP

1.1 New Project

(1) Create a Spring Boot project

(2) Set GAV

(3) Select dependency, without the support of MyBatis, you need to download it from the warehouse

(4) The setting is complete

(5) Pom.xml file

      
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.4. RELEASE</version>
		<relativePath/> <! -- lookup parent from repository -->
	</parent>
	<groupId>com.pangsir</groupId>
	<artifactId>mybatis-plus-02</artifactId>
	<version>0.0.1 - the SNAPSHOT</version>
	<name>mybatis-plus-02</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<! -- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

Copy the code

1.2 Configuring core Files

MySQL data source configuration
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: JDBC: mysql: / / 127.0.0.1:3306 / mytest? serverTimezone=Hongkong
    username: root
    password:

# MyBatis Plus configuration - Can be ignored to load mapping files
mybatis-plus:
  mapper-locations: classpath:mapper/**/*Mapper.xml # mapping file
  type-aliases-package: com.pangsir.entity # persist location
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
Copy the code

The resource > application. Yml file

1.3 Test Environment

(1) Execute the script file

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- In real development, we need to add the following fields: version(optimistic lock), DELETED (logical deletion), GMT_CREATE, gmT_Modified
INSERT INTO `student` VALUES (1.'Tom'.'[email protected]'.'1'.22);
INSERT INTO `student` VALUES (2.'Jerry'.'[email protected]'.'0'.25);
INSERT INTO `student` VALUES (3.'Black'.'[email protected]'.'1'.30);
INSERT INTO `student` VALUES (4.'White'.'[email protected]'.'0'.35);
INSERT INTO `student` VALUES (5.'wukong'.'[email protected]'.'1'.18);
INSERT INTO `student` VALUES (6.'pig'.'[email protected]'.'1'.18);
Copy the code

(2) Create a persistent class

package com.pangsir.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "student")
public class Student {
	@TableId(value = "id",type = IdType.AUTO)
	private Integer studentId;
	@TableField(value = "student_name")// Hump conversion is enabled by default
	private String studentName;
	private String email;
	private Integer gender;
	private Integer age;
}

Copy the code

(3) Create StudentMapper and inherit BaseMapper

package com.pangsir.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
public interface StudentMapper extends BaseMapper<Student> {// Note that generics are persistent classes
}

Copy the code

(4) Configure the startup class for scanning

package com.pangsir;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(value = {"com.pangsir.**.mapper"})
public class MyBatisPlusMain {
	public static void main(String[] args) { SpringApplication.run(MyBatisPlusMain.class,args); }}Copy the code

(5) Test whether it is available

package com.pangsir.test;

import com.pangsir.entity.Student;
import com.pangsir.mapper.StudentMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class MyBatisPlusTest {
	@Autowired
	private StudentMapper studentMapper;
	@Test
	public void selectByIdTest(a){
		Student student = studentMapper.selectById(7);
		System.out.println("student = "+ student); }}Copy the code

2. CRUD operations

2.1 Simple CRUD Operations

By default, dynamic concatenated SQL statements are used, so persistent classes try not to use primitive data types

package com.pangsir.test;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.pangsir.entity.Student;
import com.pangsir.mapper.StudentMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

@SpringBootTest
public class MyBatisPlusTest {
	@Autowired
	private StudentMapper studentMapper;
	@Test
	public void testInsertMethod(a){
		Student student = new Student();
		student.setStudentName("Tang's monk");
		student.setEmail("[email protected]");
		student.setAge(18);
		student.setGender(1);
		int row = studentMapper.insert(student);// The return value int is the number of affected rows
	}
	@Test
	public void testUpdateMethod(a){
		Student student = new Student();
		student.setStudentId(6);
		student.setStudentName("Marshal Of the Canopy");
		int row = studentMapper.updateById(student);// The return value int is the number of affected rows
	}
	@Test
	public void testDeleteMethod(a){
		studentMapper.deleteById(8);

	}
	@Test
	public void selectByIdTest(a){
		Student student = studentMapper.selectById(7);
		System.out.println("student = "+ student); }}Copy the code

Simple CRUD operations are done!

2.2 Automatic filling operation

All database tables should contain gmT_CREATE, GMT_modified time, and need to be automated processing,

(1) Database level operation: Navicat tools can be directly operated, the actual development rarely use this way, you can understand.

(2) Code-level operation:

After canceling the database-level operation, we write the code. Refer to the help document of MyBatisPlus. The properties of creation time and update time are set as follows

@Data
@TableName(value = "student")
public class Student {
	@TableId(value = "id",type = IdType.AUTO)
	private Integer studentId;
	@TableField(value = "student_name")// Hump conversion is enabled by default
	private String studentName;
	private String email;
	private Integer gender;
	private Integer age;
	@TableField(fill = FieldFill.INSERT)
	private Date gmtCreate;
	@TableField(fill = FieldFill.INSERT_UPDATE)
	private Date gmtModified;
}
Copy the code

Programmed processor: Mybatis. Plus/Guide/Auto…

package com.pangsir.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.Date;

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

	@Override
	public void insertFill(MetaObject metaObject) {
		log.info("start insert fill ....");
		this.setFieldValByName("gmtCreate".new Date(), metaObject);
		this.setFieldValByName("gmtModified".new Date(), metaObject);
	}

	@Override
	public void updateFill(MetaObject metaObject) {
		log.info("start update fill ....");
		this.setFieldValByName("gmtModified".newDate(), metaObject); }}Copy the code

Add or update the test.

2.3 optimistic locking

In the interview process, we are often asked about the optimistic lock and the pessimistic lock. Explain briefly!

Optimistic lock: very optimistic, he always thinks there will be no problem, no matter what to do to lock, if there is a problem, update the value again.

Pessimistic lock: very pessimistic, he always think there will be a problem, no matter what to lock!

Mybatis. Plus/Guide/Inter…

• When the record is fetched, the current version is retrieved
• When you update, take this version with you
• When performing an update, set version = newVersion where version = oldVersion
• If the version is incorrect, the update fails

## Optimistic lock:1.Query to obtain the version number, for example, version=1
-- Thread A executes
update student set student_name='liu bei',version=version+1 where id=3 and version=1 
If version=2, thread A will fail to modify
update student set student_name='zhang fei',version=version+1 where id=3 and version=1 
Copy the code

(1) Add the Version field

(2) Modify the persistent class

@Data
@TableName(value = "student")
public class Student {
	@TableId(value = "id",type = IdType.AUTO)
	private Integer studentId;
	@TableField(value = "student_name")// Hump conversion is enabled by default
	private String studentName;
	private String email;
	private Integer gender;
	private Integer age;
	@TableField(fill = FieldFill.INSERT)
	private Date gmtCreate;
	@TableField(fill = FieldFill.INSERT_UPDATE)
	private Date gmtModified;
	@Version
	private Integer version;
}
Copy the code

(3) Add optimistic lock plug-in

package com.pangsir.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
public class MyBatisPlusConfig {
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
		returninterceptor; }}Copy the code

(4) Simulate multithreading test

@Test
public voidOptimistic lock simulation test code (){1. Query version
    Student student = studentMapper.selectById(11);
    student.setEmail("Liu bei @ 163. com." ");
    // Simulate thread B 2. Query the email address and modify the email address
    Student s1 = studentMapper.selectById(11);
    s1.setEmail("Liu Bei @ psychome.com");
    s1.setAge(20);
    // Thread B is executed first
    studentMapper.updateById(s1);
    // Execute thread A modification
    studentMapper.updateById(student);
}
Copy the code

==> Preparing: UPDATE student SET student_name=? , email=? , gender=? , age=? , gmt_create=? , gmt_modified=? , version=? WHERE id=? AND version=? ==> Parameters: Liu Bei (String), Liu Bei @ psychology. COM(String), 1(Integer), 20(Integer), 2020-10-20 17:00:50.0(Timestamp), 2020-10-20 17:28:10.656(Timestamp), 4(Integer), 11(Integer), 3(Integer) <== Updates: 1 Successful ==> Preparing: UPDATE student SET student_name=? , email=? , gender=? , age=? , gmt_create=? , gmt_modified=? , version=? WHERE id=? AND version=? ==> Parameters: Liu Bei (String), Liu Bei @163.com(String), 1(Integer), 20(Integer), 2020-10-20 17:00:50.0(Timestamp), 2020-10-20 17:28:10.741(Timestamp), 4(Integer), 11(Integer), 3(Integer) <== Updates: 0 failedCopy the code

2.4 Paging plug-in configuration

@Configuration
public class MyBatisPlusConfig {
	MybatisConfiguration#useDeprecatedExecutor = false to avoid cache problems (this property will be removed when the old plugin is removed) */
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());/ / optimistic locking
		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));// Paging plug-in
		return interceptor;
	}
	@Bean
	public ConfigurationCustomizer configurationCustomizer(a) {
		return configuration -> configuration.setUseDeprecatedExecutor(false); }}Copy the code

@Test
public voidPaging plug-in configuration (){Page<Student> studentPage =new Page<>(0.2);
    studentMapper.selectPage(studentPage,null);
    studentPage.getRecords().forEach(student-> System.out.println(student));
    System.out.println("= = = = = = = = =");
    studentPage = new Page<>(2.2);
    studentMapper.selectPage(studentPage,null);
    studentPage.getRecords().forEach(student-> System.out.println(student));
}
Copy the code

2.5 Logical Deletion

(1) Add a field to the database, default value is set to 0, do not delete

(2) Add persistent class attributes

package com.pangsir.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.util.Date;

@Data
@TableName(value = "student")
public class Student {
	@TableId(value = "id",type = IdType.AUTO)
	private Integer studentId;
	@TableField(value = "student_name")// Hump conversion is enabled by default
	private String studentName;
	private String email;
	private Integer gender;
	private Integer age;
	@TableField(fill = FieldFill.INSERT)
	private Date gmtCreate;
	@TableField(fill = FieldFill.INSERT_UPDATE)
	private Date gmtModified;
	@Version
	private Integer version;
	@TableLogic// Delete logical use
	private Integer deleted;

}

Copy the code

(3) Setting and configuration

mybatis-plus:
  mapper-locations: classpath:mapper/**/*Mapper.xml # mapping file
  type-aliases-package: com.pangsir.entity # persist location
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      logic-delete-field: deleted  # delete entity field name (since 3.3.0)
      logic-delete-value: 1 # Logical deleted value (default: 1)
      logic-not-delete-value: 0 # Logical undeleted value (default: 0)
Copy the code

(4) Test

@Test
public voidStudentmapper.deletebyid (){studentmapper.deleteById ()7);
}
Copy the code

==>  Preparing: UPDATE student SET deleted=1 WHERE id=? AND deleted=0
==> Parameters: 7(Integer)
< ==    Updates: 1
Copy the code

(5) Query test

@Test
public void selectListTest(a){
    List<Student> students = studentMapper.selectList(null);
    students.forEach(System.out::println);
}
Copy the code

The following SQL statement is produced:

SELECT id AS studentId,student_name,email,gender,age,gmt_create,gmt_modified,version,deleted FROM student WHERE deleted=0
Copy the code

Filter criteria are automatically added. To query all data, perform the following operations

@TableLogic// Logical delete use - can be omitted
@TableField(select = true)
private Integer deleted;
Copy the code