3-2 条件构造器（1）

数据库表格中数据：

1.名字中包含雨并且年龄小于40

name like '%雨' and age < 40

@Test
    public void selectByWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
//        QueryWrapper<User> query = Wrappers.query(); 上面的不同方式的相同结果
        queryWrapper.like("name","雨").lt("age",40);

        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 < ?)
DEBUG==> Parameters: %雨%(String), 40(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
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: 2
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)

           

2.名字中包含雨并且年龄大于20且等于40并且email不为空

name like %雨% and age between 20 and 40 and email is not null

@Test
    public void selectByWrapper2(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.like("name","雨").between("age",20,40).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 BETWEEN ? AND ? AND email IS NOT NULL)
DEBUG==> Parameters: %雨%(String), 20(Integer), 40(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
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: 2
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)

           

3.名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列

name like '王%' or age >= 40 order by age desc,id asc

@Test
    public void selectByWrapper3(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name","王").or().ge("age",5).orderByDesc("age").orderByAsc("id");

        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 >= ?) ORDER BY age DESC,id ASC
DEBUG==> Parameters: 王%(String), 5(Integer)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1087982557332887553, 大boss, 40, [email protected], null, 2019-01-11 14:20:20
TRACE<==        Row: 1094590409767661570, 张雨琪, 39, [email protected], 1088248166370832856, 2019-01-14 09:15:15
TRACE<==        Row: 1323901761463898113, 刘明强, 31, null, 108825443231254, 2020-11-04 16:15:51
TRACE<==        Row: 1088250446457380958, 李艺伟, 28, [email protected], 1088248166370832385, 2019-02-14 08:31:16
TRACE<==        Row: 1094592041087729666, 刘红雨, 28, [email protected], 1088248166370832856, 2019-01-11 14:20:20
TRACE<==        Row: 1323903451814535170, 向南, 27, null, 108825443231254, 2020-11-04 16:22:34
TRACE<==        Row: 1323904316671606786, 向东, 27, null, 108825443231254, 2020-11-04 16:26:01
TRACE<==        Row: 1323904984857817090, 向西, 27, null, 108825443231254, 2020-11-04 16:28:40
TRACE<==        Row: 1088248166370832385, 王天风, 25, [email protected], 1087982257332887553, 2019-02-05 11:12:22
TRACE<==        Row: 1323914571316994049, 向前, 25, null, 108825443231254, 2020-11-04 17:06:46
TRACE<==        Row: 1323916606343041026, 向后, 25, null, 108825443231254, 2020-11-04 17:14:51
TRACE<==        Row: 1323917108271218690, 向中, 25, null, 108825443231254, 2020-11-04 17:16:50
DEBUG<==      Total: 12
User(id=1087982557332887553, name=大boss, age=40, [email protected], managerId=null, createTime=2019-01-11T14:20:20)
User(id=1094590409767661570, name=张雨琪, age=39, [email protected], managerId=1088248166370832856, createTime=2019-01-14T09:15:15)
User(id=1323901761463898113, name=刘明强, age=31, email=null, managerId=108825443231254, createTime=2020-11-04T16:15:51)
User(id=1088250446457380958, name=李艺伟, age=28, [email protected], managerId=1088248166370832385, createTime=2019-02-14T08:31:16)
User(id=1094592041087729666, name=刘红雨, age=28, [email protected], managerId=1088248166370832856, createTime=2019-01-11T14:20:20)
User(id=1323903451814535170, name=向南, age=27, email=null, managerId=108825443231254, createTime=2020-11-04T16:22:34)
User(id=1323904316671606786, name=向东, age=27, email=null, managerId=108825443231254, createTime=2020-11-04T16:26:01)
User(id=1323904984857817090, name=向西, age=27, email=null, managerId=108825443231254, createTime=2020-11-04T16:28:40)
User(id=1088248166370832385, name=王天风, age=25, [email protected], managerId=1087982257332887553, createTime=2019-02-05T11:12:22)
User(id=1323914571316994049, name=向前, age=25, email=null, managerId=108825443231254, createTime=2020-11-04T17:06:46)
User(id=1323916606343041026, name=向后, age=25, email=null, managerId=108825443231254, createTime=2020-11-04T17:14:51)
User(id=1323917108271218690, name=向中, age=25, email=null, managerId=108825443231254, createTime=2020-11-04T17:16:50)

           

4.创建日期为2019年2月14日并且直属上级为姓名为王姓

data_format(create_time.'%Y-%m-%d') and manager_id in (select id from user where name like '王%')

@Test
    public void selectByWrapper4(){
        QueryWrapper<User> queryWrapper = new QueryWrapper();
//        queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}","2019-02-14") 不存在SQL注入，建议使用
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = '2019-02-14'")
                .inSql("manager_id","select id from user where name like '王%'");

        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 (date_format(create_time,'%Y-%m-%d') = ? AND manager_id IN (select id from user where name like '王%'))
DEBUG==> Parameters: 2019-02-14(String)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 1088250446457380958, 李艺伟, 28, [email protected], 1088248166370832385, 2019-02-14 08:31:16
DEBUG<==      Total: 1
User(id=1088250446457380958, name=李艺伟, age=28, [email protected], managerId=1088248166370832385, createTime=2019-02-14T08:31:16)