前后端分页-java黑洞网

本站消息

站长简介/公众号

出租广告位,需要合作请联系站长

小光头吃饭不用愁

601

文章

303974

访问

+关注

分类

暂无分类

日期归档

2024-11(1)

前后端分页

发布于2021-05-29 21:20 阅读(800) 评论(0) 点赞(18) 收藏(1)

文章目录

使用pagehelper实现后端分页

如果你也在用Mybatis，建议尝试该分页插件，这一定是最方便使用的分页插件。分页插件支持任何复杂的表单、多表分页。

如果你使用Maven，你只需要在pom.xml中添加下面的依赖：

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>最新版本</version>
</dependency>

如果你使用Spring Boot，那么PageHelper-Spring-Boot-Starter帮助你集成分页插件到Spring Boot。

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

我们来看下对比下分页前、分页后，接口的查询结果。
为了便于查看sql的执行情况，将mapper层的日志级别调整至trace，即在application.properties中添加：

logging.level.com.jepcc.test.mapper = trace

分页前

在这里插入图片描述
关键日志如下：

c.j.t.m.EbookMapper.selectByExample      : ==>  Preparing: select id, name, category1_id, category2_id, description, cover, doc_count, view_count, vote_count from ebook
c.j.t.m.EbookMapper.selectByExample      : ==> Parameters: 
c.j.t.m.EbookMapper.selectByExample      : <==    Columns: id, name, category1_id, category2_id, description, cover, doc_count, view_count, vote_count
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 1, SpringBoot入门教程, null, null, 零基础入门Java，企业级应用开发最佳首选框架, /image/cover1.png, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 2, Vue入门教程, null, null, 零基础入门Vue，企业级应用开发最佳首选框架, /image/cover2.png, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 3, Python入门教程, null, null, 零基础入门Python，企业级应用开发最佳首选框架, null, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 4, Mysql入门教程, null, null, 零基础入门Mysql，企业级应用开发最佳首选框架, null, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 5, Oracle入门教程, null, null, 零基础入门Oracle，企业级应用开发最佳首选框架, null, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==      Total: 5

分页后

在com.jepcc.test.service.EbookService类中添加分页代码：

package com.jepcc.test.service;

import com.github.pagehelper.PageHelper;
import com.jepcc.test.mapper.EbookMapper;
import com.jepcc.test.model.Ebook;
import com.jepcc.test.model.EbookExample;
import com.jepcc.test.req.EbookReq;
import com.jepcc.test.resp.EbookResp;
import com.jepcc.test.util.CopyUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.List;


@Service
public class EbookService {
    @Autowired
    private EbookMapper ebookMapper;
    public List<EbookResp> list(EbookReq req){

        EbookExample ebookExample = new EbookExample();
        EbookExample.Criteria criteria = ebookExample.or();
        if(!ObjectUtils.isEmpty(req.getName())){
            criteria.andNameLike("%"+req.getName()+"%");
        }
        PageHelper.startPage(1,3);
        List<Ebook> ebookList = ebookMapper.selectByExample(ebookExample);

        return CopyUtil.copyList(ebookList,EbookResp.class);
    }
}

在这里插入图片描述

关键日志如下：

c.j.t.m.E.selectByExample_COUNT          : ==>  Preparing: SELECT count(0) FROM ebook
c.j.t.m.E.selectByExample_COUNT          : ==> Parameters: 
c.j.t.m.E.selectByExample_COUNT          : <==    Columns: count(0)
c.j.t.m.E.selectByExample_COUNT          : <==        Row: 5
c.j.t.m.E.selectByExample_COUNT          : <==      Total: 1
c.j.t.m.EbookMapper.selectByExample      : ==>  Preparing: select id, name, category1_id, category2_id, description, cover, doc_count, view_count, vote_count from ebook LIMIT ?
c.j.t.m.EbookMapper.selectByExample      : ==> Parameters: 3(Integer)
c.j.t.m.EbookMapper.selectByExample      : <==    Columns: id, name, category1_id, category2_id, description, cover, doc_count, view_count, vote_count
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 1, SpringBoot入门教程, null, null, 零基础入门Java，企业级应用开发最佳首选框架, /image/cover1.png, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 2, Vue入门教程, null, null, 零基础入门Vue，企业级应用开发最佳首选框架, /image/cover2.png, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==        Row: 3, Python入门教程, null, null, 零基础入门Python，企业级应用开发最佳首选框架, null, null, null, null
c.j.t.m.EbookMapper.selectByExample      : <==      Total: 3

