MyBatisPlus的使用

1、我们为什么使用MyBatisPlus（MP）？

1.1、它是MyBatis的增强的工具，简称MP

1.2、它的特征

1）支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错

2）支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer2005、SQLServer 等多种数据库
3）支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
4）支持 XML 热加载：Mapper 对应的 XML 支持热加载，对于简单的 CRUD 操作，甚至可以无 XML 启动
5）内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
6）内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询

2、最基本的使用

1）创建一个数据库
2）创建实体类
3）创建mapper接口实现BaseMapper<User类
4）写测试类

3、如果想进行复杂条件查询，那么需要使用条件构造器 Wapper

3.1wapper介绍

3.2一些SQL语句的例子

ge、gt、le、lt、isNull、isNotNull

SQL：delete from user WHERE AND name IS NULL AND age >= ? AND email IS NOT NULL

@Test
public void testDelete() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .isNull("name")
        .ge("age", 12)
        .isNotNull("email");
    int result = userMapper.delete(queryWrapper);
    System.out.println("delete return count = " + result);
}

eq、ne

SQL：select id,name,age,email,create_time,update_time,deleted,version FROM user WHERE name = ?

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

between、notbetween

SQL：SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?

@Test
public void testSelectCount() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.between("age", 20, 30);
    Integer count = userMapper.selectCount(queryWrapper);
    System.out.println(count);
}

allEq

SQL：SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE name = ? AND id = ? AND age = ?

@Test
public void testSelectList() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    Map<String, Object> map = new HashMap<>();
    map.put("id", 2);
    map.put("name", "Jack");
    map.put("age", 20);
    queryWrapper.allEq(map);
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

like、notLike、likeLeft、likeRight

SQL：SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE name NOT LIKE ? AND email LIKE ?

@Test
public void testSelectMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
        .notLike("name", "e")
        .likeRight("email", "t");
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
    maps.forEach(System.out::println);
}

in、notIn、inSql、notinSql、exists、notExists

SQL：SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)

@Test
public void testSelectObjs() {
  QueryWrapper<User> queryWrapper = new QueryWrapper<>();
  //queryWrapper.in("id", 1, 2, 3);
  queryWrapper.inSql("id", "select id from user where id < 3");
  List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
  objects.forEach(System.out::println);
}

嵌套or、嵌套and

SQL：UPDATE user SET name=?, age=?, update_time=?
WHERE deleted=0 AND name LIKE ?
OR ( name = ? AND age <> ? )

@Test
public void testUpdate2() {
    //修改值
    User user = new User();
    user.setAge(99);
    user.setName("Andy");
    //修改条件
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper
        .like("name", "h")
        .or(i -> i.eq("name", "李白").ne("age", 20));
    int result = userMapper.update(user, userUpdateWrapper);
    System.out.println(result);
}

orderBy、orderByDesc、orderByAsc

SQL：SELECT id,name,age,email,create_time,update_time,deleted,version
FROM user WHERE deleted=0 ORDER BY id DESC

@Test
public void testSelectListOrderBy() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByDesc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}