我们在从数据库中查询所有数据时，如果数据过多，前端显示就成了问题，所以需要进行进行分页。
分页又分为两种：前端分页和后端分页
- 前端分页：将后端传来的所有数据进行分页显示（实际上所有数据都在内存中，也叫假分页）
- 后端分页：每次点击下一页都会发送一次请求，每次请求一定量的数据（比如10条）,相当于每次数据真的是只有10条
我们可以来将前后端都实现分页，实现真正的分页（前端加载时就渲染10条数据，每次点击页码数或者下一页，都会发送一次请求，得到相对于页的数据）
1.环境
后端单表查询利器：MyBatis-Plus
前端表格插件：Datatables
MySQL8
SpringBoot
Thymeleaf
2.数据库表关系
3.插入测试数据
insert into academy values (null,'软件学院');
insert into department values (null,'软件工程',1);
insert into building values (null,1);
insert into building values (null,0);
insert into building values (null,0);
insert into building values (null,1);
insert into building values (null,1);
insert into building values (null,1);
insert into dormitory values (101,null,1,1,6,null);
insert into dormitory values (101,null,2,1,6,null);
insert into dormitory values (101,null,3,1,6,null);
insert into dormitory values (101,null,4,1,6,null);
insert into dormitory values (101,null,5,1,6,null);
insert into dormitory values (101,null,6,1,6,null);
insert into dormitory values (304,null,1,1,6,null);
insert into dormitory values (305,null,1,3,6,null);
insert into dormitory values (306,null,1,2,6,null);
insert into dormitory values (304,null,2,2,6,null);
insert into property values (null,'桌子','1',304,50.0,1);
insert into property values (null,'椅子','1',304,30.0,1);
insert into property values (null,'桌子','1',305,50.0,1);
insert into property values (null,'椅子','1',305,30.0,0);
insert into property values (null,'桌子','1',306,50.0,1);
insert into property values (null,'窗帘','1',304,100.0,1);
insert into property values (null,'暖瓶','1',304,40.0,1);
insert into property values (null,'窗帘','1',305,100.0,1);
insert into property values (null,'暖瓶','1',305,40.0,0);
insert into property values (null,'窗帘','1',306,100.0,1);
insert into property values (null,'门','1',306,150.0,1);
insert into property values (null,'灯管','1',306,60.0,1);
insert into property values (null,'簸箕','1',306,9.99,1);
insert into property values (null,'簸箕','2',304,9.99,1);
insert into role values (null,'管理员');
insert into administrator values ('2018006509','manster','bebd3785c6ff7d5f60573bcbde9281f1',1);
4.配置分页
1.配置主类
@SpringBootApplication
@MapperScan("cn.tyut.ks.dao")
public class KsApplication {
public static void main(String[] args) {
SpringApplication.run(KsApplication.class, args);
}
}
2.配置分页插件
package cn.tyut.ks.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @Author manster
* @Date 2021/3/1
**/
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
3.实体类
package cn.tyut.ks.entity;
import lombok.Data;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* 财产
*
* @author
*/
@Data
public class Property implements Serializable {
private Integer id;
/**
* 财产名
*/
private String name;
/**
* 宿舍号
*/
private Integer did;
/**
* 财产价格
*/
private BigDecimal price;
/**
* 财产状态，有正常与故障两种
*/
private String state;
private static final long serialVersionUID = 1L;
}
5.测试分页
package cn.tyut.ks.mapper;
import cn.tyut.ks.dao.PropertyMapper;
import cn.tyut.ks.entity.Property;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
/**
* @Author manster
* @Date 2021/3/2
**/
@SpringBootTest
public class PropertyMapperTest {
@Autowired
private PropertyMapper propertyMapper;
@Test
public void selectPage(){
LambdaQueryWrapper<Property> userLambdaQueryWrapper = Wrappers.lambdaQuery();
userLambdaQueryWrapper.like(Property::getName , "桌");
Page<Property> propertyPage = new Page<>(1 , 2);
IPage<Property> propertyIPage = propertyMapper.selectPage(propertyPage , userLambdaQueryWrapper);
System.out.println("总页数： "+propertyIPage.getPages());
System.out.println("总记录数： "+propertyIPage.getTotal());
propertyIPage.getRecords().forEach(System.out::println);
}
}
总页数： 2
总记录数： 3
Property(id=1, name=桌子, did=304, price=50.00, state=1)
Property(id=3, name=桌子, did=305, price=50.00, state=1)
查询全部进行分页,就是将 queryWrapper 置为 null
@Test
public void selectPage(){
Page<Property> propertyPage = new Page<>(1 , 2);
IPage<Property> propertyIPage = propertyMapper.selectPage(propertyPage , null);
System.out.println("总页数： "+propertyIPage.getPages());
System.out.println("总记录数： "+propertyIPage.getTotal());
propertyIPage.getRecords().forEach(System.out::println);
}
总页数： 6
总记录数： 12
Property(id=1, name=桌子, did=304, price=50.00, state=1)
Property(id=2, name=椅子, did=304, price=30.00, state=1)
6.实现分页
1.html
<table id="mytable" class="table table-bordered table-hover">
<thead>
<tr>
<th>财产ID</th>
<th>财产名</th>
<th>宿舍号</th>
<th>财产价格</th>
<th>财产状态</th>
<th>操作</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr>
<th>财产ID</th>
<th>财产名</th>
<th>宿舍号</th>
<th>财产价格</th>
<th>财产状态</th>
<th>操作</th>
</tr>
</tfoot>
</table>
<script>
var _datatable;
$(function () {
var _columns = [
{"data": "id"},
{"data": "name"},
{"data": "did"},
{
"data": "price",
"render": function (data, type, full){
return data.toFixed(2);
}
},
{
"data": "state",
"render": function (data, type, full) {
switch (data) {
case '1':
return "正常";
break;
case '0':
return "故障";
break;
}
}
},
{
"data": function (row, type, val, meta) {
var _id = row.id;
var editUrl = "/property/edit?id=" + _id;
var deleteUrl = "/property/delete?id=" + _id;
return '<a href="/property/edit?id=' + _id + '" type="button" class="btn btn-sm btn-primary"><i class="fa fa-edit">修改</i></a> '
+ '<a href="/property/delete?id=' + _id + '" type="button" class="btn btn-sm btn-danger" ><i class="fa fa-trash">删除</i></button></a>';
}
}
];
_datatable = App.initDataTables("/property/page",_columns);
});
function search(){
var did = $("#did").val();
var param = {
"did": did
};
_datatable.settings()[0].ajax.data = param;
_datatable.ajax.reload();
}
</script>
2.app.js
var App = function () {
/**
* 初始化DataTables
*/
var handlerInitDataTables = function (url,columns) {
var _datatable = $("#mytable").DataTable({
"paging": true,
"lengthChange": false,
"searching": false,
"ordering": false,
"info": true,
"processing": true,
"autoWidth": false,
"responsive": true,
"language": {
"sProcessing": "处理中...",
"sLengthMenu": "显示 _MENU_ 项结果",
"sZeroRecords": "没有匹配结果",
"sInfo": "显示第 _START_ 至 _END_ 项结果，共 _TOTAL_ 项",
"sInfoEmpty": "显示第 0 至 0 项结果，共 0 项",
"sInfoFiltered": "(由 _MAX_ 项结果过滤)",
"sInfoPostFix": "",
"sSearch": "搜索:",
"sUrl": "",
"sEmptyTable": "表中数据为空",
"sLoadingRecords": "载入中...",
"sInfoThousands": ",",
"oPaginate": {
"sFirst": "首页",
"sPrevious": "上页",
"sNext": "下页",
"sLast": "末页"
},
"oAria": {
"sSortAscending": ": 以升序排列此列",
"sSortDescending": ": 以降序排列此列"
}
},
"serverSide": true,
"ajax": {
"url": url,
"type": "post"
},
"columns": columns,
"language": {
"sProcessing": "处理中...",
"sLengthMenu": "显示 _MENU_ 项结果",
"sZeroRecords": "没有匹配结果",
"sInfo": "显示第 _START_ 至 _END_ 项结果，共 _TOTAL_ 项",
"sInfoEmpty": "显示第 0 至 0 项结果，共 0 项",
"sInfoFiltered": "(由 _MAX_ 项结果过滤)",
"sInfoPostFix": "",
"sSearch": "搜索:",
"sUrl": "",
"sEmptyTable": "表中数据为空",
"sLoadingRecords": "载入中...",
"sInfoThousands": ",",
"oPaginate": {
"sFirst": "首页",
"sPrevious": "上页",
"sNext": "下页",
"sLast": "末页"
},
"oAria": {
"sSortAscending": ": 以升序排列此列",
"sSortDescending": ": 以降序排列此列"
}
},
});
return _datatable;
};
return {
/**
* 初始化datatables
* @param url
* @param columns
* @returns {jQuery}
*/
initDataTables: function (url,columns) {
return handlerInitDataTables(url,columns);
},
}
}();
3.PropertyController
/**
* 查询全部数据
* @param draw 请求次数
* @param start 其实数据记录
* @param length 页面大小
* @param did 如果有值则为搜索
* @return
*/
@ResponseBody
@RequestMapping(value = "page", method = RequestMethod.POST)
public PageUtil<Property> page(Integer draw, Integer start, Integer length, String did){
//datatable服务传回的数据为start为第几条，而mybatisplus要的是页码，这里转换一下
int index = 1;
if(start > 0){
index += start/length;
}
IPage<Property> propertyIPage;
if(StringUtils.hasText(did)){
propertyIPage = propertyService.searchPage(Integer.parseInt(did), index, length);
}else {
propertyIPage = propertyService.allPage(index, length);
}
pageUtil = new PageUtil();
pageUtil.setDraw(draw);
pageUtil.setData(propertyIPage.getRecords());
pageUtil.setRecordsFiltered(propertyIPage.getTotal());
pageUtil.setRecordsTotal(propertyIPage.getTotal());
return pageUtil;
}
4.PageUtil
@Data
public class PageUtil<T> {
private int draw;//请求次数
private long recordsFiltered; //过滤后总数
private long recordsTotal; //记录总数
private List<T> data; //分页查询到的数据
}
5.PropertyServiceImpl
/**
* 实现分页
* @param index 页码
* @param size 页面数据量
* @return
*/
public IPage<Property> allPage(long index, long size) {
Page<Property> propertyPage = new Page<>(index , size);
IPage<Property> propertyIPage = propertyMapper.selectPage(propertyPage , null);
return propertyIPage;
}
/**
* 实现分页搜索
* @param did 宿舍号
* @param index 页码
* @param size 每页数据量
* @return
*/
@Override
public IPage<Property> searchPage(int did, long index, long size) {
QueryWrapper<Property> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("did",did);
Page<Property> propertyPage = new Page<>(index , size);
IPage<Property> propertyIPage = propertyMapper.selectPage(propertyPage , queryWrapper);
return propertyIPage;
}