Mybatis Plus just need to read this one

Mybatis – Plus is a MyBatis enhancement tool designed to simplify development and improve efficiency. The following uses the abbreviation MP to simplify the expression of Mybatis -plus, this article mainly introduces the use of MP with SpringBoot.

Note: the MP version used in this article is the latest 3.4.2. Please refer to the documentation for differences in earlier versions

Official website: baomidou.com/

Quick start

Create a SpringBoot project
Import dependence

   <! -- pom.xml -->
   
      
   <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.example</groupId>
       <artifactId>mybatis-plus</artifactId>
       <version>0.0.1 - the SNAPSHOT</version>
       <name>mybatis-plus</name>
       <properties>
           <java.version>1.8</java.version>
       </properties>
       <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.springframework.boot</groupId>
               <artifactId>spring-boot-configuration-processor</artifactId>
           </dependency>
           <dependency>
               <groupId>com.baomidou</groupId>
               <artifactId>mybatis-plus-boot-starter</artifactId>
               <version>3.4.2</version>
           </dependency>
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <scope>runtime</scope>
           </dependency>
           <dependency>
               <groupId>org.projectlombok</groupId>
               <artifactId>lombok</artifactId>
           </dependency>
       </dependencies>
       <build>
           <plugins>
               <plugin>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-maven-plugin</artifactId>
               </plugin>
           </plugins>
       </build>
   </project>
Copy the code

Configuring the Database

   # application.yml
   spring:
     datasource:
       driver-class-name: com.mysql.cj.jdbc.Driver
       url: jdbc:mysql://localhost:3306/yogurt? serverTimezone=Asia/Shanghai
       username: root
       password: root
       
   mybatis-plus:
     configuration:
       log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # Enable SQL statement printing
Copy the code

Create an entity class

   package com.example.mp.po;
   import lombok.Data;
   import java.time.LocalDateTime;
   @Data
   public class User {
   	private Long id;
   	private String name;
   	private Integer age;
   	private String email;
   	private Long managerId;
   	private LocalDateTime createTime;
   }
Copy the code

Create a Mapper interface

   package com.example.mp.mappers;
   import com.baomidou.mybatisplus.core.mapper.BaseMapper;
   import com.example.mp.po.User;
   public interface UserMapper extends BaseMapper<User> {}Copy the code

Configure a scan path for the Mapper interface on the SpringBoot boot class

   package com.example.mp;
   import org.mybatis.spring.annotation.MapperScan;
   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   @SpringBootApplication
   @MapperScan("com.example.mp.mappers")
   public class MybatisPlusApplication {
   	public static void main(String[] args) { SpringApplication.run(MybatisPlusApplication.class, args); }}Copy the code

Create tables in the database

   DROP TABLE IF EXISTS user;
   CREATE TABLE user (
   id BIGINT(20) PRIMARY KEY NOT NULL COMMENT 'primary key',
   name VARCHAR(30) DEFAULT NULL COMMENT 'name',
   age INT(11) DEFAULT NULL COMMENT 'age',
   email VARCHAR(50) DEFAULT NULL COMMENT 'email',
   manager_id BIGINT(20) DEFAULT NULL COMMENT 'Immediate superior ID',
   create_time DATETIME DEFAULT NULL COMMENT 'Creation time'.CONSTRAINT manager_fk FOREIGN KEY(manager_id) REFERENCES user (id)
   ) ENGINE=INNODB CHARSET=UTF8;
   
   INSERT INTO user (id, name, age ,email, manager_id, create_time) VALUES
   (1.'big BOSS'.40.'[email protected]'.NULL.'the 2021-03-22 09:48:00'),
   (2.'Manager Li'.40.'[email protected]'.1.'the 2021-01-22 09:48:00'),
   (3.'Supervisor Huang'.40.'[email protected]'.2.'the 2021-01-22 09:48:00'),
   (4.'Group Leader Wu'.40.'[email protected]'.2.'the 2021-02-22 09:48:00'),
   (5.'side'.40.'[email protected]'.2.'the 2021-02-22 09:48:00')
Copy the code

Write a SpringBoot test class

   package com.example.mp;
   import com.example.mp.mappers.UserMapper;
   import com.example.mp.po.User;
   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.test.context.junit4.SpringRunner;
   import java.util.List;
   import static org.junit.Assert.*;
   @RunWith(SpringRunner.class)
   @SpringBootTest
   public class SampleTest {
   	@Autowired
   	private UserMapper mapper;
   	@Test
   	public void testSelect(a) {
   		List<User> list = mapper.selectList(null);
   		assertEquals(5, list.size()); list.forEach(System.out::println); }}Copy the code

Preparatory work completed

The database is as follows

The project catalog is as follows

Run test classes

As you can see, for basic CRUD operations on a single table, all you need to do is create an entity class and create an interface that inherits from BaseMapper, which is very simple. Also, notice that managerId and createTime attributes in the User class automatically correspond to manager_id and create_time attributes in the database table, because MP automatically converts the database underscore name to the Java class’s camel name.

The core function

annotations

Mp provides a total of eight annotations that are used on Java entity classes.

@TableName

Annotations on a class specify the mapping between a class and a database table. This annotation may not be specified if the entity class name (converted to lowercase) is the same as the database table name.
@TableId

An annotation on a field of an entity class that represents the primary key of the database table for that field. When the primary key name is ID (the column name in the table is ID, the field name in the entity class is ID), you do not need to specify the primary key explicitly with this annotation, and mp is automatically associated. If the field name of the class is inconsistent with the column name of the table, use the value attribute to specify the column name of the table. In addition, this annotation has an important attribute type, which specifies the primary key policy.
@TableField

Annotation specifies the mapping between a field of a Java entity class and a column of a database table on a field. There are several applications for this annotation.
- Exclude non-table fields
  
  If a field in a Java entity class does not correspond to any column in the table, but is used to hold some additional or assembled data, you can set the exist property to false so that the field is ignored when the entity object is inserted. Excluding non-table fields can also be done in other ways, such as using the static or transient keyword, but I think it is not very reasonable, SO I will not repeat it
- Field validation strategy
  
  Configured with the insertStrategy, updateStrategy, whereStrategy properties, you can control how fields in an entity object are assembled into SQL statements when the object is inserted, updated, or used as a WHERE condition.
- Field filling strategy
  
  Specified by the fill attribute, the field is automatically filled when empty
@Version

Optimistic Lock annotations
@EnumValue

Annotations are on enumeration fields
@TableLogic

Logic to delete
KeySequence

Sequential Primary Key Policy (Oracle)
InterceptorIgnore

Plug-in Filtering Rules

CRUD interface

Mp encapsulates some of the most basic CRUD methods, just need to directly inherit the INTERFACE provided by MP, without writing any SQL, can be eaten. Mp provides two sets of interfaces, namely Mapper CRUD interface and Service CRUD interface. And MP also provides a condition constructor, Wrapper, to easily assemble WHERE conditions in SQL statements.

Mapper CRUD interface

Simply define the entity class, then create an interface that inherits the BaseMapper provided by MP, and serve. When myBatis starts, MP automatically resolves the mapping between entity classes and tables and injects mapper with common CRUD methods. The methods provided in BaseMapper are listed as follows:

