使用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>