MyBatisPlus 入门到上手 (一)

一：简介

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

官网传送门====>https://mp.baomidou.com
个人git传送门====>https://gitee.com/remember0324/mybatisPlus

二：ORM框架

ORM（Object Relational Mapping）框架采用元数据来描述对象与关系映射的细节，元数据一般采用XML格式，并且存放在专门的对象一映射文件中。只要提供了持久化类与表的映射关系，ORM框架在运行时就能参照映射文件的信息，把对象持久化到数据库中。

三：MP与JPA对比

在使用的角度来说少写一句sql就少写一句，在单表操做过程中两者都基本都是能满足这个特点。关于学习难度来说其实两者差不多，没有另外拔高知识点，只是在底层原理不同。

四：构建项目

前提准备

创建数据库：mp 数据表：tb_user

创建表

CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `password` varchar(20) DEFAULT NULL COMMENT '密码',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `version` int(10) DEFAULT '1',
  `deleted` int(1) DEFAULT '0' COMMENT '1代表删除，0代表未删除',
  `sex` int(1) DEFAULT '1' COMMENT '1-男，2-女',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我使用的是IDEA工具
Lombok
springBoot:2.2.4
mybatisPlus:3.1.1
mysql:8.1.19

pom文件

<dependencies>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.19</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!--代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

yml文件

