學(xué)習(xí)視頻：【編程不良人】Mybatis-Plus整合SpringBoot實(shí)戰(zhàn)教程,提高的你開發(fā)效率,后端人員必備!

查詢方法詳解

• 普通查詢

  // 根據(jù)主鍵id去查詢單個(gè)結(jié)果的。
  @Test
  public void selectById() {
      User user = userMapper.selectById(1739970502337392641L);
      System.out.println(user);
  }
  
  //根據(jù)多個(gè)主鍵id批量查詢結(jié)果的
  @Test
  public void selectIds() {
      List<Long> list = Arrays.asList(1739970502337392641L, 1739983903621038082L, 1739984905459900417L);
      List<User> userList = userMapper.selectBatchIds(list);
      userList.forEach(System.out::println);
  }
  // 根據(jù)多個(gè)條件查詢結(jié)果的
  @Test
  public void selectByMap() {
      // map.put("name","小明")
      // map.put("age",30)
      // 相當(dāng)于 where name ="小明" and age=30
      Map<String, Object> columnMap = new HashMap<>();
      columnMap.put("name", "小剛");
      columnMap.put("age", "18");
      List<User> userList = userMapper.selectByMap(columnMap);
      userList.forEach(System.out::println);
  }
  

• 條件構(gòu)造器查詢 【重要】

  AbstractWrapper?是 MyBatis Plus 中的一個(gè)抽象類，用于構(gòu)建 SQL 查詢條件。定義了泛型?T、C?和?Children。其中，T?表示實(shí)體類的類型，C?表示查詢條件的類型，Children?表示子類的類型，用于支持鏈?zhǔn)秸{(diào)用。它提供了一系列方法，用于構(gòu)建 SQL 查詢條件，包括設(shè)置查詢字段、設(shè)置查詢條件、排序等。

  常用實(shí)現(xiàn)類包括?QueryWrapper?和?UpdateWrapper。這兩個(gè)類都是 MyBatis Plus 提供的具體實(shí)現(xiàn)，用于構(gòu)建查詢條件和更新條件。

• 條件構(gòu)造器查詢示例

  @SpringBootTest
  public class SelectTests {
      @Autowired
      private UserMapper userMapper;
  
      /**
       * 1.名字中包含劉且年齡大于20
       * name like '%劉%' and age>20
       */
      @Test
      public void selectByWrapper() {
          // 創(chuàng)建queryWrapper的兩種方式
          QueryWrapper<User> queryWrapper = new QueryWrapper<>();
  //        QueryWrapper<User> queryWrapper1 = Wrappers.<User>query();
          queryWrapper.like("name", "劉").gt("age", 20);
          List<User> 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<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.like("name", "劉").between("age", 20, 30).isNull("email");
          List<User> 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<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.like("name", "劉").or().ge("age",20)
                  .orderByDesc("age")
                  .orderByAsc("user_id");
  
          List<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
      /**
       * 4.創(chuàng)建日期為2023年12月17日并且直屬上級(jí)為王姓
       * date_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}", "2023-12-27") //  使用占位符避免sql注入的風(fēng)險(xiǎn)
                  .inSql("manager_id","select user_id from mp_user where name like '王%'");
          List<User> 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<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.likeRight("name", "劉")
                  .and(wq -> wq.lt("age", 30).or().isNotNull("email"));
          List<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
      /**
       * 6.名字為劉姓或者（年齡小于30且大于20并且郵箱不為空）
       * name like '劉%' or (age<30 and age>20 and email is not null)
       */
      @Test
      public void selectByWrapper6() {
          QueryWrapper<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.likeRight("name", "劉")
                  .or(wq -> wq.lt("age", 30).gt("age", 20)
                          .isNotNull("email"));
  
          List<User> 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<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.nested(wq -> wq.lt("age", 30)
                          .or().isNotNull("email"))
                  .likeRight("name", "劉");
          List<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
      /**
       * 8. 年齡為 21 25 33
       *  age in(21,25,33)
       */
      @Test
      public void selectByWrapper8() {
          QueryWrapper<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.in("age", 21, 25, 33);
          List<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
      /**
       * 9. 只返回滿足條件的其中一條語(yǔ)句即可
       *  limit 1
       */
      @Test
      public void selectByWrapper9() {
          QueryWrapper<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.in("age", 21, 25, 33).last("limit 1"); // last() 有sql注入的風(fēng)險(xiǎn)，謹(jǐn)慎使用
          List<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
  }
  

• select 不列出全部字段

   /**
     * 10.名字中包含劉且年齡小于30（需求1加強(qiáng)版）
     */
    @Test
    public void selectByWrapper() {
        QueryWrapper<User> 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<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
  

• condition作用

  根據(jù)判斷決定查詢條件是否加到sql語(yǔ)句里面

  @Test
      public void testCondition() {
          String name = "東";
          String email = "";
          condition(name, email);
      }
      private void condition(String name, String email) {
          QueryWrapper<User> 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<User> list = userMapper.selectList(queryWrapper);
          list.forEach(System.out::println);
      }
  

• 實(shí)體作為條件構(gòu)造器構(gòu)造方法的參數(shù)

  @Test
  public void selectByWrapperEntity() {
      User user = new User();
      user.setRealName("小剛");
      user.setAge(18);
      QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
      List<User> list = userMapper.selectList(queryWrapper);
      list.forEach(System.out::println);
  }
  運(yùn)行結(jié)果的sql語(yǔ)句：SELECT * FROM mp_user WHERE **name=? AND age=?**
  會(huì)把set注入的屬性和值映射到where條件中
  

  • 通過SqlCondition 更改查詢條件

    

    用法
    @TableField(condition = SqlCondition.LIKE)
    private String name;
    @TableField(condition = "%s<#{%s}") //根據(jù)定義的常量，自己也可以參考并更改
    private Integer age;
    運(yùn)行結(jié)果：SELECT * FROM mp_user WHERE **name LIKE CONCAT('%',?,'%')** AND AND age<?
    

• allEq

  allEq是一個(gè)查詢條件配置方法，用于構(gòu)建查詢條件。它可以根據(jù)傳入的條件參數(shù)，生成一個(gè)包含所有等值條件的查詢條件對(duì)象。

  @Test
  public void selectByWrapperAllEq() {
      QueryWrapper<User> queryWrapper = new QueryWrapper<>();
      Map<String, Object> params = new HashMap<>();
      params.put("name", "小明");
      params.put("age", null);
      **queryWrapper.allEq(params,false)**; // false代表忽略值為null的字段，即不參與where條件
      List<User> list = userMapper.selectList(queryWrapper);
      list.forEach(System.out::println);
  }
  

• 其他條件構(gòu)造器

  **selectMaps 返回**

  @Test
      public void selectByWrapperMaps() {
          QueryWrapper<User> queryWrapper = new QueryWrapper<>();
          queryWrapper.select("user_id","name").like("name", "劉").lt("age", 30);
  //        List<User> list = userMapper.selectList(queryWrapper);// 返回值為實(shí)體類的話，沒設(shè)置的值還是會(huì)返回，只不過值為null
          List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);// 只返回限定的字段
          userList.forEach(System.out::println);
      }
  
      /** 示例
       * 11.按照直屬上級(jí)分組，查詢每組的平均年齡、最大年齡、最小年齡，并且至取年齡總和為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<User> 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<String, Object>> userList = userMapper.selectMaps(queryWrapper);
          userList.forEach(System.out::println);
      }
  

• lambda條件構(gòu)造器

  @Test
  public void selectLambda() {
      //創(chuàng)建lambda條件構(gòu)造器的三種方式
      LambdaQueryWrapper<User> lambda = new LambdaQueryWrapper<>();
      LambdaQueryWrapper<User> lambda2 = new QueryWrapper<User>().lambda();
      LambdaQueryWrapper<User> lambda3= Wrappers.<User>lambdaQuery();
  
      lambda3.like(User::getName, "劉")
              .lt(User::getAge, 30); // where name like '%雨%' and age<30
      List<User> userList = userMapper.selectList(lambda3);
      userList.forEach(System.out::println);
  }
  /**
   * 5.名字為劉姓且（年齡小于30或郵箱不為空）
   * name like '劉%' and (age<30 or email is not null)
   */
  @Test
  public void selectLambda2() {
      LambdaQueryWrapper<User> lambda= Wrappers.<User>lambdaQuery();
      lambda.like(User::getName, "劉")
              .and(wq -> wq.lt(User::getAge, 20)
                      .or().isNotNull(User::getEmail));
      List<User> userList = userMapper.selectList(lambda);
      userList.forEach(System.out::println);
  }
  // 鏈?zhǔn)秸{(diào)用多個(gè)條件 LambdaQueryChainWrapper
  @Test
  public void selectLambda3() {
      List<User> userList = new LambdaQueryChainWrapper<>(userMapper)
              .like(User::getName, "劉").ge(User::getAge, 20).list();
      userList.forEach(System.out::println);
  }
  

下一章：Spring Boot學(xué)習(xí)隨筆- 集成MyBatis-Plus（三）自定義SQL、分頁(yè)實(shí)現(xiàn)（PaginationInterceptor ）、更新和刪除詳細(xì)方法文章來源地址http://www.zghlxwxcb.cn/news/detail-765081.html

到了這里，關(guān)于Spring Boot學(xué)習(xí)隨筆- 集成MyBatis-Plus（二）條件查詢QueryWrapper、聚合函數(shù)的使用、Lambda條件查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！