mp条件构造器（1）

Z时代
2024-01-10
分类：综合

条件构造器（1）

抽象类AbstractWrapper 定义了一些构造条件的方法，在使用的使用我们使用他的子类QueryWrapper 实例化后构造条件.

条件构造器通常返回符合条件的List集合

QueryWrapper<User> query = new QueryWrapper<>();
//构造条件
 List<User> list = userMapper.selectList(query);
 list.forEach(System.out::println);

1. like 、and、 lt（小于）

  /**
     * 名字中包含雨，并且年龄小于40
     * SELECT * FROM `user`
     * WHERE `name` LIKE "%雨%" AND `age`< 40
     */
    @Test
    public void selectList_like_lt() {
//        QueryWrapper<User> query = Wrappers.<User>query();
        QueryWrapper<User> query = new QueryWrapper<>();
        query.like("name", "雨").lt("age", 40);
        List<User> list = userMapper.selectList(query);
        list.forEach(System.out::println);
    }

2. like、 and、 between 、isNotNull


    /**
     * 名字中包含雨，并且年龄大于等于20且小于等于40，并且email不为空
     * SELECT * FROM `user`
     * WHERE `name` LIKE "%雨%" AND `age` <= 40 AND `age` >= 20 AND `email` IS NOT NULL
     */
    @Test
    public void selectList_between_isNotNull() {
        QueryWrapper<User> query = new QueryWrapper<>();
        query.like("name", "雨").between("age", 20, 40).isNotNull("email");
        List<User> list = userMapper.selectList(query);
        list.forEach(System.out::println);
    }

3. likeRight 、orderByAsc 、orderByDesc、 ge(大于)

 /**
     * 姓赵或者年龄大于等于25，按照年龄降序排列，年龄相同则按照id升序排列
     * SELECT * FROM `user`
     * WHERE `name` LIKE "赵%" OR `age` >= 25 ORDER BY `age` DESC , `id` ASC;
     */
    @Test
    public void selectList_or_orderByDesc_orderByAsc() {
        QueryWrapper<User> query = new QueryWrapper<>();
        query.likeRight("name", "赵").or().ge("age", 20)
            .orderByDesc("age").orderByAsc("id");
        List<User> list = userMapper.selectList(query);
        list.forEach(System.out::println);
    }

4. mysql函数和子查询和sql注入演示

  /**
     * 创建日期为2019年2月14日，且直属上级姓王
     * SELECT * FROM `user`
     * WHERE DATE_FORMAT(create_time,"%Y-%m-%d")="2019-02-14"
     * AND manager_id IN (SELECT id FROM `user` WHERE `name` LIKE "王%")
     */
    @Test
    public void selectList_apply_inSql() {
        QueryWrapper<User> query = new QueryWrapper<>();
        query.apply("DATE_FORMAT(create_time,"%Y-%m-%d")={0}", "2019-02-14")
            .inSql("manager_id", "SELECT id FROM `user` WHERE `name` LIKE "王%"");
        List<User> list = userMapper.selectList(query);
        list.forEach(System.out::println);
    }
 /**
     * 创建日期为2019年2月14日，且直属上级姓王
     * SELECT * FROM `user`
     * WHERE DATE_FORMAT(create_time,"%Y-%m-%d")="2019-02-14"
     * AND manager_id IN (SELECT id FROM `user` WHERE `name` LIKE "王%")
     */
    @Test
    public void selectList_apply_inSql() {
        QueryWrapper<User> query = new QueryWrapper<>();
        query.apply("DATE_FORMAT(create_time,"%Y-%m-%d")="2019-02-14" or ture or true")
            .inSql("manager_id", "SELECT id FROM `user` WHERE `name` LIKE "王%"");
        List<User> list = userMapper.selectList(query);
        list.forEach(System.out::println);
    }