spring:
  application:
    name: mybatisPlusSpringBoot
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/mp?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf8
    username: root
    password: root
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    #关闭自动驼峰映射，该参数不能和mybatis-plus: config-location同时存在:
    # map-underscore-to-camel-case: false
    #全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存，默认为 true。
    #cache-enabled: false
  #配置自定义的mapper文件 (多表查询适用)
  mapper-locations: classpath*:mybatis/*.xml
  #配置实体对象扫描包===在mapper.xml中简化使用
  type-aliases-package: com.hhz.mp.pojo
  #配置全局主键生成策略
  #  global-config:
  #    db-config:
  #      id-type: auto
  #配置全局表名前缀
  # global-config:
  # db-config:
  #  table-prefix: tb_
  #乐观锁配置
  global-config:
    db-config:
      # 逻辑已删除值(默认为 1)
      logic-delete-value: 1
      # 逻辑未删除值(默认为 0)
      logic-not-delete-value: 0
  # 枚举包扫描
  type-enums-package: com.hhz.mp.enums

创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = true)
@TableName("tb_user")
public class User extends Model<User> {
    //设置按照数据库自增长
    @TableId(type = IdType.AUTO)
    private Long id;
    //下划线可自动转驼峰命名 这里可以不写
    @TableField(value = "user_name")
    private String userName;
    //查询时不返回该字段的值
    //fill =FieldFill.INSERT  对插入密码的时候可以进行填充
    @TableField(select = false, fill = FieldFill.INSERT)
    private String password;
    private String name;
    private Integer age;
    //字段名与数据库名不一致
    @TableField(value = "email")
    private String mail;
    //忽略在数据库的字段
    @TableField(exist = false)
    private String address;
    //添加版本信息__乐观锁
    @Version
    private Integer version;
    //逻辑删除
    @TableLogic
    private Integer deleted;
    //配置枚举值
    private SexEnum sex;
    public User(String userName, Integer age) {
        this.userName = userName;
        this.age = age;
    }
    public User(String userName, String password, String name, Integer age, String mail) {
        this.userName = userName;
        this.password = password;
        this.name = name;
        this.age = age;
        this.mail = mail;
    }
    public User(Long id) {
        this.id = id;
    }
    public User(Long id, String userName, String password, String name, Integer age, String mail) {
        this.id = id;
        this.userName = userName;
        this.password = password;
        this.name = name;
        this.age = age;
        this.mail = mail;
    }
}

创建dao层

@Repository
public interface UserMapper extends BaseMapper<User> {
}

创建一个测试类

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class MyApplicationTest {
    @Autowired
    private UserMapper userMapper;
}

五：正式开始

5.1 基础增删改

如果id未设置自增长默认是推特的 雪花算法

mysql设置主键类型

@TableId(type = IdType.AUTO)

oracle设置主键类型

yml文件中

mybatis-plus:
  global-config:
    #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    id-type: 1
    # Sequence序列接口实现类配置
    key-generator: com.baomidou.mybatisplus.incrementer.OracleKeyGenerator

实体类上设置

@KeySequence(value = "序列名称",clazz = 主键类型.class)

主键上设置

@TableId(type=IdType.INPUT)

新增

    @Test
    public void testInsert() {
        User user = new User("guanyu", "222", "关羽", 42, "guanyu@shu.com");
        int insert = userMapper.insert(user);
        Long id = user.getId();
        //改变的记录数
        System.err.println("change:" + insert);
        //id未设置增长策略结果：1233004593171144706
        System.err.println("id:" + id); 
    }

根据id新增

    @Test
    public void testupdateById() {
        User user = new User(9L, "zhuge", "111", "诸葛", 36, "zhuge@shu.com");
        int update = userMapper.updateById(user);
        //改变的行数
        System.err.println(update);
    }

根据id删除

    @Test
    public void testdeleteById() {
        int result = userMapper.deleteById(9L);
        System.out.println(result);
    }

构造map条件删除

    @Test
    public void testdeleteByMap() {
        //DELETE FROM tb_user WHERE name = ?
        HashMap<String, Object> map = new HashMap<>();
        map.put("name", "关羽");
        int result = userMapper.deleteByMap(map);
        System.err.println(result);
    }

构造wrapper条件删除

    @Test
    public void testdelete() {
        //第一种办法
        //DELETE FROM tb_user WHERE (password = ? AND age <= ?)
        QueryWrapper<User> wrapper1 = new QueryWrapper<>();
        wrapper1.eq("password", "123").le("age", 21);
        //第二种办法:直接面向对象   但是大于小于不好使用   单个删除建议使用方法二
        //DELETE FROM tb_user WHERE password=? AND age=?
        User user = new User();
        user.setPassword("123");
        user.setAge(21);
        QueryWrapper<User> wrapper2 = new QueryWrapper<>(user);
        int result = userMapper.delete(wrapper2);
        System.err.println(result);
    }

批量删除

    @Test
    public void testdeleteBatchIds() {
        //根据id批量删除
        //DELETE FROM tb_user WHERE id IN ( ? , ? )
        List<Long> longs = Arrays.asList(6L, 7L);
        userMapper.deleteBatchIds(longs);
    }

根据id更新

    @Test
    public void testupdateById() {
        User user = new User(9L, "zhuge", "111", "诸葛", 36, "zhuge@shu.com");
        int update = userMapper.updateById(user);
        //改变的行数
        System.err.println(update);
    }

根据条件更新====QueryWrapper

    @Test
    public void testupdate() {
        User user = new User();
        //设置参数
        user.setAge(29);
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //构建where条件user_name为zhuge的
        wrapper.eq("user_name", "zhuge");
        // UPDATE tb_user  SET age=? WHERE user_name = ?
        int update = userMapper.update(user, wrapper);
        //改变的行数
        System.err.println(update);
    }

根据条件更新2====UpdateWrapper

    @Test
    public void testupdate2() {
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        //构建where条件 user_name为zhuge并且 设置修改的参数
        wrapper.set("password", "abc456").eq("user_name", "zhuge");
        //UPDATE tb_user SET password = ? WHERE AND user_name = ?
        int update = userMapper.update(null, wrapper);
        System.err.println(update);
    }

5.2 mybatisPlus的查询

根据id查询

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

查询所有

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

根据多个id批量查询

    @Test
    public void testSelectOne() {
        List<Long> longs = Arrays.asList(4L, 5L);
        List<User> users = userMapper.selectBatchIds(longs);
        users.forEach(System.out::println);
    }

根据条件查询一个

    @Test
    public void testSelectBatchIds() {
        //没有数据返回null  多条数据报错
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("name", "张三");
        User user = userMapper.selectOne(wrapper);
        System.err.println(user);
    }

查询记录数

    @Test
    public void testSelectCount() {
        //所有记录数  wrapper 设为空即可
        //Integer integer = userMapper.selectCount(null);
        //查询年龄大于25岁的有多少人
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 25);
        Integer integer = userMapper.selectCount(wrapper);
        System.err.println(integer);
    }

分页查询

前题注入分页插件

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        //注入sql分析插件（生产环境最好不用） 阻止全表更新
        List<ISqlParser> sqlParserList = new ArrayList<>();
        sqlParserList.add(new BlockAttackSqlParser());
        paginationInterceptor.setSqlParserList(sqlParserList);
        return paginationInterceptor;
    }

分页查询

    @Test
    public void testSelectPage() {
        //SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (age > ?) ORDER BY age ASC LIMIT ?,?
        //查询年龄大于20岁的第二页的数据
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 20).orderByAsc("age");
        //查询第二页数据  每页三条   current：0和1都是只第一页
        Page<User> page = new Page<>(2L, 3L);
        IPage<User> userIPage = userMapper.selectPage(page, wrapper);
        System.err.println("总记录:" + userIPage.getRecords().toString());
        System.err.println("总页数:" + userIPage.getPages());
        System.err.println("总条数:" + userIPage.getTotal());
        System.err.println("当前页:" + userIPage.getCurrent());
        System.err.println("页大小:" + userIPage.getSize());
    }

总记录:[User(id=3, userName=wangwu, password=null, name=王五, age=28, mail=test3@itcast.cn, address=null, version=1, deleted=0, sex=男), User(id=11, userName=sunce, password=null, name=孙策, age=28, mail=null, address=null, version=1, deleted=0, sex=男), User(id=20, userName=null, password=null, name=小乔, age=28, mail=null, address=null, version=1, deleted=0, sex=女)]
总页数:4
总条数:12
当前页:2
页大小:3

分页查询 只要记录 不需要记录数

    @Test
    public void testSelectPage2() {
        //查询年龄大于20岁的第二页的数据
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 20).orderByAsc("age");
        //查询第二页数据  每页三条  不查询总记录数
        //Page(long current, long size, boolean isSearchCount)
        Page<User> page = new Page<>(2L, 3L, false);
        IPage<User> userIPage = userMapper.selectPage(page, wrapper);
        System.err.println("总记录:" + userIPage.getRecords().toString());
        System.err.println("总页数:" + userIPage.getPages());
        System.err.println("总条数:" + userIPage.getTotal());
        System.err.println("当前页:" + userIPage.getCurrent());
        System.err.println("页大小:" + userIPage.getSize());
    }

总记录:[User(id=3, userName=wangwu, password=null, name=王五, age=28, mail=test3@itcast.cn, address=null, version=1, deleted=0, sex=男), User(id=11, userName=sunce, password=null, name=孙策, age=28, mail=null, address=null, version=1, deleted=0, sex=男), User(id=20, userName=null, password=null, name=小乔, age=28, mail=null, address=null, version=1, deleted=0, sex=女)]
总页数:0
总条数:0
当前页:2
页大小:3

自定义mapper.xml的使用

在yml文件中添加

  #配置自定义的mapper文件 (多表查询适用)
  mapper-locations: classpath*:mybatis/*.xml
  #配置实体对象扫描包===>在mapper.xml中简化使用
  type-aliases-package: com.hhz.mp.pojo

在resources中建，mybatis文件夹再在下面建立userMapper.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.hhz.mp.mapper.UserMapper">
    <!--com.hhz.mp.pojo.User-->
    <select id="getById" resultType="User">
        select * from tb_user where id = #{id}
    </select>
    <select id="selectPageUser" resultType="User">
        select * from tb_user ${ew.customSqlSegment}
    </select>
</mapper>

//在mapper.xml中写SQL
User getById(Long id);

//自定义sql
@Select("select * from tb_user ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
//自定义分页  在mapper.xml中写SQL
IPage<User> selectPageUser(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> wrapper);

根据id查询

    @Test
    public void testSelectMapper() {
        User user = userMapper.getById(2L);
        System.out.println(user);
    }

自定义sql的使用

    @Test
    public void selectMy() {
        //SELECT * FROM tb_user WHERE age > ?
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 30);
        List<User> userList = userMapper.selectAll(wrapper);
        for (User user : userList) {
            System.out.println(user);
        }
    }

自定义分页

    @Test
    public void selectPageUser() {
        Page<User> page = new Page<>(1, 3);
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.gt("age", 25);
        IPage<User> userIPage = userMapper.selectPageUser(page, wrapper);
        System.err.println("总记录:" + userIPage.getRecords().toString());
        System.err.println("总条数:" + userIPage.getTotal());
    }

5.3 wrapper 条件构造器

allEq(Map params) 根据map给定字段的条件查询

    @Test
    public void testllEq() {
        //SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (password IS NULL AND user_name = ? AND age = ?)
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);
        wrapper.allEq(map);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

allEq(Map params, boolean null2IsNull) 根据map给定字段的条件查询 过滤字段为空的数据

    @Test
    public void testAllEq2() {
        //SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (user_name = ? AND age = ?)
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);
        wrapper.allEq(map, false);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

    @Test
    public void testAllEq3() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);
        wrapper.allEq((k, v) -> k.equals("age") || k.equals("name") || k.equals("password"), map, false);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

eq 等于
ge 大于等于
in 在范围内
gt 大于
lt 小于
le 小于等于
between 两个之间
isNotNULL 非空

    @Test
    public void testEq() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //SELECT id, 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.forEach(System.err::println);
    }

模糊查询like

/**
     * 模糊查询
     * like(R column, Object val)                           LIKE '%值%'
     * like(boolean condition, R column, Object val)
     * likeLeft(boolean condition, R column, Object val)    LIKE '%值'
     * likeRight(boolean condition, R column, Object val)   LIKE '值%'
     */
    @Test
    public void testLike() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // wrapper.like("name", "孙");
        User user = new User();
        user.setName("孙");
        //condition  条件判断  如果为false 该条就会被忽略
        wrapper.like(StringUtils.isNotBlank(user.getName()), "name", "孙");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

