JQUERY datatables 分页取总记录数 datatables后端分页

转载

mob6454cc784c23 2023-11-23 13:32:46

文章标签 javascript jquery spring boot mybatis 分页 文章分类 jQuery 前端开发

我们在从数据库中查询所有数据时，如果数据过多，前端显示就成了问题，所以需要进行进行分页。

分页又分为两种：前端分页和后端分页

前端分页：将后端传来的所有数据进行分页显示（实际上所有数据都在内存中，也叫假分页）
后端分页：每次点击下一页都会发送一次请求，每次请求一定量的数据（比如10条）,相当于每次数据真的是只有10条

我们可以来将前后端都实现分页，实现真正的分页（前端加载时就渲染10条数据，每次点击页码数或者下一页，都会发送一次请求，得到相对于页的数据）

1.环境

后端单表查询利器：MyBatis-Plus

前端表格插件：Datatables

MySQL8

SpringBoot

Thymeleaf

2.数据库表关系

JQUERY datatables 分页取总记录数 datatables后端分页_javascript

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.实现分页

点击财产列表 PropertyController PropertyService PropertyMapper 打开页面请求页面信息'/property/list' 返回'propertylist'页面 ajax请求分页信息'/property/page' 调用allpage(Long index,Long size)方法调用selectPage(Page<Property> page,@Param Wrapper<Property> queryWarpper) 返回'IPage<Property> propertyIPage' 返回'IPage<Property> propertyIPage' 返回封装好的pageUtil(页面信息类) 点击财产列表 PropertyController PropertyService PropertyMapper

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;
    }

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。