MyBatis-Plus笔记

参考：黑马程序员

一、MyBatis-Plus简介：

MyBatis-Plus（简称MP）是一个MyBatis的增强工具，在MyBatis的基础上只做增强不做改变，为简化开发、提高效率而生。
官网：MyBatis-Plus官网

二、准备工作

1. 准备好数据库、表、和测试数据：

-- 创建数据库
CREATE DATABASE `mp`;
-- 创建测试表
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 插入测试数据
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES
('1', 'zhangsan', '123456', '张三', '18', 'test1@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES
('2', 'lisi', '123456', '李四', '20', 'test2@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES
('3', 'wangwu', '123456', '王五', '28', 'test3@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES
('4', 'zhaoliu', '123456', '赵六', '21', 'test4@itcast.cn');
INSERT INTO `tb_user` (`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES
('5', 'sunqi', '123456', '孙七', '24', 'test5@itcast.cn');

2. 创建SpringBoot工程

GroupId: cn.itcast.mp
ArtifactId: itcast-mp-springboot
Version: 1.0-SNAPSHOT

3. 导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.1.4.RELEASE</version>
    </parent>
    <groupId>cn.itcast.mp</groupId>
    <artifactId>itcast-mp-springboot</artifactId>
    <version>1.0-SNAPSHOT</version>

    <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>
      <!--简化代码的工具包-->
      <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
      </dependency>
      <!--mybatis-plus的springboot支持-->
      <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.1.1</version>
      </dependency>
      <!--mysql驱动-->
      <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>
    </dependencies>
</project>

4. 日志配置log4j.properties

log4j.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

5. SpringBoot配置application.properties

spring.application.name=itcast-mp-springboot
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mp?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

6. 编写实体类User.java

package cn.itcast.mp.pojo;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String mail;
    private String address;
}

7. 编写映射器UserMapper.java

package cn.itcast.mp.mapper;
public interface UserMapper extends BaseMapper<User> {}

8. 编写启动类MyApplication.java

package cn.itcast.mp;

@MapperScan("cn.itcast.mp.mapper") //设置mapper接口的扫描包
@SpringBootApplication
public class MyApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class, args);
    }
}

这样一个SpringBoot工程基本搭建好了，只差实现具体的持久层方法，下面使用MyBatis-Plus来实现CRUD操作。

三、MyBatis-Plus通用CRUD

首先，要使用MP需先让映射器继承BaseMapper.java类，活得其通用方法；并且，要在实体类头部指定对应的数据表名称。代码如下：

public interface UserMapper extends BaseMapper<User> {}

@TableName("tb_user")
public class User {...}

BaseMaper的单表操作如下图：

1. 插入操作

package cn.itcast.mp;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTests {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert() {
        User user = new User();
        user.setAge(20);
        user.setMail("test@itcast.cn");
        user.setName("曹操");
        user.setUserName("caocao");
        user.setPassword("123456");
        int result = userMapper.insert(user);
        System.out.println(result);
        System.out.println(user.getId());
    }
}

此时，插入的数据id是MP生成的，并未按照自增原则。可以在实体类的id属性上面加上注解@TableId(type=IdType.AUTO)使其自增。

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

为了解决实体类的属性与数据表的字段存在名称不一致的问题，可以采用@TableField(value="*")

    @TableField(value = "email")
    private String mail;

若实体类中某个属性在数据表中并无字段，可以使用@TableField(exist = false)标记。

    @TableField(exist = false)
    private String address;

若有些大字段（如text、longtext）不想被查询，则可以使用@TableField(select = false)

    @TableField(select = false)
    private String password;

2. 更新操作

• 根据id更新

    @Test
    public void testUpdateById() {
        User user = new User();
        user.setId(6L);
        user.setAge(25);
        userMapper.updateById(user);
    }

• 根据条件更新

    @Test
    public void testUpdate() {
        User user = new User();
        user.setAge(22);
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("user_name", "caocao");
        userMapper.update(user, wrapper);
    }

3. 删除操作

• 根据id删除

    @Test
    public void testDeleteById() {
        userMapper.deleteById(6L);
    }

• 根据条件删除

    @Test
    public void testDeleteByMap() {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("age", 20);
        columnMap.put("user_name", "caocao");
        userMapper.deleteByMap(columnMap);
    }

• 根据条件构造器删除

    @Test
    public void testDelete() {
        User user = new User();
        user.setAge(20);
        user.setUserName("caocao");
        QueryWrapper<User> wrapper = new QueryWrapper<>(user);
        userMapper.delete(wrapper);
    }

• 根据id批量删除

    @Test
    public void testDeleteBatchIds() {
        userMapper.deleteBatchIds(Arrays.asList(1, 2, 5));
    }

4. 查询操作

• 根据id查询

    @Test
    public void testSelectById() {
        User user = userMapper.selectById(3L);
        System.out.println(user);
    }

• 根据id批量查询

    @Test
    public void testSelectBatchIds() {
        List<User> users = userMapper.selectBatchIds(Arrays.asList(3, 4));
        for (User user : users) {
            System.out.println(user);
        }
    }

• 查询单条数据

    @Test
    public void testSelectOne() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.eq("name", "王五");
        User user = userMapper.selectOne(wrapper);
        System.out.println(user);
    }

• 查询记录数量

    @Test
    public void testSelectCount() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.gt("age", 24);
        Integer count = userMapper.selectCount(wrapper);
        System.out.println(count);
    }

• 查询全部

    @Test
    public void testSelect() {
        List<User> users = userMapper.selectList(null);
        for (User user : users) {
            System.out.println(user);
        }
    }

• 分页查询
  首先要配置分页插件：

package cn.itcast.mp.config;

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

@Configuration
@MapperScan("cn.itcast.mp.mapper")  //设置mapper接口的扫描包
public class MybatisPlusConfig {
    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

获得IPage对象，得到分页查询结果：

    @Test
    public void testSelectPage() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.gt("age", 20);
        Page<User> page = new Page<>(1, 1);
        IPage<User> iPage = userMapper.selectPage(page, wrapper);
        System.out.println("数据总条数：" + iPage.getTotal());
        System.out.println("总页数：" + iPage.getTotal());
        List<User> users = iPage.getRecords();
        for (User user : users) {
            System.out.println(user);
        }
    }

四、配置

1. 基本配置

• configLocation
  如果有单独的MyBatis配置，应将其路径配置到configLocation参数中。

mybatis-plus.config-location = classpath:mybatis-config.xml

• mapperLocations
  如果在Mapper中有自定义方法，需要进行该配置以告诉Mapper对应的XML文件位置。

mybatis-plus.mapper-locations = classpath*:mybatis/*.xml

• typeAliasesPackage
  MyBatis别名包扫描路径，通过该属性可以给包中的类注册别名，注册后在Mapper对应的XML文件可以直接使用类名，而不用写全限定的类名。

mybatis-plus.type-aliases-package = cn.itcast.mp.pojo

2. 进阶配置

• mapUnderscoreToCamelCase
  是否开启自动驼峰命名规则映射，即自动将数据库字段a_bc与Java属性aBc映射起来，默认为true。

mybatis-plus-configuration.map-underscore-to-camel-case = false

• cacheEnabled
  全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存，默认为true。

mybatis-plus.configuration.cache-enabled = false

3. DB策略配置

• idType
  全局默认主键类型，配置后，即可省略实体对象中的@TableId(type=IdType.AUTO)配置

mybatis-plus.global-config.db-config.id-type = auto

• tablePrefix
  表名前缀，全局配置后可省略@TableName()配置

mybatis-plus.global-config.table-prefix = tb_

五、条件构造器

1. allEq

allEq(Map<R, V> params)
allEq(Map<R, v> params, boolean null2IsNull)

params为筛选条件，null2IsNull表示是否要忽略匹配值为null的参数

QueryWrapper<User> wrapper = new QueryWrapper<>();
//设置条件
Map<String,Object> params = new HashMap<>();
params.put("name", "曹操");
params.put("age", "20");
params.put("password", null);
// wrapper.allEq(params); -> SELECT * FROM tb_user WHERE password IS NULL AND name = ? AND age = ?
// wrapper.allEq(params,false); -> SELECT * FROM tb_user WHERE name = ? AND age = ?
List<User> users = this.userMapper.selectList(wrapper);

2. 基本比较操作

• eq：等于=
• ne：不等于<>
• gt：大于>
• ge：大于等于>=
• lt：小于<
• le：小于等于<=
• between：BETWEEN 值1 AND 值2
• notBetween：NOT BETWEEN 值1 AND 值2
• in：字段 IN (v0, v1, ...)
• notIn：字段 NOT IN (v0, v1, ...)

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT id,user_name,password,name,age,email FROM tb_user WHERE password = ? AND age >= ? AND name IN (?,?,?)
wrapper.eq("password", "123456")
       .ge("age", 20)
       .in("name", "李四", "王五", "赵六");
List<User> users = this.userMapper.selectList(wrapper);

3. 模糊查询

• like：LIKE '%值%'
  例：like("name", "王") ---> name like '%王%
• notLike：NOT LIKE '%值%'
  例：notLike("name", "王") ---> name not like '%王%'
• likeLeft：LIKE '%值'
  例：likeLeft("name", "王") ---> name like '%王'
• likeRight：LIKE '值%'
  例：likeRight("name", "王") ---> name like '王%'
  注意：模糊查询方法设置值的时候无需带%

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ?
//Parameters: %曹%(String)
wrapper.like("name", "曹");
List<User> users = this.userMapper.selectList(wrapper);

4. 排序

• orderBy：ORDER BY 字段, ...
  例：orderBy(true, true, "id", "name") ---> order by id ASC,name ASC
• orderByAsc：ORDER BY 字段, ... ASC
  例：orderByAsc("id", "name") ---> order by id ASC,name ASC
• orderByDesc：ORDER BY 字段, ... DESC
  例：orderByDesc("id", "name") ---> order by id DESC,name DESC

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age DESC
wrapper.orderByDesc("age");
List<User> users = this.userMapper.selectList(wrapper);

5. 逻辑查询

• or：拼接OR
• and：AND嵌套

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? OR age = ?
wrapper.eq("name","李四").or().eq("age", 24);
List<User> users = this.userMapper.selectList(wrapper);

6. select
   在MP中默认查询所有字段，如果有需要也可以通过select方法指定字段。

QueryWrapper<User> wrapper = new QueryWrapper<>();
//SELECT id,name,age FROM tb_user WHERE name = ? OR age = ?
wrapper.eq("name", "李四")
       .or()
       .eq("age", 24)
       .select("id", "name", "age");
List<User> users = this.userMapper.selectList(wrapper);