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条数据
3.3.2以后的版本,PaginationInterceptor取消,新的类是PaginationInnerInterceptor,充当了以前PaginationInterceptor的功能,这里也有maxLimit参数,功能和上面的相同
page类也增加了maxLimit参数,默认是null
这里的逻辑和以前的稍微不同,当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是很耗时的。
所以对于不需要获取count的情况,需要将此参数设置为false,但是因为该参数是protected,所以不能直接进行设置,可以继承Page新建一个自定义的Page,重写该参数即可。