MyBatis-Plus 框架学习笔记

文章目录

- 1. 基本使用
- 2. 常用注解
- - 2.1. @TableName
  - 2.2. @TableId
  - 2.3. @TableField
  - 2.4. @Version
  - 2.5. @EnumValue
  - 2.6. @TableLogic
- 3. CRUD
- - 3.1. 查询
  - - 3.1.1. selectList
    - - 3.1.1.1. 单条件查询示例
      - 3.1.1.2. 多条件查询示例
      - 3.1.1.3. 查询结果列的控制
      - 3.1.1.4. 条件作用，非空判断
      - 3.1.1.5. allEq
    - 3.1.2. selectById
    - 3.1.3. selectBatchIds
    - 3.1.4. selectByMap
    - 3.1.5. selectCount
    - 3.1.6. selectMaps
    - 3.1.7. selectPage（分页查询）
    - 3.1.8. selectMapsPage（分页查询）
    - 3.1.9. selectObjs
    - 3.1.10. selectOne
  - 3.2. 添加
  - - 3.2.1. insert
    - 3.2.2. insertOrUpdate
  - 3.3. 删除
  - - 3.3.1. deleteById
    - 3.3.2. deleteBatchIds
    - 3.3.3. delete
    - 3.3.4. deleteByMap
  - 3.4. 修改
  - - 3.4.1. updateById
    - 3.4.2. update
- 4. Lambda 条件构造器
- 5. 自定义方法 SQL
- - 5.1. 简单自定义
  - 5.2. 复杂自定义
  - 5.3. 自定义分页
- 6. service 方法调用
- 7. MyBatis-Plus 自动生成
- 8. 其他配置
- 9. 注意事项

1. 基本使用

1、创建 Maven 工程

MyBatis-Plus 框架学习笔记

2、pom.xml 引入 MyBatis Plus 的依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.2</version>
</dependency>

3、创建实体类

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
}

4、创建 Mapper 接口

package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

5、application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: 123456789
#SQL语句输出，不设置这个，直接用下边的方法，设置对应包的日志级别也行
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#打印SQL语句，trace级别能把SQL执行结果打印出来，设置mapper包为trace级别
logging.level.com.example.demo.generator.mapper=trace

6、启动类需要添加 @MapperScan(“mapper 所在的包”)，否则无法加载 Mppaer bean。

package com.example.mybatisplus;

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

@SpringBootApplication
@MapperScan(basePackages = {"com.example.mybatisplus.mapper"})
public class MybatisplusApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisplusApplication.class, args);
    }
}

7、测试

package com.example.mybatisplus.mapper;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class UserMapperTest {

    @Autowired
    private UserMapper mapper;

    @Test
    void test(){
        mapper.selectList(null).forEach(System.out::println);
    }

}

2. 常用注解

2.1. @TableName

mp 会自动将实体驼峰命名对应为数据库下划线命名，如果命名没有对应上，需要用该注解进行指定表名。

value

属性映射数据库的表名。

package com.example.mybatisplus.entity;

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

@Data
@TableName(value = "user")
public class Account {
    private Long id;
    private String name;
    private Integer age;
}

2.2. @TableId

设置主键映射。mp 用来指定主键 id，默认会直接将 id 字段作为主键，如果主键不是 id，需要使用该注解进行指定。

value

属性映射主键字段名。当主键名和实体类主键字段名不一致的时候使用。

type

属性设置主键类型，即主键的生成策略，默认为

IdType.NONE

。

AUTO：数据库主键自增，开发者无需赋值。该类型请确保数据库设置了 ID自增，否则无效。主键值会回填到参数对象的主键字段中。如果开发者手动赋值了，赋值无效，依然是数据库自增。
NONE：type 的默认类型，如果开发者没有手动赋值，则数据库通过雪花算法算出一个 Long 值给主键赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
INPUT：如果开发者没有手动赋值，则数据库通过自增的方式给主键赋值（该功能需要在数据库表的主键开启自动自增属性，否则会报 id 不能为 null 的错误了），主键值不会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
ASSIGN_ID：mp 自动赋值，雪花算法。主键类型可为数值和字符串。如果开发者没有手动赋值，则通过雪花算法的方式给主键赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。
ASSIGN_UUID：主键的数据类型必须是 String，自动生成 UUID 进行赋值，主键值会回填到参数对象的主键字段中。如果开发者手动赋值，则存入该值。

package com.example.mybatisplus.entity;

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

@Data
public class User {
	@TableId(value = "id", type = IdType.AUTO)
    private Long num;
    private String name;
    private Integer age;
}

2.3. @TableField

映射非主键字段。

value

属性映射字段名。

exist

属性表示是否为数据库字段，默认 true 是，false 不是。如果实体类中的成员变量在数据库中没有对应的字段，则可以使用该属性。主要用于 VO、DTO。

select

属性表示是否查询该字段。大字段可设置为 false 不加入 select 查询范围，逻辑删除字段 deleted 也可以使用该属性。

fill

属性表示是否自动填充，将对象存入数据库的时候，由 MyBatis-Plus 自动给某些字段赋值。常用于 create_time、update_time 赋值。也用于新增人、修改人字段赋值。

1、给表添加 create_time、update_time 字段

2、实体类中添加成员变量

package com.example.mybatisplus.entity;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

import java.util.Date;

@Data
public class User {
    private Long id;
    @TableField(value = "name",select = false)
    private String title;
    private Integer age;
    @TableField(exist = false)
    private String gender;
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

3、创建自动填充处理器

package com.example.mybatisplus.handler;

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

import java.util.Date;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        //判断表中是否有对应字段，没有对应字段的时候，不去执行自动填充，提高性能
        boolean hasCreateTimeSetter = metaObject.hasSetter("createTime");
        if (hasCreateTimeSetter){
            this.setFieldValByName("createTime", new Date(), metaObject);
        }
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter){
            this.setFieldValByName("updateTime", new Date(), metaObject);
        }
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        //判断表中是否有对应字段，没有对应字段的时候，不去执行自动填充，提高性能
        boolean hasUpdateTimeSetter = metaObject.hasSetter("updateTime");
        if (hasUpdateTimeSetter) {
            //判断该字段是否已经被手动赋值，手动赋值的将不会被自动填充，否则会被覆盖的
            Object val = this.getFieldValByName("updateTime", metaObject);
            if (val == null){
                this.setFieldValByName("updateTime", new Date(), metaObject);
            }
        }
    }
}

2.4. @Version

标记乐观锁，通过

version

字段来保证数据的安全性，当修改数据的时候，会以

version

作为条件，当条件成立的时候才会修改成功。用于并发

update

操作。每次

updaate

语句都会在条件中拼接

version

字段。

version = 1，线程1和线程2，并发执行，只有一个能成功。

线程 1：update … set version = 2 where version = 1

线程 2：update … set version = 2 where version = 1

1、数据库表添加 version 字段，默认值为 1

2、实体类添加 version 成员变量，并且添加 @Version 注解。

package com.example.mybatisplus.entity;

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

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    @Version
    private Integer version;
}

3、注册配置类

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    //老版本
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
    //新版本（老版本提示已删除的则写这个）
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
	    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
	    interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
	    return interceptor;
	}
    
}

2.5. @EnumValue

1、通用枚举类注解，将数据库字段映射成实体类的枚举类型成员变量。

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.EnumValue;

public enum StatusEnum {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    @EnumValue
    private Integer code;
    private String msg;
}

package com.example.mybatisplus.entity;

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

import java.util.Date;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    
    private StatusEnum status;
}

application.yml 配置枚举包扫描，枚举值和枚举自动转换。

mybatis-plus
  type-enums-package: 
  	com.example.mybatisplus.enums

2、还有一种方法是通过实现接口的方式

package com.example.mybatisplus.enums;

import com.baomidou.mybatisplus.annotation.IEnum;

public enum StatusEnum implements IEnum<Integer> {
    WORK(1,"上班"),
    REST(0,"休息");

