SprintBoot系列（十二）：JPA的分页和排序实战

分页查询，是互联网查询必备技能。因为可以减轻服务器的压力。分页查询一般分为两种方式

PC端：页码Page和每页条数Size

Mo端：Start开始数和Count每页条数

PC端的方式和Mo端（mobile）方式是可以互换的

下面提供两个方法，最后一种是最优的方案哟。

方法一原生代码分页：

1.1创建一个Bo的PageCounter类

在项目下创建一个package,并命名为bo

在bo下面创建一个类，并命名为PageCounter

package com.fcors.fcors.bo;

import lombok.Builder;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@Builder
public class PageCounter {

    private Integer page;
    private Integer count;
}

1.2创建一个公用方法处理类

在项目下创建一个package,并命名为util。在util下创建一个CommonUtil类

package com.fcors.fcors.util;

import com.fcors.fcors.bo.PageCounter;

public class CommonUtil {

    public static PageCounter converToPageParameter(Integer start,Integer count) {
        int pageNum = start / count;
        
        PageCounter pageCounter = PageCounter.builder()
                .page(pageNum)
                .count(count)
                .build();
        return pageCounter;

    }

}

1.3在vo下创建一个Paging类

package com.fcors.fcors.vo;

import lombok.*;
import org.springframework.data.domain.Page;

import java.util.List;

@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Paging<T> {
    private Long total;
    private Integer count;
    private Integer page;
    private Integer totalPage;
    private List<T> items;

    public Paging(Page<T> pageT) {
        this.initPageParameters(pageT);
        this.items = pageT.getContent();
    }

    void initPageParameters(Page<T> pageT){
        this.total = pageT.getTotalElements();
        this.count = pageT.getSize();
        this.page = pageT.getNumber();
        this.totalPage = pageT.getTotalPages();
    }

}

1.4修改Service

spuService接口

package com.fcors.fcors.service;

import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.SpuSimplifyVO;

import java.util.List;

public interface SpuService {
    SpuEntity findOneById(Long id);
    SpuSimplifyVO findOneByIdFiltter(Long id);
    Paging<SpuSimplifyVO> getLastPagingSpu(Integer start, Integer count);
}

1.5spuService类

package com.fcors.fcors.service;

import com.fcors.fcors.bo.PageCounter;
import com.fcors.fcors.model.SkuEntity;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.model.SpuImgEntity;
import com.fcors.fcors.repository.SpuRepository;
import com.fcors.fcors.util.CommonUtil;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.SpuSimplifyVO;
import com.github.dozermapper.core.DozerBeanMapperBuilder;
import com.github.dozermapper.core.Mapper;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
@Transactional
@Service
public class SpuServiceImpl implements SpuService{

    @Autowired
    SpuRepository spuRepository;

    @Override
    public SpuEntity findOneById(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);

        return spuEntity;
    }
    @Override
    public SpuSimplifyVO findOneByIdFiltter(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);
        SpuSimplifyVO vo = new SpuSimplifyVO();
        BeanUtils.copyProperties(spuEntity,vo);
        return vo;
    }
    @Override
    public Paging<SpuSimplifyVO> getLastPagingSpu(Integer pageNum,Integer size) {
        /* 传入分页参数*/
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities = this.spuRepository.findAll(page);
        /*重组paging,追加总数等信息*/
        Paging<SpuEntity> paging= new Paging<>(spuEntities);
        Mapper mapper = DozerBeanMapperBuilder.buildDefault();
        List<SpuSimplifyVO> vos = new ArrayList<>();
        paging.getItems().forEach(s->{
            SpuSimplifyVO vo = mapper.map(s,SpuSimplifyVO.class);
            vos.add(vo);
        });
        /*泛性使用builder(),记得在Paging上面添加＠builder注解*/
        Paging<SpuSimplifyVO> callback = Paging.<SpuSimplifyVO>builder()
                .total(paging.getTotal())
                .count(paging.getCount())
                .page(paging.getPage())
                .totalPage(paging.getTotalPage())
                .items(vos)
                .build();
        return callback;

        
    }
}

1.6修改Controller

package com.fcors.fcors.api.sample.v1;


import com.fcors.fcors.exception.NotFoundException;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.service.SpuService;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.SpuSimplifyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Positive;
import java.util.List;

