Mybatis Plus 使用学习九 QueryWrapper使用下

1、模糊查找，like

方法：

    default Children like(R column, Object val) {
        return like(true, column, val);
    }

代码：

    @Test
    public void testLike() {
        System.out.println(("----- like method test ------"));
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.like("Name", "Ja");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.out::println);
    }

输出：

==>  Preparing: SELECT id,name,age,email FROM user WHERE (Name LIKE ?) 
==> Parameters: %Ja%(String)
<==    Columns: ID, NAME, AGE, EMAIL
<==        Row: 2, Jack, 20, test2@baomidou.com
<==      Total: 1

User(id=2, name=Jack, age=20, email=test2@baomidou.com)

还有相似功能的方法：notLike、likeLeft、likeRight

2、查找指定的某列值为空

SQL中的 is null

方法：

    default Children isNull(R column) {
        return isNull(true, column);
    }

代码：

    @Test
    public void testIsNull() {
        System.out.println(("----- isNull method test ------"));
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.isNull("Name");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.out::println);
    }

输出：

==>  Preparing: SELECT id,name,age,email FROM user WHERE (Name IS NULL) 
==> Parameters: 
<==      Total: 0

还有类似的方法：isNotNull

3、匹配多个值

SQL中的 in(x，xx, xxx)

方法：

    default Children in(R column, Object... values) {
        return in(true, column, values);
    }

代码：

    @Test
    public void testIn() {
        System.out.println(("----- in method test ------"));
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.in("Age",20,21,22,23);
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.out::println);
    }

输出：

==>  Preparing: SELECT id,name,age,email FROM user WHERE (Age IN (?,?,?,?)) 
==> Parameters: 20(Integer), 21(Integer), 22(Integer), 23(Integer)
<==    Columns: ID, NAME, AGE, EMAIL
<==        Row: 2, Jack, 20, test2@baomidou.com
<==        Row: 4, Sandy, 21, test4@baomidou.com
<==      Total: 2

User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)

类似的方法：notIn、inSql、notInSql

我们使用in的时候，有时候会使用子查询，也就是in的括号里是一个sql语句，inSql、notInSql两个方法就是支持sql语句的in语法。

4、数据分组

SQL：groupBy

方法：

    default Children groupBy(R column) {
        return groupBy(true, column);
    }

代码：按照年龄求所有人的年龄分组列表，也就是看看当前表里所有人都在哪个年龄

    @Test
    public void testGroupBy() {
        System.out.println(("----- groupBy method test ------"));
        LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper();
        lqw.groupBy(User::getAge).select(User::getAge);
        List<User> userList = userMapper.selectList(lqw);
        userList.forEach(System.out::println);
    }

输出：

==>  Preparing: SELECT age FROM user GROUP BY age 
==> Parameters: 
<==    Columns: AGE
<==        Row: 18
<==        Row: 20
<==        Row: 21
<==        Row: 24
<==        Row: 28
<==      Total: 5

User(id=null, name=null, age=18, email=null)
User(id=null, name=null, age=20, email=null)
User(id=null, name=null, age=21, email=null)
User(id=null, name=null, age=24, email=null)
User(id=null, name=null, age=28, email=null)

5、按照指定属性列的顺序排序，并输出数据

SQL:ORDER BY

方法：

    default Children orderByAsc(R column) {
        return orderByAsc(true, column);
    }

代码：

    @Test
    public void testOrderBy() {
        System.out.println(("----- orderBy method test ------"));
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.orderByAsc("Age");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.out::println);
    }

输出：

==>  Preparing: SELECT id,name,age,email FROM user ORDER BY Age ASC 
==> Parameters: 
<==    Columns: ID, NAME, AGE, EMAIL
<==        Row: 1, Jone, 18, test1@baomidou.com
<==        Row: 2, Jack, 20, test2@baomidou.com
<==        Row: 4, Sandy, 21, test4@baomidou.com
<==        Row: 5, Billie, 24, test5@baomidou.com
<==        Row: 3, Tom, 28, test3@baomidou.com
<==      Total: 5

User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)

QueryWrapper提供的条件配置方法还有很多，你可以自己动手写一写，练一练。