排序

/**
     * orderByAsc   默认顺序
     * orderByDesc  倒序
     */
    @Test
    public void testOrderBy() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.orderByDesc("age", "user_name");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

or 逻辑关联

/**
     * Or  逻辑关联
     * 查找姓名为孙权或者年龄为20的人
     * SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (name = ? OR age = ?)
     */
    @Test
    public void testOr() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.eq("name", "孙权").or().eq("age", "20");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

条件逻辑关联

/**
     * or(Function<Param, Param> func)  逻辑关联
     * 查找姓氏为李 或者  年龄28到30的女性
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? OR (age BETWEEN ? AND ? AND sex = ?)
     */
    @Test
    public void testOr2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.likeRight("name", "李").or(wrap -> wrap.between("age", 28, 30).eq("sex", SexEnum.WOMAN));
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

select 选择需要的字段

/**
     * select 选择需要的字段
     * SELECT name, age FROM tb_user
     */
    @Test
    public void testSelect() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.select("name", "age");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

select 排除字段

 /**
     * select 排除字段  deleted   version  email
     * SELECT id, user_name, password, name, age, sex FROM tb_user WHERE deleted = 0
     */
    @Test
    public void testSelect2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.select(User.class, t -> !t.getColumn().equals("deleted") &&
                !t.getColumn().equals("version") &&
                !t.getColumn().equals("email")
        );
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