分页前的sql查询相当于：select * from ebook；
分页后的sql查询相当于：首先查下全表共有多少条数据，select count(0) from ebook；然后取出当前页的前3条，select * from ebook limit 3;
在这里插入图片描述
也就是说，PageHelper自动帮我们做了：

执行select count(0) from ebook，查询全表数据总数；
添加limit，限制查询条数

刚刚分页时我们使用了PageHelper，还有个类我们也经常用到PageInfo，我们来看下用它。
在com.jepcc.test.service.EbookService添加：

package com.jepcc.test.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jepcc.test.mapper.EbookMapper;
import com.jepcc.test.model.Ebook;
import com.jepcc.test.model.EbookExample;
import com.jepcc.test.req.EbookReq;
import com.jepcc.test.resp.EbookResp;
import com.jepcc.test.util.CopyUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.List;


@Service
public class EbookService {
    private static final Logger LOG = LoggerFactory.getLogger(EbookService.class);
    @Autowired
    private EbookMapper ebookMapper;
    public List<EbookResp> list(EbookReq req){

        EbookExample ebookExample = new EbookExample();
        EbookExample.Criteria criteria = ebookExample.or();
        if(!ObjectUtils.isEmpty(req.getName())){
            criteria.andNameLike("%"+req.getName()+"%");
        }
        PageHelper.startPage(1,3);
        List<Ebook> ebookList = ebookMapper.selectByExample(ebookExample);

        PageInfo<Ebook> pageinfo = new PageInfo<>(ebookList);
        LOG.info("总条数：{}", pageinfo.getTotal());
        LOG.info("总页数：{}", pageinfo.getPages());


        return CopyUtil.copyList(ebookList,EbookResp.class);
    }
}

在这里插入图片描述

封装分页请求参数和返回参数

首先，封装请求参数，在req目录下新增类PageReq：

package com.jepcc.test.req;

public class PageReq {
    private int page;

    private int size;

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }

    @Override
    public String toString() {
        return "PageReq{" +
                "page=" + page +
                ", size=" + size +
                '}';
    }
}

新建好类PageReq后，让EbookReq继承PageReg，这样代码改动小，维护起来也更容易。
所以，接下来修改下com.jepcc.test.EbookService就可以了。

package com.jepcc.test.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jepcc.test.mapper.EbookMapper;
import com.jepcc.test.model.Ebook;
import com.jepcc.test.model.EbookExample;
import com.jepcc.test.req.EbookReq;
import com.jepcc.test.resp.EbookResp;
import com.jepcc.test.util.CopyUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.List;


@Service
public class EbookService {
    private static final Logger LOG = LoggerFactory.getLogger(EbookService.class);
    @Autowired
    private EbookMapper ebookMapper;
    public List<EbookResp> list(EbookReq req){

        EbookExample ebookExample = new EbookExample();
        EbookExample.Criteria criteria = ebookExample.or();
        if(!ObjectUtils.isEmpty(req.getName())){
            criteria.andNameLike("%"+req.getName()+"%");
        }

        PageHelper.startPage(req.getPage(),req.getSize());
        List<Ebook> ebookList = ebookMapper.selectByExample(ebookExample);

        PageInfo<Ebook> pageinfo = new PageInfo<>(ebookList);
        LOG.info("总条数：{}", pageinfo.getTotal());
        LOG.info("总页数：{}", pageinfo.getPages());


        return CopyUtil.copyList(ebookList,EbookResp.class);
    }
}

在这里插入图片描述
验证下：发送请求，调用接口，查看返回结果及日志。

我们再来封装返回参数并修改相关代码。
在resp目录下新建类PageResp：

package com.jepcc.test.resp;

import java.util.List;