    StatusEnum(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    private Integer code;
    private String msg;
	//这里配置的是转换的值，也就是时候数据库存的是code值，查询出来会被自动转换为枚举 WORK 或者 REST
    @Override
    public Integer getValue() {
        return this.code;
    }
}

2.6. @TableLogic

映射逻辑删除。

逻辑删除，则 delete 语句会变为 update 语句，会将 deleted 字段置为已删除的值，比如1。
查询语句和更新语句会自动加过滤条件，比如 deleted=0（0表示未删除，1表示删除）。
自定义 SQL 语句不会添加逻辑删除标志。需要自己添加这个过滤条件 deleted=0。

1、数据表添加 deleted 字段。

2、实体类添加注解和字段。

package com.example.mybatisplus.entity;

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

import java.util.Date;

@Data
public class User {
    private Long id;
    private String title;
    private Integer age;
    @TableLogic
    private Integer deleted;
}

3、application.yml 添加配置。配置0表示未删除，1表示已删除。

mybatis-plus
  global-config:
    db-config:
      logic-not-delete-value: 0
      logic-delete-value: 1

3. CRUD

3.1. 查询

3.1.1. selectList

3.1.1.1. 单条件查询示例

//SELECT * FROM user;
List<User> userList = userMapper.selectList(null);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name = '张三')
wrapper.eq("name", "张三");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (status = 1)
wrapper.eq("status", StatusEnum.WORK);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", 20);
//SELECT * FROM user WHERE (name = '张三' AND age = 20)
wrapper.allEq(map);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age < 20)
wrapper.lt("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age > 20)
wrapper.gt("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <= 20)
wrapper.le("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age >= 20)
wrapper.ge("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (age <> 20)
wrapper.ne("age", 20);
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%马%')
wrapper.like("name", "马");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '张%')
wrapper.likeRight("name", "张");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (name LIKE '%三')
wrapper.likeLeft("name", "三");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (
// id IN (select id from user where id < 10) AND
// age IN (select age from user where age > 3)
// )
wrapper.inSql("id", "select id from user where id < 10");
wrapper.inSql("age", "select age from user where age > 3");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age DESC
wrapper.orderByDesc("age");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user ORDER BY age ASC
wrapper.orderByAsc("age");
List<User> userList = userMapper.selectList(wrapper);

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user HAVING id > 5 ORDER BY age ASC
wrapper.orderByAsc("age");
wrapper.having("id > 5" );
List<User> userList = userMapper.selectList(wrapper);

3.1.1.2. 多条件查询示例

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//QueryWrapper<User> queryWrapper1 = Wrappers.query();
queryWrapper.like("name", "雨").lt("age", 40);
//WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
//WHERE (name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or().ge("age", 25)
        .orderByDesc("age").orderByAsc("id");
//WHERE (name LIKE '王%' OR age >= 25) ORDER BY age DESC,id ASC
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2020-11-03")
       .inSql("manager_id", "select id from user where name like '王%'");
//WHERE (date_format(create_time,'%Y-%m-%d') = '2020-11-03' AND manager_id IN (select id from user where name like '王%'))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("create_time", "2020-11-03 14:42:22").le("create_time", "2020-11-13 14:42:22");
//WHERE (create_time >= ? AND create_time <= ?)
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").and(
        wq -> wq.lt("age", 40).or().isNotNull("email")
);
//WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or(
        wq -> wq.lt("age", 40).isNotNull("email")
);
//WHERE (name LIKE '王%' OR (age < 40 AND email IS NOT NULL))
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
//WHERE ((age < 40 OR email IS NOT NULL) AND name LIKE '王%')
List<User> userList = userMapper.selectList(queryWrapper);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30,31,32,33)).last("limit 1");
//WHERE (age IN (30,31,32,33)) limit 1
List<User> userList = userMapper.selectList(queryWrapper);

3.1.1.3. 查询结果列的控制

只查询某几列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

不查询某几列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(User.class,
        info-> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id")
).like("name", "雨").lt("age", 40);
//SELECT id,name,age,email FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<User> userList = userMapper.selectList(queryWrapper);

3.1.1.4. 条件作用，非空判断

@Test
public void conditionTest(){
    String name = "王";
    String email = "";
    condition(name, email);
}
public void condition(String name, String email){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like(StringUtils.isNotBlank(name),"name", name)
            .eq(StringUtils.isNotBlank(email),"email", email);
    //WHERE (name LIKE ?)
    List<User> userList = userMapper.selectList(queryWrapper);
    userList.forEach(System.out::println);
}

3.1.1.5. allEq

Map<String, Object> params = new HashMap<>();
params.put("name", "王天风");
params.put("age", 25);
params.put("email", null);

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(params);
//FROM user WHERE (name = '王天风' AND age = 25 AND email IS NULL)
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);

QueryWrapper<User> queryWrapper1 = new QueryWrapper<>();
queryWrapper1.allEq(params, false);
//WHERE (name = '王天风' AND age = ?)
List<User> userList1 = userMapper.selectList(queryWrapper1);
userList1.forEach(System.out::println);

