3-4 条件构造器查询（2）

5.名字为王姓并且（年龄小于40或邮箱不为空）

name like '王%' and (age < 40 or email is not null)

@Test
    public void selectByWrapper5(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name","王").and(wq->wq.lt("age",40).or().isNotNull("email"));

        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

           

运行结果：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? AND (age < ? OR email IS NOT NULL))
DEBUG==> Parameters: 王%(String), 40(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1088248166370832385, 王天风, 25, [email protected], 1087982257332887553, 2019-02-05 11:12:22
DEBUG<==      Total: 1
User(id=1088248166370832385, name=王天风, age=25, [email protected], managerId=1087982257332887553, createTime=2019-02-05T11:12:22)

           

6.名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）

name like '王%' or (age < 40 and age > 20 and email is not null)

@Test
    public void selectByWrapper7(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name","王").or(wq -> wq.lt("age",40)
                .gt("age",20).isNotNull("email"));


        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

           

输出结果：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (name LIKE ? OR (age < ? AND age > ? AND email IS NOT NULL))
DEBUG==> Parameters: 王%(String), 40(Integer), 20(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1088248166370832385, 王天风, 25, [email protected], 1087982257332887553, 2019-02-05 11:12:22
TRACE<==        Row: 1088250446457380958, 李艺伟, 28, [email protected], 1088248166370832385, 2019-02-14 08:31:16
TRACE<==        Row: 1094590409767661570, 张雨琪, 39, [email protected], 1088248166370832856, 2019-01-14 09:15:15
TRACE<==        Row: 1094592041087729666, 刘红雨, 28, [email protected], 1088248166370832856, 2019-01-11 14:20:20
DEBUG<==      Total: 4
User(id=1088248166370832385, name=王天风, age=25, [email protected], managerId=1087982257332887553, createTime=2019-02-05T11:12:22)
User(id=1088250446457380958, name=李艺伟, age=28, [email protected], managerId=1088248166370832385, createTime=2019-02-14T08:31:16)
User(id=1094590409767661570, name=张雨琪, age=39, [email protected], managerId=1088248166370832856, createTime=2019-01-14T09:15:15)
User(id=1094592041087729666, name=刘红雨, age=28, [email protected], managerId=1088248166370832856, createTime=2019-01-11T14:20:20)

           

7.（年龄小于40或邮箱不为空）并且名字为王姓

(age < 40 or email is not null) and name like '王%'

@Test
    public void selectByWrapper8(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.nested(wq -> wq.lt("age",40).or().isNotNull("email"))
                .likeRight("name","王");


        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

           

输出结果：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)
DEBUG==> Parameters: 40(Integer), 王%(String)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1088248166370832385, 王天风, 25, [email protected], 1087982257332887553, 2019-02-05 11:12:22
DEBUG<==      Total: 1
User(id=1088248166370832385, name=王天风, age=25, [email protected], managerId=1087982257332887553, createTime=2019-02-05T11:12:22)

           

8.年龄为30、31、34、35

age in(30、31、34、35、)

@Test
    public void selectByWrapper9(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.in("age",Arrays.asList(30,31,34,35));

        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }
           

输出结果：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (age IN (?,?,?,?))
DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1323901761463898113, 刘明强, 31, null, 108825443231254, 2020-11-04 16:15:51
DEBUG<==      Total: 1
User(id=1323901761463898113, name=刘明强, age=31, email=null, managerId=108825443231254, createTime=2020-11-04T16:15:51)

           

9.只返回满足条件的其中一条语句即可

limit 1

@Test
    public void selectByWrapper10(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.in("age",Arrays.asList(30,31,34,35)).last("limit 1");

        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);
    }

           

输出结果：

DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE (age IN (?,?,?,?)) limit 1
DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1323901761463898113, 刘明强, 31, null, 108825443231254, 2020-11-04 16:15:51
DEBUG<==      Total: 1
User(id=1323901761463898113, name=刘明强, age=31, email=null, managerId=108825443231254, createTime=2020-11-04T16:15:51)