public class PageResp<T> {
    private long total;

    private List<T> list;

    public long getTotal() {
        return total;
    }

    public void setTotal(long total) {
        this.total = total;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    @Override
    public String toString() {
        return "PageResp{" +
                "total=" + total +
                ", list=" + list +
                '}';
    }
}

并修改com.jepcc.test.service.EbookService和com.jepcc.test.controller.EbookController，

package com.jepcc.test.service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jepcc.test.mapper.EbookMapper;
import com.jepcc.test.model.Ebook;
import com.jepcc.test.model.EbookExample;
import com.jepcc.test.req.EbookReq;
import com.jepcc.test.resp.EbookResp;
import com.jepcc.test.resp.PageResp;
import com.jepcc.test.util.CopyUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.util.List;


@Service
public class EbookService {
    private static final Logger LOG = LoggerFactory.getLogger(EbookService.class);
    @Autowired
    private EbookMapper ebookMapper;
    public PageResp<EbookResp> list(EbookReq req){

        EbookExample ebookExample = new EbookExample();
        EbookExample.Criteria criteria = ebookExample.or();
        if(!ObjectUtils.isEmpty(req.getName())){
            criteria.andNameLike("%"+req.getName()+"%");
        }

        PageHelper.startPage(req.getPage(),req.getSize());
        List<Ebook> ebookList = ebookMapper.selectByExample(ebookExample);

        PageInfo<Ebook> pageinfo = new PageInfo<>(ebookList);

        List<EbookResp> list = CopyUtil.copyList(ebookList,EbookResp.class);
        PageResp<EbookResp> pageResp = new PageResp<>();
        pageResp.setTotal(pageinfo.getTotal());
        pageResp.setList(list);

        return pageResp;
    }
}

在这里插入图片描述

package com.jepcc.test.controller;

import com.jepcc.test.req.EbookReq;
import com.jepcc.test.resp.CommonResp;
import com.jepcc.test.resp.EbookResp;
import com.jepcc.test.resp.PageResp;
import com.jepcc.test.service.EbookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/ebook")
public class EbookController {
    @Autowired
    private EbookService ebookService;


    @GetMapping("/list")
    public CommonResp list(EbookReq req){
        CommonResp resp = new CommonResp();
        PageResp<EbookResp> list = ebookService.list(req);
        resp.setContent(list);
        return resp;
    }
}

在这里插入图片描述
验证下：

前端分页

这里要注意axios的使用，具体可参考这里。

修改Home.vue

<template>
  <a-layout>
    <a-layout-sider width="200" style="background: #fff">
      <a-menu
          mode="inline"
          v-model:selectedKeys="selectedKeys2"
          v-model:openKeys="openKeys"
          :style="{ height: '100%', borderRight: 0 }"
      >
        <a-sub-menu key="sub1">
          <template #title>
              <span>
                <user-outlined />
                subnav 1
              </span>
          </template>
          <a-menu-item key="1">option1</a-menu-item>
          <a-menu-item key="2">option2</a-menu-item>
          <a-menu-item key="3">option3</a-menu-item>
          <a-menu-item key="4">option4</a-menu-item>
        </a-sub-menu>
        <a-sub-menu key="sub2">
          <template #title>
              <span>
                <laptop-outlined />
                subnav 2
              </span>
          </template>
          <a-menu-item key="5">option5</a-menu-item>
          <a-menu-item key="6">option6</a-menu-item>
          <a-menu-item key="7">option7</a-menu-item>
          <a-menu-item key="8">option8</a-menu-item>
        </a-sub-menu>
        <a-sub-menu key="sub3">
          <template #title>
              <span>
                <notification-outlined />
                subnav 3
              </span>
          </template>
          <a-menu-item key="9">option9</a-menu-item>
          <a-menu-item key="10">option10</a-menu-item>
          <a-menu-item key="11">option11</a-menu-item>
          <a-menu-item key="12">option12</a-menu-item>
        </a-sub-menu>
      </a-menu>
    </a-layout-sider>
    <a-layout-content
        :style="{ background: '#fff', padding: '24px', margin: 0, minHeight: '280px' }"
    >
      <a-list item-layout="vertical" size="large" :data-source="ebooks" :grid="{ gutter: 16, column: 3 }">
        <template #renderItem="{ item }">
          <a-list-item key="item.title">
            <template #actions>
          <span v-for="{ type, text } in actions" :key="type">
            <component v-bind:is="type" style="margin-right: 8px" />
            {{ text }}
          </span>
            </template>
            <a-list-item-meta :description="item.description">
              <template #title>
                <a :href="item.href">{{ item.name }}</a>
              </template>
              <template #avatar><a-avatar :src="item.cover" /></template>
            </a-list-item-meta>
          </a-list-item>
        </template>
      </a-list>
    </a-layout-content>

