@select注解模糊查询_Mybatis plus学习笔记（常用注解/条件构造器）

一.常用注解

1.表字段注解

1. 指定表名：@TableName("user")
2. 指定表主键：@TableId
3. 指定字段在数据库中对应哪一列：@TableField("name")

2.排除非表字段

1. transient：指定不是数据库字段
2. static：不能生成get/set方法
3. @TableField(exist = false)

二.普通查询

1.列表查询

    @Test
    void selectIds() {
        List<Long> ids = Arrays.asList(1L, 2L, 3L);
        List<User> users = userMapper.selectBatchIds(ids);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
    @Test
    void selectByMap() {
        Map<String, Object> map = new HashMap<>();
        map.put("id", 1);
        map.put("name", "张三");
        List<User> users = userMapper.selectByMap(map);
        users.forEach(System.out::println);
    }

2.条件构造器查询

    /**
     * 查询行王的，年龄大于20岁
     */
    @Test
    public void selectWrapper() {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name", "王");
        queryWrapper.gt("age", 20);
        List list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
------------------------------------------------------------
    /**
     * 查询姓王的，年龄大于等于20，小于等于40.并且email不为空
     */
    @Test
    public void selectWrapper01() {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name", "王");
        queryWrapper.between("age", 20, 40);
        queryWrapper.isNotNull("email");
        List list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * 查询姓王的，或者年龄大于30。按照年龄降序，如果年龄相同按照id升序
     */
    @Test
    public void selectWrapper02() {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("name", "王");
        queryWrapper.or();
        queryWrapper.ge("age", 30);
        queryWrapper.orderByDesc("age");
        queryWrapper.orderByAsc("id");
        List list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * 名字为王姓，并且年龄小于40或邮箱不为空
     */
    @Test
    public void selectWrapper04() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", "王");
        queryWrapper.and(qw -> qw.lt("age", 40).or().isNotNull("email"));
        List list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * (年龄小于40或邮箱不为空)，并且名字为王姓
     */
    @Test
    public void selectWrapper06() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(qw -> qw.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * 模拟in查询
     */
    @Test
    public void selectWrapper07() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(21, 26, 28));
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * 返回一条数据
     */
    @Test
    public void selectWrapper08() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(21, 26, 28)).last("limit 1");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

3.select不展示出全部的例子

    /**
     * 返回需要的字段
     */
    @Test
    public void selectWrapper09() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name", "age", "phone").likeRight("name", "王").le("age", 20);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
   /**
     * 过滤不需要的字段
     */
    @Test
    public void selectWrapper10() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class, p -> !p.getColumn().equals("parent_id") && !p.getColumn().equals("create_time")).likeRight("name", "王");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

4.condition的作用

    /**
     * condition测试
     * @param name
     * @param email
     */
    public void selectWrapper11(String name, String email) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isEmpty(name), "name", name).like(StringUtils.isEmpty(email), "email", email);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

5.传入实体参数

    // 设置name前后模糊
    @TableField(condition = SqlCondition.LIKE)
    private String name;
   /**
     * 传入实体对象
     */
    @Test
    public void selectWrapper12() {
        User user = new User();
        user.setName("张");
        user.setAge(28);
        QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

6.AllEq参数

    /**
     * AllEq的用法
     */
    @Test
    public void selectWrapper13() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("name", "张三");
        // 普通查询
        // map.put("age",28);
        // queryWrapper.allEq(map);
        // --------------------------
        // 传入false自动过滤为null的
        map.put("age", null);
        queryWrapper.allEq(map, false);
        // --------------------------
        // 自动过滤name参数
        queryWrapper.allEq((key, value) -> !key.equals("name"), map);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

7.其它构造方法

    /**
     * 其它构造条件
     * 按照直属上级分组，查询每个组的平均年龄，最大年龄，最小年龄
     * 并且只取最小年龄总和小于500的组
     */
    @Test
    public void selectWrapper14() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("avg(age) AS avg_age", "min(age) AS min_age", "max(age) AS max_age");
        queryWrapper.groupBy("parent_id").having("sum(age) < {0}", 300);
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(map -> map.forEach((key, value) -> System.out.println(key + "----" + value)));
    }

8.lambda条件构造器

    /**
     * 三种不同的lambda构造方法
     */
    @Test
    public void selectWrapper15() {
        // 方式一
        LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
        // 方式二
        LambdaQueryWrapper<User> lambda2 = new LambdaQueryWrapper<>();
        // 方式三
        LambdaQueryWrapper<User> lambda3 = Wrappers.<User>lambdaQuery();
        lambda3.like(User::getName, "张").lt(User::getAge, 30);
        List<User> users = userMapper.selectList(lambda3);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
    /**
     * lambda实现，王姓并且（年龄小于40或邮箱不为空）
     */
    public void selectWrapper16() {
        LambdaQueryWrapper<User> wq = Wrappers.<User>lambdaQuery();
        wq.like(User::getName, "张").and(user -> user.lt(User::getAge, 40).or().isNotNull(User::getEmail));
        List<User> users = userMapper.selectList(wq);
        users.forEach(System.out::println);
    }
------------------------------------------------------------
    /**
     * lambda实现，王姓并且年龄小于40
     */
    public void selectWrapper17() {
        List<User> users = new LambdaQueryChainWrapper<User>(userMapper).like(User::getName, "张").lt(User::getAge, 40).list();
        users.forEach(System.out::println);
    }