spring boot 下 dataTable|pagehelper 组合进行分页筛选排序

1）Js 需提前引用 jquery.dataTables

    $(function () {

        //提示信息  初始化设置 一般不需要改
        var lang = {
            "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": "末页",
                "sJump": "跳转"
            },
            "oAria": {
                "sSortAscending": ": 以升序排列此列",
                "sSortDescending": ": 以降序排列此列"
            }
        };


        //重要修改 表格内容的自定义，需要根据业务定制
        var table = $(".table-sort").dataTable({
            language:lang,  //提示信息
            autoWidth: false,  //禁用自动调整列宽
            stripeClasses: ["odd", "even"],  //为奇偶行加上样式，兼容不支持CSS伪类的场合
            processing: true,  //隐藏加载提示,自行处理
            serverSide: true,  //启用服务器端分页
            searching: false,  //禁用原生搜索
            orderMulti: true,  //启用多列排序
            ordering:true,//使用排序
            bStateSave:true,//记录cookie
            columnDefs:[{
               // "targets": 'nosort',  //列的样式名
               // "orderable": false    //包含上样式名‘nosort’的禁止排序
                'targets' : [0,2,3,4,5,6,7,8],    //除第六，第七两列外，都默认不排序
                'orderable' : false,
            }],
            order: [],  //取消默认排序查询,否则复选框一列会出现小箭头
            renderer: "bootstrap",  //渲染样式：Bootstrap和jquery-ui
            pagingType: "simple_numbers",  //分页样式：simple,simple_numbers,full,full_numbers
            ajax: function (data, callback, settings) {

                //封装请求参数
                var param = {};
                param.pageSize= data.length;//页面显示记录条数，在页面显示每页显示多少项的时候
                param.startIndex = data.start;//开始的记录序号
                param.pageIndex = (data.start / data.length);//当前页码
                //自定义的参数
                param.type=$("select[name='type']").val();//获取要筛选的类别
                param.name=$("input[name='name']").val();//获取要筛选的名字
                //获取排序的参数
                console.log(data.order);
                param.order=data.order;
                //ajax请求数据方法
                $.ajax({
                    type: "GET",
                    url: "/query",//url请求的地址
                    cache: false,  //禁用缓存
                    data: param,  //传入组装的参数
                    dataType: "json",
                    success: function (result) {

                        //封装返回数据重要
                        var returnData = {};
                        //这里直接自行返回了draw计数器,应该由后台返回
                        returnData.draw = data.draw;
                        //返回数据全部记录
                        returnData.recordsTotal = result.totalCount;
                        //后台不实现过滤功能，每次查询均视作全部结果
                        returnData.recordsFiltered = result.totalCount;
                        //返回的数据列表
                        returnData.data = result.data;

                        //调用DataTables提供的callback方法，代表数据已封装完成并传回DataTables进行渲染
                        //此时的数据需确保正确无误，异常判断应在执行此回调前自行处理完毕
                        callback(returnData);
                    }
                });
            },
            //列表表头字段
            columns: [
                { "data": "id" },
                { "data": "id" },
                { "data": "name" },
                { "data": "icon" },
                { "data": "url" },
                { "data": "addtime" },
                { "data": "sort" },
                { "data": "remarks" },
                { "data": "id" },
            ],
            //新建列的 数据内容
            "createdRow": function ( row, data, index ) {
                //行渲染回调,在这里可以对该行dom元素进行任何操作
                var $btn = $('<div class="btn-group text-cen">'+
                    '<button type="button" class="btn btn-sm btn-primary btn-edit">修改</button>'+
                    '<button type="button" class="btn btn-sm btn-danger btn-del">删除</button>'+
                    '</div>'+
                    '</div>');
                $('td', row).eq(8).append($btn);
            }
        }).api();
        //此处需调用api()方法,否则返回的是JQuery对象而不是DataTables的API对象


        //查询按钮
        $("#SelectServer").click(function(){
            table.ajax.reload();
        });
    });

2)HTML 筛选部分

    <select name="type" class="select">
            <option value="-1">全部分类</option>
            <option value="0">父级</option>
            <option value="1">子集</option>
        </select>

   <input type="text" name="name" id="name" placeholder=" 菜单名称" style="width:250px" class="input-text">
   <button name="" id="SelectServer" class="btn btn-success" type="submit"><i class="Hui-iconfont">&#xe665;</i> 搜目录</button>

3)后台的数据库实体及表格需要的参数对象

