学习视频:【编程不良人】Mybatis-Plus整合SpringBoot实战教程,提高的你开发效率,后端人员必备!

查询方法详解

  • 普通查询

    // 根据主键id去查询单个结果的。@Testpublic void selectById() {    User user = userMapper.selectById(1739970502337392641L);    System.out.println(user);}//根据多个主键id批量查询结果的@Testpublic void selectIds() {    List list = Arrays.asList(1739970502337392641L, 1739983903621038082L, 1739984905459900417L);    List userList = userMapper.selectBatchIds(list);    userList.forEach(System.out::println);}// 根据多个条件查询结果的@Testpublic void selectByMap() {    // map.put("name","小明")    // map.put("age",30)    // 相当于 where name ="小明" and age=30    Map columnMap = new HashMap();    columnMap.put("name", "小刚");    columnMap.put("age", "18");    List userList = userMapper.selectByMap(columnMap);    userList.forEach(System.out::println);}
  • 条件构造器查询 【重要】

    AbstractWrapper是 MyBatis Plus 中的一个抽象类,用于构建 SQL 查询条件。定义了泛型TCChildren。其中,T表示实体类的类型,C表示查询条件的类型,Children表示子类的类型,用于支持链式调用。它提供了一系列方法,用于构建 SQL 查询条件,包括设置查询字段、设置查询条件、排序等。

    常用实现类包括QueryWrapperUpdateWrapper。这两个类都是 MyBatis Plus 提供的具体实现,用于构建查询条件和更新条件。

  • 条件构造器查询示例

    @SpringBootTestpublic class SelectTests {    @Autowired    private UserMapper userMapper;    /**     * 1.名字中包含刘且年龄大于20     * name like '%刘%' and age>20     */    @Test    public void selectByWrapper() {        // 创建queryWrapper的两种方式        QueryWrapper queryWrapper = new QueryWrapper();//        QueryWrapper queryWrapper1 = Wrappers.query();        queryWrapper.like("name", "刘").gt("age", 20);        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 2.名字中包含刘且年龄大于等于20且小于30并且email不为空     * name like ‘%刘’ and age between 20 and 30 and email is not null     */    @Test    public void selectByWrapper2() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.like("name", "刘").between("age", 20, 30).isNull("email");        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 3.名字中包含刘或年龄大于等于20,按照年龄降序排序,年龄相同按照id升序排列     * name like ‘%刘’ and age between 20 and 30 and email is not null     */    @Test    public void selectByWrapper3() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.like("name", "刘").or().ge("age",20)                .orderByDesc("age")                .orderByAsc("user_id");        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 4.创建日期为2023年12月17日并且直属上级为王姓     * date_format(create_time,'%Y-%m-$d') and manager_id in(select id from user where name like '王%'     */    @Test    public void selectByWrapper4() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2023-12-27") //  使用占位符避免sql注入的风险                .inSql("manager_id","select user_id from mp_user where name like '王%'");        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 5.名字为刘姓且年龄小于30或者邮箱不为空     * name like '王%' and (age<20 or email is not null)     */    @Test    public void selectByWrapper5() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.likeRight("name", "刘")                .and(wq -> wq.lt("age", 30).or().isNotNull("email"));        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 6.名字为刘姓或者(年龄小于30且大于20并且邮箱不为空)     * name like '刘%' or (age20 and email is not null)     */    @Test    public void selectByWrapper6() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.likeRight("name", "刘")                .or(wq -> wq.lt("age", 30).gt("age", 20)                        .isNotNull("email"));        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 7.(年龄小于30且邮箱不为空)并且名字为刘姓     *  (age<30 or email is not null) and name like '刘%'     */    @Test    public void selectByWrapper7() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.nested(wq -> wq.lt("age", 30)                        .or().isNotNull("email"))                .likeRight("name", "刘");        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 8. 年龄为 21 25 33     *  age in(21,25,33)     */    @Test    public void selectByWrapper8() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.in("age", 21, 25, 33);        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }    /**     * 9. 只返回满足条件的其中一条语句即可     *  limit 1     */    @Test    public void selectByWrapper9() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.in("age", 21, 25, 33).last("limit 1"); // last() 有sql注入的风险,谨慎使用        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }}
  • select 不列出全部字段

     /**   * 10.名字中包含刘且年龄小于30(需求1加强版)   */  @Test  public void selectByWrapper() {      QueryWrapper queryWrapper = new QueryWrapper();//        queryWrapper.select("user_id","name","age").like("name", "刘").lt("age", 30); // 包含字段      queryWrapper.like("name", "刘").lt("age", 30)              .select(User.class, info ->                      !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id"));// 排除字段      List list = userMapper.selectList(queryWrapper);      list.forEach(System.out::println);  }
  • condition作用

    根据判断决定查询条件是否加到sql语句里面

    @Test    public void testCondition() {        String name = "东";        String email = "";        condition(name, email);    }    private void condition(String name, String email) {        QueryWrapper queryWrapper = new QueryWrapper();//        if (StringUtils.isNotEmpty(name)) {//            queryWrapper.like("name", name);//        }//        if (StringUtils.isNotEmpty(email)) {//            queryWrapper.like("email", email);//        }        queryWrapper.like(StringUtils.isNotEmpty(name), "name", name)                .like(StringUtils.isNotEmpty(email), "email", email);        List list = userMapper.selectList(queryWrapper);        list.forEach(System.out::println);    }
  • 实体作为条件构造器构造方法的参数

    @Testpublic void selectByWrapperEntity() {    User user = new User();    user.setRealName("小刚");    user.setAge(18);    QueryWrapper queryWrapper = new QueryWrapper(user);    List list = userMapper.selectList(queryWrapper);    list.forEach(System.out::println);}运行结果的sql语句:SELECT * FROM mp_user WHERE **name=? AND age=?**会把set注入的属性和值映射到where条件中
    • 通过SqlCondition 更改查询条件

      用法@TableField(condition = SqlCondition.LIKE)private String name;@TableField(condition = "%s<#{%s}") //根据定义的常量,自己也可以参考并更改private Integer age;运行结果:SELECT * FROM mp_user WHERE **name LIKE CONCAT('%',?,'%')** AND AND age<?
  • allEq

    allEq是一个查询条件配置方法,用于构建查询条件。它可以根据传入的条件参数,生成一个包含所有等值条件的查询条件对象。

    @Testpublic void selectByWrapperAllEq() {    QueryWrapper queryWrapper = new QueryWrapper();    Map params = new HashMap();    params.put("name", "小明");    params.put("age", null);    **queryWrapper.allEq(params,false)**; // false代表忽略值为null的字段,即不参与where条件    List list = userMapper.selectList(queryWrapper);    list.forEach(System.out::println);}
  • 其他条件构造器

    **selectMaps 返回**

    @Test    public void selectByWrapperMaps() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.select("user_id","name").like("name", "刘").lt("age", 30);//        List list = userMapper.selectList(queryWrapper);// 返回值为实体类的话,没设置的值还是会返回,只不过值为null        List<Map> userList = userMapper.selectMaps(queryWrapper);// 只返回限定的字段        userList.forEach(System.out::println);    }    /** 示例     * 11.按照直属上级分组,查询每组的平均年龄、最大年龄、最小年龄,并且至取年龄总和为500的组     * select avg(age)avg_age,min(age) min_age,max(age) max_age from user group by manager_id having sum(age)<500     */    @Test    public void selectByWrapperMaps2() {        QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age")                .groupBy("manager_id").having("sum(age)<{0}", 500);        List<Map> userList = userMapper.selectMaps(queryWrapper);        userList.forEach(System.out::println);    }
  • lambda条件构造器

    @Testpublic void selectLambda() {    //创建lambda条件构造器的三种方式    LambdaQueryWrapper lambda = new LambdaQueryWrapper();    LambdaQueryWrapper lambda2 = new QueryWrapper().lambda();    LambdaQueryWrapper lambda3= Wrappers.lambdaQuery();    lambda3.like(User::getName, "刘")            .lt(User::getAge, 30); // where name like '%雨%' and age<30    List userList = userMapper.selectList(lambda3);    userList.forEach(System.out::println);}/** * 5.名字为刘姓且(年龄小于30或邮箱不为空) * name like '刘%' and (age<30 or email is not null) */@Testpublic void selectLambda2() {    LambdaQueryWrapper lambda= Wrappers.lambdaQuery();    lambda.like(User::getName, "刘")            .and(wq -> wq.lt(User::getAge, 20)                    .or().isNotNull(User::getEmail));    List userList = userMapper.selectList(lambda);    userList.forEach(System.out::println);}// 链式调用多个条件 LambdaQueryChainWrapper@Testpublic void selectLambda3() {    List userList = new LambdaQueryChainWrapper(userMapper)            .like(User::getName, "刘").ge(User::getAge, 20).list();    userList.forEach(System.out::println);}

作者:扬眉剑出鞘
出处: https://www.cnblogs.com/eyewink/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。