insert(T entity)Insert a record
deleteById(Serializable id)Deletes a record based on the primary key ID
delete(Wrapper<T> wrapper)Delete according to the conditional constructor Wrapper
selectById(Serializable id)Search by primary key ID
selectBatchIds(Collection idList)Batch search based on primary key ID
selectByMap(Map<String,Object> map)Based on the column names and column values specified in the mapContour matchingTo find the
selectMaps(Wrapper<T> wrapper)According to the Wrapper condition, the records are queried and the query result is encapsulated as a Map with the key as the result column and the value as the value
selectList(Wrapper<T> wrapper)According to the conditional constructorwrapperThe query
update(T entity, Wrapper<T> wrapper)According to the conditional constructorwrapperupdated
updateById(T entity)
.

Here are some of the more unusual methods

selectMaps

The BaseMapper interface also provides a selectMaps method that encapsulates the query result as a Map with the key as the result column and the value as the value

The usage scenarios of this method are as follows:

Look up only partial columns

When there are too many columns in a table and only a few columns need to be selected during the SELECT process, and the query results do not need to be encapsulated as Java entity-like objects (many attributes in the entity object will be null after the query results are encapsulated into a partial column), you can use selectMaps to obtain the specified columns and then process them by yourself

Such as

  	@Test
  	public void test3(a) {
  		QueryWrapper<User> wrapper = new QueryWrapper<>();
  		wrapper.select("id"."name"."email").likeRight("name"."Yellow");
  		List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
  		maps.forEach(System.out::println);
  	}
Copy the code

Make statistics

