当前位置: 首页>后端>正文

mybatisplus分页插件踩坑

1.如何使用

MyBatis Plus自带分页插件(即BaseMapper接口中的selectPage()方法),只要简单的配置即可实现分页功能,具体步骤如下:

1. 配置分页插件

maven依赖

<dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
         <version>3.4.2</version>
 </dependency>
/**
 * MyBatisPlus 分页插件配置
 *
 * @Author
 * @Date 2022-05-04 14:41
 **/
@Configuration
public class MyBatisPlusPaginationInnerConfig {

    /**
     * 分页插件(官网最新)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

2.编写分页代码

package com.alibaba.fetchdata.bidao.model;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.alibaba.fetchdata.common.file.csv.ExcludeColumn;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

@Data
@TableName(value = "card")
@NoArgsConstructor
public class CardDO{

    @TableField(value = "id")
    @ExcludeColumn
    private Integer id;

    @TableField(value = "cd_id")
    private String cdId;

    @TableField(value = "dom_id")
    private String domId;

    @TableField(value = "p_id")
    private String pId;

    @TableField(value = "ds_id")
    private String dsId;

    @TableField(value = "u_id")
    private String uId;

    @TableField(value = "cd_type")
    private Integer cdType;

    @TableField(value = "chart_type")
    private Integer chartType;

    @TableField(value = "name")
    private String name;

    @TableField(value = "display_type")
    private Integer displayType;

    @TableField(value = "is_del")
    private Integer isDel;

    @TableField(value = "ctime")
    private Date ctime;

    @TableField(value = "utime")
    private Date utime;

}

package com.alibaba.fetchdata.bidao.DAO;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.alibaba.fetchdata.bidao.model.CardDO;

public interface CardDAO extends BaseMapper<CardDO> {
}

package com.alibaba.fetchdata.service.repository.bi;

import com.alibaba.fetchdata.bidao.DAO.CardDAO;
import com.alibaba.fetchdata.bidao.enums.TableNameEnum;
import com.alibaba.fetchdata.bidao.model.CardDO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class CardRepository extends BIBaseRepository<CardDO, CardDAO>{

    @Autowired
    public CardRepository(CardDAO cardDAO) {
        super(cardDAO);
    }

    @Override
    public String getTableName() {
        return TableNameEnum.CARD.getName();
    }
}

package com.alibaba.fetchdata.service.repository.bi;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.Lists;
import com.alibaba.fetchdata.service.repository.bi.I.BIBaseRepositoryI;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import java.util.List;

public abstract class BIBaseRepository<T, V extends BaseMapper> extends BIBase<V> implements BIBaseRepositoryI<T> {

    public BIBaseRepository(V dao) {
        this.dao = dao;
    }

    @Override
    public List<T> getDatasByTimeRangePage(String startTime, String endTime, int page, int pageSize) throws Exception{
        startTime = handleStartTime(startTime, endTime);
        Wrapper wrapper = buildQueryWrapper(startTime, endTime);
        Page mPage = new Page<>(page, pageSize);
        IPage DOIPage = dao.selectPage(mPage, wrapper);
        List DOList = DOIPage.getRecords();
        if(CollectionUtils.isEmpty(DOList)){
            return Lists.newArrayList();
        }

        return DOList;
    }

    private Wrapper buildQueryWrapper(String startTime, String endTime){
        QueryWrapper wrapper = new QueryWrapper<>();
        if(StringUtils.isNotBlank(startTime)){
            wrapper.ge(queryFieldName, startTime);
        }
        wrapper.le(StringUtils.isNotBlank(endTime), queryFieldName, endTime);
        wrapper.orderByAsc("id");

        return wrapper;
    }

    public String handleStartTime(String startTime, String endTime) {
        return null;
    }
}
package com.alibaba.fetchdata.service.repository.bi.I;
import java.util.List;
import com.alibaba.fetchdata.service.repository.bi.BaseRepository;

public interface BIBaseRepositoryI<T> extends BaseRepository {

    /**
     * 分页
     * @param startTime
     * @param endTime
     * @param page
     * @param pageSize
     * @return
     */
    List<T> getDatasByTimeRangePage(String startTime, String endTime, int page, int pageSize) throws Exception;

    /**
     * 是否需要分页
     * @return
     */
    default boolean isNeedPagination(){
        return true;
    }
}
package com.alibaba.fetchdata.service.repository.bi;

public interface BaseRepository {
    /**
     * 获取对应的csv文件名
     * @return
     */
    String getTableName();
}

2.实际项目遇到的问题

1.pageSize设置无效问题

Page mPage = new Page<>(page, pageSize);

mybatis-plus-boot-starter插件3.3.2以前(包括)版本,PaginationInterceptor插件如果不设置maxLimit(限制pageSize的上限),则默认是500,当分页的pageSize大于500时,则也只能查询出500条数据


mybatisplus分页插件踩坑,第1张

mybatisplus分页插件踩坑,第2张

mybatisplus分页插件踩坑,第3张

3.3.2以后的版本,PaginationInterceptor取消,新的类是PaginationInnerInterceptor,充当了以前PaginationInterceptor的功能,这里也有maxLimit参数,功能和上面的相同


mybatisplus分页插件踩坑,第4张
mybatisplus分页插件踩坑,第5张

page类也增加了maxLimit参数,默认是null


mybatisplus分页插件踩坑,第6张

这里的逻辑和以前的稍微不同,当Page mPage = new Page<>(page, pageSize)时mPage默认的maxLimit为null,maxLimit上限先取mPage设置的maxLimit,当maxLimit没设置时,取PaginationInnerInterceptor设置的maxLimit,如果都没设置,则不做pageSize限制;当maxLimit不为null时,而且pageSize>maxLimit时pageSize=maxLimit,否则取pageSize设置的值。所以新版本要想限制maxLimit,可以在Page或者PaginationInnerInterceptor设置,不设置则默认是不限制,老版本和新版本这个地方有点不太一样。

2.性能问题

使用分页插件一定得注意,分页插件默认是会查询满足条件的记录总数的,如果数据表很大,对于关系型数据库(特别是MySQL),count是很耗时的。


mybatisplus分页插件踩坑,第7张

mybatisplus分页插件踩坑,第8张

所以对于不需要获取count的情况,需要将此参数设置为false,但是因为该参数是protected,所以不能直接进行设置,可以继承Page新建一个自定义的Page,重写该参数即可。


https://www.xamrdz.com/backend/3st1941555.html

相关文章: