MyBatisPlus learning to organize (1)

This article is a collection of notes from MyBatisPlus through moOC-related courses.

MyBatisPlus entry: -) the teacher said good, also won’t MyBatisPlus friends can listen to the door. MyBatisPlus official website MyBatisPlus source address

MyBatisPlus architecture diagram (steal the official website, invasion, delete.

SpringBoot’s first simple application

1. Creating a database table

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 ' ', email VARCHAR(50) DEFAULT NULL COMMENT ' ', Manager_id BIGINT(20) DEFAULT NULL COMMENT 'admin ', create_time DATETIME DEFAULT NULL COMMENT' admin ', CONSTRAINT manager_fk FOREIGN KEY (manager_id) REFERENCES user (id) ) ENGINE=INNODB CHARSET=UTF8; # Initialize data: INSERT INTO user (id, name, age, email, manager_id, create_time) VALUES (1087982257332887553, 40, '[email protected]', NULL, '2019-01-11 14:20:20'), (1088248166370832385, 'Wang Tianfeng ', 25, '[email protected]', 1087982257332887553, '2019-02-05 11:12:22'), (1088250446457389058, 'Li Yiwei ', 28, '[email protected]', 1088248166370832385, '2019-02-14 08:31:16'), (1094590409767661570, 'Zhang Yuqi ', 31, '[email protected]', 1088248166370832385, '2019-01-14 09:15:15'), (1094592041087729666, 'Liu Hongyu ', 32, '[email protected]', 1088248166370832385, '2019-01-14 09:48:16');Copy the code

2. Rely on

<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>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.baomidou</groupId> < artifactId > mybatis - plus - the boot - starter < / artifactId > < version > 3.1.2 < / version > < / dependency >Copy the code

3. Springboot configuration file

spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver username: root password: root url: jdbc:mysql://localhost:3306/test? serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true logging: level: root: warn org.ywb.demo.dao: trace pattern: console: '%p%m%n'Copy the code

4. Create the relevant package, as shown below:

5. Create a class that maps to the database user table in the POJO package

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    private String managerId;
    private LocalDateTime createTime;
}
Copy the code

6. Create the Mapper interface in the DAO package and integrate the BaseMapper of mybatisPlus

public interface UserMapper extends BaseMapper<User> {

}
Copy the code

7. Add in springBoot boot class@MapperScanScan the DAO layer interface

@MapperScan("org.ywb.demo.dao") @SpringBootApplication public class MybatisPlusDemoApplication { public static void main(String[] args) { SpringApplication.run(MybatisPlusDemoApplication.class, args); }}Copy the code

8. Write test classes

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

    @Resource
    private UserMapper userMapper;
    
    @Test
    public void select(){
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }

}
Copy the code

Running result:

---

Commonly used annotations

MyBatisPlus provides some annotations for use when the entity class and table information do not correspond. Logical matching is done by using annotations.

Note the name	instructions
@TableName	The name of the entity class is inconsistent with the name of the database table
@TableId	The primary key name of the entity class is inconsistent with that of the table
@TableField	The name of the member in the entity class is inconsistent with the name of the field in the table

@Data
@TableName("t_user")
public class User {
    @TableId("user_id")
    private Long id;
    @TableField("real_name")
    private String name;
    private Integer age;
    private String email;
    private Long managerId;
    private LocalDateTime createTime;
}
Copy the code

Exclude non-table fields from entity classes

1. usetransientThe keyword modifies a non-table field, but istransientCannot serialize after decorating.
2. usestaticBecause we are using get/set methods generated by the Lombok framework, we need to manually generate get/set methods for static variables.
3. use@TableField(exist = false)annotations

CURD

BaseMapper encapsulates many methods about adding and deleting the search, which will be automatically generated in the later stage. We can directly call the relevant methods in the interface to complete the corresponding operation. BaseMapper part code

public interface BaseMapper<T> extends Mapper<T> { int insert(T entity); int deleteById(Serializable id); int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap); int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper); int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList); int updateById(@Param(Constants.ENTITY) T entity); . }Copy the code

Insert a record test:

@Test public void insert(){ User user = new User(); user.setAge(31); user.setManagerId(1088250446457389058L); user.setCreateTime(LocalDateTime.now()); int insert = userMapper.insert(user); System.out.println(" image record number: "+insert); }Copy the code

Conditional constructor query

In addition toBaseMapperIn addition to providing a simple method of adding, deleting, modifying and querying, it also provides a lot of query functions about interval query, multi-table join query, grouping and so on. The class diagram is as follows:As you can see from looking at the class diagram, when we need these functions, we just create themQueryWrapperObject is fine.

1. Fuzzy query

Public void selectByWrapper(){QueryWrapper<User>  queryWrapper = new QueryWrapper<>(); QueryWrapper. Like (" name ", "rain".) lt (" age ", 40); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

2. Nested query

* date_format(create_time,'%Y-%m-%d') and manager_id in (select id from user where name = 'name' Public void selectByWrapper2(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-02-14") .inSql("manager_id","select id from user Where name like '%'"); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

Pay attention toThe date query above uses placeholder form of the query to prevent the risk of SQL injection. Source code for the apply method

   /** * SQL * <p>!! SQL injection risk!! * < / p > < p > example 1: apply (" id = 1 ") * < / p > < p > example 2: apply (" date_format (dateColumn, 'Y - m - % d % %) =' 2008-08-08 '") < / p > * < p > example 3: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", LocalDate.now())</p> * *@paramCondition Specifies the execution condition *@return children
     */
    Children apply(boolean condition, String applySql, Object... value);
Copy the code

Example of SQL injection:

queryWrapper.apply("date_format(create_time,'%Y-%m-%d')=2019-02-14 or true=true") .inSql("manager_id","select id from User where name like '%'");Copy the code

3. and & or

/** * My first name is Wang, */ @test public void selectByWrapper3(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); QueryWrapper. LikeRight (" name ", "the king") and (wq - > wq. Lt (" age ", 40) or (.) isNotNull (" email ")); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

4. between & and

/** * name = wang, age < 40 and age > 20, */ @test public void selectWrapper4(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); Querywrapper.likeright ("name", "wang "). And (WQQ -> wq.isnotnull ("email"))); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

5. nested

Public void selectWrapper5(){/** * (age<40 or email is not null) and name like '% %' */ @test public void selectWrapper5(){ QueryWrapper<User> queryWrapper = new QueryWrapper<>(); QueryWrapper. Nested (wq - > wq. Lt (" age ", 40) or (.) isNotNull (" email ")). LikeRight (" name ", "the king"); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

6. in

Public void selectWrapper6(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); QueryWrapper. In (" age ", the Arrays. AsList (30,31,34,35)); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

7. Last has SQL injection risk!!

<p> For example: </p> * * @param condition * @param lastSql SQL statement * @return children */  Children last(boolean condition, String lastSql);Copy the code

/** * Return only one statement * limit 1 */ @test public void selectWrapper7(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); QueryWrapper. In (" age ", the Arrays. AsList (30,31,34,35)). The last (" limit 1 "); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

8. Query specified columns

Public void selectWrapper8(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); QueryWrapper. Select (" name ", "age"). LikeRight (" name ", "the king"); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

9. Use a filter to query the specified column

Public void selectWrapper9(){QueryWrapper<User> QueryWrapper = new QueryWrapper<>(); queryWrapper.select(User.class,info->! info.getColumn().equals("create_time") &&! info.getColumn().equals("manager_id")); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

The effect of condition

In the query we call, we can see by looking at the source code (using the apply method as an example) that the first parameter of each query method is a Boolean parameter. In the overloaded method, we are passed true by default.

 default Children apply(String applySql, Object... value) {
        return apply(true, applySql, value);
    }
    Children apply(boolean condition, String applySql, Object... value);
Copy the code

The condition executes the SQL condition if it is true, and ignores the SQL condition if it is false.

Entities are used as parameters to conditional constructors

In Web development, the Controller layer often passes us an object for a user, such as querying a list of users by their name and age. We can pass the passed object directly to QueryWrapper as a construct parameter, and MyBatisPlus will automatically build the SQL statement corresponding to the query based on the properties in the entity object.

@Test public void selectWrapper10(){ User user = new User(); User.setname (" liu Hongyu "); user.setAge(32); QueryWrapper<User> queryWrapper = new QueryWrapper<>(user); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

If you want to make fuzzy queries based on certain attributes of an object, you can annotate the corresponding attribute in the entity class corresponding to the database table. Let’s say we want to vaguely query a list of users by name.

@TableField(condition = SqlCondition.LIKE)
    private String name;
Copy the code

@Test public void selectWrapper10(){ User user = new User(); User. Elegantly-named setName (" red "); user.setAge(32); QueryWrapper<User> queryWrapper = new QueryWrapper<>(user); List<User> userList = userMapper.selectList(queryWrapper); userList.forEach(System.out::println); }Copy the code

Lambda conditional constructor

MybatisPlus provides four ways to create a lambda conditional constructor, the first three of which look like this

        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda();
        LambdaQueryWrapper<User> lambdaQueryWrapper1 = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<User> lambdaQueryWrapper2 = Wrappers.lambdaQuery();
Copy the code

1. Select * from employee whose name contains’ rain ‘and whose age is less than 40

    @Test
    public void lambdaSelect(a){
        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
        lambdaQueryWrapper.like(User::getName,"Rain").lt(User::getAge,40);

        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        userList.forEach(System.out::println);
    }
Copy the code

The QueryWrapper class already provides a lot of power, and the lambda conditional constructor does the same thing as the QueryWrapper class. Why should there be redundant lambda conditional constructors?

The QueryWrapper constructor constructs the WHERE condition by writing the corresponding properties in the table itself. It is prone to misspelling and does not report errors at compile time, but only at run time. The Lambda constructor calls a method in the entity class and reports an error if the name of the method is incorrectly written. So lambda error correction is much earlier than QueryWrapper. One example: Find information about employees whose names contain the word “rain.” Using QueryWrapper

QueryWrapper. Like (" name ", "rain");Copy the code

Using the lambda

LambdaQueryWrapper. Like (User: : getName, "rain");Copy the code

If the name is spelled naem by mistake, the program will not report an error, but if the method name is getNaem, the program will immediately report an error.

The fourth type of lambda constructor, for those of you who are careful, will find it troublesome to pass the object to mapper’s selectList method every time you write a conditional constructor, whether it’s the lambda constructor or queryWrapper. MyBatisPlus provides a fourth way of functional programming that doesn’t have to be passed every time.

1. Query information about an employee whose name contains the word “rain” and whose age is over 20

    @Test
    public void lambdaSelect(a){
        List<User> userList = new LambdaQueryChainWrapper<>(userMapper).like(User::getName, "Rain").ge(User::getAge, 20).list();
        userList.forEach(System.out::println);
    }
Copy the code

Custom SQL

1. Create a mapper folder in the Resources folder and configure the path of the mapper folder in the configuration file

mybatis-plus:
  mapper-locations: mapper/*.xml
Copy the code

2. Create userMapper.xml in the mapper folder.
3. Write interface in UseMapper interface like MyBatis, and write SQL in UserMapper interface.

UserMapper

public interface UserMapper extends BaseMapper<User> {

    /** * Query information about all users *@return list
     */
    List<User> selectAll(a);
}
Copy the code

UserMapper.xml

      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.ywb.demo.dao.UserMapper">

    <select id="selectAll" resultType="org.ywb.demo.pojo.User">
        select * from user
    </select>
</mapper>
Copy the code

Paging query

MyBatis provides logical paging, which queries all data out each time, stores it in memory, and then returns it page by page according to page capacity. If the table is very large, it is undoubtedly a disaster! MyBatisPlus physical paging plugin

1. Create a new config class, created in the config classPaginationInterceptorobject

@Configuration
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(a){
        return newPaginationInterceptor(); }}Copy the code

2. Test: queries information about users older than 20 and returns it in two pages per page.

    @Test
    public void selectPage(a){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age".20);

        // Set the current page and page capacity
        Page<User> page = new Page<>(1.2);

        IPage<User> userIPage = userMapper.selectPage(page, queryWrapper);

        System.out.println("Total pages:"+userIPage.getPages());
        System.out.println(Total number of records:+userIPage.getTotal());
        userIPage.getRecords().forEach(System.out::println);
    }
Copy the code

The IPage class construction parameter provides an overload of parameters. If the third parameter is false, the total number of records will not be queried.

public Page(long current, long size, boolean isSearchCount) {
        this(current, size, 0, isSearchCount);
}
Copy the code

update

1. Update user information using the method provided by userMapper

    @Test
    public void updateTest1(a){
        User user = new User();
        user.setId(1088250446457389058L);
        user.setEmail("update@email");
        int rows = userMapper.updateById(user);
        System.out.println(rows);
    }
Copy the code

2. Update data with UpdateWrapper (equivalent to a federated primary key)

    @Test
    public void updateTest2(a){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("name"."Li Yiwei").eq("age".26);

        User user = new User();
        user.setEmail("update2@email");
        int rows = userMapper.update(user, updateWrapper);
        System.out.println(rows);
    }
Copy the code

3. When we update a small amount of user information, we can directly update the properties by calling the set method without creating an object.

    @Test
    public void updateTest3(a){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("name"."Li Yiwei").eq("age".26).set("email"."[email protected]");
        userMapper.update(null,updateWrapper);
    }
Copy the code

4. Update the data with lambda

    @Test
    public void updateByLambda(a){
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate();
        lambdaUpdateWrapper.eq(User::getName,"Li Yiwei").eq(User::getAge,26).set(User::getAge,27);
        userMapper.update(null,lambdaUpdateWrapper);
    }
Copy the code

delete

The deletion method is very similar to that of an Update.

AR mode (Active Record)

Directly through the entity class to complete the data to add, delete, change and check.

1. The entity class inherits from the Model class

@Data
@EqualsAndHashCode(callSuper = false)
public class User extends Model<User> {
    private Long id;
    @TableField(condition = SqlCondition.LIKE)
    private String name;
    private Integer age;
    private String email;
    private Long managerId;
    private LocalDateTime createTime;
}
Copy the code

Model class encapsulates many methods of adding, deleting, and modifying data. UserMapper can be used to complete adding, deleting, and modifying data.

1. Example Query information about all users

    @Test
    public void test(){
        User user = new User();
        user.selectAll().forEach(System.out::println);
    }
Copy the code

The primary key strategy

The primary key policy of MyBatisPlus is encapsulated in the IdType enumeration class.

@Getter
public enum IdType {
    /** * Database ID increment */
    AUTO(0),
    /** * This type is not set to the primary key type (will follow the global) */
    NONE(1),
    /** * User input ID * 
 This type can be filled by registering the autofill plugin yourself 
 */
    INPUT(2),

    /* This parameter is automatically filled only when the insert object ID is null. * /
    /** * Globally unique ID (idWorker) */
    ID_WORKER(3),
    /**
     * 全局唯一ID (UUID)
     */
    UUID(4),
    /** * String globally unique ID (the string representation of the idWorker) */
    ID_WORKER_STR(5);

    private final int key;

    IdType(int key) {
        this.key = key; }}Copy the code

To complete the primary key configuration, annotate TableId(type=’ XXX ‘) on the primary key ID attribute of the corresponding database in the entity class.

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

The primary key policy configured in this mode can only take effect in this table, but other tables need to be configured. To avoid redundancy, MybatisPlus provides global configuration. You can configure the primary key policy in the configuration file.

mybatis-plus:
  mapper-locations: mapper/*.xml
  global-config:
    db-config:
      id-type: auto
Copy the code

If both global and local policies are set, the local policy takes precedence.

The basic configuration

MyBatisPlus official document

Mybtis-plus: mapper-locations: mapper/*.xml global-config: db-config: # mybtis-plus: mapper-locations: mapper/*.xml global-config: db-config: # mybtis-plus: mapper-locations: mapper/*. Default: table-underline: true # Add mybatis config file config-location: Type-aliases-package: org.ywb.demo.pojo map-underscore-to-camel-case: trueCopy the code

• The appendix

1. MybatisPlus (mybatisPlus)
2. Source address: github.com/xiao-ren-wu…