Mybatis-Plus使用

Mybatis-Plus

官网地址：mybatis-plus官网

1 基础使用

1.1 pom.xml依赖引入

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
		</dependency>
		
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.2</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.2.11</version>
		</dependency>
	
	
	</dependencies>

1.2 properties.yml

spring:
      datasource:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
            username: root
            password: 123456
mybatis-plus:
      configuration:
            log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

1.3 mapper接口继承BaseMapper

public interface UserMapper extends BaseMapper<User> {
}

1.4 MainApplication主启动类配置扫描注解

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

1.5 User实体类

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

1.6 MyTest测试类

@SpringBootTest
class MainApplicationTests {

    @Autowired
    UserMapper userMapper;

    @Test
    void contextLoads() {
    }

    @Test
    void mapperTest(){
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
        Map<String, Object> map = new HashMap<>();
        map.put("name", "zhangsan");
        map.put("age", 13);
        userMapper.deleteByMap(map);
        User user = new User(6L, "zhangsan", 13, "email112");
        userMapper.insert(user);
        // userMapper.selectBatchIds(Arrays.asList(new Long[]{1L, 2L, 6L})).forEach(System.out::println);

        userMapper.selectByMap(map).forEach(System.out::println);
    }

1.6 IService接口

//UserService继承IService<User>泛型User即是实体类，又映射表名user
public interface UserService extends IService<User> {
}

//UserServiceImpl实现类
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {}

//测试类
@Autowired
UserService userService;
@Test
void serviceTest(){
    System.out.println(userService.count());
}

2 自定义sql，定义mapper.xml文件

mybatis-plus会自动扫描resources/mapper目录及其子目录下的所有xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
	  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatisplus.mybatisplus.mapper.UserMapper">
	<select id="selectById" resultType="map">
		select id, name, age, email from t_user where id = #{id}
	</select>
</mapper>

若有自定义的mapper.xml文件路径，在application.xml文件配置

mybatis-plus:
      mapper-locations: # mapper.xml文件路径，例如：classpath:*/mapper/**/*

3 常用注解

3.1 @TableName

对于实体类是User而表名是t_user的情况

三种方案：

​ 1.User类加注解@TableName(“t_user”)，其他每个实体类如不对应都需加此注解

​ 2.application.yml加配置前缀t_，所有实体类映射时，自动加前缀

​ 3.mapper.xml，配置文件单独配置映射，里的自定义sql的表名不受配置和注解影响

User类

@NoArgsConstructor
@AllArgsConstructor
@Data
@TableName("t_user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

application.yml

spring:
      datasource:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
            username: root
            password: 333
mybatis-plus:
      configuration:
            log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
      global-config:
        db-config:
              table-prefix: t_

3.2 @TableId

没有任何配置的情况下，默认只使用id作为主键

配置@TableId的属性会被认定为主键

@TableId
private Long Uid;

主键字段映射

@TableId(value=“aaa”)

value属性会将当前标志的实体类主键与数据库字段aaa主键匹配

@TableId("uid")
private Long id;

主键生成策略

@TableId(type=IdType.ASSIGN_ID)

type属性标识主键生成策略，默认是IdType.ASSIGN_NONE，会采用雪花算法

• 雪花算法：IdType.ASSIGN_ID
• 自动递增：IdType.AUTO，将数据库主键设置为自动递增，再将type=IdType.AUTO
• UUID算法：ASSIGN_UUID

@NoArgsConstructor
@AllArgsConstructor
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

全局配置

mybatis-plus:
      configuration:
            log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
      global-config:
        db-config:
              table-prefix: t_
              id-type: # none # input # auto # assign_uuid # assign_id

当插入的user的主键id已经手动赋值时，则主键生成策略失效

3.3 @TableField

字段映射

1. mybatis会自动将下划线转驼峰，但字段名不一致时，需用@TableField(“字段名”) 来指定映射
2. @TableField(exist = false)表示当前字段不是数据库字段

@TableField("user_name")
private String userName;

@TableField(exist = false)
private List<Integer> permissionIdList;

3.4 @TableLogic

逻辑删除

配置@TableLogic注解后，被@TableLogic标识的属性映射字段会被作为删除标志，执行删除并不会真正删除数据库字段，而是将删除语句自动变为更新语句，将is_logic字段修改为1，则此后查询等操作会忽略此字段，即为假删除，便于恢复数据

@NoArgsConstructor
@AllArgsConstructor
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
    @TableLogic
    private Integer isLogic;
    public User(Long id, String name, Integer age, String email){
        this(id, name, age, email, null);
    }
}

全局配置逻辑删除字段

mybatis-plus:
      configuration:
            log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
      global-config:
            db-config:
                  logic-delete-field: del
                  logic-not-delete-value: 0
                  logic-delete-value: 1

4 条件构造器

4.1 QueryWrapper

查询

