点击这里 > mongoTemplate结合Criteria实现分页、模糊、二级嵌套和时间范围查询等
自带分页api
pageNum:当前页,pageSize:页大小
query.skip((pageNum - 1) * pageSize);
query.limit(pageSize);
当数据量大的时候查询效率降低,所以进行优化设计
自定义分页
1、实现Pageable接口
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import javax.validation.constraints.Min;
/**
* 自定义分页实现
*/
@NoArgsConstructor
@AllArgsConstructor
public class SpringDataPageable implements Pageable {
/** 当前页 */
@Min(1)
private int pageNumber;
/** 页大小*/
@Min(1)
private int pageSize;
/** 排序设置 */
private Sort sort;
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
}
@Override
public int getPageNumber() {
return this.pageNumber;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
@Override
public int getPageSize() {
return this.pageSize;
}
@Override
public long getOffset() {
return (getPageNumber() - 1) * getPageSize();
}
public void setSort(Sort sort) {
this.sort = sort;
}
@Override
public Sort getSort() {
return this.sort;
}
@Override
public Pageable next() {
return null;
}
@Override
public Pageable previousOrFirst() {
return null;
}
@Override
public Pageable first() {
return null;
}
@Override
public Pageable withPage(int i) {
return null;
}
@Override
public boolean hasPrevious() {
return false;
}
}
2、使用自定义分页
@Transactional
public Result<PageResult<SubjectInfo>> list(SubjectInfoDTO subjectInfoDTO){
Criteria criteria = new Criteria();
// 模糊查询
if (ObjectUtils.isNotEmpty(subjectInfoDTO.getTitle())) {
criteria.and("title").regex(subjectInfoDTO.getTitle());
}
// 设置排序
Sort sort = Sort.by(Sort.Direction.ASC,"sort");
Query query = new Query(criteria).with(sort);
// 总数
long count = subjectInfoDao.count(query);
// 分页条件
SpringDataPageable pageable = new SpringDataPageable();
pageable.setPageNumber(subjectInfoDTO.getPageNum());
pageable.setPageSize(subjectInfoDTO.getPageSize());
pageable.setSort(sort);
List<SubjectInfo> list = subjectInfoDao.list(query.with(pageable));
PageResult<SubjectInfo> pageResult = new PageResult<>();
pageResult.setTotal((int)count);
pageResult.setList(list);
return new Result<PageResult<SubjectInfo>>().ok(pageResult);
}
优化方案:
使用情况:在_id自增或者自定义生成的_id有序的情况下
public Result<Map<String, Object>> list(Map<String, Object> params) {
// 查询条件
String name = MapUtils.getString(params, "name");
Criteria criteria = new Criteria();
if (ObjectUtils.isNotEmpty(name)){
criteria.and("name").regex(name);
}
Query query = new Query(criteria).with(Sort.by(Sort.Direction.DESC,"_id"));
long count = personDao.count(query);
getPage(params, query);
List<Person> list = personDao.list(query);
Map<String, Object> result = new HashMap<>();
result.put("total", (int)count);
result.put("list", list);
return new Result<Map<String, Object>>().ok(result);
}
// 优化
private void getPage(Map<String, Object> params, Query query) {
int pageNum = MapUtils.getInteger(params, "pageNum");
int pageSize = MapUtils.getInteger(params, "pageSize");
// 查询优化
if (pageNum > 1) {
// number参数是为了取上一页的最后一条数据
int number = (pageNum - 1) * pageSize;
query.limit(number);
List<Person> lists = personDao.list(query);
// 取出最后一条数据
Person person = lists.get(lists.size() - 1);
// 取出id当作条件查询后面的数据
String id = person.getId();
// 从上一页最后一条开始查(小于不包括当前条)
query.addCriteria(Criteria.where("_id").lt(id));
}
query.limit(pageSize);
}