MybatisPlus详解

北漂IT民工_程序员_ZG

已于 2024-12-06 22:35:23 修改

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏：研究自创文章标签： spring java mybatis

于 2022-03-19 21:54:47 首次发布

本文链接：https://blog.csdn.net/weixin_41919486/article/details/123602799

研究同时被 2 个专栏收录

42 篇文章

订阅专栏

自创

24 篇文章

订阅专栏

MybatisPlus详解

SpringBoot+MP基础用法
SpringBoot+MP进阶用法

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

SpringBoot+MP基础用法

SpringBoot+MP工程搭建

一、yml内容

<?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>

    <groupId>cn.yx.zg</groupId>
    <artifactId>SpringBoot-MybatisPlus</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</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.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- mybatis plus 代码生成器 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.28</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

二、建表sql语句

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`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;


INSERT INTO `tb_user` VALUES (1, 'zhangsan2', '123456', '张三', 20, 'test@itcast.cn');
INSERT INTO `tb_user` VALUES (2, 'lisi', '123456', '李四', 20, 'test2@itcast.cn');
INSERT INTO `tb_user` VALUES (3, 'wangwu', '123456', '王五', 28, 'test3@itcast.cn');
INSERT INTO `tb_user` VALUES (4, 'zhaoliu', '123456', '赵六', 21, 'test4@itcast.cn');
INSERT INTO `tb_user` VALUES (5, 'sunqi', '123456', '孙七', 24, 'test5@itcast.cn');

三、User实体

@Data
@NoArgsConstructor
@AllArgsConstructor
/**
 * 指定该实体对应的表名,如果不使用@TableName注解,
 * 表名称默认是类名,首字母小写
 */
@TableName("tb_user")
public class User {
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

四、Mapper接口

public interface UserMapper extends BaseMapper<User> {
}

五、SpringBoot主启动类MybatisPlusApplication

@MapperScan("cn.yx.zg.mapper")
@SpringBootApplication
public class MybatisPlusApplication {
    public static void main(String[] args) {
        // 启动spring 应用
        SpringApplication.run(MybatisPlusApplication.class, args);
    }
}

通过以上代码，咱们就搭建了一个SpringBoot+MP的环境。

六、测试类

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

    /**
     * 查询
     */
    @Test
    public void testSelect() {
        List<User> userList = userMapper.selectList(null);
        for (User user : userList) {
            log.info(user.toString());
        }
    }


    /**
     * 新增
     */
    @Test
    public void testInsert() {
        User user = new User();
        user.setAge(20);
        user.setEmail("test@itcast.cn");
        user.setName("曹操");
        user.setUserName("caocao");
        user.setPassword("123456");
        int result = userMapper.insert(user);
        //返回的result是受影响的行数，并不是自增 后的id
        log.info("result = " + result);
        log.info("插入后的自增ID自动回填到对象中:{}", user.getId());
    }

    @Test
    public void testUpdate() {
        User user = new User();
        user.setAge(20);
        user.setEmail("test@itcast.cn");
        user.setName("张三");
        user.setUserName("zhangsan2");
        user.setPassword("123456");
        user.setId(1L);
        int result = userMapper.updateById(user);
        //返回的result是受影响的行数，并不是自增 后的id
        log.info("result = " + result);
    }
}

MP中的一些注解、属性

主键策略

枚举IdType 中定义了主键支持的策略。

public enum IdType {
	//默认就是数据库自增，开发者无需赋值。
    AUTO(0),
    //MP set 主键，雪花算法实现。 也是MP默认的方式
    NONE(1),
    //需要开发者手动赋值
    INPUT(2),
    //全局唯一ID (idWorker)
    ID_WORKER(3),
    //主键的数据类型必须是 String，自动生成 UUID 进行赋值
    UUID(4),
    //字符串全局唯一ID (idWorker 的字符串表示)
    ID_WORKER_STR(5);
    private final int key;
    private IdType(int key) {
        this.key = key;
    }
    public int getKey() {
        return this.key;
    }
}

修改主键ID是自增策略。插入数据后返回自增ID。

@Data
@NoArgsConstructor
@AllArgsConstructor
/**
 * 指定该实体对应的表名,如果不使用@TableName注解,
 * 表名称默认是类名,首字母小写
 */
@TableName("tb_user")
public class User {
    //修改主键ID是自增策略
    @TableId(type = IdType.AUTO)
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String email;
}

@TableField属性

在MP中通过@TableField注解可以指定字段的一些属性，常常解决的问题有2个：

1、对象中的属性名和数据库字段名不一致的问题（非驼峰）。
2、对象中的属性字段在数据库字段中不存在。

@Data
@NoArgsConstructor
@AllArgsConstructor
/**
 * 指定该实体对应的表名,如果不使用@TableName注解,
 * 表名称默认是类名,首字母小写
 */
@TableName("tb_user")
public class User {
    //修改主键ID是自增策略
    @TableId(type = IdType.AUTO)
    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    //数据库中是email,实体中是mail时,用该字段映射
    @TableField(value = "email")
    private String mail;
    /**
     * 该字段在数据库中不存在,如果不使用注解,则查询会报错
     * Cause: java.sql.SQLSyntaxErrorException: Unknown column 'address' in 'field list'
     * 如果使用了注解,查询正常
     */
    @TableField(exist = false)
    private String address;
}

MP中增删改查的一些写法

    /**
     * QueryWrapper更新
     */
    @Test
    public void testUpdate2() {
        //User存放更新的字段,QueryWrapper存放更新的条件
        User user = new User();
        user.setAge(99);
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("id", 5);
        wrapper.eq("name", "孙七");
        userMapper.update(user, wrapper);
    }

    /**
     * UpdateWrapper更新
     */
    @Test
    public void testUpdate3() {
        //更新的条件以及字段
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", 5);
        wrapper.eq("name", "孙七");
        wrapper.set("age", 44);
        userMapper.update(null, wrapper);
    }

    /**
     * 删除1
     */
    @Test
    public void testDeleteByMap() {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("age", 20);
        columnMap.put("name", "张三");
        //将columnMap中的元素设置为删除的条件，多个之间为and关系
        int result = this.userMapper.deleteByMap(columnMap);
        System.out.println("result = " + result);
    }

    /**
     * 删除2
     */
    @Test
    public void testDeleteByMap2() {
        User user = new User();
        user.setAge(20);
        user.setName("张三");
        //将实体对象进行包装，包装为操作条件
        QueryWrapper<User> wrapper = new QueryWrapper<>(user);
        int result = this.userMapper.delete(wrapper);
        System.out.println("result = " + result);
    }

    /**
     * 根据ID批量删除
     */
    @Test
    public void deleteBatchIds() {
        //根据id集合批量删除
        int result = this.userMapper.deleteBatchIds(Arrays.asList(1L, 10L, 20L));
        System.out.println("result = " + result);
    }

    /**
     * 批量查询
     */
    @Test
    public void selectBatchIds() {
        //根据id集合批量查询
        List<User> users = this.userMapper.selectBatchIds(Arrays.asList(2L, 3L, 10L));
        for (User user : users) {
            System.out.println(user);
        }
    }

    /**
     * selectOne
     * 根据 entity 条件，查询一条记录
     */
    @Test
    public void TestselectOne() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.eq("name", "李四");
        //根据条件查询一条数据，如果结果超过一条会报错
        User user = this.userMapper.selectOne(wrapper);
        System.out.println(user);
    }

    /**
     * selectCount
     * 根据 Wrapper 条件，查询总记录数
     */
    @Test
    public void TestselectCount() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.gt("age", 23); //年龄大于23岁
        // 根据条件查询数据条数
        Integer count = this.userMapper.selectCount(wrapper);
        System.out.println(count);
    }

    /**
     * selectList
     * 根据 Wrapper 条件，查询list
     */
    @Test
    public void TestselectList() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.gt("age", 23); //年龄大于23岁
        // 根据条件查询数据
        List<User> users = this.userMapper.selectList(wrapper);
        for (User user : users) {
            System.out.println("user = " + user);
        }
    }

MP中的分页

新建分页配置类

@Configuration
public class MybatisPlusConfig {
  /**
   * 分页插件
   */
  @Bean
  public PaginationInterceptor paginationInterceptor() {
      return new PaginationInterceptor();
  }
}

分页测试方法

    /**
     * MP分页
     */
    @Test
    public void testSelectPage() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        wrapper.gt("age", 20);
        //年龄大于20岁
        Page<User> page = new Page<>(1, 1);
        //根据条件查询数据
        IPage<User> iPage = this.userMapper.selectPage(page, wrapper);
        System.out.println("数据总条数：" + iPage.getTotal());
        System.out.println("总页数：" + iPage.getPages());
        List<User> users = iPage.getRecords();
        for (User user : users) {
            System.out.println("user = " + user);
        }
    }

MP在application.yml的一些配置

server:
  port: 80
spring:
  #数据库连接配置
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8&useSSL=false
    username: root
    password: root
mybatis-plus:
  #mapper配置文件
  mapper-locations: classpath:mapper/*.xml
  #类别名
  type-aliases-package: cn.yx.zg.pojo
  #开启驼峰命名
  configuration:
    #开启数据库驼峰命名,比如数据库字段user_name,实体类userName可以自动映射上
    map-underscore-to-camel-case: true
    # 延迟加载
    lazy-loading-enabled: true
    #false 为按需加载
    aggressive-lazy-loading: false
  global-config:
    db-config:
      #统一设置主键自增策略
      id-type: auto
      #统一设置表名前缀,可省略TableName()配置
      table-prefix: tb_

SpringBoot+MP进阶用法

allEq用法

代码案例1

 /**
     * allEq(Map<R, V> params) ；
     * allEq(Map<R, V> params, boolean null2IsNull) ；
     * allEq(boolean condition, Map<R, V> params, boolean null2IsNull)；
     *
     * 参数说明:
     * params:key为数据库字段名, value 为字段值.
     * null2IsNull:为true时,params的值为空时，则调用is null; 为false时，params的值为空时则忽略,默认true.
     * condition:为true时，params过虑条件生效，为false时，过滤条件不生效,默认是true.
     */

    @Test
    public void testWrapper1() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        Map<String, Object> params = new HashMap<>();
        params.put("name", "曹操");
        params.put("age", "20");
        params.put("password", null);

        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (password IS NULL AND name = ? AND age = ?)
//        wrapper.allEq(params);
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name = ? AND age = ?)
//        wrapper.allEq(params,false);
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name = ? AND age = ?)
        wrapper.allEq(false,params,true);
        List<User> users = this.userMapper.selectList(wrapper);
        users.stream().forEach(System.out::println);
    }

代码案例2

    /**
     * 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)
     * 参数说明
     * filter: 过滤函数,是否允许字段传入比对条件中
     * condition,params与null2IsNull同上个案例一样
     * */
    @Test
    public void testWrapper2() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        Map<String, Object> params = new HashMap<>();
        params.put("name", "曹操");
        params.put("age", "20");
        params.put("password", null);
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name = ? AND age = ?)
        wrapper.allEq((k, v) -> (k.equals("name") || k.equals("age")) ,params);
        List<User> users = this.userMapper.selectList(wrapper);
        users.stream().forEach(System.out::println);
    }

基本比较操作

    /**
     * eq:等于 =
     * ne:不等于 <>
     * gt:大于 >
     * ge:大于等于 >=
     * lt:小于 <
     * le:小于等于 <=
     * between:BETWEEN 值1 AND 值2
     * notBetween:NOT BETWEEN 值1 AND 值2
     * in:字段 IN (value.get(0), value.get(1), ...)
     * notIn:字段 NOT IN (v0, v1, ...)
     */
    @Test
    public void testWrapper3() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        //SELECT id,password,email AS mail,name,user_name,age 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);
        users.stream().forEach(System.out::println);
    }

模糊查询

  /**
     * like:LIKE '%值%'
     * notLike:NOT LIKE '%值%'
     * likeLeft:LIKE '%值'
     * likeRight:LIKE '值%'
     */
    @Test
    public void testWrapper4() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name LIKE '%曹%')
        wrapper.like("name", "曹");
        List<User> users = this.userMapper.selectList(wrapper);
        users.stream().forEach(System.out::println);
    }

排序

    /**
     * orderBy:ORDER BY 字段, ...
     * orderByAsc:ORDER BY 字段, ... ASC
     * orderByDesc:ORDER BY 字段, ... DESC
     */
    @Test
    public void testWrapper5() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user ORDER BY age DESC 
        wrapper.orderByDesc("age");
        List<User> users = this.userMapper.selectList(wrapper);
        users.stream().forEach(System.out::println);
    }

逻辑查询

    /**
     * or:主动调用or表示紧接着下一个方法不是用 and 连接 (不调用 or 则默认为使用 and 连接)
     * and:
     */
    @Test
    public void testWrapper6() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name = ? OR age = ?)
//        wrapper.eq("name", "李四").or().eq("age", 24);
        //SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE ( (name = ?) ) OR ( (age = ?) ) 
        wrapper.and(i->i.eq("name","李四")).or(i->i.eq("age",24));
        List<User> users = this.userMapper.selectList(wrapper);
        users.stream().forEach(System.out::println);
    }

select 指定字段查询

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

    @Test
    public void testWrapper7() {
        QueryWrapper<User> wrapper = new QueryWrapper<User>();
        //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);
        users.stream().forEach(System.out::println);
    }

骚气的Lambad写法(推荐)

获得LambdaQueryWrapper的三种方法

    @Test
    public void testWrapperLambad1() {
        //创建lambda 条件构造器 的三种方法
        LambdaQueryWrapper<User> lambda1 = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<User> lambda2 = new QueryWrapper<User>().lambda();
        //SQL: SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name LIKE ? AND age < ?)
        LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();
            lambda3.like(User::getName, "雨")
                .lt(User::getAge, 40);
        List<User> users = this.userMapper.selectList(lambda3);
        users.stream().forEach(System.out::println);
    }

LambdaQueryChainWrapper<实体>(xxxxMapper)

	//MP3.0.7 新增的方式
    @Test
    public void testWrapperLambad2() {
        //SQL:SELECT id,password,email AS mail,name,user_name,age FROM tb_user WHERE (name LIKE ? AND age >= ?) 
        List<User> users = new LambdaQueryChainWrapper<User>(userMapper)
                .like(User::getName, "雨").ge(User::getAge, 20).list();
        users.stream().forEach(System.out::println);
    }