Such as

  // Query the average age, maximum age, minimum age of each group
  /** select avg(age) avg_age ,min(age) min_age, max(age) max_age from user group by manager_id having sum(age) < 500; * * /
  
  @Test
  public void test3(a) {
  	QueryWrapper<User> wrapper = new QueryWrapper<>();
  	wrapper.select("manager_id"."avg(age) avg_age"."min(age) min_age"."max(age) max_age")
  			.groupBy("manager_id").having("sum(age) < {0}".500);
  	List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
  	maps.forEach(System.out::println);
  }
Copy the code

selectObjs

Only the value of the first field (the first column) is returned; the other fields are discarded

Such as

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		wrapper.select("id"."name").like("name"."Yellow");
		List<Object> objects = userMapper.selectObjs(wrapper);
		objects.forEach(System.out::println);
	}
Copy the code

The result only encapsulates the ID of the first column

selectCount

Query the total number of columns that meet the criteria. Note that with this method you cannot set the column to be queried by calling the QueryWrapper SELECT method. This method will automatically add select count(1)

Such as

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		wrapper.like("name"."Yellow"); Integer count = userMapper.selectCount(wrapper); System.out.println(count); } Duplicate codeCopy the code

The Service of the CRUD interface

Another SET of CRUDS is Service layer, just need to write an interface, inherit IService, and create an interface implementation class, can be served. The CRUD method provided by IService is similar to that provided by Mapper. The difference is that IService supports more batch operations such as saveBatch and saveOrUpdateBatch.

Examples are as follows

First, create an interface and inheritIService

   package com.example.mp.service;
   
   import com.baomidou.mybatisplus.extension.service.IService;
   import com.example.mp.po.User;
   
   public interface UserService extends IService<User> {}Copy the code

Create an implementation class for this interface and inherit itServiceImpl, and finally@ServiceAnnotations, registered in the Spring container, ready to eat

   package com.example.mp.service.impl;
   
   import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
   import com.example.mp.mappers.UserMapper;
   import com.example.mp.po.User;
   import com.example.mp.service.UserService;
   import org.springframework.stereotype.Service;
   
   @Service
   public class UserServiceImpl extends ServiceImpl<UserMapper.User> implements UserService {}Copy the code

The test code

   package com.example.mp;
   
   import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
   import com.baomidou.mybatisplus.core.toolkit.Wrappers;
   import com.example.mp.po.User;
   import com.example.mp.service.UserService;
   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.test.context.junit4.SpringRunner;
   @RunWith(SpringRunner.class)
   @SpringBootTest
   public class ServiceTest {
   	@Autowired
   	private UserService userService;
   	@Test
   	public void testGetOne(a) {
   		LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery();
   		wrapper.gt(User::getAge, 28);
   		User one = userService.getOne(wrapper, false); // The second argument is specified as false so that when multiple rows are found, the first record is returned without an exceptionSystem.out.println(one); }}Copy the code

The results of

In addition, IService also supports chain call, the code is very simple to write, the query example is as follows

	@Test
	public void testChain(a) {
		List<User> list = userService.lambdaQuery()
				.gt(User::getAge, 39)
				.likeRight(User::getName, "The king")
				.list();
		list.forEach(System.out::println);
	}
Copy the code

The following is an example of an update

	@Test
	public void testChain(a) {
		userService.lambdaUpdate()
				.gt(User::getAge, 39)
				.likeRight(User::getName, "The king")
				.set(User::getEmail, "[email protected]")
				.update();
	}
Copy the code

The following is an example of a deletion

	@Test
	public void testChain(a) {
		userService.lambdaUpdate()
				.like(User::getName, "Frog")
				.remove();
	}
Copy the code

Conditional constructor

One thing I find extremely convenient about MP is that it provides a powerful condition constructor, Wrapper, which makes it very easy to construct WHERE conditions. The conditional constructor consists of three classes, AbstractWrapper. QueryWrapper, UpdateWrapper, their class relationships are as follows

AbstractWrapper provides a large number of methods for constructing a WHERE condition, QueryWrapper provides a SELECT () method for the SELECT statement, and UpdateWrapper for the UPDATE statement, The set() method is provided to construct a set statement. Conditional constructors also support lambda expressions, which are very comfortable to write.

The following is a partial enumeration of the AbstractWrapper method for constructing WHERE conditions in SQL statements

eqequals
allEq: all equals
ne: not equals
gt: greater than, greater than>
ge: greater than or equalsp
lt: Less than, less than<
le: less than or equalsOr less
between: is equivalent to BETWEEN in SQL
notBetween
like: Fuzzy matching.The like (" name ", "yellow"), equivalent to SQLName like '% yellow %'
likeRight: Fuzzy matches the right half.LikeRight (" name ", "yellow"), equivalent to SQLName like 'yellow %'
likeLeft: Fuzzy match left half.LikeLeft (" name ", "yellow"), equivalent to SQLName like '% yellow '
notLike:NotLike (" name ", "yellow"), equivalent to SQLName not like '% yellow %'
isNull
isNotNull
in
and: SQL connection character AND
or: SQL connection character OR
apply: used to concatenate SQL. This method can be used with database functions and can be used to pass parameters dynamically
.

Use the sample

Let’s practice using conditional constructors with some concrete examples. (Use the user table created above)

// This example shows the SQL statement that needs to be completed, and then shows how to write the Wrapper

// 1. Have a good name and be younger than 25 years old
// SELECT * FROM user WHERE name like '% % %' AND age < 25
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like("name"."Good").lt("age".25);
List<User> users = userMapper.selectList(wrapper);
// When SQL is displayed below, only the WHERE condition is displayed; When the code is shown, only the Wrapper build part is shown

// 2. The name must be huang, and the age must be between 20 and 40. The email field is not empty
// name like 'yellow %' AND age BETWEEN 20 AND 40 AND email is not null
wrapper.likeRight("name"."Yellow").between("age".20.40).isNotNull("email");

// 3. If your surname is Huang, or your age is greater than or equal to 40, you will be listed in descending order. If your age is the same, you will be listed in ascending order
// name like 'yellow %' OR age >= 40 Order by age desc, id ASC
wrapper.likeRight("name"."Yellow").or().ge("age".40).orderByDesc("age").orderByAsc("id");

// 4. The date of creation is March 22, 2021, and the name of the immediate superior is Li
// date_format(create_time,'%Y-%m-%d') = '2021-03-22' AND manager_id IN (SELECT id FROM user WHERE name like 'lee %')
wrapper.apply("date_format(create_time, '%Y-%m-%d') = {0}"."2021-03-22")  {index} is recommended for dynamic parameter transmission to prevent SQL injection
				.inSql("manager_id"."SELECT id FROM user WHERE name like '%'");
// If the date is an external parameter, this method has the risk of SQL injection
wrapper.apply("date_format(create_time, '%Y-%m-%d') = '2021-03-22'");

// 5. The name is Wang, and (the age is less than 40, or the mailbox is not empty)
// name like 'king %' AND (age < 40 OR email is not null)
wrapper.likeRight("name"."The king").and(q -> q.lt("age".40).or().isNotNull("email"));

// 6. The name is Wang, or (the age is less than 40 and the age is more than 20 and the mailbox is not empty)
// name like 'king %' OR (age < 40 AND age > 20 AND email is not null)
wrapper.likeRight("name"."The king").or(
				q -> q.lt("age".40)
						.gt("age".20)
						.isNotNull("email"));// 7. (the age is less than 40 or the mailbox is not empty) and the name is Wang
// (age < 40 OR email is not null) AND name like 'king %'
wrapper.nested(q -> q.lt("age".40).or().isNotNull("email"))
				.likeRight("name"."The king");

// 8. Age: 30,31,34,35
/ / the age (30,31,34,35) IN
wrapper.in("age", Arrays.asList(30.31.34.35));
/ / or
wrapper.inSql("age"."30,31,34,35");

If the age is 30,31,34,35, return the first record that satisfies the condition
// age IN (30,31,34,35) LIMIT 1
wrapper.in("age", Arrays.asList(30.31.34.35)).last("LIMIT 1");

// 10. Select only id, name columns (QueryWrapper only)
// SELECT id, name FROM user;
wrapper.select("id"."name");

// 11. Select ID, name, age, email, which equals to exclude manager_id and create_time
// When there are too many columns and only a few columns need to be excluded, the above method may require many columns. You can use the overloaded SELECT method to specify the columns to be excluded
wrapper.select(User.class, info -> {
			String columnName = info.getColumn();
			return !"create_time".equals(columnName) && !"manager_id".equals(columnName);
		});
Copy the code

Condition

The condition constructor’s methods can specify a Boolean condition parameter that determines whether the condition is included in the resulting WHERE statement, for example

String name = "Yellow"; // Assume that the name variable is an external passed parameter
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.like(StringUtils.hasText(name), "name", name);
// Only if stringutils.hastext (name) is true will the like statement be concatenated to WHERE
// This is a simplification of the following code
if (StringUtils.hasText(name)) {
	wrapper.like("name", name);
}
Copy the code

Entity objects as conditions

When you call the constructor to create a Wrapper object, you can pass in an entity object. Subsequent use of the Wrapper builds a WHERE condition from a non-empty property in the entity object. (By default, a WHERE condition is built with an equivalent match. This behavior can be changed by the condition attribute in the @TableField annotation on each field in the entity class.)

The sample is as follows

	@Test
	public void test3(a) {
		User user = new User();
		user.setName("Supervisor Huang");
		user.setAge(28);
		QueryWrapper<User> wrapper = newQueryWrapper<>(user); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); } Duplicate codeCopy the code

The result is as follows: As you can see, an equivalent matching query is performed based on a non-empty attribute in the entity object.

If you want to change the behavior of equivalent matching for certain attributes, you can configure it in the entity class using the @TableField annotation, as shown in the following example

package com.example.mp.po;
import com.baomidou.mybatisplus.annotation.SqlCondition;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class User {
	private Long id;
	@TableField(condition = SqlCondition.LIKE)   // Configure the field to be concatenated with like
	private String name;
	private Integer age;
	private String email;
	private Long managerId;
	private LocalDateTime createTime;
}
Copy the code

Run the following test code

	@Test
	public void test3(a) {
		User user = new User();
		user.setName("Yellow");
		QueryWrapper<User> wrapper = new QueryWrapper<>(user);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

As can be seen from the following figure, for the name field in the entity object, like is used for splicing

The condition attribute configured in @tableField is actually a string, and the SqlCondition class has predefined strings to choose from

package com.baomidou.mybatisplus.annotation;

public class SqlCondition {
    // In the following string, %s is the placeholder, the first %s is the column name, and the second %s is the column value
    public static final String EQUAL = "%s=#{%s}";
    public static final String NOT_EQUAL = "%s< > #{%s}";
    public static final String LIKE = "%s LIKE CONCAT('%%',#{%s},'%%')";
    public static final String LIKE_LEFT = "%s LIKE CONCAT('%%',#{%s})";
    public static final String LIKE_RIGHT = "%s LIKE CONCAT(#{%s},'%%')";
}
Copy the code

The configuration provided in SqlCondition is limited, and when we need concatenation methods such as < or >, we need to define our own. Such as

package com.example.mp.po;
import com.baomidou.mybatisplus.annotation.SqlCondition;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class User {
	private Long id;
	@TableField(condition = SqlCondition.LIKE)
	private String name;
    @TableField(condition = "%s &gt; #{%s}")   // this is equivalent to greater than, where > >; Is a character entity
	private Integer age;
	private String email;
	private Long managerId;
	private LocalDateTime createTime;
}
Copy the code

Test the following

	@Test
	public void test3(a) {
		User user = new User();
		user.setName("Yellow");
        user.setAge(30);
		QueryWrapper<User> wrapper = new QueryWrapper<>(user);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

As you can see from the following figure, the name attribute is concatenated with like and the age attribute is concatenated with >

AllEq method

The allEq method passes in a map for equivalence matching

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		Map<String, Object> param = new HashMap<>();
		param.put("age".40);
		param.put("name"."Huang Fei Fei");
		wrapper.allEq(param);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

If the allEq method passes a Map with a value of NULL, the default value is NULL

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		Map<String, Object> param = new HashMap<>();
		param.put("age".40);
		param.put("name".null);
		wrapper.allEq(param);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

If you want to ignore elements whose value is null in the map, set the Boolean null2IsNull to false when allEq is called

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		Map<String, Object> param = new HashMap<>();
		param.put("age".40);
		param.put("name".null);
		wrapper.allEq(param, false);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

Call allEq(BiPredicate

filter, Map

params).
,>
,>

	@Test
	public void test3(a) {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		Map<String, Object> param = new HashMap<>();
		param.put("age".40);
		param.put("name"."Huang Fei Fei");
		wrapper.allEq((k,v) -> !"name".equals(k), param); // Filter elements whose key is name from the map
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

Lambda conditional constructor

The lambda conditional constructor, which supports lambda expressions, does not specify column names as strings, as normal conditional constructors do. Instead, it can specify columns directly as method references of entity classes. The sample is as follows

	@Test
	public void testLambda(a) {
		LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
		wrapper.like(User::getName, "Yellow").lt(User::getAge, 30);
		List<User> users = userMapper.selectList(wrapper);
		users.forEach(System.out::println);
	}
Copy the code

Like normal conditional constructors, column names are specified as strings and cannot be checked for validity at compile time, which is less elegant than lambda conditional constructors.

In addition, there is a chained lambda conditional constructor, as shown in the following example

	@Test
	public void testLambda(a) {
		LambdaQueryChainWrapper<User> chainWrapper = new LambdaQueryChainWrapper<>(userMapper);
		List<User> users = chainWrapper.like(User::getName, "Yellow").gt(User::getAge, 30).list();
		users.forEach(System.out::println);
	}
Copy the code

The update operation

The above are all query operations, now update and delete operations.

BaseMapper provides two update methods

updateById(T entity)

UPDATE based on the id (primary key) of the input entity. Non-empty properties of the entity appear after the SET of the UPDATE statement, i.e. non-empty properties of the entity are updated to the database, as shown in the following example

  @RunWith(SpringRunner.class)
  @SpringBootTest
  public class UpdateTest {
  	@Autowired
  	private UserMapper userMapper;
  	@Test
  	public void testUpdate(a) {
  		User user = new User();
  		user.setId(2L);
  		user.setAge(18); userMapper.updateById(user); }}Copy the code

update(T entity, Wrapper<T> wrapper)

Updates based on entity Entity and conditional constructor Wrapper, as shown below

  	@Test
  	public void testUpdate2(a) {
  		User user = new User();
  		user.setName(Three Eggs of the King);
  		LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
  		wrapper.between(User::getAge, 26.31).likeRight(User::getName,"Wu");
  		userMapper.update(user, wrapper);
  	}
Copy the code

As an additional demonstration, pass an entity object into the Wrapper and use the entity object to construct a case of WHERE conditions

  	@Test
  	public void testUpdate3(a) {
  		User whereUser = new User();
  		whereUser.setAge(40);
  		whereUser.setName("The king");
  
  		LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>(whereUser);
  		User user = new User();
  		user.setEmail("[email protected]");
  		user.setManagerId(10L);
  
  		userMapper.update(user, wrapper);
  	}
Copy the code

Notice that in our User class, the name and age properties are set as follows

@Data
public class User {
	private Long id;
	@TableField(condition = SqlCondition.LIKE)
	private String name;
	@TableField(condition = "%s &gt; #{%s}")
	private Integer age;
	private String email;
	private Long managerId;
	private LocalDateTime createTime;
}
Copy the code

The execution result

As an additional demonstration, use of the chained lambda conditional constructor

	@Test
	public void testUpdate5(a) {
		LambdaUpdateChainWrapper<User> wrapper = new LambdaUpdateChainWrapper<>(userMapper);
		wrapper.likeRight(User::getEmail, "share")
				.like(User::getName, "Feifei")
				.set(User::getEmail, "[email protected]")
				.update();
	}
Copy the code

reflection

Since both of BaseMapper’s update methods pass in an entity object to perform the update, this is fine if there are many columns that need to be updated, but if there are only one or two columns that need to be updated, creating an entity object becomes a bit of a hassle. In this case, UpdateWrapper provides a set method that allows you to manually concatenate set statements in SQL without passing in entity objects, as shown in the following example

	@Test
	public void testUpdate4(a) {
		LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
		wrapper.likeRight(User::getEmail, "share").set(User::getManagerId, 9L);
		userMapper.update(null, wrapper);
	}
Copy the code

Delete operation

BaseMapper provides the following methods for deleting

deleteByIdDelete by primary key ID
deleteBatchIdsDelete data in batches based on the primary key ID
deleteByMapDelete by Map (key is the column name, value is the value, and equivalence matching is performed by column and value)
delete(Wrapper<T> wrapper)According to the conditional constructorWrapperTo delete

With the previous query and update operations are similar, do not repeat

Custom SQL

When the methods provided by MP do not meet the requirements, you can customize the SQL.

Native mybatis

The sample is as follows

Annotation way

package com.example.mp.mappers;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mp.po.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/ * * *@Author yogurtzzz
 * @Date2021/3/18 * * / calling
public interface UserMapper extends BaseMapper<User> {
	
	@Select("select * from user")
	List<User> selectRaw(a);
}
Copy the code

XML way

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.example.mp.mappers.UserMapper"> <select id="selectRaw" resultType="com.example.mp.po.User"> SELECT * FROM  user </select> </mapper> package com.example.mp.mappers; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.example.mp.po.User; import org.apache.ibatis.annotations.Select; import java.util.List; public interface UserMapper extends BaseMapper<User> { List<User> selectRaw(); }Copy the code

If XML files and mapper interface files are not in the same directory, you need to configure a path for storing mapper. XML in application.yml

mybatis-plus:
  mapper-locations: /mappers/*
Copy the code

If there are multiple places to store the Mapper, configure it as an array

mybatis-plus:
  mapper-locations: 
  - /mappers/*
  - /com/example/mp/*
Copy the code

The test code is as follows

	@Test
	public void testCustomRawSql(a) {
		List<User> users = userMapper.selectRaw();
		users.forEach(System.out::println);
	}
Copy the code

The results of

mybatis-plus

You can also customize SQL using the Wrapper condition constructor provided by MP

The sample is as follows

Annotation way

package com.example.mp.mappers;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.mp.po.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;

public interface UserMapper extends BaseMapper<User> {

    ${ew.customsqlSegment} ${ew.customsqlSegment}
	@Select("select * from user ${ew.customSqlSegment}")
	List<User> findAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);
}
Copy the code

XML way

package com.example.mp.mappers;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mp.po.User;
import java.util.List;

public interface UserMapper extends BaseMapper<User> {
	List<User> findAll(Wrapper<User> wrapper); } Copy code <! -- UserMapper.xml --> <? xml version="1.0" encoding="UTF-8"? > <! DOCTYPE mapper PUBLIC"- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mp.mappers.UserMapper">

    <select id="findAll" resultType="com.example.mp.po.User">
        SELECT * FROM user ${ew.customSqlSegment}
    </select>
</mapper>
Copy the code

Paging query

BaseMapper provides two methods for paging queries, selectPage and selectMapsPage. The former encapsulates the query results as Java entity objects, and the latter encapsulates the query results as Map

. An example of a paging query is shown below
,object>

Create a page interceptor for MP and register it with the Spring container

   package com.example.mp.config;
   import com.baomidou.mybatisplus.annotation.DbType;
   import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
   import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   
   @Configuration
   public class MybatisPlusConfig {
   
       /** new version of mp **/
   	@Bean
   	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
   		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
   		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
   		return interceptor;
   	}
       /** Older versions of MP use PaginationInterceptor **/
   }