 @Test
    void queryWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name", "zhang")
                .between("age", 10, 20)
                .isNotNull("email");
        userMapper.selectList(queryWrapper).forEach(System.out::println);
    }

实际查询语句
Preparing: SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 AND (user_name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
==> Parameters: %zhang%(String), 10(Integer), 20(Integer)
<==    Columns: id, name, age, email, is_logic
<==        Row: 6, zhangsan, 13, email112, 0
<==      Total: 1

排序

@Test
void orderWrapper(){
    //先按照年龄升序，再按照id降序排列
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 ORDER BY age ASC,id DESC
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByAsc("age")
        .orderByDesc("id");
    userMapper.selectList(queryWrapper).forEach(System.out::println);
}

删除

@Test
void deleteWrapper(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("user_name", "zhang");
    userMapper.delete(queryWrapper);
}
//由于存在逻辑删除，所以删除语句变为修改语句
UPDATE t_user SET is_logic=1 WHERE is_logic=0 AND (user_name LIKE ?)

修改

@Test
void updateWrapper1(){
    //将年龄(大于20且名称包含"a")或邮箱为null的字段更新为user信息
    QueryWrapper<User> queryWrapper1 = new QueryWrapper<>();
    //and的优先级比or高
    queryWrapper1.gt("age", 20)
        .like("user_name", "a")
        .or()
        .isNull("email");
    User user = new User(null, "wangwu", 14, "email@163.com");
    userMapper.update(user, queryWrapper1);
    //UPDATE t_user SET user_name=?, age=?, email=? WHERE is_logic=0 AND (age > ? AND user_name LIKE ? OR email IS NULL)
}

@Test
void updateWrapper2(){
    //将年龄大于20且(名称包含"a"或邮箱为null)的字段更新为user信息
    QueryWrapper<User> queryWrapper2 = new QueryWrapper<>();
    //lambda表达式内部的优先级高
    queryWrapper2.gt("age", 20)
        .and(i->i.like("user_name", "a").or().isNull("email"));
    User user = new User(null, "xiaoming", 13, "email@164.com");
    userMapper.update(user, queryWrapper2);
    //UPDATE t_user SET user_name=?, age=?, email=? WHERE is_logic=0 AND (age > ? AND (user_name LIKE ? OR email IS NULL))
}

查询部分字段

@Test
void columns(){
    //查询部分字段
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "user_name");
    userMapper.selectMaps(queryWrapper).forEach(System.out::println);
    //SELECT id,user_name FROM t_user WHERE is_logic=0
}

子查询

//where id in (select id ...)
@Test
void inSql(){
    //查询id<=100的所有字段
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.inSql("id", "select id from t_user where id<=100");
    userMapper.selectList(queryWrapper).forEach(System.out::println);
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 AND (id IN (select id from t_user where id<=100))
}

4.2 updateWrapper

@Test
void updateWrapper(){
    //将用户名包含a且(年龄大于20或邮箱为null)的字段的名称改为lili，邮箱改为eu
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper.like("user_name", "a")
        .and(i->i.gt("age", 20).or().isNull("email"));
    updateWrapper.set("user_name", "lili")
        .set("email", "eu");
    int result = userMapper.update(null, updateWrapper);
    //UPDATE t_user SET user_name=?,email=? WHERE is_logic=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
}

字段拼接查询

if条件判断决定是否拼接字段

@Test
void joint(){
    String userName = "";
    Integer startAge = null;
    Integer endAge = 10;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    if(StringUtils.isNotBlank(userName)){
        //如果userName不为空字符串，不为null且不为空白符
        queryWrapper.like("user_name", userName);
    }
    if(startAge!=null){
        queryWrapper.le("age", startAge);
    }
    if(endAge!=null){
        queryWrapper.ge("age", endAge);
    }
    userMapper.selectList(queryWrapper).forEach(System.out::println);
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 AND (age >= ?)
}