//将name字段过滤掉
QueryWrapper<User> queryWrapper2 = new QueryWrapper<>();
queryWrapper2.allEq((k,v)->!k.equals("name"), params);
//WHERE (age = 25 AND email IS NULL)
List<User> userList2 = userMapper.selectList(queryWrapper2);
userList2.forEach(System.out::println);

3.1.2. selectById

//SELECT * FROM user WHERE id=7
User user = userMapper.selectById(7);

3.1.3. selectBatchIds

//SELECT * FROM user WHERE id IN ( 7 , 8 , 9 )
List<User> userList = userMapper.selectBatchIds(Arrays.asList(7,8,9));

3.1.4. selectByMap

Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", 20);
//只能做等值判断，和allEq用法类似
//SELECT * FROM user WHERE name = '张三' AND age = 20
List<User> userList = userMapper.selectByMap(map);

3.1.5. selectCount

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT COUNT( * ) FROM user WHERE (id > ?)
wrapper.gt("id", 1);
Integer count = userMapper.selectCount(wrapper);

3.1.6. selectMaps

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT * FROM user WHERE (id > ?)
wrapper.gt("id", 1);
//返回的是map，不是user对象
List<Map<String, Object>> mapList = userMapper.selectMaps(wrapper);

只查询某几列，返回 map，不会出现没查询的字段为 null

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name").like("name", "雨").lt("age", 40);
//SELECT id,name FROM user WHERE (name LIKE '%雨%' AND age < 40?)
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

分组查询统计，使用 map

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);
userList.forEach(System.out::println);

3.1.7. selectPage（分页查询）

分页查询都需要先配置 PaginationInterceptor 。

package com.example.mybatisplus.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    /**
     * 分页查询
     */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

新版本配置：

package com.example.demo.common.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;

/**
 * @author wangbo
 * @date 2021/5/12
 */
@Configuration
public class MyBatisPlusConfig {