Copy the code

Perform paging query

   	@Test
   	public void testPage(a) {
   		LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
   		wrapper.ge(User::getAge, 28);
           // Set the page information, look up page 3, 2 data per page
   		Page<User> page = new Page<>(3.2);
           // Perform paging queries
   		Page<User> userPage = userMapper.selectPage(page, wrapper);
   		System.out.println("Total records =" + userPage.getTotal());
   		System.out.println("Total pages =" + userPage.getPages());
   		System.out.println("Current page number =" + userPage.getCurrent());
           // Get paging query results
   		List<User> records = userPage.getRecords();
   		records.forEach(System.out::println);
   	}
Copy the code

The results of
other
- Note that the paged query issues the SQL twice, once for the total number of records and once for the specific data.If you do not want to look up the total number of records, look up only the paging results. Can be achieved byPageOverloaded constructor, specifiedisSearchCountforfalseCan be

     public Page(long current, long size, boolean isSearchCount)
Copy the code

In actual development, multi-table joint query may occur. In this case, the single-table paging query method provided in BaseMapper cannot meet the requirements, and it is necessary to customize SQL, as shown in the following example (the SQL of single-table query is used to demonstrate, and the SQL statement can be modified in actual multi-table joint query).
1. Define a function in the Mapper interface that takes a Page object as a parameter and writes custom SQL

        // Pure annotations are used here. Of course, if SQL is complex, it is recommended to use XML
        @Select("SELECT * FROM user ${ew.customSqlSegment}")
        Page<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);
