Mysql实现分页显示

使用Mysql分页查询实现分页功能。

代码

1. IndexController.java
   前端需要传入两个参数，page(当前页面)和size(页面容量),先给他们一个初始值，分别为1和5，代表从第一页开始，页面容量为5

 public String index(HttpServletRequest request,
                        Model model,
                        @RequestParam(name="page",defaultValue = "1") Integer page,
                        @RequestParam(name="size",defaultValue = "5") Integer size){
        PaginationDTO pagination=questionService.list(page,size);
        model.addAttribute("pagination",pagination);
        return "index";
    }

2. questionService.java
   service层对数据进行查询，分页，最终返回一个对象给Controller

package com.listj.community.community.service;

import com.listj.community.community.dto.PaginationDTO;
import com.listj.community.community.dto.QuestionDTO;
import com.listj.community.community.mapper.QuestionMapper;
import com.listj.community.community.mapper.UserMapper;
import com.listj.community.community.model.Question;
import com.listj.community.community.model.User;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class QuestionService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private QuestionMapper questionMapper;

    public PaginationDTO list(Integer page, Integer size) {
        PaginationDTO paginationDTO = new PaginationDTO();
        Integer totalCount = questionMapper.count();
        paginationDTO.setPagination(totalCount,page,size);
        //防止越界
        if(page<1){
            page=1;
        }
        if(page>paginationDTO.getTotalPage()){
            page=paginationDTO.getTotalPage();
        }
        //计算偏移量size*(page-1)
        Integer offset =size*(page-1);
        List<Question> questions = questionMapper.list(offset,size);
        List<QuestionDTO> questionDTOList=new ArrayList<>();
        for(Question question : questions){
            User user=userMapper.findById(question.getCreator());
            QuestionDTO questionDTO = new QuestionDTO();
            BeanUtils.copyProperties(question,questionDTO);
            questionDTO.setUser(user);
            questionDTOList.add(questionDTO);
        }
        paginationDTO.setQuestions(questionDTOList);
        return paginationDTO;
    }
}

3. QuestionMapper.java
   对数据进行查询

    @Select("select * from question limit #{offset},#{size}")
    List<Question> list(@Param(value = "offset") Integer offset,@Param(value = "size") Integer size);

    @Select("select count(1) from question")
    Integer count();

3. PaginationDTO.java
   定义了分页栏中的各个属性，以及页面的跳转处理。

package com.listj.community.community.dto;

import lombok.Data;

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

@Data
public class PaginationDTO {
    private List<QuestionDTO> questions;
    private boolean showPrevious;
    private boolean showFirstPage;
    private boolean showNext;
    private boolean showEndPage;
    private Integer page;
    private List<Integer> pages=new ArrayList<>();
    private Integer totalPage;

    public void setPagination(Integer totalCount, Integer page, Integer size) {
        if(totalCount%size==0){
            totalPage=totalCount/size;
        }else{
            totalPage=totalCount/size+1;
        }

        if(page<1){
            page=1;
        }
        if(page>totalPage){
            page=totalPage;
        }
        this.page=page;

        pages.add(page);
        for(int i=1;i<=3;i++){
            if (page - i > 0){
                pages.add(0,page-i);
            }
            if (page + i <= totalPage ) {
                pages.add(page+i);
            }
        }

        //是否显示上一页
        if (page==1){
            showPrevious=false;
        }else{
            showPrevious=true;
        }
        //是否显示下一页
        if (page == totalPage) {
            showNext=false;
        }else{
            showNext=true;
        }

        //是否展示第一页
        if(pages.contains(1)){
            showFirstPage=false;
        }else{
            showFirstPage=true;
        }

        //是否展示最后一页
        if (pages.contains(totalPage)) {
            showEndPage=false;
        }else{
            showEndPage=true;
        }

    }
}

5. index.html
   前端使用了thyleaf组件

<nav aria-label="Page navigation">
                <ul class="pagination">
                    <li th:if="${pagination.showFirstPage}">
                        <a href="/?page=1" aria-label="Previous">
                            <span aria-hidden="true">&lt;&lt;</span>
                        </a>
                    </li>
                    <li th:if="${pagination.showPrevious}">
                        <a th:href="@{/(page=${pagination.page - 1})}" aria-label="Previous">
                            <span aria-hidden="true">&lt;</span>
                        </a>
                    </li>
                    <li th:each="page : ${pagination.pages}" th:class="${pagination.page==page}? 'active' : ''">
                        <a th:href="@{/(page=${page})}" th:text="${page}"></a></li>
                    <li th:if="${pagination.showNext}">
                        <a th:href="@{/(page=${pagination.page+1})}" aria-label="Previous">
                            <span aria-hidden="true">&gt;</span>
                        </a>
                    </li>
                    <li th:if="${pagination.showEndPage}">
                        <a th:href="@{/(page=${pagination.totalPage})}" aria-label="Previous">
                            <span aria-hidden="true">&gt;&gt;</span>
                        </a>
                    </li>
                </ul>
            </nav>

页面显示