MybatisPlus中QueryWrapper常用方法

爱被打了一巴掌 2024-03-22 20:39 128阅读 0赞

#### 文章目录 ####

*   *  QueryWrapper常用方法列表
     *  常用操作
     *   *  1.插入
         *  2.根据id获取
         *  3.删除
         *  4.根据id更新
         *  5.获取列表
         *  6.获取单条数据
     *  注意：
     *  写在最后

### QueryWrapper常用方法列表 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>例子</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>eq</td> 
   <td>等于(=)</td> 
   <td>queryWrapper.eq(“user_name”, “张三”); //user_name=“张三”</td> 
  </tr> 
  <tr> 
   <td>ne</td> 
   <td>不等于(&lt;&gt;)</td> 
   <td>queryWrapper.ne(“user_name”, “张三”); //user_name&lt;&gt;“张三”</td> 
  </tr> 
  <tr> 
   <td>gt</td> 
   <td>大于(&gt;)</td> 
   <td>queryWrapper.gt(“level”, 1); //level&gt;1</td> 
  </tr> 
  <tr> 
   <td>ge</td> 
   <td>大于等于(&gt;=)</td> 
   <td>queryWrapper.ge(“level”, 1); //level&gt;=1</td> 
  </tr> 
  <tr> 
   <td>lt</td> 
   <td>小于(&lt;)</td> 
   <td>queryWrapper.lt(“level”, 1); //level&lt;1</td> 
  </tr> 
  <tr> 
   <td>le</td> 
   <td>小于等于(&lt;=)</td> 
   <td>queryWrapper.le(“level”, 1); //level&lt;=1</td> 
  </tr> 
  <tr> 
   <td>between</td> 
   <td>值1和值2之间</td> 
   <td>queryWrapper.between(“level”, 1,10); //level&gt;=1 and level &lt;=10</td> 
  </tr> 
  <tr> 
   <td>notBetween</td> 
   <td>不在值1和值2之间</td> 
   <td>queryWrapper.notBetween(“level”, 1,10);//level&lt;1 or level &gt;10</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td>模糊匹配 like %值%</td> 
   <td>queryWrapper.like(“user_name”, “张三”); //user_name like “%张三%”</td> 
  </tr> 
  <tr> 
   <td>not like</td> 
   <td>不模糊匹配 not like %值%</td> 
   <td>queryWrapper.notLike(“user_name”, “张三”); //user_name not like “%张三%”</td> 
  </tr> 
  <tr> 
   <td>likeLeft</td> 
   <td>左模糊匹配 like %值</td> 
   <td>queryWrapper.likeLeft(“user_name”, “张三”); //user_name like “%张三”</td> 
  </tr> 
  <tr> 
   <td>likeRight</td> 
   <td>右模糊匹配 like 值%</td> 
   <td>queryWrapper.likeRight(“user_name”, “张三”); //user_name like “张三%”</td> 
  </tr> 
  <tr> 
   <td>isNull</td> 
   <td>字段 为空</td> 
   <td>queryWrapper.isNull(“user_name”);//user_name is null</td> 
  </tr> 
  <tr> 
   <td>isNotNull</td> 
   <td>字段 不为空</td> 
   <td>queryWrapper.isNotNull(“user_name”);//user_name is not null</td> 
  </tr> 
  <tr> 
   <td>in</td> 
   <td>字段 in (v0，v1，…)</td> 
   <td>queryWrapper.in(“user_name”, {“张三”,“李四”,“王五”}); // user_name in （“张三”,“李四”,“王五”）</td> 
  </tr> 
  <tr> 
   <td>notIn</td> 
   <td>字段 not in (v0，vl，…)</td> 
   <td>queryWrapper.notIn(“user_name”, {“张三”,“李四”,“王五”}); // user_name not in （“张三”,“李四”,“王五”）</td> 
  </tr> 
  <tr> 
   <td>inSql</td> 
   <td>字段 in ( sql语句)</td> 
   <td>queryWrapper.in(“user_name”, (select name from student where age&lt; 23)); // user_name in (select name from student where age&lt; 23)</td> 
  </tr> 
  <tr> 
   <td>notInSql</td> 
   <td>字段 not in ( sql语句)</td> 
   <td>queryWrapper.notIn(“user_name”, (select name from student where age&lt; 23)); // user_name not in (select name from student where age&lt; 23)</td> 
  </tr> 
  <tr> 
   <td>groupBy</td> 
   <td>分组:GROUP BY 字段</td> 
   <td>queryWrapper.groupBy(“user_name”);//group by user_name</td> 
  </tr> 
  <tr> 
   <td>orderByAsc</td> 
   <td>排序:ORDER BY 字段 ASC</td> 
   <td>queryWrapper.orderByAsc(“createTime”);//order by createTime asc</td> 
  </tr> 
  <tr> 
   <td>orderByDesc</td> 
   <td>排序:ORDER BY 字段 DESC</td> 
   <td>queryWrapper.orderByDesc(“createTime”);//order by createTime desc</td> 
  </tr> 
  <tr> 
   <td>orderBy</td> 
   <td>排序:ORDER BY 字段</td> 
   <td>queryWrapper.orderBy(true, true, “createTime”);//order by createTime asc</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>拼接 OR</td> 
   <td>说明:主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接);queryWrapper.eq(“id”, “1”).or().eq(“user_name”, “张三”); //id = 1 or name =‘张三’</td> 
  </tr> 
  <tr> 
   <td>and</td> 
   <td>拼接 AND</td> 
   <td>queryWrapper.eq(“id”, “1”).and(i-&gt;i.eq(“user_name”, “张三”)); //id = 1 and name =‘张三’</td> 
  </tr> 
  <tr> 
   <td>apply</td> 
   <td>拼接 sql</td> 
   <td>说明: 该方法可用于数据库函数动态入参的params;queryWrapper.eq(“id”, user.getId()).apply(“date_format(‘createTime’,‘%Y-%m-%d’) = {0}”,“2023-07-25”);//date_format(‘createTime’,'%Y-%m-%d) =‘2023-07-25’")</td> 
  </tr> 
  <tr> 
   <td>last</td> 
   <td>无视优化规则直接拼接到 sql 的最后</td> 
   <td>说明:只能调用一次,多次调用以最后一次为准 有sq1注入的风险,请谨慎使用queryWrapper.last(" limit 1");// limit 1</td> 
  </tr> 
  <tr> 
   <td>exists</td> 
   <td>拼接 EXISTS(sql语句)</td> 
   <td>queryWrapper.exists(“SELECT id FROM Student WHERE User.id = Student.userId”);// exists (“SELECT id FROM Student WHERE User.id = Student.userId”)</td> 
  </tr> 
  <tr> 
   <td>notExists</td> 
   <td>拼接 NOT EXISTS (sg1语句)</td> 
   <td>queryWrapper.notExists(“SELECT id FROM Student WHERE User.id = Student.userId”);// not exists (“SELECT id FROM Student WHERE User.id = Student.userId”)</td> 
  </tr> 
  <tr> 
   <td>nested</td> 
   <td>正常嵌套不带 AND或者 OR</td> 
   <td>queryWrapper.nested(i-&gt;i.eq(“id”,“1”).eq(“user_name”,“张三”));// id = 1 and user_name=“张三”</td> 
  </tr> 
  <tr> 
   <td>having</td> 
   <td>having ( sql语句)</td> 
   <td>queryWrapper.having(“sum(age) &gt; {0}”,10);//having sum(age) &gt; 10;</td> 
  </tr> 
 </tbody> 
</table>

### 常用操作 ###

#### 1.插入 ####

@Override
    public void save(User user) {
        userMapper.insert(user);
    }

#### 2.根据id获取 ####

@Override
    public User getById(String id) {
        return userMapper.selectById(id);
    }

#### 3.删除 ####

@Override
    public void removeById(String id) {
        userMapper.deleteById(id);
    }

#### 4.根据id更新 ####

userMapper.updateById(user);

#### 5.获取列表 ####

userMapper.selectList(queryWrapper);

#### 6.获取单条数据 ####