Copy the code

2. Execute the queryCopy the code

        	@Test
        	public void testPage2(a) {
        		LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        		wrapper.ge(User::getAge, 28).likeRight(User::getName, "The king");
        		Page<User> page = new Page<>(3.2);
        		Page<User> userPage = userMapper.selectUserPage(page, wrapper);
        		System.out.println("Total records =" + userPage.getTotal());
        		System.out.println("Total pages =" + userPage.getPages());
        		userPage.getRecords().forEach(System.out::println);
        	}
Copy the code

The results of

AR model

ActiveRecord mode: Directly operates database tables by manipulating entity objects. This is somewhat similar to ORM.

The sample is as follows

Let the entity classUserInherited fromModel

   package com.example.mp.po;
   
   import com.baomidou.mybatisplus.annotation.SqlCondition;
   import com.baomidou.mybatisplus.annotation.TableField;
   import com.baomidou.mybatisplus.extension.activerecord.Model;
   import lombok.Data;
   import lombok.EqualsAndHashCode;
   import java.time.LocalDateTime;
   
   @EqualsAndHashCode(callSuper = false)
   @Data
   public class User extends Model<User> {
   	private Long id;
   	@TableField(condition = SqlCondition.LIKE)
   	private String name;
   	@TableField(condition = "%s &gt; #{%s}")
   	private Integer age;
   	private String email;
   	private Long managerId;
   	private LocalDateTime createTime;
   }
Copy the code

Calls methods directly on entity objects

   	@Test
   	public void insertAr(a) {
   		User user = new User();
   		user.setId(15L);
   		user.setName("I'm an AR pig.");
   		user.setAge(1);
   		user.setEmail("[email protected]");
   		user.setManagerId(1L);
   		boolean success = user.insert(); / / insert
   		System.out.println(success);
   	}
Copy the code

The results of

Other examples

	/ / query
	@Test
	public void selectAr(a) {
		User user = new User();
        user.setId(15L);
		User result = user.selectById();
		System.out.println(result);
	}
	/ / update
	@Test
	public void updateAr(a) {
		User user = new User();
		user.setId(15L);
		user.setName("King's Whole egg");
		user.updateById();
	}
	/ / delete
	@Test
	public void deleteAr(a) {
		User user = new User();
		user.setId(15L);
		user.deleteById();
	}
Copy the code

The primary key strategy

When you define an entity class, you use @tableID to specify the primary key, and its Type property, you can specify the primary key policy.

Mp supports a variety of primary key policies, the default policy is based on the snowflake algorithm auto-increment ID. All primary key policies are defined in the enumeration class IdType, which has the following values

AUTO

The database ID is self-increasing and depends on the database. When an insert operation generates an SQL statement, the primary key column is not inserted
NONE

The primary key type is not set. If the primary key is not set manually in the code, it is automatically generated based on the global policy for the primary key (the default global policy for the primary key is based on the auto-increment ID of the Snowflake algorithm).
INPUT

You need to manually set the primary key. When an insert operation generates an SQL statement, the primary key column will be null. Oracle’s sequence primary key needs to use this approach
ASSIGN_ID

When the primary key is not set manually, that is, the primary key attribute in the entity class is empty, it will be filled automatically, using the snowflake algorithm
ASSIGN_UUID

When the primary key attribute of the entity class is empty, it is automatically populated, using the UUID
. (Several others are outdated, so I will not list them.)

For each entity class, you can specify the primary key policy for that entity class using the @TableID annotation, which can be understood as a local policy. If you want to use the same primary key policy for all entity classes, it is too cumbersome to configure each entity class one by one. In this case, you can use the global primary key policy. Just configure it in application.yml. For example, the global auto-increment primary key policy is configured

# application.yml
mybatis-plus:
  global-config:
    db-config:
      id-type: auto
Copy the code

The behavior of different primary key policies is illustrated below

AUTO

MYSQL > select * from User where id = ‘User’;

  @EqualsAndHashCode(callSuper = false)
  @Data
  public class User extends Model<User> {
  	@TableId(type = IdType.AUTO)
  	private Long id;
  	@TableField(condition = SqlCondition.LIKE)
  	private String name;
  	@TableField(condition = "%s &gt; #{%s}")
  	private Integer age;
  	private String email;
  	private Long managerId;
  	private LocalDateTime createTime;
  }
Copy the code

test

  	@Test
  	public void testAuto(a) {
  		User user = new User();
  		user.setName("I am a frog croak.");
  		user.setAge(99);
  		user.setEmail("[email protected]");
  		user.setCreateTime(LocalDateTime.now());
  		userMapper.insert(user);
          System.out.println(user.getId());
  	}
Copy the code

The results of

As you can see, the primary key ID is not set in the code, nor is the primary key ID set in the ISSUED SQL statement, and the primary key ID is written back to the entity object after the insertion.

NONE

MYSQL > alter table user; delete primary key increment. Then modify the User class (the default primary key policy is NONE without the @tableId annotation)

  @TableId(type = IdType.NONE)
  private Long id;
Copy the code

When inserting, use the primary key ID of the entity class if it has a value; If the primary key ID is empty, the global primary key policy is used to generate an ID.

The rest of the strategies are similar and will not be repeated

summary

AUTO depends on the auto-increment primary key of the database. You do not need to set the primary key for the entity. After the AUTO is successfully inserted, the primary key is written back to the entity.

INPUT 'is entirely dependent on user INPUT. The primary key ID of the entity object is set when it is inserted into the database. Set the value if it has a value, or null if it has a valueCopy the code

The remaining policies are automatically generated when the primary key ID in the entity object is null.

NONE follows the global policy, ASSIGN_ID uses the snowflake algorithm, and ASSIGN_UUID uses the UUID

The global configuration can be done in application. Yml. For a local configuration of a single entity class, use @tableID. For an entity class, if it has a local primary key policy, it adopts it; otherwise, it follows the global policy.

configuration

Mybatis Plus has a number of configurable items that can be configured in application.yml, such as the global primary key policy above. The following lists some configuration items

The basic configuration

configLocationIf there is a separate MyBatis configuration, use this annotation to specify the myBatis configuration file (myBatis global configuration file)
mapperLocations: Indicates the location of the XML file corresponding to mybatis mapper
typeAliasesPackage: alias packet scan path of Mybatis
.

The advanced configuration

MapUnderscoreToCamelCase: Indicates whether to enable automatic hump naming rule mapping. (Enabled by default)
DbTpe: indicates the database type. Generally, it is not required. It is automatically identified according to the DATABASE connection URL
FieldStrategy :(obsolete) field validation strategy. This configuration item is not found in the latest version of mp documentation and is subdivided into insertStrategy, updateStrategy, and selectStrategy. The default value is NOT_NULL, meaning that fields that are not empty for entity objects are assembled into the final SQL statement.

The configuration options are as follows
- IGNORED: Ignores verification. That is, no checks. All fields in the entity object, regardless of value, are faithfully assembled into SQL statements (forNULLIs assembled in the SQL statement asNULL).
- NOT_NULL:NULLCheck. Will not onlyNULLTo assemble the fields into SQL statements
- NOT_EMPTY: Non-null check. When a field is a string, only non-empty strings are assembled. For other types of fields, equal toNOT_NULL
- NEVER: Does not add SQL. All fields are not added to the SQL statement
This configuration item can be configured globally in application.yml or locally in an entity class with the @TableField annotation for a field

What is the use of this field validation policy? If we UPDATE a User object, we want to UPDATE only the non-empty attributes of the User object to the database. Otherwise, NOT_NULL will suffice. When updateStrategy is configured as IGNORED, no non-null judgment is performed and all attributes of the entity object are faithfully assembled into SQL. Therefore, when performing UPDATE, some fields that you do not want to UPDATE may be set to NULL.
TablePrefix: Adds the table name prefix

Such as

  mybatis-plus:
    global-config:
      db-config:
        table-prefix: xx_
Copy the code

MYSQL > alter table MYSQL > alter table MYSQL > alter table MYSQL > alter table MYSQL But the Java entity class remains the same (still User).

test

  	@Test
  	public void test3(a) {
  		QueryWrapper<User> wrapper = new QueryWrapper<>();
  		wrapper.like("name"."Yellow");
  		Integer count = userMapper.selectCount(wrapper);
  		System.out.println(count);
  	}
Copy the code

You can see the concatenated SQL, prefixed to the table name

Code generator

Mp provides a generator, can quickly generate Entity class, Mapper interface, Service, Controller and other full set of code.

The sample is as follows

public class GeneratorTest {
	@Test
	public void generate(a) {
		AutoGenerator generator = new AutoGenerator();

		// Global configuration
		GlobalConfig config = new GlobalConfig();
		String projectPath = System.getProperty("user.dir");
		// Set the directory to output to
		config.setOutputDir(projectPath + "/src/main/java");
		config.setAuthor("yogurt");
		// Whether to open the folder after the build is complete
		config.setOpen(false);

		// Add global configuration to generator
		generator.setGlobalConfig(config);

		// Data source configuration
		DataSourceConfig dataSourceConfig = new DataSourceConfig();
		dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/yogurt? serverTimezone=Asia/Shanghai");
		dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
		dataSourceConfig.setUsername("root");
		dataSourceConfig.setPassword("root");

		// Add the data source configuration to the generator
		generator.setDataSource(dataSourceConfig);

		// Package configuration, which package to put the generated code under
		PackageConfig packageConfig = new PackageConfig();
		packageConfig.setParent("com.example.mp.generator");

		// Add the package configuration to the generator
		generator.setPackageInfo(packageConfig);

		// Policy configuration
		StrategyConfig strategyConfig = new StrategyConfig();
		// Underline camel name conversion
		strategyConfig.setNaming(NamingStrategy.underline_to_camel);
		strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);
		/ / open lombok
		strategyConfig.setEntityLombokModel(true);
		/ / open RestController
		strategyConfig.setRestControllerStyle(true);
		generator.setStrategy(strategyConfig);
		generator.setTemplateEngine(new FreemarkerTemplateEngine());