    /**
     * 分页插件配置
     * @return MybatisPlusInterceptor
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

然后直接使用 Page 对象进行分页查询。

//SELECT * FROM user LIMIT 3,3
Page<User> page = new Page<>(2,3);
Page<User> result = userMapper.selectPage(page, null);
//每页大小
System.out.println(result.getSize());
//总记录数
System.out.println(result.getTotal());
//记录
result.getRecords().forEach(System.out::println);

详细版本写法：

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page = new Page<>(3, 2);
IPage<User> iPage = userMapper.selectPage(page, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数："+ iPage.getPages());
System.out.println("总记录数：" + iPage.getTotal());
List<User> userList = iPage.getRecords();
userList.forEach(System.out::println);

不查总记录数，可少执行一条SQL

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<User> page2 = new Page<>(3, 2, false);
IPage<User> iPage2 = userMapper.selectPage(page2, queryWrapper);
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数："+ iPage2.getPages());
System.out.println("总记录数：" + iPage2.getTotal());
List<User> userList2 = iPage2.getRecords();
userList2.forEach(System.out::println);

3.1.8. selectMapsPage（分页查询）

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 26);

Page<Map<String, Object>> page1 = new Page<>(3, 2);
IPage<Map<String, Object>> iPage1 = userMapper.selectMapsPage(page1, queryWrapper);
//SELECT COUNT(1) FROM user WHERE (age >= 26)
//SELECT * FROM user WHERE (age >= 26) LIMIT 4,2
System.out.println("总页数1："+ iPage.getPages());
System.out.println("总记录数1：" + iPage.getTotal());
List<Map<String, Object>> userList1 = iPage1.getRecords();
userList1.forEach(System.out::println);

3.1.9. selectObjs

只返回了主键

//SELECT * FROM user
List<Object> list = userMapper.selectObjs(null);

只返回第一列

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
        .groupBy("manager_id").having("sum(age)<{0}", 500);
//SELECT manager_id,avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age)<500
List<Object> userList = userMapper.selectObjs(queryWrapper);
userList.forEach(System.out::println);

3.1.10. selectOne

查询一条记录数，可以为null，注意不能为多条

//SELECT * FROM user WHERE (id = 1)
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1);
//结果集必须是一条记录
User user = userMapper.selectOne(wrapper);
System.out.println(user);

3.2. 添加

3.2.1. insert

User user = new User();
user.setName("小明");
user.setAge(22);
int i = userMapper.insert(user);
System.out.println(i);
System.out.println(user);

3.2.2. insertOrUpdate

无ID 就是插入，有 ID 会先执行查询，有则更新，无则插入。

User user = new User();
user.setId(3L);
user.setAge(32);
user.setName("史蒂夫");
boolean result = user.insertOrUpdate();
System.out.println("插入是否成功：" + result);
//SELECT * FROM user WHERE id=3
//INSERT INTO user ( name, age) VALUES ( '史蒂夫', 32 )
//自动插入的记录ID按照自增原则，生成的是8，并不是3

3.3. 删除

3.3.1. deleteById

//DELETE FROM user WHERE id=1
userMapper.deleteById(1);

3.3.2. deleteBatchIds

//DELETE FROM user WHERE id IN ( 1 , 2, 3 )
userMapper.deleteBatchIds(Arrays.asList(1,2,3));

3.3.3. delete

QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("age", 14);
//DELETE FROM user WHERE (age = 14)
userMapper.delete(wrapper);

3.3.4. deleteByMap

Map<String, Object> map = new HashMap<>();
map.put("name", "张三");
//DELETE FROM user WHERE name = '张三'
userMapper.deleteByMap(map);

3.4. 修改

3.4.1. updateById

User user = userMapper.selectById(6);
user.setName("内马尔休闲鞋");
//UPDATE user SET age=22, name='内马尔休闲鞋' WHERE id=6
userMapper.updateById(user);

3.4.2. update

更新大量字段使用实体

User user = new User();
user.setAge(30);
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("name", "张三");
//UPDATE user SET age=30 WHERE (name = '张三')
userMapper.update(user, wrapper);

User whereUser = new User();
whereUser.setEmail("[email protected]");

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>(whereUser);
updateWrapper.eq("name", "李艺伟").eq("age", 30);

User user = new User();
user.setAge(32);
user.setEmail("[email protected]");

int rows = userMapper.update(user, updateWrapper);
//UPDATE user SET age=?, email=? WHERE email=? AND (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

更新少量字段，不用使用实体，直接使用 set

UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name", "李艺伟").eq("age", 32).set("age", 50);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

Lambda 可防止误写字段

LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.eq(User::getName, "李艺伟").eq(User::getAge, 50).set(User::getAge, 51);

int rows = userMapper.update(null, updateWrapper);
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("修改记录数：" + rows);

Lambda可防止误写字段，链式调用

boolean update = new LambdaUpdateChainWrapper<User>(userMapper)
	.eq(User::getName, "李艺伟").eq(User::getAge, 50)
	.set(User::getAge, 51).update();
//UPDATE user SET age=? WHERE (name = ? AND age = ?)
System.out.println("是否修改成功：" + update);

4. Lambda 条件构造器

Lambda 条件构造器，防止写错列名。

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
//LambdaQueryWrapper<User> lambdaQueryWrapper1 = new QueryWrapper<User>().lambda();
//LambdaQueryWrapper<User> lambdaQueryWrapper2 = Wrappers.lambdaQuery();
lambdaQueryWrapper.like(User::getName, "雨").lt(User::getAge, 40);
//WHERE (name LIKE '%雨%' AND age < 40)
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//WHERE (name LIKE '王' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectList(lambdaQueryWrapper);
userList.forEach(System.out::println);

链式调用

//WHERE (name LIKE '%雨%' AND age >= 20)
List<User> userList = new LambdaQueryChainWrapper<>(userMapper)
	.like(User::getName, "雨").ge(User::getAge, 20).list();

//SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name = ?)
User user = new LambdaQueryChainWrapper<>(userMapper).eq(User::getName, "张三").one();

5. 自定义方法 SQL

5.1. 简单自定义

package com.example.mybatisplus.entity;

import lombok.Data;

@Data
public class ProductVO {
    private Integer category;
    private Integer count;
    private String description;
    private Integer userId;
    private String userName;
}

package com.example.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.southwind.mybatisplus.entity.ProductVO;
import com.southwind.mybatisplus.entity.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {
    @Select("select p.*, u.name as userName from product p, user u where p.user_id = u.id and u.id = #{id}")
    List<ProductVO> productList(Integer id);
}

List<ProductVO> productVOList = userMapper.productList(7);
productVOList.forEach(System.out::println);

5.2. 复杂自定义

public interface UserMapper extends BaseMapper<User> {
    //自定义方法，可以直接使用注解，也可以xml里配置，都是可以实现的
    //@Select("select * from user ${ew.customSqlSegment}")
    List<User> selectCustom(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
}

xml 中配置 SQL：

<select id="selectCustom" resultType="com.example.demo.entity.User">
    select * from user ${ew.customSqlSegment}
</select>

LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.likeRight(User::getName, "王").and(
        lwq -> lwq.lt(User::getAge, 40).or().isNotNull(User::getEmail)
);
//select * from user WHERE (name LIKE '王%' AND (age < 40 OR email IS NOT NULL))
List<User> userList = userMapper.selectCustom(lambdaQueryWrapper);
userList.forEach(System.out::println);

5.3. 自定义分页

public interface UserMapper extends BaseMapper<User> {
    //自定义分页
    IPage<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

    //自定义分页
    IPage<User> selectUserPage1(Page<User> page, @Param("user") User user);
}

xml 中配置 SQL：

<select id="selectUserPage" resultType="com.example.demo.entity.User">
    select * FROM user ${ew.customSqlSegment}
</select>

<select id="selectUserPage1" resultType="com.example.demo.entity.User">
    select * FROM user
    <where>
        <if test="user.id != null">
            and id &gt; #{user.id}
        </if>
        <if test="user.name != null">
            and name = #{user.name}
        </if>
    </where>
</select>

@Test
public void selectPageCustom() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.ge("age", 26);

    Page<User> page = new Page<>(1, 2);
    IPage<User> iPage = userMapper.selectUserPage(page, queryWrapper);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("总页数："+ iPage.getPages());
    System.out.println("总记录数：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

@Test
public void selectPageCustom1() {
    User user = new User();
    user.setId(2L);
    //user.setName("王伯当");

    Page<User> page = new Page<>(3, 2);
    IPage<User> iPage = userMapper.selectUserPage1(page, user);
    //SELECT COUNT(1) FROM user WHERE (age >= 26)
    //select * FROM user WHERE (age >= 26) LIMIT 2
    System.out.println("总页数："+ iPage.getPages());
    System.out.println("总记录数：" + iPage.getTotal());
    List<User> userList = iPage.getRecords();
    userList.forEach(System.out::println);
}

6. service 方法调用

和直接调用 mapper 的方法有略微的差别。

package com.example.demo.mp;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.demo.entity.User;
import com.example.demo.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;

import java.util.Arrays;
import java.util.List;

/**
 * @auther wangbo
 * @date 2020-11-03 16:50
 */
@SpringBootTest
public class ServiceTest {
    @Autowired
    private UserService userService;