  </a-layout>
</template>

<script lang="ts">
import { defineComponent,onMounted,ref } from 'vue';
import axios from 'axios';
import { StarOutlined, LikeOutlined, MessageOutlined } from '@ant-design/icons-vue';

export default defineComponent({
  name: 'Home',
  components: {
    StarOutlined,
    LikeOutlined,
    MessageOutlined
  },
  setup(){

    const actions: Record<string, string>[] = [
      { type: 'StarOutlined', text: '156' },
      { type: 'LikeOutlined', text: '156' },
      { type: 'MessageOutlined', text: '2' },
    ];
    const ebooks = ref();

    onMounted(() => {
      axios.get("/ebook/list",{
        params:{
          page:1,
          size:1000
        }
      }).then(resp => {
        ebooks.value = resp.data.content.list
      });
    })

    return {
      ebooks,
      actions
    };
  }
});
</script>

在这里插入图片描述

修改admin-ebook.vue

<template>
  <a-layout>
    <a-layout-content
        :style="{ background: '#fff', padding: '24px', margin: 0, minHeight: '280px' }"
    >
      <a-table :columns="columns" 
        :dataSource="ebooks"
        :row-key="record=>record.id"
        :pagination="pagination"
        :loading="loading"
        @change="handleTableChange">
        <template #cover="{text:cover}">
          <img v-if="cover" :src="cover" alt="avatar">
        </template>
        <template #action="{text,record}">
          <a-space>
            <a-button type="primary">编辑</a-button>
            <a-button type="danger">删除</a-button>
          </a-space>
        </template>
      </a-table>
    </a-layout-content>

  </a-layout>
</template>


<script lang="ts">
import { defineComponent,ref,onMounted } from 'vue';
import axios from "axios";
export default defineComponent({
  name:"AdminEbook",
  setup() {
    const ebooks = ref();
    const pagination = ref({
      current:1,
      pageSize:4,
      total:0
    });
    const loading = ref(false);
    const columns = [
      {
        title:"封面",
        dataIndex:"cover",
        slots:{customRender:'cover'}
      },
      {
        title:"名称",
        dataIndex:"name"
      },
      {
        title:"分类一",
        key:"category1Id",
        dataIndex:"category1Id"
      },
      {
        title:"分类二",
        // key:"category2Id",
        dataIndex:"category2Id"
      },
      {
        title:"文档数",
        dataIndex:"docCount"
      },
      {
        title:"阅读数",
        dataIndex:"viewCount"
      },
      {
        title:"点赞数",
        dataIndex:"voteCount"
      },
      {
        title:"操作",
        key:"action",
        // dataIndex:"action",
        slots:{customRender:'action'}

      }
    ];

    const handleQuery = (params:any) => {
      loading.value = true;
      axios.get("/ebook/list",{params:params}).then(response => {
        loading.value = false;
        const data = response.data.content;
        ebooks.value = data.list;
        
        pagination.value.total = data.total;
        pagination.value.current = params.page;
      })
    };

    const handleTableChange = (pagination:any) => {
      handleQuery({
        page:pagination.current,
        size:pagination.pageSize
      });
    }
    
    onMounted(() => {
      handleQuery({
        page:1,
        size:pagination.value.pageSize
      });
    })

    return {
      columns,
      ebooks,
      loading,
      pagination,
      handleTableChange
    }
  }
})
</script>


<style scoped>
img{
  width: 48px;
  height: 48px;
}
</style>