这重写SimpleJpaRepository的findAll方法实现分页改造
最近项目中提出一个需求,在查询的页码大于总页码数时,将查询页改成最后一页。
如> 总页数10,查询页数传的是11时,查询结果时间返回第10页数据。
主要是为了防止有多人操作时,删和查同时进行的问题。
背景;
项目中关于数据库查询都是使用JPA的接口
分页查询使用JPA中的findAll 传参 Pageable
查看源码不难发现
JPA findAll 操作实际上分为以下几步
- 获取pageNumber 和pageSize 获取limit 的范围【start,end】
- 执行语句 select 查询字段 from table where 条件 limit start,end 获取到具体字段数据
- 执行 select count(id) from table where 条件 获取页数和总记录数
如果不重写findAll方法,我们需要自己在每次分页查询前先进行count运算,计算出当前请求的页码是不是超过总页数,如果超过将请求页码改为最后一页。
这样做有几个弊端:
- 每次做分页都要进行count,代码量增加,麻烦,还有可能忘记处理。当前项目要求所有分页查业务数据的地方都做处理
- JPA的findAll分页查询的方法本身就有count查询,自己在service里再写count,导致多次进行SQL查询,无疑会降低查询效率
首先看一下SimpleJpaRepository 源码
public Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable) {
// 获取查询条件和查询语句
TypedQuery<T> query = getQuery(spec, pageable);
// 获取查询结果、总页数、总记录数封装成Page返回
return isUnpaged(pageable) ? new PageImpl<T>(query.getResultList())
: readPage(query, getDomainClass(), pageable, spec);
}
修改分页操作,重点看readPage函数,实际上重写的是readPage的函数
/**
* Reads the given {@link TypedQuery} into a {@link Page} applying the given {@link Pageable} and
* {@link Specification}.
*
* @param query must not be {@literal null}.
* @param domainClass must not be {@literal null}.
* @param spec can be {@literal null}.
* @param pageable can be {@literal null}.
* @return
*/
protected <S extends T> Page<S> readPage(TypedQuery<S> query, final Class<S> domainClass, Pageable pageable,
@Nullable Specification<S> spec) {
// 此处设置limit范围
if (pageable.isPaged()) {
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
}
return PageableExecutionUtils.getPage(query.getResultList(), pageable,
() -> executeCountQuery(getCountQuery(spec, domainClass)));
}
query.getResultList() 查询获取sql语句的数据结果
先查询结果,再执行executeCountQuery 进行count运算获取总页数和总记录数
query对应实现类 AbstractProducedQuery
@Override
@SuppressWarnings("unchecked")
public QueryImplementor setFirstResult(int startPosition) {
getProducer().checkOpen();
if ( startPosition < 0 ) {
throw new IllegalArgumentException( "first-result value cannot be negative : " + startPosition );
}
queryOptions.setFirstRow( startPosition );
return this;
}
@Override
@SuppressWarnings("unchecked")
public QueryImplementor setMaxResults(int maxResult) {
getProducer().checkOpen();
if ( maxResult < 0 ) {
throw new IllegalArgumentException( "max-results cannot be negative" );
}
else {
queryOptions.setMaxRows( maxResult );
}
return this;
}
回到需求,我们要做的有几个地方:
- 调整SQL语句的顺序,将count的sql语句提前,先计算总记录数和总页数,再查数据记录
- 判断当前页码是否超出总页数,并处理
- 将查询数据的SQL的limit范围值修改
2和3归根结底就是要改变pageable的pageNumber
修改后的主要方法如下;
protected <S extends T> Page<S> readPage(TypedQuery<S> query, final Class<S> domainClass, Pageable pageable,
@Nullable Specification<S> spec) {
long counNum = executeCountQuery(getCountQuery(spec, domainClass));
int pageNumber = pageable.getPageNumber();
int pageSize = pageable.getPageSize();
if (pageable.isPaged()) {
// 如果传入页码大于总页数,跳转到最后一页
if(pageable.getOffset()>0 && pageNumber*pageSize>counNum) {
pint lastPageNum = (int) (counNum%pageSize==0? counNum/pageSize-1: counNum/pageSize);
Pageable newPageable = PageRequest.of(lastPageNum, pageSize, pageable.getSort());
pageable = newPageable;
}
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
}
return PageableExecutionUtils.getPage(query.getResultList(), pageable,
() -> counNum);
}
要修改的地方已经确定
接下来的问题就是,怎么让该Springboot项目的所有JPA接口默认用这个方法呢
首先创建一个类继承SimpleJpaRepository然后按以上方式重写findAll方法,修改readPage相关逻辑
如
public class PageSimpleJpaRepository<T,ID> extends SimpleJpaRepository<T, ID>{
public PageSimpleJpaRepository(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
super(entityInformation, entityManager);
}
public PageSimpleJpaRepository(Class<T> domainClass, EntityManager em) {
super(domainClass, em);
}
/*
* (non-Javadoc)
* @see org.springframework.data.repository.PagingAndSortingRepository#findAll(org.springframework.data.domain.Pageable)
*/
@Override
public Page<T> findAll(Pageable pageable) {
if (isUnpaged(pageable)) {
return new PageImpl<T>(findAll());
}
return findAll((Specification<T>) null, pageable);
}
/**
* 重写findAll方法
*/
@Override
public Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable){
TypedQuery<T> query = getQuery(spec, pageable);
return isUnpaged(pageable) ? new PageImpl<T>(query.getResultList())
: readPage(query, getDomainClass(), pageable, spec);
}
private static boolean isUnpaged(Pageable pageable) {
return pageable.isUnpaged();
}
/**
*
* 重写方法,如果传入页码大于总页数,跳转到查询最后一页
* Reads the given {@link TypedQuery} into a {@link Page} applying the given {@link Pageable} and
* {@link Specification}.
*
* @param query must not be {@literal null}.
* @param domainClass must not be {@literal null}.
* @param spec can be {@literal null}.
* @param pageable can be {@literal null}.
* @return
*/
protected <S extends T> Page<S> readPage(TypedQuery<S> query, final Class<S> domainClass, Pageable pageable,
@Nullable Specification<S> spec) {
long counNum = executeCountQuery(getCountQuery(spec, domainClass));
int pageNumber = pageable.getPageNumber();
int pageSize = pageable.getPageSize();
if (pageable.isPaged()) {
// 如果传入页码大于总页数,跳转到最后一页
if(pageable.getOffset()>0 && pageNumber*pageSize>counNum) {
int lastPageNum = (int) (counNum%pageSize==0? counNum/pageSize-1: counNum/pageSize);
Pageable newPageable = PageRequest.of(lastPageNum, pageSize, pageable.getSort());
pageable = newPageable;
}
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
}
return PageableExecutionUtils.getPage(query.getResultList(), pageable,
() -> counNum);
}
/**
* Executes a count query and transparently sums up all values returned.
*
* @param query must not be {@literal null}.
* @return
*/
private static long executeCountQuery(TypedQuery<Long> query) {
Assert.notNull(query, "TypedQuery must not be null!");
List<Long> totals = query.getResultList();
long total = 0L;
for (Long element : totals) {
total += element == null ? 0 : element;
}
return total;
}
}
然后在配置类或者启动类上利用注解@EnableJpaRepositories 指定repositoryBaseClass
@EnableJpaRepositories(
repositoryBaseClass = PageSimpleJpaRepository.class,
………其他配置………
)
之后所有的findAll的接口方法都会指向PageSimpleJpaRepository 里的findAll方法。
完善
上面的类发现拷贝了很多父类的私有和保护类的方法,其实没必要,仔细看看源码还是有公共方法的,当时时间赶,粗心了
public Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable) {
TypedQuery<T> query = getQuery(spec, pageable);
// the current Pageable does not contain pagination information
if (pageable.isUnpaged()) {
return new PageImpl<T>(query.getResultList());
}
Long totalSize = super.count(spec);
int pageNumber = pageable.getPageNumber();
int pageSize = pageable.getPageSize();
// pageNumber is error
if (pageable.getOffset() > 0 && pageNumber * pageSize > totalSize) {
// reset pageNumber
int resetPageNumber = (int) (totalSize / pageSize);
if (totalSize > 0 && 0 == totalSize % pageSize) {
resetPageNumber--;
}
pageable = PageRequest.of(resetPageNumber, pageSize, pageable.getSort());
}
// normal
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
return PageableExecutionUtils.getPage(query.getResultList(), pageable, () -> totalSize);
}
今天刚改完,还没测好,不知道是否有坑,如果写的不对,望留言指出,多谢
若有问题后续持续更新……