Review of MyBatis

1.1. Review MyBatis

1.1.1. Database construction and table construction

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL.`password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Copy the code

1.1.2 introducing dependencies

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.2</version>
		</dependency>
 
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
Copy the code

1.1.3 and application properties

spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql:///db? useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
spring.datasource.druid.username=root
spring.datasource.druid.password=123456
Copy the code

1.1.4. Write Mapper interface

public interface UserMapper {
    List<Employee> selectAll(a);
}
Copy the code

1.1.5 write mapper.xml

  <select id="selectAll" resultMap="BaseResultMap">
    select id,username,password
    from user
  </select>
Copy the code

1.1.6 Disadvantages of MyBatis

We can find that traditional MyBatis has a fatal problem, each entity table corresponds to an entity class, corresponding to a Mapper. Java interface, corresponding to a mapper. XML configuration file each mapper. Java interface has repeated CRUD methods, Each mapper.xml has repeated SQL configuration for CRUD. The only way to solve this problem is to use MyBatis-Plus.

Learn about Mybatis-Plus

MyBatis-Plus (MP for short) is a MyBatis enhancement tool, on the basis of MyBatis only do enhancement do not change, to simplify the development and improve efficiency.

2.1. Code and documentation

Mybatis. Plus/Guide/source code address: github.com/baomidou/my…

2.2, features,

1. No intrusion: only enhancements are made, no changes are made, and its introduction will not affect the existing project, as smooth as silk.
2. Low loss: Basic CURD will be injected automatically upon startup, with basically no loss in performance and direct object-oriented operation.
3. Powerful CRUD operations: built-in universal Mapper, universal Service, only through a small amount of configuration can achieve a single table most CRUD operations, more powerful condition constructor, to meet all types of use requirements.
4. Support Lambda form call: through Lambda expressions, it is convenient to write all kinds of query conditions, without worrying about field write errors.
5. Supports multiple databases: supports MySQL, MariaDB, Oracle, DB2, H2, HSQL, SQLite, Postgre, SQLServer2005, SQLServer and other databases.
6. Support automatic generation of primary keys: support up to four primary key policies (including distributed unique ID generator – Sequence), can be freely configured, perfect solution to the primary key problem.
7. Support for XML hot loading: Mapper’s CORRESPONDING XML supports hot loading and can even start without XML for simple CRUD operations.
8. Support for ActiveRecord mode: Support for ActiveRecord form calls, entity classes only need to inherit Model classes for powerful CRUD operations.
9. Support for customized global universal operations: Support Write once (use anywhere).
10. Support automatic keyword escape: support database keywords (order, key……) Automatic escape, but also custom keywords.
11. Built-in code generator: using code or Maven plug-in can quickly generate Mapper, Model, Service, Controller layer code, support template engine, more than a lot of custom configuration you to use.
12. Built-in paging plug-in: Based on MyBatis physical paging, developers do not need to care about specific operations, after configuring the plug-in, write paging is equal to ordinary List query.
13. Built-in performance analysis plug-in: outputs Sql statements and their execution time. It is recommended to enable this function during development and testing to quickly find out slow queries.
14. Built-in global interception plug-in: provides intelligent analysis and blocking of delete and UPDATE operations on all tables, and can customize interception rules to prevent misoperations.
15. Built-in Sql injection stripper: supports Sql injection stripper to effectively prevent Sql injection attacks.

2.3. Start fast

2.3.1 Importing dependencies

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    <exclusions>
      <exclusion>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-logging</artifactId>
      </exclusion>
    </exclusions>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
  <! -- Simplify code toolkit -->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
  </dependency>
  <! -- Springboot support for Mybatis -->
  <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.1</version>
  </dependency>
  <! - mysql driver - >
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
  </dependency>
  <dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
  </dependency>
Copy the code

2.3.2, log4j properties

og4j.rootLogger=DEBUG,A1
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n
Copy the code

2.3.3. Write entity classes

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
  private Integer id;
  private String username;
  private String password;
  private String type;
}
Copy the code

2.3.4. Write mapper

Myabtis-plus BaseMapper ()
public interface UserMapper extends BaseMapper<User> {}Copy the code

2.3.5. Write startup classes

@SpringBootApplication
// Set the mapper interface to scan packets
@MapperScan("cn.linstudy.mapper")
public class DemoApplication {
  public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); }}Copy the code

2.3.6, test,

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
  @Autowired
  private UserMapper userMapper;
  @Test
  public void testSelect(a) {
    List<User> userList = userMapper.selectList(null);
    for(User user : userList) { System.out.println(user); }}}Copy the code

2.4, architecture,

3. Common annotations

3.1, @ TableName

MyBatis-Plus uses @tablename as an annotation to specify which table is mapped to the current entity class. The default TableName in MyBatis-Plus is the same as the entity class name. MyBatis-Plus will report an error when the entity class name and TableName are different.

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User {
  private Integer id;
  private String username;
  private String password;
  private String type;
}
Copy the code

3.2, @ TableId

There is a strange phenomenon when we use the INSERT method. He’s generating an extra long ID, and that’s because he’s using an algorithm that uses the Snowflake algorithm to generate ids, and we want to increment the ID, so we need to set a strategy for primary key growth.

We can use@TableIdThis note. It is used as a primary key annotation to mark the primary key of the current attribute mapping table, where type is the primary key type specified by the attribute. It has these values:

1. Idtype. AUTO: indicates that the DATABASE ID is automatically increased.
2. Idtype. NONE: stateless. This type is of unset primary key type.
3. Idtype. INPUT: set the primary key before insert.
4. IdType. ASSIGN_ID: Assign ID(primary key type Number(Long and Integer) or String)(since 3.3.0), using the method of interface IdentifierGenerator nextId (default implementation class for DefaultIdentifierGenerator snowflake algorithm).
5. Assign UUID, primary key type String(since 3.3.0), nextUUID(default default)

3.3, @ TableField

Sometimes we might have a field name in the database that’s not the same as the name of the entity class, or we might have a field in the entity class that’s not in the database, so we need to use the @TableField annotation. The @tableField annotation is used to mark non-primary key fields. Its purpose is to specify which column in the current attribute mapping database table. The default is consistent with the attribute name. It is often used to solve the following two problems:

1. Inconsistent property and field names in objects (non-hump)
2. Object property field does not exist in the table

It can also be used to exclude a field from a query.

Iv. General CRUD

As we have learned before, when using MyBatis-Plus, we do not need to write the methods in Mapper interface, just need to inherit BaseMapper interface can obtain a variety of single table operations.

4.1. Insert Operation

4.1.1. Method Definition

MyBatis-Plus defines the insert method as:

/** * Insert a record **@paramEntity Entity object */
int insert(T entity);
Copy the code

4.1.2, test,

package cn.linstudy.test

import cn.itcast.mp.mapper.UserMapper;
import cn.itcast.mp.pojo.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;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {

  @Autowired
  private UserMapper userMapper;

  @Test
  public void testInsert(a) {
    User user = new User();
    user.setAge(20);
    user.setEmail("[email protected]");
    user.setName("Cao cao");
    user.setUserName("caocao");
    user.setPassword("123456");
    int result = this.userMapper.insert(user); // Result is the number of rows affected, not the incremented ID
    System.out.println("result = " + result);
    System.out.println(user.getId()); // The added ID is backfilled into the object}}Copy the code

4.2 Update operation

2, updateById

4.2.1.1 method Definition

/** * Change ** according to ID@paramEntity Entity object */
int updateById(@Param(Constants.ENTITY) T entity);
Copy the code

4.2.1.2, test,

	// Requirement: Change the name of the user whose id=1 to xiaolin

    @Test

    public void testUpdateById(a){

        Employee employee = new Employee();
        employee.setId(1L);
        employee.setName("xiaolin");
        employeeMapper.updateById(employee);

    }

	// Note: When concatenating SQL, all non-null fields are set concatenated

	// UPDATE employee SET name=? , age=? , admin=? WHERE id=?

	// The way to improve is to look first, then replace, and finally update

	// Requirement: Change the name of the user whose id=1 to xiaolin

    @Test

    public void testUpdateById2(a){

        Employee employee = employeeMapper.selectById(1L);

        employee.setName("xiaolin");

        employeeMapper.updateById(employee);

    }
Copy the code

4.2.2, update

4.2.2.1 Method Definition

  /** * Update the record ** according to the whereEntity condition@paramEntity Entity object (set conditional value, which can be null) *@paramThe updateWrapper entity object encapsulates the action class (which can be null, where the entity is used to generate the WHERE statement) */
  int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T>
      updateWrapper);
	
Copy the code

4.2.2.2, test,

public class UserMapperTest {

  @Autowired
  private UserMapper userMapper;

  Method 1: Use QueryWrapper
  @Test
  public void testUpdate(a) {
    User user = new User();
    user.setAge(22); // The updated field
	// Update conditions
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.eq("id".6);
	// Perform the update operation
    int result = this.userMapper.update(user, wrapper);
    System.out.println("result = " + result);
  }
    
   // Method 2: Update with UpdateWrapper
    @Test
	public void testUpdate(a){
		// Update conditions and fields
    	UpdateWrapper<User> wrapper=new UpdateWrapper<>();
    	wrapper.eq("id".6).set("age".23);
		// Perform the update operation
    	int result=this.userMapper.update(null,wrapper);
    	System.out.println("result = "+result); }}Copy the code

4.2.2.3 Suggestions

1. Know the ID, and all updates use updateById

2. For partial field updates, use update

4.3 Delete operations

4.3.1, deleteById

4.3.1.1. Method definition

/** * delete ** based on ID@paramId Primary key ID */
int deleteById(Serializable id);
Copy the code

4.3.1.2, test,

@Test
public void testDeleteById(a) {
	// Perform the delete operation
	int result = this.userMapper.deleteById(6L);
	System.out.println("result = " + result);
}
Copy the code

4.3.2, deleteByMap

4.3.2.1 method definition

/** * Drop the record ** according to the columnMap condition@paramColumnMap Table field map object */
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
Copy the code

4.3.2.2, test,

@Test
public void testDeleteByMap(a) {
	Map<String, Object> columnMap = new HashMap<>();
	columnMap.put("age".20);
	columnMap.put("name"."Zhang");
	// Set the elements in the columnMap to the condition for deletion
	int result = this.userMapper.deleteByMap(columnMap);
	System.out.println("result = " + result);
}
Copy the code

4.3.3, delete

4.3.3.1. Method definition

/** * Delete record ** according to entity condition@paramThe Wrapper entity object encapsulates the operation class (which can be null) */
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
Copy the code

4.3.3.2, test,

@Test
public void testDeleteByMap(a) {
	User user = new User();
	user.setAge(20);
	user.setName("Zhang");
	// Wrap the entity object as the operating condition
	QueryWrapper<User> wrapper = new QueryWrapper<>(user);
	int result = this.userMapper.delete(wrapper);
	System.out.println("result = " + result);
}
Copy the code

4.3.4, deleteBatchIds

4.3.4.1. Method definition

/** * delete (batch delete by ID) **@paramIdList List of primary key ids (cannot be null or empty) */
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable>
idList);
Copy the code

4.3.4.2, test,

@Test
public void testDeleteByMap(a) {
	// Delete the data in batches based on the id set
	int result = this.userMapper.deleteBatchIds(Arrays.asList(1L.10L.20L));
	System.out.println("result = " + result);
}
Copy the code

4.4. Query operations

MyBatis-Plus provides a variety of query operations, including query by ID, batch query, query single data, query list, paging query and other operations.

4.4.1, selectById

4.4.1.1 method definition

/** * query ** by ID@paramId Primary key ID */
T selectById(Serializable id);
Copy the code

4.1.1.2, test,

@Test
public void testSelectById(a) {
	// Query data by id
	User user = this.userMapper.selectById(2L);
	System.out.println("result = " + user);
}
Copy the code

4.4.2, selectBatchIds

4.4.2.1 method definition

/** * query (by ID) **@paramIdList List of primary key ids (cannot be null or empty) */
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable>
idList);
Copy the code

4.2.2.2, test,

@Test
public void testSelectBatchIds(a) {
	// Batch query based on id set
	List<User> users = this.userMapper.selectBatchIds(Arrays.asList(2L.3L.10L));
	for(User user : users) { System.out.println(user); }}Copy the code

4.4.3, selectOne

4.4.3.1 method definition

/** * Query a record ** according to the entity condition@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

4.4.3.2, test,

@Test
public void testSelectOne(a) {
	QueryWrapper<User> wrapper = new QueryWrapper<User>();
	wrapper.eq("name"."Bill");
	// Query data according to the condition, if the result is more than one, an error is reported
	User user = this.userMapper.selectOne(wrapper);
    System.out.println(user);
}
Copy the code

4.4.4, selectCount

4.4.4.1 method definition

/** * Query the total number of records according to the Wrapper condition **@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

4.4.4.2, test,

@Test
public void testSelectCount(a) {
	QueryWrapper<User> wrapper = new QueryWrapper<User>();
	wrapper.gt("age".23); // Age > 23
    Integer count = this.userMapper.selectCount(wrapper);
	System.out.println("count = " + count);
}
Copy the code

4.4.5, selectList

4.4.5.1 method definition

/** * query all records ** according to entity condition@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

4.4.5.2, test,

@Test
public void testSelectList(a) {
QueryWrapper<User> wrapper = new QueryWrapper<User>();
wrapper.gt("age".23); // Age > 23
    // Query data based on conditions
	List<User> users = this.userMapper.selectList(wrapper);
	for (User user : users) {
	System.out.println("user = "+ user); }}Copy the code

4.4.6, selectPage

4.4.6.1 Method definition

/** * Query all records according to entity condition **@paramPage paging query criteria (can be rowbound.default) *@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

4.4.6.2. Configure the paging plug-in

@Configuration
@MapperScan("cn.itcast.mp.mapper") // Set scan packets for the mapper interface
public class MybatisPlusConfig {
/** ** paging plug-in */
@Bean
public PaginationInterceptor paginationInterceptor(a) {
    return newPaginationInterceptor(); }}Copy the code

4.4.6.3, test,

@Test
public void testSelectPage(a) {
	QueryWrapper<User> wrapper = new QueryWrapper<User>();
	wrapper.gt("age".20); // Over 20 years old
	Page<User> page = new Page<>(1.1);
	// Query data based on conditions
	IPage<User> iPage = this.userMapper.selectPage(page, wrapper);
	System.out.println("Total number of data items:" + iPage.getTotal());
	System.out.println("Total pages:" + iPage.getPages());
	List<User> users = iPage.getRecords();
	for (User user : users) {
	System.out.println("user = "+ user); }}Copy the code

4.4.7 Principle of SQL injection

MP registers a series of methods in BaseMapper into meppedStatements after startup. How do you inject them? What’s the process like?

In MP, ISqlInjector is responsible for the injection of SQL, it is an interface, AbstractSqlInjector is its implementation class, implementation relationship as follows:

In AbstractSqlInjector, it is injected primarily by the inspectInject() method:

@Override
public void inspectInject(MapperBuilderAssistant builderAssistant, Class
        mapperClass) { Class<? > modelClass = extractModelClass(mapperClass);if(modelClass ! =null) {
		String className = mapperClass.toString();
		Set<String> mapperRegistryCache =
		GlobalConfigUtils.getMapperRegistryCache(builderAssistant.getConfiguration());
	if(! mapperRegistryCache.contains(className)) { List<AbstractMethod> methodList =this.getMethodList();
		if (CollectionUtils.isNotEmpty(methodList)) {
			TableInfo tableInfo = TableInfoHelper.initTableInfo(builderAssistant,
modelClass);
			// Loop to inject custom methods
			methodList.forEach(m -> m.inject(builderAssistant, mapperClass,
modelClass, tableInfo));
		} else {	
            logger.debug(mapperClass.toString() + ", No effective injection method
was found."); } mapperRegistryCache.add(className); }}}Copy the code

Use SelectById as an example:

public class SelectById extends AbstractMethod {
	@Override
	public MappedStatement injectMappedStatement(Class
        mapperClass, Class
        modelClass, TableInfo tableInfo) {
		SqlMethod sqlMethod = SqlMethod.LOGIC_SELECT_BY_ID;
		SqlSource sqlSource = new RawSqlSource(configuration,
String.format(sqlMethod.getSql(),
		sqlSelectColumns(tableInfo, false),
		tableInfo.getTableName(), tableInfo.getKeyColumn(),
		tableInfo.getKeyProperty(),
		tableInfo.getLogicDeleteSql(true.false)), Object.class);
		return this.addSelectMappedStatement(mapperClass, sqlMethod.getMethod(), sqlSource, modelClass, tableInfo); }}Copy the code

As you can see, the SqlSource object is generated and the SQL is added to the meppedStatements using the addSelectMappedStatement method.

Conditional constructor

A conditional constructor can be understood simply as a conditional concatenation object that is used to generate SQL WHERE conditions.

5.1 Inheritance system

In MyBatis-Plus, the Wrapper interface implementation class relationship is as follows:

1. AbstractWrapper: A WHERE condition used to encapsulate query conditions and generate SQL.

2. QueryWrapper: Entity object encapsulates action classes, not in lambda syntax.

3. UpdateWrapper: Update conditional wrapper for Entity object Update operations.

4. AbstractLambdaWrapper: Lambda syntax uniformly handles parsing Lambda to fetch column using Wrapper.

5. LambdaQueryWrapper: The query Wrapper used for Lambda syntax.

6. LambdaUpdateWrapper: Lambda updates the Wrapper.

5.2 Update operation

5.2.1. Common updates

@Test
    public void testUpdate(a){
        Employee employee = new Employee();
        employee.setId(1L);
        employee.setName("xiaolin");
        employeeMapper.updateById(employee);
    }
Copy the code

This update will result in data loss because I only want to update some of the fields.

5.2.2 UpdateWrapper update

5.2.2.1, set

If we need to update a partial field we can update it with UpdateWrapper. There are two main methods:

1. set(String column, Object val)
2. set(boolean condition, String column, Object val)

// Select * from employee where id=1 and name = xiaolin
	@Test
    public void testUpdate2(a){
        UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
        wrapper.eq("id".1L);
        // Set name = xiaolin
        wrapper.set("name"."xiaolin");
        employeeMapper.update(null, wrapper);

    }
Copy the code

5.2.2.2,

MyBatis-Plus also provides another way to modify, which is to write SQL statements directly using setSql.

	// Requirement: change user name (id=1) to xiaolin
	@Test
    public void testUpdate3(a){
        UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
        wrapper.eq("id".1L);
        wrapper.setSql("name='xiaolin'");
        employeeMapper.update(null, wrapper);

    }
Copy the code

5.2.3 LambdaUpdateWrapper update

We can also use JDK8’s new syntax in conjunction with LambdaUpdateWrapper.

   // Requirement: change user name (id=1) to xiaolin
   @Test
    public void testUpdate4(a){
        LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
        wrapper.eq(Employee::getId, 1L);
        wrapper.set(Employee::getName, "xiaolin");
        employeeMapper.update(null, wrapper);
    }
Copy the code

5.2.4 Development Suggestions

LambdaUpdateWrapper is recommended.

5.3. Query operations

5.3.1 Common Query

  // Query user name=xiaolin, age=18
    @Test
    public void testQuery1(a){
        Map<String, Object> map = new HashMap<>();
        map.put("name"."xiaolin");
        map.put("age".18);
        System.out.println(employeeMapper.selectByMap(map));

    }
Copy the code

5.3.2 QueryWrapper query

  // Query user name=xiaolin, age=18
    @Test
    public void testQuery2(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.eq("name"."xiaolin").eq("age".18);
        System.out.println(employeeMapper.selectList(wrapper));
    }
Copy the code

5.3.3 LambdaQueryWrapper query

  // Query user name=xiaolin, age=18
    @Test
    public void testQuery3(a){
        LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Employee::getName, "xiaolin").eq(Employee::getAge, 18);
        System.out.println(employeeMapper.selectList(wrapper));

    }
Copy the code

5.3.4 Development Suggestions

LambdaUpdateWrapper is recommended

5.4 advanced Query

5.4.1 Column projection

A strong projection is the column that is returned after a specified query. We use the select method to implement. He has three overload methods:

1. select(String… SqlSelect) : Parameter is the column returned after specified query.
2. Select (Predicate
   Predicate) : Parameters are Predicate functions that are returned only when the specified Predicate logical column is satisfied.
   
3. Select (Class
   
   entityClass, Predicate
   Predicate) : Parameter 1 is used to map the columns in the table by the attributes of the entity. Parameter 2 is used to map the columns of the table.
   

  // Request: query all employees, return employee name, age column
    @Test
    public void testQuery4(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.select("name"."age");
        employeeMapper.selectList(wrapper);

    }

  // Requirements: Query all employees, return the employee column starting with a letter
    @Test
    public void testQuery4(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.select(Employee.class, tableFieldInfo->tableFieldInfo.getProperty().startsWith("a"));
        employeeMapper.selectList(wrapper);

    }
Copy the code

5.4.2, sorting,

5.4.2.1, orderByAsc/orderByDesc

SQL > select… From table ORDER BY… ASC;

1. OrderByAsc: orderByAsc.
2. OrderByDesc: Order in reverse order.

  // Requirement: Query the information of all employees in age order. If the age is the same, the information is in id order
    @Test
    public void testQuery5(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("age"."id");
        employeeMapper.selectList(wrapper);

    }
Copy the code

5.4.2.2, orderBy

If the officially written sort does not work for us, we can use the custom sort -order by. Equivalent SQL: select.. From table ORDER BY;

orderBy(boolean condition, boolean isAsc, R… Columns) : parameter 1: controls whether the columns are sorted and parameter 2: controls whether the columns are sorted.

  // Requirement: Query the information of all employees in age order. If the age is the same, the information is in id order
    @Test
    public void testQuery5(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        OrderBy (true, true, "id", "name") is equivalent to order by id ASC,name ASC
        //apper.orderByAsc("age", "id");
        // This is equivalent to:
        wrapper.orderBy(true.true."age"."id");
        employeeMapper.selectList(wrapper);

    }

  // Requirement: Query the information of all employees in positive order by age. If the age is the same, the information is in reverse order by ID
    @Test
    public void testQuery7(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.orderByAsc("age");
        wrapper.orderByDesc("id");
        employeeMapper.selectList(wrapper);

    }
Copy the code

5.5. Conditional query

5.5.1 allEq (Congruent Matching)

5.5.1.1, methods,

allEq(Map<R, V> params) // params: key is the database field name,value is the field value
allEq(Map<R, V> params, boolean null2IsNull) // If the map value is true, isNull is called; if the map value is false, null is ignored
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
Copy the code

Null2IsNull: isNull is called when the map value isNull if it is true. When the map value is false, null is ignored. Such as:

1. AllEq ({id:1,name:” allEq “,age:null})– >id = 1 and name = “allEq” and age is null
2. AllEq ({id:1,name:” allEq “,age:null}, false) –> id = 1 and name = “allEq”

5.5.1.2 sample,

  // Query employee information where name=xiaolin, age=18
    @Test
    public void testQuery8(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name"."xiaolin");
        map.put("age".18);
        wrapper.allEq(map);
        employeeMapper.selectList(wrapper);
    }

	@Test
    public void testQuery8(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name"."xiaolin");
        map.put("age".18);
		map.put("dept_id".null);
        wrapper.allEq(map, true);
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.2 allEq (Congruent Matching with Conditional Filtering)

5.5.2.1, methods,

allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
Copy the code

Filter: specifies whether fields are allowed to pass in params and null2IsNull. For example:

1. AllEq ((k, v) – > k.i ndexOf (” a “) > = 0, {id: 1, name: “wang”, the age: null}) – > name = ‘Lao wang’ and the age is null
2. AllEq ((k, v) – > k.i ndexOf (” a “) > = 0, {id: 1, name: “wang”, the age: null}, false) – > name = ‘Lao wang’

5.5.2.2, sample

   // Requirement: Query the employee information that meets the condition. Note that only the column containing a in the map condition is involved in the condition query
    @Test
    public void testQuery9(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name"."xiaolin");
        map.put("age".18);
        wrapper.allEq((k, v)-> k.contains("m"), map);
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.3, eq

We can use eq to determine whether individual parameters are equal or not. Eq (“name”, “Lao Wang “) equivalent to name = ‘Lao Wang’.

5.5.3.1, methods,

eq(R column, Object val)
eq(boolean condition, R column, Object val) // If the value is null, the value is null. // If the value is null, the value is null.
Copy the code

5.5.3.2 sample,

  // Need: query information about the employee named =xiaolin
    @Test
    public void testQuery10(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.eq("name"."xiaolin");
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.4, ne

We can use ne to determine if a parameter is not equal. Ne (“name”, “Lao Wang “) is equivalent to name! = ‘Lao Wang’.

  // Query name! = Xiaolin Employee information
    @Test
    public void testQuery11(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.ne("name"."xiaolin");
        employeeMapper.selectList(wrapper);

    }
Copy the code

5.5.5, gt

Gt indicates greater than. Gt (“age”, 18) is equivalent to age > 18

5.5.5.1, methods,

gt(R column, Object val)
gt(boolean condition, R column, Object val)
Copy the code

5.5.5.2 sample,

  // Request: query information about employees whose age > 12
    @Test
    public void testQuery12(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.gt("age"."12");
        employeeMapper.selectList(wrapper);
  }
Copy the code

5.5.6, ge,

Ge means greater than or equal to. Ge (“age”, 18) is equivalent to age > =18

5.5.6.1, methods,

ge(R column, Object val)
ge(boolean condition, R column, Object val)
Copy the code

Sample 5.5.6.2,

  // Requirement: Query information about employees whose age >= 12
    @Test
    public void testQuery12(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.ge("age"."12");
        employeeMapper.selectList(wrapper);
  }
Copy the code

5.5.7, lt

Lt means less than 1. Lt (“age”, 18) is equivalent to age < 18

5.5.7.1, methods,

lt(R column, Object val)
lt(boolean condition, R column, Object val
Copy the code

5.5.7.2 sample,

  // Requirement: Query information about employees whose age < 12
    @Test
    public void testQuery12(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.lt("age"."12");
        employeeMapper.selectList(wrapper);
  }
Copy the code

5.5.8, lt

Lt means less than 1. Lt (“age”, 18) is equivalent to age < 18

5.5.8, methods,

lt(R column, Object val)
lt(boolean condition, R column, Object val
Copy the code

5.5.7.2 sample,

  // Requirement: Query information about employees whose age < 12
    @Test
    public void testQuery12(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.lt("age"."12");
        employeeMapper.selectList(wrapper);
  }
Copy the code

5.5.9, le

Le means less than or equal to. Le (“age”, 18) is equivalent to age <= 18.

le(R column, Object val)
le(boolean condition, R column, Object val)
Copy the code

5.5.10. Between and notBetween

We use between/notBetween to mean between/notBetween.

Between (“age”, 18, 30) is the same thing as age between 18 and 30.

NotBetween (“age”, 18, 30) is equivalent to age not between 18 and 30

5.5.10.1, methods,

// Between: between values 1 AND 2
between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)
   
// notBetween: NOT BETWEEN 1 AND 2
notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
Copy the code

5.5.10.2 sample,

  // Request: Query information about employees aged between 18 and 30
    @Test
    public void testQuery13(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.between("age".18.30);
        employeeMapper.selectList(wrapper);
    }

  Select * from employee where age < 18 and age > 30;
    @Test
    public void testQuery13(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.notBetween("age".18.30);
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.11, isNull, isNotNull

We can use isNull/isNotNull to indicate null/not null.

IsNull (“name”) is equivalent to name is NULL.

IsNotNull (“name”) is equivalent to name is not null.

5.5.11.1, methods,

// isNull: the field IS NULL
isNull(R column)
isNull(boolean condition, R column)
    
// isNotNull: Field IS NOT NULL
isNotNull(R column)
isNotNull(boolean condition, R column)
Copy the code

5.5.11.2 sample,

  // Query employee information whose dept_id is null
	@Test
    public void testQuery16(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.isNull("dept_id");
        employeeMapper.selectList(wrapper);
    }

  // Requirement: Query employee information whose dept_id is not null
	@Test
    public void testQuery16(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.isNotNull("dept_id");
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.12, in, notIn

We can use in/notIn to indicate that the value is/is notIn here.

In (” age “, {1, 2, 3}) – > age in (1, 2, 3)

NotIn (” age “, {1, 2, 3}) – > age not in (1, 2, 3)

5.5.12.1, methods,

// in : 字段 IN (value1, value2, ...)in(R column, Collection<? > value) in(booleancondition, R column, Collection<? > value)// notIn: field NOT IN (value1, value2...)
notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)
Copy the code

5.5.12.2 sample,

  // Requirement: Query information about employees whose ids are 1 and 2
	@Test
    public void testQuery18(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.in("id".1L.2L);
        employeeMapper.selectList(wrapper);
    }

  // Requirement: Query information about employees whose ids are not 1 or 2
    @Test
    public void testQuery19(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.notIn("id".1L.2L);
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.5.13. InSql and notInSql

Different from the previous one, his format is: field IN (SQL statement)/field NOT IN (SQL statement), followed by the SQL statement fragment.

5.5.13.1, methods,

InSql (” age “, “6”) is equivalent to the age in (6)

    notInSql(“id”, “select id from table where id < 3”)—>id not in (select id from table where id < 3)

// inSql: field IN (SQL statement)
inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

// 	notInSql ： 字段 NOT  IN ( sql语句 )
notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)
Copy the code

5.5.13.2 sample,

  // Requirement: Query information about employees whose ids are 1 and 2
    @Test
    public void testQuery20(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.inSql("id"."1, 2,");
        employeeMapper.selectList(wrapper);
    }

  // Requirement: Query information about employees whose ids are not 1 or 2
    @Test
    public void testQuery21(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.notInSql("id"."1, 2,");
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.6. Fuzzy query

5.6.1. Like, notLike

5.6.1.1, methods,

/ / like: like '% % values: like (" name ", "the king") is equivalent to a name like' % % king '
like: LIKE '% % values'
like(R column, Object val)
like(boolean condition, R column, Object val)

// notLike("name", "king ")-- >name not like '% king %'
notLike : NOT LIKE '% % values'
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
Copy the code

5.6.1.2 sample,

  // Select * from employee whose name contains Lin
    @Test
    public void testQuery14(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.like("name"."lin");
        employeeMapper.selectList(wrapper);
    }

  // Query employee whose name does not contain Lin
    @Test
    public void testQuery14(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.notLike("name"."lin");
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.6.2 likeLeft, likeRight

5.6.2.1, methods,

// likeLeft("name", "king ")-- >name like '% king 'LikeLeft: LIKE'% values'
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

// likeRight("name", "king ")-- >name like 'king %'LikeRight: LIKE'value %'
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
Copy the code

5.6.2.2 sample,

  // Request: query employee information whose name ends in Lin
    @Test
    public void testQuery15(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.likeLeft("name"."lin");
        employeeMapper.selectList(wrapper);
    }

  // Request: query information about the employee surnamed Wang
    @Test
    public void testQuery16(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.likeRight("name"."The king");
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.7. Logical operators

5.7.1, or

5.7.1.1, methods,

/ / eq (" id ", 1) or (.) eq (" name ", "wang") -- - > id = 1 or name = 'Lao wang'Or: joining togetherOR
or(a)
or(boolean condition)
    
// Or can also be nested
/ / the or (I - > appropriate precautions q (" name ", "li bai"). Ne (" status ", "live") -- - > the or (name = 'li bai' and status < > 'alive')
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)
    
Copy the code

Calling or actively means that the next method is not joined with and! (Default to use and if you do not call OR)

5.7.1.2 sample,

  // Requirement: Query user age = 18, name=xiaolin, or ID =1
    @Test
    public void testQuery24(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.eq("age".18)
                .or()
                .eq("name"."xiaolin")
                .or()
                .eq("id".1L);
        employeeMapper.selectList(wrapper);
    }

  Select * from user where name = 'Lin' and age between 18 and 30 '
    @Test
    public void testQuery25(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.like("name"."lin")
                .or(wr -> wr.le("age".30).ge("age".18));
        employeeMapper.selectList(wrapper);
    }
Copy the code

5.7.2, and

5.7.2.1, methods,

// nested and:
and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)
Copy the code

5.7.2.2 sample,

  // Request: Query information about employees aged between 18 and 30
    @Test
    public void testQuery26(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.le("age".30).ge("age".18);
        employeeMapper.selectList(wrapper);
    }

  Select * from user where name = 'Lin' and age < 18 or > 30
    @Test
    public void testQuery27(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.like("name"."lin")
                .and(wr -> wr.le("age".30)
                        .or()
                        .ge("age".18));
        employeeMapper.selectList(wrapper);
    }  
Copy the code

5.8 group Query

5.8.1, groupBy

5.8.1.1, methods,

// groupBy("id", "name")--->group by id,nameGroupBy: groupBy: GROUP BY:... groupBy(R... columns) groupBy(boolean condition, R... columns)
Copy the code

5.8.1.2 sample,

   // Requirement: Query the number of employees in each department by department ID
    @Test
    public void testQuery22(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.groupBy("dept_id");
        wrapper.select("dept_id"."count(id) count");
        employeeMapper.selectMaps(wrapper);
    }
Copy the code

5.8.2, having

5.8.2.1, methods,

// having("sum(age) > 10")--->having sum(age) > 10
// having("sum(age) > {0}", 11)--->having sum(age) > 11Having: having(SQL) having(String sqlHaving, Object... params) having(boolean condition, String sqlHaving, Object... params)
Copy the code

5.8.2.2 sample,

  // Requirement: Query the number of employees in each department by department ID and filter out departments with more than three employees
    @Test
    public void testQuery23(a){
        QueryWrapper<Employee> wrapper = new QueryWrapper<>();
        wrapper.groupBy("dept_id")
                .select("dept_id"."count(id) count")
                //.having("count > {0}", 3)
                .having("count >3");
        employeeMapper.selectMaps(wrapper);
    }
Copy the code

Universal Service interface

6.1. Traditional methods

In the old business layer, we had to write interfaces and implementation classes, many of which were repetitive CRUD with no technical content.

6.1.1. Service Interface

public interface EmployeeService {
    void save(Employee employee);
    void update(Employee employee);
    void delete(Long id);
    Employee get(Long id);
    List<Employee> list(a);
}
Copy the code

6.1.2, ServiceImpl

@Service
public class EmployeeServiceImpl implements EmployeeService {
    @Autowired
    private EmployeeMapper mapper;

    @Override
    public void save(Employee employee) {
        mapper.insert(employee);
    }

    @Override
    public void update(Employee employee) {
        mapper.updateById(employee);  // Must be fully updated
    }

    @Override
    public void delete(Long id) {
        mapper.deleteById(id);
    }

    @Override
    public Employee get(Long id) {
        return mapper.selectById(id);
    }

    @Override
    public List<Employee> list(a) {
        return mapper.selectList(null); }}Copy the code

6.2 General Service interface of MyBatis-Plus

Since we need to repeatedly write so much code without technical content, so sure MyBatis-Plus will help us do well, we only need two simple MyBatis-Plus to write CRUD methods for us, he will automatically call mapper interface methods.

1. The custom service interface inherits the IService interface, where generics are entity-class objects

public interface IEmployeeService extends IService<Employee> {}Copy the code

2. Service interface implementation class integrated IService interface implementation class ServiceImpl also implements custom interfaces, generic one is entity class mapper interface, generic two is entity class.

@Service
public class EmployeeServiceImpl extends ServiceImpl<EmployeeMapper.Employee> implements IEmployeeService {}Copy the code

6.2.1. Common methods

1. GetBaseMapper () : Get the referenced XxxxMapper object.
2. GetOne (wrapper) : specifies a single query condition. If more than one data is queried, an error will be reported.
3. List (wrapper) : Specifies multiple criteria to query.

6.2.2, paging

Paging is done using page(page, wrapper), which can also be used in conjunction with advanced queries.

6.2.2.1. Configure the paging plug-in

We need to configure the paging plug-in in the configuration class.

/ / paging

    @Bean

    public MybatisPlusInterceptor mybatisPlusInterceptor(a) {

        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);

        paginationInnerInterceptor.setOverflow(true); / / to rationalize

        interceptor.addInnerInterceptor(paginationInnerInterceptor);

        return interceptor;

    }
Copy the code

6.2.2.2 write paging code

MyBatis-Plus’s pagination object is IPage, pagination information encapsulation object, which has various page-related information equivalent to the previous PageInfo.

// Requirement: query employee information on page 2, 3 items per page, sorted by ID
@Test
public void testPage(a){
    EmployeeQuery qo = new EmployeeQuery();
    qo.setPageSize(3);
    qo.setCurrentPage(2);
    IPage<Employee> page = employeeService.query(qo);
    System.out.println("Current page:" + page.getCurrent());
    System.out.println("Total pages:" + page.getPages());
    System.out.println("Number of items per page:" + page.getSize());
    System.out.println("Total records:" + page.getTotal());
    System.out.println("Current page display record:" + page.getRecords());
}
Copy the code

Seven, ActiveRecord

7.1. What is ActiveRecord

ActiveRecord also belongs to the ORM (Object relational Mapping) layer, which was first introduced by Rails and follows the standard ORM model: tables map to records, records map to objects, and fields map to object properties. With the naming and configuration conventions that follow, the operation of the model is largely fast and easy to understand.

The main ideas of ActiveRecord are:

1. Each database table corresponds to the creation of a class, each object instance of the class corresponds to the database table row record; Typically, each Field of a table has a corresponding Field in the class.
2. ActiveRecord is also responsible for its own persistence, encapsulating access to the database, called CURD, in ActiveRecord.
3. ActiveRecord is a Domain Model that encapsulates part of the business logic.

ActiveRecord (AR for short) has always been popular with dynamic languages (PHP, Ruby, etc.), while Java is a quasi-static language, ActiveRecord is often only elegant, so we have also explored the AR road, and we hope you can like it.

7.2. Start the AR journey

In MP, turning on AR is as simple as inheriting entity objects from Model.

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee extends Model<Employee> {

  @TableId(type = IdType.AUTO)
  private Long id;
  private String username;
  private String name;
  private String password;
  private String email;
  private Integer age;
  private Boolean admin;
  private Long deptId;
  private Boolean status;
}
Copy the code

7.2.1. Query All information

@RunWith(SpringRunner.class)
@SpringBootTest
public class Test1 {

  @Autowired
  EmployeeMapper employeeMapper;

  /** * test query all */
  @Test
  public void test(a){
    Employee employee = new Employee();
    List<Employee> employees = employee.selectAll();
    for(Employee employee1 : employees) { System.out.println(employee1); }}}Copy the code

7.2.2. Query by ID

@RunWith(SpringRunner.class)
@SpringBootTest
public class Test2 {
  /** * tests queries by id */
  @Test
  public void test2(a){
    Employee employee = new Employee();
    employee.setId(1L); System.out.println(employee.selectById()); }}Copy the code

7.2.3 Query according to conditions

  /** * is used to test query by condition */
  @Test
  public void test4(a){
    Employee employee = new Employee();
    QueryWrapper<Employee> userQueryWrapper = new QueryWrapper<>();
    userQueryWrapper.le("password"."123");
    List<Employee> employees = employee.selectList(userQueryWrapper);
    for(Employee employee1 : employees) { System.out.println(employee1); }}Copy the code

7.2.4 New Data

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestAR {

  /** * used to test new data */
  @Test
  public void test3(a){
    Employee employee = new Employee();
    employee.setId(11L);
    employee.setPassword("123"); employee.insert(); }}Copy the code

7.2.5 Update data

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestAR {
  /** * for testing updates */
  @Test
  public void test5(a){
      Employee employee = new Employee();
      employee.setId(1L);
      employee.setPassword("123456789"); employee.updateById(); }}Copy the code

7.2.6 Delete Data

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestAR {
  /** * test delete */
  @Test
  public void test6(a){
    Employee employee = new Employee();
    employee.setId(1L); employee.deleteById(); }}Copy the code

8. Plug-in mechanism

8.1 Introduction to plug-in mechanism

MyBatis allows you to intercept calls at certain points during the execution of mapped statements. By default, MyBatis allows you to intercept method calls using plug-ins:

1. Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
2. ParameterHandler (getParameterObject, setParameters)
3. ResultSetHandler (handleResultSets, handleOutputParameters)
4. StatementHandler (prepare, parameterize, batch, update, query)

We’ve seen some methods that can intercept the Executor interface, such as the Update, Query, COMMIT, rollback methods, and others. The overall summary is as follows:

1. Intercepting the executor method.
2. Intercepting the processing of parameters.
3. Intercept processing of result sets.
4. Intercepting the processing of Sql syntax builds.

8.2. Execute the analysis plug-in

In MP provides the analysis of SQL execution plug-in, can be used to block the full table update, delete operations, note: this plug-in is only suitable for development environment, not for production environment. We first need to configure on the startup class:

  @Bean
  public SqlExplainInterceptor sqlExplainInterceptor(a){
    SqlExplainInterceptor sqlExplainInterceptor = new SqlExplainInterceptor();
    List<ISqlParser> sqlParserList = new ArrayList<>();
	// Attack SQL to block the parser and join the parse chain
    sqlParserList.add(new BlockAttackSqlParser());
    sqlExplainInterceptor.setSqlParserList(sqlParserList);
    return sqlExplainInterceptor;
  }
Copy the code

The test class:

@Test
public void testUpdate(a){
Employee employee = new Employee();
employee.setPassword("123456");
int result = this.employeeMapper.update(employee, null);
System.out.println("result = " + result);
}
Copy the code

After execution, you will find that the console reported an error and will throw an exception when performing a full table update, which effectively prevents some misoperations.

[img-jpBjrx8S-1620720482907] (Mybatisplus. assets/image-20210509210703826.png)

8.3. Optimistic Lock plug-in

8.3.1 Application Scenarios

When a record is updated, it is hoped that the record has not been updated by others.

Optimistic locking can be implemented as follows:

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

8.3.2 Plug-in configuration

We need to configure this in spring.xml.

<bean class="com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor"/>
Copy the code

It is then configured in the boot class of SingBoot.

Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(a) {
	return new OptimisticLockerInterceptor();
}
Copy the code

8.3.3 annotate entity fields

Add the Version field to the table and assign an initial value of 1

ALTER TABLE `employee`
ADD COLUMN `version` int(10) NULL AFTER `email`;
UPDATE `tb_user` SET `version`='1';
Copy the code

Add the Version field for the entity class and add the @Version annotation

@Version
private Integer version;
Copy the code

test

@Test
public void testUpdate(a){
	Employee employee = new Employee();
	user.setPassword("456789");
	user.setId(2L);
	user.setVersion(1); // Set version to 1
	int result = this.userMapper.updateById(user);
	System.out.println("result = " + result);
}	
Copy the code

8.3.4, description,

1. Support the type of data is only: int, Integer, long, long, the Date and Timestamp, LocalDateTime.
2. In the integer format, newVersion = oldVersion + 1.
3. NewVersion is written back to entity and only updateById(ID) and Update (Entity, Wrapper) methods are supported.
4. The Wrapper cannot be reused under the update(Entity, Wrapper) method.