【Mybatis-Plus】条件参数查询手册

超、凢脫俗 2023-02-12 07:26 76阅读 0赞

**【引言】**

使用mybatis-plus框架的基础上，直接使用其中的条件参数进行查询还是很方便的。但每次使用到像大于、不等于这样一些不常用条件时，都需要现查，所以记录在这篇博客里，当作一个自己的查询手册。

**【手册】**

<table> 
 <thead> 
  <tr> 
   <th>查询方式</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>select</td> 
   <td>设置查询字段</td> 
  </tr> 
  <tr> 
   <td>and</td> 
   <td>AND 语句，拼接 + AND (字段=值)</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>OR 语句，拼接 + OR (字段=值)</td> 
  </tr> 
  <tr> 
   <td>eq</td> 
   <td>等于=</td> 
  </tr> 
  <tr> 
   <td>allEq</td> 
   <td>基于 map 内容等于=</td> 
  </tr> 
  <tr> 
   <td>ne</td> 
   <td>不等于&lt;&gt;</td> 
  </tr> 
  <tr> 
   <td>gt</td> 
   <td>大于&gt;</td> 
  </tr> 
  <tr> 
   <td>ge</td> 
   <td>大于等于&gt;=</td> 
  </tr> 
  <tr> 
   <td>lt</td> 
   <td>小于&lt;</td> 
  </tr> 
  <tr> 
   <td>le</td> 
   <td>小于等于&lt;=</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td>模糊查询</td> 
  </tr> 
  <tr> 
   <td>notLike</td> 
   <td>模糊查询 NOT LIKE</td> 
  </tr> 
  <tr> 
   <td>in</td> 
   <td>IN 查询</td> 
  </tr> 
  <tr> 
   <td>notIn</td> 
   <td>NOT IN 查询</td> 
  </tr> 
  <tr> 
   <td>isNull</td> 
   <td>NULL 值查询</td> 
  </tr> 
  <tr> 
   <td>isNotNull</td> 
   <td>IS NOT NULL</td> 
  </tr> 
  <tr> 
   <td>groupBy</td> 
   <td>分组 GROUP BY</td> 
  </tr> 
  <tr> 
   <td>having</td> 
   <td>HAVING 关键词</td> 
  </tr> 
  <tr> 
   <td>orderBy</td> 
   <td>排序 ORDER BY</td> 
  </tr> 
  <tr> 
   <td>orderAsc</td> 
   <td>ASC 排序 ORDER BY</td> 
  </tr> 
  <tr> 
   <td>orderDesc</td> 
   <td>DESC 排序 ORDER BY</td> 
  </tr> 
  <tr> 
   <td>exists</td> 
   <td>EXISTS 条件语句</td> 
  </tr> 
  <tr> 
   <td>notExists</td> 
   <td>NOT EXISTS 条件语句</td> 
  </tr> 
  <tr> 
   <td>between</td> 
   <td>BETWEEN 条件语句</td> 
  </tr> 
  <tr> 
   <td>notBetween</td> 
   <td>NOT BETWEEN 条件语句</td> 
  </tr> 
  <tr> 
   <td>last</td> 
   <td>拼接在最后，例如：last(“LIMIT 1”)</td> 
  </tr> 
 </tbody> 
</table>

**【示例】**

之前[《Mybatis-Plus 3.X 条件查询》][Mybatis-Plus 3.X]博客中，也写过一些查询的使用，下面总结几个其中没有提到的，使用的mybatis-plus版本是3.1.1。

*  **select查询指定字段**

代码使用：

//查询作者和编码字段，返回Article中其他字段的值均为null
    public Article searchOne(Integer id) {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.select(Article::getAuthor,Article::getCode).eq(Article::getId,id);
        return articleMapper.selectOne(queryWrapper);
    }

sql打印：  
![在这里插入图片描述][20200525153417563.png]

*  **and和or：并且或者条件**

代码使用：

public List<Article> searchMore(String keywords) {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Article::getCatId,10);
        queryWrapper.and(x->x.like(Article::getKeywords,keywords).or().like(Article::getTitle,keywords));
        return articleMapper.selectList(queryWrapper);
    }

sql打印：  
![在这里插入图片描述][20200525153953609.png]

*  **ge：大于等于条件**

代码使用：

//查询条件：访问量大于等于100
    public List<Article> searchByCondition() {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        //大于等于
        queryWrapper.ge(Article::getVisits,100);
        //查询指定字段
        queryWrapper.select(Article::getAuthor,Article::getCode,Article::getTitle,Article::getVisits);
        return articleMapper.selectList(queryWrapper);
    }

sql打印：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70]

*  **in：批量条件**

代码使用：

//栏目Id属于10和20的
    public List<Article> searchByCondition() {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        //in
        Long[] catId = {10L,20L};
        List<Long> catList = Arrays.asList(catId);
        queryWrapper.in(Article::getCatId,catList);
        //查询指定字段
        queryWrapper.select(Article::getAuthor,Article::getCode,Article::getTitle,Article::getVisits);
        return articleMapper.selectList(queryWrapper);
    }

sql打印:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70 1]

*  **between：范围条件**

代码使用：

//查询发布时间在2020-05-01至2020-06-25
    public List<Article> searchByCondition() {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        //between
        queryWrapper.between(Article::getPublishTime, LocalDate.of(2020,5,1),LocalDate.now().plusMonths(1));
        //查询指定字段
        queryWrapper.select(Article::getAuthor,Article::getCode,Article::getTitle,Article::getVisits);
        return articleMapper.selectList(queryWrapper);
    }

sql打印：  
![在这里插入图片描述][20200525163033116.png]

*  **order：排序条件**

代码使用：

//查询指定栏目下所有，并按访问量和创建时间排序
    public List<Article> searchByCondition() {
        LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Article::getCatId,20);
        //查询指定字段
        queryWrapper.select(Article::getAuthor,Article::getCode,Article::getTitle,Article::getVisits);
        //按访问量和创建时间排序
        queryWrapper.orderByDesc(Article::getVisits).orderByAsc(Article::getCreateTime);
        return articleMapper.selectList(queryWrapper);
    }

sql打印：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70 2]

[Mybatis-Plus 3.X]: https://blog.csdn.net/hu_zhiting/article/details/104923533
[20200525153417563.png]: https://img-blog.csdnimg.cn/20200525153417563.png
[20200525153953609.png]: https://img-blog.csdnimg.cn/20200525153953609.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200525160714856.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200525162340972.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM=,size_16,color_FFFFFF,t_70
[20200525163033116.png]: https://img-blog.csdnimg.cn/20200525163033116.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200525164614986.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM=,size_16,color_FFFFFF,t_70