condition决定是否拼接字段

@Test
void isJoint(){
    String name = "";
    Integer age = 12;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //会根据条件condition是否成立来决定是否拼接当前字段(condition, column, value)
    queryWrapper.like(StringUtils.isNotBlank(name), "user_name", name)
        .ge(age!=null, "age", age);
    List<User> userList = userMapper.selectList(queryWrapper);
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 AND (age >= ?)
}

4.3 LambdaQueryWrapper

@Test
void lambdaQueryWrapper(){
    String name = "";
    Integer age = 12;
    LambdaQueryWrapper<User> lambdaQW = new LambdaQueryWrapper<>();
    //会自动匹配User的name属性映射的字段，防止字段写错
    lambdaQW.like(StringUtils.isNotBlank(name), User::getName, name)
        .ge(age!=null, User::getAge, age);
    List<User> userList = userMapper.selectList(lambdaQW);
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 AND (age >= ?)
}

4.4 LambdaUpdateWrapper

 @Test
    void lambdaUpdateWrapper(){
        //将用户名包含a且(年龄大于20或邮箱为null)的字段的名称改为lili，邮箱改为eu
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.like(User::getName, "a")
                .and(i->i.gt(User::getAge, 20).or().isNull(User::getEmail));
        lambdaUpdateWrapper.set(User::getName, "lili")
                .set(User::getEmail, "eu");
        int result = userMapper.update(null, lambdaUpdateWrapper);
        //UPDATE t_user SET user_name=?,email=? WHERE is_logic=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
    }

5 分页

分页首先需要配置MybatisPlus拦截器

@Configuration
@MapperScan("com.mybatisplus.main.mapper") //将mapper包扫描移动到配置类
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

5.1 直接使用

@Test
void paging(){
    Page<User> page = new Page<>(2, 3);
    Page<User> userPage = userMapper.selectPage(page, null);
    List<User> userList = userPage.getRecords();
    Long total = userPage.getTotal();
    //SELECT id,user_name AS name,age,email,is_logic FROM t_user WHERE is_logic=0 LIMIT ?,?
}

5.2 在mapper.xml中自定义的sql使用

mapper接口类

@Repository
public interface UserMapper extends BaseMapper<User> {
    /**
     * @param page 分页对象，必须放在第一个参数位置
     * @param age 正常传参
     * @return 返回分页对象
     */
    Page<User> selectByAgePage(@Param("page") Page<User> page, @Param("age") Integer age);
}

mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
	  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatisplus.mybatisplus.mapper.UserMapper">
	<select id="selectByAgePage" resultType="User">
		select id, user_name, age, email, is_logic from t_user where age > #{age}
	</select>
</mapper>

为了在resultType中使用实体类别名，则需在yml配置文件中配置

mybatis-plus:
      type-aliases-package: com.mybatisplus.main.pojo

测试使用

@Test
void myPaging(){
    Page<User> page = new Page<>(2, 3);
    Integer age = 12;
    Page<User> userPage = userMapper.selectByAgePage(page, age);
    List<User> userList = userPage.getRecords();
    Long total = userPage.getTotal();
    //select id, user_name, age, email, is_logic from t_user where age > ? LIMIT ?,?
}

6 乐观锁与悲观锁

当A，B同时对数据进行修改，A，B同时获取到数据，A修改完提交，之后B修改完提交会把A修改的操作遮盖掉

乐观锁：加版本号，A，B同时获取到数据为版本1，A修改完更新操作发现版本号为1则提交并将版本+1，之后B修改完提交发现版本号为2，则重新获取数据再修改提交

悲观锁：A，B必须排队依次执行

乐观锁实现

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Product {
    private Integer id;
    private String name;
    private Integer price;
    //记录版本号
    @Version
    private Integer version;
}

mapper接口

@Repository
public interface ProductMapper extends BaseMapper<Product> {
}

配置类

@Configuration
@MapperScan("com.mybatisplus.main.mapper")
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //分页插件
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        //乐观锁插件
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

测试类