        // start generatinggenerator.execute(); }}Copy the code

When it runs, you can see the full set of code generated as shown in the figure below

Advanced features

The demonstration of advanced functionality requires a new table, user2

DROP TABLE IF EXISTS user2;
CREATE TABLE user2 (
id BIGINT(20) PRIMARY KEY NOT NULL COMMENT 'primary key id',
name VARCHAR(30) DEFAULT NULL COMMENT 'name',
age INT(11) DEFAULT NULL COMMENT 'age',
email VARCHAR(50) DEFAULT NULL COMMENT 'email',
manager_id BIGINT(20) DEFAULT NULL COMMENT 'Immediate superior ID',
create_time DATETIME DEFAULT NULL COMMENT 'Creation time',
update_time DATETIME DEFAULT NULL COMMENT 'Modification time',
version INT(11) DEFAULT '1' COMMENT 'version',
deleted INT(1) DEFAULT '0' COMMENT 'Logical deletion identifier,0- Not deleted,1- Deleted'.CONSTRAINT manager_fk FOREIGN KEY(manager_id) REFERENCES user2(id)
) ENGINE = INNODB CHARSET=UTF8;

INSERT INTO user2(id, name, age, email, manager_id, create_time)
VALUES
(1.'the boss'.40 ,'[email protected]' ,NULL.'the 2021-03-28 13:12:40'),
(2.'King Dog egg'.40 ,'[email protected]' ,1.'the 2021-03-28 13:12:40'),
(3.'King's Egg'.40 ,'[email protected]' ,2.'the 2021-03-28 13:12:40'),
(4.'King duck egg'.40 ,'[email protected]' ,2.'the 2021-03-28 13:12:40'),
(5.'King pig eggs'.40 ,'[email protected]' ,2.'the 2021-03-28 13:12:40'),
(6.'Soft King'.40 ,'[email protected]' ,2.'the 2021-03-28 13:12:40'),
(7.'Iron egg'.40 ,'[email protected]' ,2.'the 2021-03-28 13:12:40') Copy codeCopy the code

And create the corresponding entity class User2

package com.example.mp.po;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class User2 {
	private Long id;
	private String name;
	private Integer age;
	private String email;
	private Long managerId;
	private LocalDateTime createTime;
	private LocalDateTime updateTime;
	private Integer version;
	private Integer deleted;
}
Copy the code

And the Mapper interface

package com.example.mp.mappers;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mp.po.User2;
public interface User2Mapper extends BaseMapper<User2> {}Copy the code

Logic to delete

First of all, why have logical deletions? Can’t you just delete it? Sure, but not later if you want to recover or need to look at the data. Logical deletion is a scheme to facilitate data recovery and protect the value of data itself.

On a daily basis, when we delete a file from our computer, we simply put it in the recycle bin, where we can view or recover it later if necessary. When we determine that a file is no longer needed, we can delete it completely from the recycle bin. It’s the same thing.

The logical deletion provided by MP is very simple to implement

You only need to configure the logical deletion in application.yml

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted  The name of the entity field to be deleted by global logic
      logic-delete-value: 1 # Logical deleted value (default: 1)
      logic-not-delete-value: 0 # Logical undeleted value (default: 0)
      If the value of the deleted and undeleted logic is the same as the default value, the two items can not be configured
Copy the code

The test code

package com.example.mp;
import com.example.mp.mappers.User2Mapper;
import com.example.mp.po.User2;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class LogicDeleteTest {
	@Autowired
	private User2Mapper mapper;
	@Test
	public void testLogicDel(a) {
		int i = mapper.deleteById(6);
		System.out.println("rowAffected = "+ i); }}Copy the code

The results of

As you can see, the SQL issued is no longer a DELETE, but an UPDATE

At this point we perform a SELECT again

	@Test
	public void testSelect(a) {
		List<User2> users = mapper.selectList(null);
	}
Copy the code

As you can see, the issued SQL statement will automatically concatenate conditions after WHERE that the logic has not deleted. In the query result, there is no WANG Soft egg whose ID is 6.

If you want to SELECT columns that do not include the column that is logically deleted, you can configure it in the entity class via @TableField

@TableField(select = false)
privateInteger deleted; Copy the codeCopy the code

As you can see, the SELECT column does not contain the deleted column

The configuration we did in application.yml is global. In general, for multiple tables, we also unify the names of fields that are logically deleted, and the values that are logically deleted and not deleted, so global configuration is ok. Of course, to configure some tables separately, use @tablelogic on the corresponding fields of the entity class

@TableLogic(value = "0", delval = "1")
private Integer deleted;
Copy the code

summary

After the logical deletion of MP is enabled, SQL is affected as follows

INSERT statement: no impact
SELECT statement: appends WHERE conditions to filter out deleted data
UPDATE statement: Appends WHERE conditions to prevent updates to deleted data
DELETE statement: Change to UPDATE statement

** Note that the above effects only apply to SQL that is automatically injected by MP. ** If you manually add custom SQL, it will not take effect. Such as

public interface User2Mapper extends BaseMapper<User2> {
	@Select("select * from user2")
	List<User2> selectRaw(a);
}
Copy the code

When this selectRaw is called, the logical deletion of mp does not take effect.

In addition, logical deletion can be configured globally in application.yml or locally in the entity class with @tablelogic.

Automatic filling

The table often has “new time”, “modify time”, “operator” and other fields. The more primitive way is to set it manually every time you insert or update it. Mp can be configured to automatically fill certain fields. The following is an example

On some fields in the entity class, pass@TableFieldSetting automatic fill

   public class User2 {
   	private Long id;
   	private String name;
   	private Integer age;
   	private String email;
   	private Long managerId;
   	@TableField(fill = FieldFill.INSERT) // Auto-fill when inserting
   	private LocalDateTime createTime;
   	@TableField(fill = FieldFill.UPDATE) // Automatically populate when updating
   	private LocalDateTime updateTime;
   	private Integer version;
   	private Integer deleted;
   }
Copy the code

Implement automatic fill processor

   package com.example.mp.component;
   import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
   import org.apache.ibatis.reflection.MetaObject;
   import org.springframework.stereotype.Component;
   import java.time.LocalDateTime;
   
   @Component // Need to register with the Spring container
   public class MyMetaObjectHandler implements MetaObjectHandler {
   
   	@Override
   	public void insertFill(MetaObject metaObject) {
           // Auto-fill when inserting
           // Note that the second parameter is the name of the field in the entity class, not the column name of the table
   		strictFillStrategy(metaObject, "createTime", LocalDateTime::now);
   	}
   
   	@Override
   	public void updateFill(MetaObject metaObject) {
           // Automatically populate when updating
   		strictFillStrategy(metaObject, "updateTime", LocalDateTime::now); }}Copy the code

test

	@Test
	public void test(a) {
		User2 user = new User2();
		user.setId(8L);
		user.setName("The King lays an egg.");
		user.setAge(29);
		user.setEmail("[email protected]");
		user.setManagerId(2L);
		mapper.insert(user);
	}
Copy the code

Based on the results below, you can see that createTime is automatically populated

Note that autofill only works if the field is empty; otherwise, the existing value is used. The following

	@Test
	public void test(a) {
		User2 user = new User2();
		user.setId(8L);
		user.setName("The King lays an egg.");
		user.setAge(29);
		user.setEmail("[email protected]");
		user.setManagerId(2L);
		user.setCreateTime(LocalDateTime.of(2000.1.1.8.0.0));
		mapper.insert(user);
	}
Copy the code

Update the auto-fill test as follows

	@Test
	public void test(a) {
		User2 user = new User2();
		user.setId(8L);
		user.setName("The King lays an egg.");
		user.setAge(99);
		mapper.updateById(user);
	}
Copy the code

Optimistic lock plug-in

When concurrent operations occur, ensure that users’ operations on data do not conflict. In this case, a concurrency control method is required. Pessimistic locking method is that when modifying a record in the database, first directly lock (database locking mechanism), lock this data, and then perform operations; Optimistic locking, as its name suggests, assumes no conflicts and checks for them when the data is actually being manipulated. One common implementation of optimistic locking is version-based concurrent transaction control, also known in MySQL as MVCC.

Optimistic locking is suitable for scenarios with many reads and few writes to reduce the performance overhead caused by locking operations and improve system throughput.

Pessimistic locks are used in scenarios where there are many writes and few reads. Otherwise, the performance deteriorates due to repeated failures of optimistic locks.

The implementation of optimistic locking is as follows:

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 oldVersion does not match the version in the database, the update fails

This idea is very similar to CAS (Compare And Swap).

The steps to implement optimistic locking are as follows

Configure the optimistic lock plug-in

   package com.example.mp.config;
   
   import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   
   @Configuration
   public class MybatisPlusConfig {
       /** For MP versions later than 3.4.0, the following configuration is recommended **/
   	@Bean
   	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
   		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
   		interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
   		return interceptor;
   	}
       /** Older versions of MP can be used in the following manner. Note that the new version of the class has the name of Inner, the old version does not, do not mismatch the **/
       /* @Bean public OptimisticLockerInterceptor opLocker() { return new OptimisticLockerInterceptor(); } * /
   }
Copy the code

Add annotations to the fields in the entity class that represent the version@Version

   @Data
   public class User2 {
   	private Long id;
   	private String name;
   	private Integer age;
   	private String email;
   	private Long managerId;
   	private LocalDateTime createTime;
   	private LocalDateTime updateTime;
   	@Version
   	private Integer version;
   	private Integer deleted;
   }
Copy the code

The test code

	@Test
	public void testOpLocker(a) {
		int version = 1; // Assume that this version was obtained from a previous query
		User2 user = new User2();
		user.setId(8L);
		user.setEmail("[email protected]");
		user.setVersion(version);
		int i = mapper.updateById(user);
	}
Copy the code

Take a look at the database before executing

Based on the following results, you can see that version related operations have been added to the SQL statement

If UPDATE returns 1, the number of affected rows is 1, and the UPDATE is successful. Otherwise, because the version after WHERE is inconsistent with that in the database, no record can be matched, the number of affected rows is 0, indicating that the update failed. After a successful update, the new version is encapsulated back into the entity object.

The version field in the entity class supports only int, LONG, Date, Timestamp, and LocalDateTime

Note that the Optimistic lock plug-in is only supportedupdateById(id)withupdate(entity, wrapper)methods

** Note: If wrapper is used, wrapper cannot be reused! ** The following is an example

	@Test
	public void testOpLocker(a) {
		User2 user = new User2();
		user.setId(8L);
		user.setVersion(1);
		user.setAge(2);

		// First use
		LambdaQueryWrapper<User2> wrapper = new LambdaQueryWrapper<>();
		wrapper.eq(User2::getName, "The King lays an egg.");
		mapper.update(user, wrapper);

		// Reuse the second time
		user.setAge(3);
		mapper.update(user, wrapper);
	}
Copy the code

You can see that on the second reuse of the Wrapper, there is a problem with version appearing twice in the following WHERE statement in the concatenated SQL.

Performance analysis plug-in

The plug-in outputs the execution time of SQL statements for performance analysis and tuning of SQL statements.

Note: after version 3.2.0, the performance analysis plug-in of MP has been removed officially, and it is recommended to use third-party performance analysis plug-in

Edible steps

Introducing Maven dependencies

   <dependency>
       <groupId>p6spy</groupId>
       <artifactId>p6spy</artifactId>
       <version>3.91.</version>
   </dependency>
Copy the code

Modify theapplication.yml

   spring:
     datasource:
       driver-class-name: com.p6spy.engine.spy.P6SpyDriver # Switch to the p6Spy driver
       url: jdbc:p6spy:mysql://localhost:3306/yogurt? serverTimezone=Asia/Shanghai # url to modify
       username: root
       password: root
Copy the code

insrc/main/resourcesAdded in the resource directoryspy.properties

   #spy.properties
#3.2.1 Use above
   modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# Real JDBC driver, multiple separated by commas, null by default. Since modulelist is set above, driverList is not required
   #driverlist=com.mysql.cj.jdbc.Driver
# Custom log printing
   logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
Log output to console
   appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
To export logs to a file, comment out appnder, or use the appender appender below, and add logFile
When appender is not configured, the default is to export to the file
   #appender=com.p6spy.engine.spy.appender.FileLogger
   #logfile=log.log
P6spy Driver agent
   deregisterdrivers=true
# remove the JDBC URL prefix
   useprefix=true
# configuration record Log exceptions, can get rid of the result set of the error, the info of batch, debug, statement, commit, rollback, the result, the resultset.
   excludecategories=info,debug,result,commit,resultset
# date format
   dateformat=yyyy-MM-dd HH:mm:ss
Whether to enable slow SQL recording
   outagedetection=true
Slow SQL record standard 2 seconds
   outagedetectioninterval=2
The default value is 0, in milliseconds
   executionThreshold=10
Copy the code

Run any test case and you can see that the execution time of the SQL is recorded

Multi-tenant SQL parser

Multi-tenant: Multiple users share a system, but their data needs to be relatively independent and isolated.

Multi-tenant data isolation can be performed in the following ways:

Different tenants use different database servers

The advantages are that different tenants have separate databases, which facilitate scaling and provide better personalization for different tenants, making it easier to recover data in the event of a failure.

Disadvantages: increased the number of databases, purchase costs, higher maintenance costs
Different tenants use the same database server, but different databases (different schemas)

The advantage is that the acquisition and maintenance costs are lower, while the disadvantage is that data recovery is difficult, because the data of different tenants are kept together
Different tenants use the same database server, use the same database, share data tables, and add tenant ids in the tables to distinguish them

Advantages are the lowest purchase and maintenance costs, support the most users, disadvantages are the lowest isolation, the lowest security

Examples are as follows

Add a multi-tenant interceptor configuration. After the configuration is added, conditions for the tenant ID are automatically concatenated at the end of the SQL statement during CRUD execution

package com.example.mp.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
			@Override
			public Expression getTenantId(a) {
				// Returns the value of the tenant ID, which is fixed to 1
                // Usually a tenant ID is fetched from the current context
				return new LongValue(1);
			}

            /** ** typically encapsulates the column names representing tenant ids, tables that need to exclude tenant ids, and other information into a configuration class (such as TenantConfig) **/
			@Override
			public String getTenantIdColumn(a) {
				// Returns the column name in the table representing the tenant ID
				return "manager_id";
			}

			@Override
			public boolean ignoreTable(String tableName) {
				// Do not concatenate multi-tenant conditions for a table whose name is not user2
				return !"user2".equals(tableName); }}));/ / if the paging plug-ins add TenantLineInnerInterceptor first, then add PaginationInnerInterceptor attention
        MybatisConfiguration#useDeprecatedExecutor = false if the paging plugin is used
		returninterceptor; }}Copy the code