apply 拼接 sql

    /**
     * apply
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age BETWEEN ? AND ?
     * <p>
     * apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
     */
    @Test
    public void testApply() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //拼接sql，   可以直接在applySql中输入值，但是有注入风险 ，用{}方式会更好
        //wrapper.between("age", 30, 42);
        //wrapper.apply("age between 30 and 42");
        wrapper.apply("age between {0} and {1}", 30, 42);
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

in

/**
     * in
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age IN (?, ?, ?)
     */
    @Test
    public void testIn() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.in("age", 20, 30, 40);
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

inSql 子查询

/**
     * inSql  子查询
     * 查询性别为女性并且和年龄和孙姓的有一样的
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND sex = ? AND age IN (SELECT age FROM tb_user WHERE name LIKE '孙%')
     */
    @Test
    public void testInSQL() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("sex", SexEnum.WOMAN).inSql("age", "select age from tb_user where name like '孙%'");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

having

 /**
     * having
     */
    @Test
    public void testHaving() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.groupBy("id").having("age > {0}", 30);
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

nested(Function func) 嵌套语句

/**
     * nested(Function<Param, Param> func) 嵌套语句
     * 查询 姓名为李开头或者是女性  或  密码是888888的人
     * SELECT id, user_name, nme, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND (name LIKE ? OR (sex <> ?)) OR password = ?
     */
    @Test
    public void testNested() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.nested(wr -> wr.likeRight("name", "李").or(w -> w.ne("sex", SexEnum.MAN))).or().eq("password", "888888");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

last 在最后拼接语句

/**
     * last  在最后拼接语句
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 LIMIT 2
     */
    @Test
    public void testLast() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.last("limit 2");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

exists

/**
     * exists
     * 拼接exists语句 子查询 返回true或者false 作为条件应用到外层sql
     */
    @Test
    public void testExists() {
        //SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND EXISTS (SELECT * FROM tb_user WHERE age > 60)
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.exists("select * from tb_user where age >60");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

5.3.2 wrapper Lambda条件构造器

lambda根据条件查询1

    @Test
    public void selectLambda() {
        //创建对象的三种方式
        LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();
        //SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? AND age < ?
        lambdaQuery.like(User::getName, "李").lt(User::getAge, 40);
        List<User> users = userMapper.selectList(lambdaQuery);
        users.forEach(System.err::println);
    }

lambda根据条件查询2

/**
     * lambda根据条件查询
     * 查询姓名为孙姓 并且 （年龄小于40或者邮箱不为空）
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? AND (age < ? OR email IS NOT NULL)
     */
    @Test
    public void selectLambda2() {
        LambdaQueryWrapper<User> wrapper = new QueryWrapper<User>().lambda();
        wrapper.likeRight(User::getName, "孙")
                .and(e -> e.lt(User::getAge, 40).or().isNotNull(User::getMail));
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

LambdaQueryChainWrapper

/**
     * 3.0.7后的新的wrapper 可以直接返回结果
     * LambdaQueryChainWrapper
     */
    @Test
    public void selectLambda3() {
        List<User> list = new LambdaQueryChainWrapper<>(userMapper)
                .like(User::getName, "孙").list();
        list.forEach(System.err::println);
    }