MP条件构造器v2

一、创建添加构造器的两种方式

//1
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//2
QueryWrapper<User> query = Wrappers.<User>query();

二、注意点

1. 条件构造器的方法中，参数条件大多使用的是数据库的字段名，而不是变量名；
2. apply、inSql方法的使用（见下方selectByWrapper4方法）；
3. and、or、nested、in方法的使用（见下方selectByWrapper5、6、7、8方法），sql语句中and的优先级>or的优先级，写多个queryWraper等同于中间and连接；
4. last方法：无视优化规则直接拼接到sql的最后，只能调用一次，多次调用以最后一次调用为准，有sql注入风险，谨慎使用；（见下方selectByWrapper9）

三、案例

    /**
    *  测试BaseMapper中的List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);方法
    */
    @Test
    public void selectByWrapper(){
        //创建添加构造器有两种方式
        //1
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //2
        //QueryWrapper<User> query = Wrappers.<User>query();

        queryWrapper.like("name", "Tom").lt("age",40);

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

    @Test
    public void selectByWrapper2(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.like("name", "o").between("age", 18, 30).isNotNull("email");

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

    @Test
    public void selectByWrapper3(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.likeRight("name", "J").or().ge("age", 18).orderByDesc("age").orderByAsc("id");

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

//  date_format(create_time,'%Y-%m-%d')='2020-10-03' and id in (select id from user where name like 'J%');
    @Test
    public void selectByWrapper4(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2020-10-03")
//        sql注入风险or true or true
//        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')= '2020-10-03' or true or true")
                .inSql("id", "select id from user where name like 'J%'");

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

    @Test
    public void selectByWrapper5(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.likeRight("name", "J").and(wq->wq.lt("age",20).or().isNull("email"));

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

//    name LIKE ? OR (age BETWEEN ? AND ? AND email IS NOT NULL)
//    name LIKE ? OR email IS NOT NULL AND age <= ?
    @Test
    public void selectByWrapper6(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.likeRight("name", "J").or(wq->wq.between(true, "age", 18, 22).isNotNull("email"));
//        queryWrapper.likeRight("name", "J").or().isNotNull("email").le("age",18);

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

//    (age < ? OR email IS NOT NULL) AND name LIKE ?
    @Test
    public void selectByWrapper7(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.nested(wq->wq.lt("age", 20).or().isNotNull("email")).likeRight("name", "J");

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

//    age IN (?,?,?) AND age IN (?,?)
    @Test
    public void selectByWrapper8(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.in("age", 20,28,21);
        queryWrapper.in("age", Arrays.asList(24,20));

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

//    last,仅保留最后n条，谨慎使用，有sql注入风险
//    (age IN (?,?,?,?)) ORDER BY id DESC limit 3 
    @Test
    public void selectByWrapper9(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();

        queryWrapper.in("age", Arrays.asList(21,18,24,20)).orderByDesc("id").last("limit 3");

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