	//只查询一个
    @Test
    public void select(){
        User one = userService.getOne(Wrappers.<User>lambdaQuery().gt(User::getAge, 25),false);
        //SELECT * FROM user WHERE age > 25
        System.out.println(one);
    }

	//批量插入
    @Test
    public void batch(){
        User user1 = new User();
        user1.setName("张三1");
        user1.setAge(28);

        User user2 = new User();
        user2.setName("张三2");
        user2.setAge(29);

        List<User> list = Arrays.asList(user1, user2);
        boolean savebatch = userService.saveBatch(list);
        System.out.println("批量插入是否成功："+ savebatch);
    }


    //链式操作
    //查询
    @Test
    public void chain(){
        List<User> list = userService.lambdaQuery().gt(User::getAge, 25).like(User::getName, "雨").list();
        list.forEach(System.out::println);
    }
    
    //更新
    @Test
    public void chain1(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").set(User::getAge, 20).update();
        System.out.println("更新是否成功："+ update);
        //UPDATE user SET age=? WHERE (age > ? AND name LIKE ?)
    }
    //删除
    @Test
    public void chain2(){
        boolean update = userService.lambdaUpdate().gt(User::getAge, 25).like(User::getName, "雨").remove();
        System.out.println("删除是否成功："+ update);
        //DELETE FROM user WHERE (age > ? AND name LIKE ?)
    }
}

7. MyBatis-Plus 自动生成

根据数据库表自动生成实体类、Mapper、Service、ServiceImpl、Controller。

1、pom.xml 导入 MyBatis Plus Generator 依赖和模板依赖。

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

三种模板：Velocity（默认）、Freemarker、Beetl。

2、启动类，配置相关项后直接执行 main 方法即可生成对应的文件。

package com.example.mybatisplus;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;

public class Main {
    public static void main(String[] args) {
        //创建 Generator对象
        AutoGenerator autoGenerator = new AutoGenerator();
        //数据源
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8");
        dataSourceConfig.setUsername("root");
        dataSourceConfig.setPassword("123456789");
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        autoGenerator.setDataSource(dataSourceConfig);
        //全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");
        globalConfig.setOpen(false);
        globalConfig.setAuthor("wangbo");//设置作者
        globalConfig.setServiceName("%sService");//指定Service名字，不指定前面会有I
        autoGenerator.setGlobalConfig(globalConfig);
        //包信息
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setParent("com.example.mybatisplus");
        packageConfig.setModuleName("generator");//生成的文件所在的包目录，直接在父包com.example.mybatisplus下就不用写了
        packageConfig.setController("controller");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setMapper("mapper");
        packageConfig.setEntity("entity");
        autoGenerator.setPackageInfo(packageConfig);
        //配置策略
        StrategyConfig strategyConfig = new StrategyConfig();
        strategyConfig.setInclude("product");//这里配置需要生成的目标表名，不配置则默认生成全部。
        strategyConfig.setEntityLombokModel(true);
        strategyConfig.setRestControllerStyle(true);
        strategyConfig.setNaming(NamingStrategy.underline_to_camel);//下划线转驼峰
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);//下划线转驼峰
        autoGenerator.setStrategy(strategyConfig);