The test code

	@Test
	public void testTenant(a) {
		LambdaQueryWrapper<User2> wrapper = new LambdaQueryWrapper<>();
		wrapper.likeRight(User2::getName, "The king")
				.select(User2::getName, User2::getAge, User2::getEmail, User2::getManagerId);
		user2Mapper.selectList(wrapper);
	}
Copy the code

Dynamic table name SQL parser

When the amount of data is very large, we usually use sub-database sub-table. In this case, you might have multiple tables with the same table structure but different table names. For example, order_1, order_2, order_3, we may need to dynamically set the table name to be queried. Mp provides a dynamic table name SQL parser, as shown in the following example

Mysql > alter table user2

Configure dynamic table name interceptors

package com.example.mp.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.HashMap;
import java.util.Random;

@Configuration
public class MybatisPlusConfig {

	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
		HashMap<String, TableNameHandler> map = new HashMap<>();
        // For table user2, set the dynamic table name
		map.put("user2", (sql, tableName) -> {
			String _ = "_";
			int random = new Random().nextInt(2) + 1;
			return tableName + _ + random; // If null is returned, the dynamic table name replacement will not be performed and user2 will still be used
		});
		dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
		interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
		returninterceptor; }}Copy the code

test

	@Test
	public void testDynamicTable(a) {
		user2Mapper.selectList(null);
	}
Copy the code

conclusion

The conditional constructor AbstractWrapper provides several methods for constructing WHERE conditions in SQL statements. Its subclass QueryWrapper provides an additional select method that can select only specific columns. Its subclass UpdateWrapper provides an additional set method. Used to SET a SET statement in SQL. In addition to regular wrappers, there are lambda express-based wrappers, such as LambdaQueryWrapper and LambdaUpdateWrapper, which specify columns in a WHERE condition directly with method references when constructing a WHERE condition. Much more elegant than a normal Wrapper specifying a string. There are also chained wrappers, such as LambdaQueryChainWrapper, which encapsulates the BaseMapper to make it easier to get results.
The condition constructor uses chained calls to concatenate multiple conditions, which are joined by AND by default
When a condition following an AND OR OR needs to be wrapped in parentheses, pass the condition in parentheses as a lambda expression, AND () OR OR () as an argument

Specifically, the nested() method can be used when () needs to be placed at the beginning of a WHERE statement
When a custom SQL statement needs to be passed in or a database function needs to be called, the Apply () method is used for SQL concatenation
Methods in the condition constructor can flexibly concatenate WHERE conditions as needed, using a Boolean condition variable (condition will concatenate SQL statements only if condition is true).
With lambda conditional constructors, you can construct conditions using attributes in entity classes directly from lambda expressions, more elegantly than normal conditional constructors
If the method provided by MP is not enough, you can extend the development in the form of custom SQL (native Mybatis)
When using MP for paging queries, you need to create a paging Interceptor, register it in the Spring container, and then pass in a paging object (Page object) to query. For single-table queries, you can use the selectPage or selectMapsPage methods provided by BaseMapper. In complex scenarios (such as multi-table query), use customized SQL.
The AR schema can manipulate the database directly by manipulating entity classes. Let the entity class inherit from Model

By Yogurtzzz juejin.cn/post/696172…

Mybatis Plus just need to read this one

Quick start

The core function

annotations

CRUD interface

Mapper CRUD interface

selectMaps

selectObjs

selectCount

The Service of the CRUD interface

Conditional constructor

Use the sample

Condition

Entity objects as conditions

AllEq method

Lambda conditional constructor

The update operation

Delete operation

Custom SQL

Native mybatis

mybatis-plus

Paging query

AR model

The primary key strategy

configuration

The basic configuration

The advanced configuration

Code generator

Advanced features

Logic to delete

Automatic filling

Optimistic lock plug-in

Performance analysis plug-in

Multi-tenant SQL parser

Dynamic table name SQL parser

conclusion

Related Posts

A collection of 2021Java knowledge points carefully compiled by the former ali architect has been collected by millions of people

Two go development utilities

How to implement MVCC concurrent transaction control based on undo log +Read View