@Test
void myOptimistic(){
    //两人同时获取到数据
    Product productWang = productMapper.selectById(1);
    System.out.println("价格王: "+productWang.getPrice());     //价格王: 100
    Product productLi = productMapper.selectById(1);
    System.out.println("价格李: "+productLi.getPrice());       ///价格李: 100

    //小王先进行加30
    productWang.setPrice(productWang.getPrice()+30);
    productMapper.updateById(productWang);
    //UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
    // Parameters: 水杯(String), 130(Integer), 1(Integer), 1(Integer), 0(Integer)
    //Updates: 1，更新条数为1，更新完版本变为1

    //小李又进行减30
    productLi.setPrice(productLi.getPrice()-30);
    int result = productMapper.updateById(productLi);
    //UPDATE t_product SET name=?, price=?, version=? WHERE id=? AND version=?
    //Parameters: 水杯(String), 70(Integer), 1(Integer), 1(Integer), 0(Integer)
    //Updates: 0，更新失败，更新时发现版本不匹配，需重新匹配
    if(result == 0){
        productLi = productMapper.selectById(1);    //此时的版本号为2
        productLi.setPrice(productLi.getPrice()-30);   
        productMapper.updateById(productLi);    //版本号匹配可以修改
    }

    //老板查看
    System.out.println("老板查看价格: "+productMapper.selectById(1).getPrice());
    //老板查看价格: 100
}

7 通用枚举

创建枚举类

@Getter
@AllArgsConstructor
public enum SexEnum {

    MALE(1, "男"),
    FEMALE(2, "女");

    @EnumValue      //将注解所表示的属性的值存储到数据库中
    private Integer sex;
    private String sexName;
}

数据库中t_user表添加sex字段int类型

实体类User中添加SexEnum sex属性

测试类

  @Test
    void enumTest(){
        User user = new User();
        user.setSex(SexEnum.MALE);
        int result = userMapper.insert(user);
        //==>  Preparing: INSERT INTO t_user ( id, sex ) VALUES ( ?, ? )
        //==> Parameters: 1562763219775320066(Long), 1(Integer)
        //<==    Updates: 1
    }

出现错误可尝试在yml中添加enmu包扫描

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

8 代码自动生成

application.yml中引入依赖

<!--代码自动生成-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-generator</artifactId>
			<version>3.5.3</version>
		</dependency>
		<dependency>
			<groupId>org.freemarker</groupId>
			<artifactId>freemarker</artifactId>
			<version>2.3.31</version>
		</dependency>

修改并调用此方法即可，可在测试类中调用

public void fastAutoGenerator(){
    String url = "jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false";
    String username = "root";
    String password = "333";
    FastAutoGenerator.create(url, username, password)
        .globalConfig(builder -> {
            builder.author("wangpl") // 设置作者
                .enableSwagger() // 开启 swagger 模式
                .outputDir("D://java/Mybatis-Plus/code"); // 指定输出目录
        })
        .packageConfig(builder -> {
            builder.parent("com.mbp.generator") // 设置父包名
                .moduleName("system") // 设置父包模块名
                .pathInfo(Collections.singletonMap(OutputFile.xml, "D://java/Mybatis-Plus/code")); // 设置mapperXml生成路径
        })
        .strategyConfig(builder -> {
            builder.addInclude("t_user") // 设置需要生成的表名
                .addTablePrefix("t_", "c_"); // 设置过滤表前缀
        })
        .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板
        .execute();
}

9 多数据源

引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.0</version>
</dependency>

application.yml配置

spring:
# 配置数据源信息
    datasource:
        dynamic:
        # 设置默认的数据源或者数据源组,默认值即为master
            primary: master
            # 严格匹配数据源,默认false.true未匹配到指定数据源时抛异常,false使用默认数据源
            strict: false
            datasource:
                master:
                    url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf8&useSSL=false
                    driver-class-name: com.mysql.cj.jdbc.Driver
                    username: root
                    password: 123456
                slave_1:
                    url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf8&useSSL=false
                    driver-class-name: com.mysql.cj.jdbc.Driver
                    username: root
                    password: 123456

UserServiceImpl

@DS("master") //指定所操作的数据源
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements
UserService {
}

ProductServiceImpl

@DS("slave_1")
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product>
implements ProductService {
}

测试

@Autowired
private UserService userService;
@Autowired
private ProductService productService;
@Test
public void testDynamicDataSource(){
    System.out.println(userService.getById(1L));
    System.out.println(productService.getById(1L));
}

10 MybatisX插件

安装MybatisX插件，快速生成各种代码
MybatisX使用