        autoGenerator.execute();
    }
}

8. 其他配置

# 配置mybatis-plus自定义SQL的xml文件位置
mybatis-plus.mapper-locations=classpath:com/example/demo/mapper/*.xml
# 配置mybatis-plus的全局主键策略，这里设置为数据库自增。
# 实体类上的局部配置优先级高于这里的全局配置。
mybatis-plus.global-config.db-config.id-type=auto
# 在自定义SQL的xml文件中resultType直接写实体的简写，可省略包名
mybatis-plus.type-aliases-package=com.example.demo.entity

9. 注意事项

在 XML 中使用自定义 SQL，注意要在配置文件中配置 xml 文件的位置：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/**/mapper/xml/*.xml

否则在调用自定义SQL的时候会出现以下错误：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.hny.pspp.album.mapper.CloudAlbumMapper.pageUserIdList

这样配置的话正常启动项目调用自定义SQL没有问题了，但是在使用单元测试调用自定义SQL的时候还是会出现上述异常：

package com.hny.pspp.album.scheduled;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ClearTaskTest {

    @Autowired
    private ClearTask clearTask;
    
    @Test
    void clear() {
        clearTask.clear();
    }
}

实测可以通过补全

locations

路径解决问题：

mybatis-plus:
  mapper-locations: classpath:com/hny/pspp/album/mapper/xml/*.xml

MyBatis-Plus 框架学习笔记

文章目录

1. 基本使用

2. 常用注解

2.1. @TableName

2.2. @TableId

2.3. @TableField

2.4. @Version

2.5. @EnumValue

2.6. @TableLogic

3. CRUD

3.1. 查询

3.1.1. selectList

3.1.1.1. 单条件查询示例

3.1.1.2. 多条件查询示例

3.1.1.3. 查询结果列的控制

3.1.1.4. 条件作用，非空判断

3.1.1.5. allEq

3.1.2. selectById

3.1.3. selectBatchIds

3.1.4. selectByMap

3.1.5. selectCount

3.1.6. selectMaps

3.1.7. selectPage（分页查询）

3.1.8. selectMapsPage（分页查询）

3.1.9. selectObjs

3.1.10. selectOne

3.2. 添加

3.2.1. insert

3.2.2. insertOrUpdate

3.3. 删除

3.3.1. deleteById

3.3.2. deleteBatchIds

3.3.3. delete

3.3.4. deleteByMap

3.4. 修改

3.4.1. updateById

3.4.2. update

4. Lambda 条件构造器

5. 自定义方法 SQL

5.1. 简单自定义

5.2. 复杂自定义

5.3. 自定义分页

6. service 方法调用

7. MyBatis-Plus 自动生成

8. 其他配置

9. 注意事项

继续阅读