[introduction]

I wrote a blog before< [Spring Boot] Mybatis Plus 2.X condition query >, we know that the query methods of Mybatis Plus 2.X and 3.X are not the same. This blog will summarize the application of various query methods of 3.X.

[example]

Take the query of article table as an example to show the use of several different query methods:

1. Query all articles under corresponding column by article column number

public List<Article> searchByCatId(Integer catId) {
    QueryWrapper<Article> queryWrapper = new QueryWrapper<>();
    queryWrapper.lambda().eq(Article::getCatId,catId);
    List<Article> articles = articleMapper.selectList(queryWrapper);
    articles.forEach(x-> System.out.println("Article column No.:" + x.getCatId() + ",Article title:" + x.getTitle()));
    return articles;
}

In this case, QueryWrapper is used to construct query conditions, eq is used to implement corresponding column numbering conditions, and the selectList method returns all the articles meeting the conditions.

2. Query specific articles by article primary key id

public Article searchOne(Integer id) {
    LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.eq(Article::getId,id);
    Article article = articleMapper.selectOne(queryWrapper);
    return article;
}

The above code directly uses LambdaQueryWrapper to construct query conditions, eq to implement corresponding number query, and selectOne method to return specific article records.

When you use selectOne query, you need to note that there must be only one result. If there are multiple results, an error will be reported.

3. Fuzzy query all eligible articles by article keyword

public List<Article> searchMore(String keywords) {
   LambdaQueryWrapper<Article> queryWrapper = new LambdaQueryWrapper<>();
   queryWrapper.like(Article::getKeywords,keywords);
   List<Article> articles = articleMapper.selectList(queryWrapper);
   articles.forEach(x-> System.out.println("Article key words:" + x.getKeywords() + ",Article title:" + x.getTitle()));
   return articles;
}

The above code uses LambdaQueryWrapper to construct query conditions, like to implement pre and post fuzzy conditions, and selectList method to return all qualified article results.

4. Use Map to construct query conditions, and accurately query according to the query article code

public List<Article> searchMoreByMap(String code) {
    Map<String,Object> queryMap = new HashMap<>();
    queryMap.put("code",code);
    List<Article> articles = articleMapper.selectByMap(queryMap);
    articles.forEach(x-> System.out.println("Article code:" + x.getCode() + ",Article title:" + x.getTitle()));
    return articles;
}

We can use map to construct the condition, the database column name corresponding to key, and the value corresponding to the value of database field. Note that the query condition here is equal to, and the select map method can realize accurate query.

5. Query all articles not deleted in pages

public PageResult<Article> searchArticlePage(Integer page, Integer size) {
    IPage<Article> articleIPage = new Page<>(page,size);
    QueryWrapper<Article> articleQueryWrapper = new QueryWrapper<>();
    articleQueryWrapper.lambda().eq(Article::getIsDeleted,0);
    IPage<Article> iPage = articleMapper.selectPage(articleIPage, articleQueryWrapper);
    PageResult<Article> pageResult = new PageResult<>();
    pageResult.setItems(iPage.getRecords());
    pageResult.setTotal(iPage.getTotal());
    System.out.println("Total articles:" + pageResult.getTotal());
    return pageResult;
}

Construct the IPage object, pass in the current page and the quantity of each page, QueryWrapper construct the query conditions, and select Page method to implement paging query.

When using paging, there is a problem. The Total number returned is 0. The solution is to remove the reference and add a mybatis plus paging plug-in configuration if pagehelper is referenced

The code is as follows:

@Configuration
public class MybatisPlusConfig {
     /**
     *   mybatis-plus Paging plugins
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor page = new PaginationInterceptor();
        page.setDialectType("mysql");
        return page;
    } 
}

The above code is in the generation code configuration project of version 3.X integrated before. Address: Mybatis Plus 3.X query application