@RestController
@RequestMapping("/spu/")
@Validated
public class SpuController {
    @Autowired
    private SpuService spuService;
    @GetMapping("/id/{id}/detail")
    public SpuEntity getOneByName(@PathVariable @Positive Long id){
        SpuEntity spu = spuService.findOneById(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }


    @GetMapping("/id/{id}/detailTest")
    public SpuSimplifyVO findOneByIdFiltter(@PathVariable @Positive Long id){
        SpuSimplifyVO spu = spuService.findOneByIdFiltter(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }

    @GetMapping("/latest")
    public Paging<SpuSimplifyVO> getLastPagingSpu(
            @RequestParam(defaultValue = "0") Integer start,
            @RequestParam(defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getLastPagingSpu(start,count);
        return SpuList;
    }
}

成功返回数据，但这个方法还不是最优方案。

方法二:引入PagingDozer类

保留demo1的1.1、1.2、1.3步骤

2.4在vo下面创建一个PagingDozer类

package com.fcors.fcors.vo;

import com.github.dozermapper.core.DozerBeanMapperBuilder;
import com.github.dozermapper.core.Mapper;
import org.springframework.data.domain.Page;

import java.util.ArrayList;
import java.util.List;

public class PagingDozer<T,K> extends Paging {
    @SuppressWarnings("unchecked")
    public PagingDozer(Page<T> pageT,Class<K> classK){
        this.initPageParameters(pageT);

        List<T> tList = pageT.getContent();
        Mapper mapper = DozerBeanMapperBuilder.buildDefault();
        List<K> voList  = new ArrayList<>();

        tList.forEach(t -> {
            K vo = mapper.map(t,classK);
            voList.add(vo);
        });
        this.setItems(voList);

    }

}

2.5修改service

2.5.1service接口

package com.fcors.fcors.service;

import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.PagingDozer;
import com.fcors.fcors.vo.SpuSimplifyVO;

import java.util.List;

public interface SpuService {
    SpuEntity findOneById(Long id);
    SpuSimplifyVO findOneByIdFiltter(Long id);
    Paging<SpuSimplifyVO> getLastPagingSpu(Integer start, Integer count);
    PagingDozer<SpuEntity,SpuSimplifyVO> getLastPagingSpunew(Integer pageNum, Integer size);
}

2.5.2service的方法类

package com.fcors.fcors.service;

import com.fcors.fcors.bo.PageCounter;
import com.fcors.fcors.model.SkuEntity;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.model.SpuImgEntity;
import com.fcors.fcors.repository.SpuRepository;
import com.fcors.fcors.util.CommonUtil;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.PagingDozer;
import com.fcors.fcors.vo.SpuSimplifyVO;
import com.github.dozermapper.core.DozerBeanMapperBuilder;
import com.github.dozermapper.core.Mapper;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
@Transactional
@Service
public class SpuServiceImpl implements SpuService{

    @Autowired
    SpuRepository spuRepository;

    @Override
    public SpuEntity findOneById(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);

        return spuEntity;
    }
    @Override
    public SpuSimplifyVO findOneByIdFiltter(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);
        SpuSimplifyVO vo = new SpuSimplifyVO();
        BeanUtils.copyProperties(spuEntity,vo);
        return vo;
    }
    @Override
    public Paging<SpuSimplifyVO> getLastPagingSpu(Integer pageNum,Integer size) {
        /* 传入分页参数*/
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities = this.spuRepository.findAll(page);
        /*重组paging,追加总数等信息*/
        Paging<SpuEntity> paging= new Paging<>(spuEntities);
        Mapper mapper = DozerBeanMapperBuilder.buildDefault();
        List<SpuSimplifyVO> vos = new ArrayList<>();
        paging.getItems().forEach(s->{
            SpuSimplifyVO vo = mapper.map(s,SpuSimplifyVO.class);
            vos.add(vo);
        });
        /*泛性使用builder(),记得在Paging上面添加＠builder注解*/
        Paging<SpuSimplifyVO> callback = Paging.<SpuSimplifyVO>builder()
                .total(paging.getTotal())
                .count(paging.getCount())
                .page(paging.getPage())
                .totalPage(paging.getTotalPage())
                .items(vos)
                .build();
        return callback;
    }


    @Override
    public PagingDozer<SpuEntity,SpuSimplifyVO> getLastPagingSpunew(Integer pageNum,Integer size) {
        /* 传入分页参数*/
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities = this.spuRepository.findAll(page);
        PagingDozer<SpuEntity,SpuSimplifyVO> pagingDozer = new PagingDozer<SpuEntity,SpuSimplifyVO>(spuEntities,SpuSimplifyVO.class);
        return pagingDozer;
    }
}

2.6 修改controller的代码

package com.fcors.fcors.api.sample.v1;


import com.fcors.fcors.exception.NotFoundException;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.service.SpuService;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.SpuSimplifyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Positive;
import java.util.List;

@RestController
@RequestMapping("/spu/")
@Validated
public class SpuController {
    @Autowired
    private SpuService spuService;
    @GetMapping("/id/{id}/detail")
    public SpuEntity getOneByName(@PathVariable @Positive Long id){
        SpuEntity spu = spuService.findOneById(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }


    @GetMapping("/id/{id}/detailTest")
    public SpuSimplifyVO findOneByIdFiltter(@PathVariable @Positive Long id){
        SpuSimplifyVO spu = spuService.findOneByIdFiltter(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }

    @GetMapping("/latest")
    public Paging<SpuSimplifyVO> getLastPagingSpu(
            @RequestParam(defaultValue = "0") Integer start,
            @RequestParam(defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getLastPagingSpu(start,count);
        return SpuList;
    }

    @GetMapping("/latestnew")
    public Paging<SpuSimplifyVO> getLastPagingSpunew(
            @RequestParam(defaultValue = "0") Integer start,
            @RequestParam(defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getLastPagingSpunew(start,count);
        return SpuList;
    }
}

分页完成，但尚未完成超出页的提醒设置。可以考虑在类中判断当前页与TotalPage，不符合则抛出异常。

分页实战demo3:根据分类返回SpuList

3.1在SpuRepository追加方法

package com.fcors.fcors.repository;

import com.fcors.fcors.model.SpuEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

/*JpaRepository<@value1,@value2>
 * @value1:model类名
 * @value:model的主键即表的主键
 * */
@Repository
public interface SpuRepository extends JpaRepository<SpuEntity,Long> {
    SpuEntity findOneById(Long id);

    Page<SpuEntity> findAll(Pageable page);

    Page<SpuEntity> findByCategoryId(Long id, Pageable page);

    Page<SpuEntity>  findByRootCategoryId(Long id,Pageable page);
}

3.2在service追加逻辑处理

3.2.1service的spu接口:getByCategory

package com.fcors.fcors.service;

import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.PagingDozer;
import com.fcors.fcors.vo.SpuSimplifyVO;

import java.util.List;

public interface SpuService {
    SpuEntity findOneById(Long id);
    SpuSimplifyVO findOneByIdFiltter(Long id);
    Paging<SpuSimplifyVO> getLastPagingSpu(Integer start, Integer count);
    Paging<SpuSimplifyVO> getLastPagingSpunew(Integer pageNum, Integer size);
    Paging<SpuSimplifyVO> getByCategory(Long cid,Boolean isRoot,Integer pageNum, Integer size);
}

3.2.2service的spu类

package com.fcors.fcors.service;

import com.fcors.fcors.bo.PageCounter;
import com.fcors.fcors.model.SkuEntity;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.model.SpuImgEntity;
import com.fcors.fcors.repository.SpuRepository;
import com.fcors.fcors.util.CommonUtil;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.PagingDozer;
import com.fcors.fcors.vo.SpuSimplifyVO;
import com.github.dozermapper.core.DozerBeanMapperBuilder;
import com.github.dozermapper.core.Mapper;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
@Transactional
@Service
public class SpuServiceImpl implements SpuService{

    @Autowired
    SpuRepository spuRepository;

    @Override
    public SpuEntity findOneById(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);

        return spuEntity;
    }

    @Override
    public PagingDozer<SpuEntity, SpuSimplifyVO> getByCategory(Long cid, Boolean isRoot, Integer pageNum, Integer size) {
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities;
        if(isRoot){
            spuEntities = this.spuRepository.findByRootCategoryId(cid,page);
        }else {
            spuEntities = this.spuRepository.findByCategoryId(cid,page);
        }
        PagingDozer<SpuEntity,SpuSimplifyVO> pagingDozer = new PagingDozer<SpuEntity,SpuSimplifyVO>(spuEntities,SpuSimplifyVO.class);
        return pagingDozer;
    }

    @Override
    public SpuSimplifyVO findOneByIdFiltter(Long id) {
        SpuEntity spuEntity = this.spuRepository.findOneById(id);
        SpuSimplifyVO vo = new SpuSimplifyVO();
        BeanUtils.copyProperties(spuEntity,vo);
        return vo;
    }
    @Override
    public Paging<SpuSimplifyVO> getLastPagingSpu(Integer pageNum,Integer size) {
        /* 传入分页参数*/
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities = this.spuRepository.findAll(page);
        /*重组paging,追加总数等信息*/
        Paging<SpuEntity> paging= new Paging<>(spuEntities);
        Mapper mapper = DozerBeanMapperBuilder.buildDefault();
        List<SpuSimplifyVO> vos = new ArrayList<>();
        paging.getItems().forEach(s->{
            SpuSimplifyVO vo = mapper.map(s,SpuSimplifyVO.class);
            vos.add(vo);
        });
        /*泛性使用builder(),记得在Paging上面添加＠builder注解*/
        Paging<SpuSimplifyVO> callback = Paging.<SpuSimplifyVO>builder()
                .total(paging.getTotal())
                .count(paging.getCount())
                .page(paging.getPage())
                .totalPage(paging.getTotalPage())
                .items(vos)
                .build();
        return callback;
    }


    @Override
    public PagingDozer<SpuEntity,SpuSimplifyVO> getLastPagingSpunew(Integer pageNum,Integer size) {
        /* 传入分页参数*/
        Pageable page = PageRequest.of(pageNum, size, Sort.by("id").descending());
        Page<SpuEntity> spuEntities = this.spuRepository.findAll(page);
        PagingDozer<SpuEntity,SpuSimplifyVO> pagingDozer = new PagingDozer<SpuEntity,SpuSimplifyVO>(spuEntities,SpuSimplifyVO.class);
        return pagingDozer;
    }
}

3.3在controller中追加访问入口

package com.fcors.fcors.api.sample.v1;


import com.fcors.fcors.exception.NotFoundException;
import com.fcors.fcors.model.SpuEntity;
import com.fcors.fcors.service.SpuService;
import com.fcors.fcors.vo.Paging;
import com.fcors.fcors.vo.SpuSimplifyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Positive;
import java.util.List;

@RestController
@RequestMapping("/spu/")
@Validated
public class SpuController {
    @Autowired
    private SpuService spuService;
    @GetMapping("/id/{id}/detail")
    public SpuEntity getOneByName(@PathVariable @Positive Long id){
        SpuEntity spu = spuService.findOneById(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }


    @GetMapping("/id/{id}/detailTest")
    public SpuSimplifyVO findOneByIdFiltter(@PathVariable @Positive Long id){
        SpuSimplifyVO spu = spuService.findOneByIdFiltter(id);
        if(spu ==null){
            throw new NotFoundException(30003);
        }
        return spu;
    }

    @GetMapping("/latest")
    public Paging<SpuSimplifyVO> getLastPagingSpu(
            @RequestParam(defaultValue = "0") Integer start,
            @RequestParam(defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getLastPagingSpu(start,count);
        return SpuList;
    }

    @GetMapping("/latestnew")
    public Paging<SpuSimplifyVO> getLastPagingSpunew(
            @RequestParam(defaultValue = "0") Integer start,
            @RequestParam(defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getLastPagingSpunew(start,count);
        return SpuList;
    }

    @GetMapping("/by/category/{id}")
    public Paging<SpuSimplifyVO> getByCategoryId(
            @PathVariable @Positive Long id,
            @RequestParam(name = "is_root", defaultValue = "false") Boolean isRoot,
            @RequestParam(name = "start", defaultValue = "0") Integer start,
            @RequestParam(name = "count", defaultValue = "10") Integer count
    )
    {
        Paging<SpuSimplifyVO> SpuList = this.spuService.getByCategory(id,isRoot,start,count);
        return SpuList;
    }

}

http://192.168.8.10:8082/api/sample//v1/spu/by/category/32?start=0&count=2&is_root=false

http://192.168.8.10:8082/api/sample//v1/spu/by/category/32?start=0&count=2

http://192.168.8.10:8082/api/sample//v1/spu/by/category/2?start=0&count=2&is_root=true

Parameter value [2] did not match expected type [java.lang.Integer (n/a)]

在测试中发现这个问题，因为参数定义的是Long型，然后model的rootCategoryId是Integer。类型不匹配，修改方法则是：修改model的类型

下面开始讲解JPA的json和list

http://www.fcors.com/%e6%8a%80%e6%9c%af%e4%b8%8e%e6%a1%86%e6%9e%b6/sprintboot%e7%b3%bb%e5%88%97%ef%bc%88%e5%8d%81%e4%b8%89%ef%bc%89%ef%bc%9ajpa%e7%9a%84json%e4%b8%8elist%e6%98%a0%e5%b0%84/