admin_menu 表格可以按照下列字段新建

package com.example.springboot.model;

import java.util.Date;

public class admin_menu {
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getIcon() {
        return icon;
    }

    public void setIcon(String icon) {
        this.icon = icon;
    }

    public Date getAddtime() {
        return addtime;
    }

    public void setAddtime(Date addtime) {
        this.addtime = addtime;
    }

    public int getSort() {
        return sort;
    }

    public void setSort(int sort) {
        this.sort = sort;
    }

    public int getParentid() {
        return parentid;
    }

    public void setParentid(int parentid) {
        this.parentid = parentid;
    }

    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }

    public Boolean getIsabort() {
        return isabort;
    }

    public void setIsabort(Boolean isabort) {
        this.isabort = isabort;
    }

    private int id;

    private String name;

    private String url;

    private String icon;

    private Date addtime;

    private int sort;

    private int parentid;

    private String remarks;

    private Boolean isabort;

}

package com.example.springboot.model;

import java.math.BigInteger;
import java.util.List;

public class RtPageInfo {
    private List Data;
    private Integer PageNum;
    private Integer PageSize;

    public List getData() {
        return Data;
    }

    public void setData(List data) {
        Data = data;
    }

    public Integer getPageNum() {
        return PageNum;
    }

    public void setPageNum(Integer pageNum) {
        PageNum = pageNum;
    }

    public Integer getPageSize() {
        return PageSize;
    }

    public void setPageSize(Integer pageSize) {
        PageSize = pageSize;
    }

    public long getTotalCount() {
        return TotalCount;
    }

    public void setTotalCount(long totalCount) {
        TotalCount = totalCount;
    }

    private long TotalCount;

}

4）拼接的筛选Sql。(*^▽^*)

 //结构化sql
    @SelectProvider(type = UserDaoProvider.class, method = "findUserByName")
    List<admin_menu> FindAllWhere(Map<String, Object> map);

    class UserDaoProvider {
        public String findUserByName(Map<String, Object> map) {
            String sql = "SELECT * FROM admin_menu";
            Integer type=Integer.parseInt(map.get("type").toString()) ;
            String name=map.get("name")!=null?map.get("name").toString():null;
            if(type!=-1||name!=null){
                sql += " where ";
                int i=1;
                if(type!=-1){
                    sql += i!=1?"and ":"";
                    sql +="parentid !=0";//测试
                    i++;
                }
                if(name!=null){
                    sql += i!=1?"and ":"";
                    sql +="name LIKE  '%"+name+"%'";
                i++;
                }
            }

            return sql;
        }
    }

5)分页帮助类PageHelper的配置

package com.example.springboot.config;

import java.util.Properties;
  
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
  
import com.github.pagehelper.PageHelper;
  
@Configuration
public class PageHelperConfig {
  
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

6)Controller 返回需要的Json

 //查询
    @RequestMapping(value = "/query", method = RequestMethod.GET)
    @ResponseBody
    public RtPageInfo query(
            @RequestParam(required = false, name = "pageSize") Integer pageSize,
            @RequestParam(required = false, name = "startIndex") Integer startIndex,
            @RequestParam(required = false, name = "pageIndex") Integer pageIndex,
            @RequestParam(required = false, name = "type") Integer type,
            @RequestParam(required = false, name = "name") String name,
            HttpServletRequest req){
        //这里做查询操作
        //验证排序内容
        //int issort=Integer.parseInt(req.getParameter("order[0][column]")) ;
        //String  sortname=req.getParameter("order[0][dir]") ;
        PageHelper.startPage(startIndex,pageSize,"id desc");
        //正常无条件检索
        //List<admin_menu> cs=admin_menuMapper.findAll();
        //多条件检索 使用map K-V参数形式
        Map<String,Object> map=new HashMap<String,Object>();
        map.put("type",type);
        map.put("name",name);
        List<admin_menu> cs=admin_menuMapper.FindAllWhere(map);
        PageInfo<admin_menu> page = new PageInfo<>(cs);
        //返回DataTable使用
        RtPageInfo pageInfo = new RtPageInfo();
        pageInfo.setData(page.getList());//这里是数据内容 List
        pageInfo.setPageNum(startIndex/pageSize);//Integer
        pageInfo.setPageSize(pageSize);//pageSize
        pageInfo.setTotalCount(page.getTotal());//BigInteger
        return pageInfo ;
    }

7)pom.xml 的引用

    <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>

8)数据库结构随便建的