BootStrap table服务端分页
涉及到的内容：
1.bootstrap-table插件；
2.mybatisplus分页查询；
3.spring封装对象匹配bootstrap-table插件格式；
4.sql查询隐藏手机号中间四位。
让我们开始：
1.想要实现bootstrap 表格服务端分页，首先需要使用插件
bootstrap-table.min.css
bootstrap-table.min.js
bootstrap-table-zh-CN.js
页面上的引用是这样的
index.html
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Bootstrap表格</title>
<script src="https://cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script>
<!-- Bootstrap -->
<!-- 最新版本的 Bootstrap 核心 CSS 文件 -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" >
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js"></script>
<link href="css/bootstrap-table.min.css" rel="stylesheet">
<script src="js/bootstrap-table.min.js"></script>
<script src="js/bootstrap-table-zh-CN.js"></script>
<script src="js/bootstrap-table-object.js"></script>
<script src="js/index.js"></script>
</head>
<body>
<h1>表格</h1>
<div class="container">
<div class="">
<table class="table table-striped table-bordered table-hover" id="userTable">
</table>
<div class="clear"></div>
</div>
</div>
</body>
</html>
index.js
var MgrUser = { id: "userTable",//表格id seItem: null, //选中的条目 table: null, layerIndex: -1 }; /** * 初始化表格的列 */ MgrUser.initColumn = function () { var columns = [ {title: 'id', field: 'id', visible: false, align: 'center', valign: 'middle'}, {title: '姓名', field: 'user_name', align: 'center', valign: 'middle', sortable: true}, {title: '电话', field: 'mobile', align: 'center', valign: 'middle', sortable: true}, {title: '时间', field: 'last_login_time', align: 'center', valign: 'middle', sortable: true}, {title: '状态', field: 'status', align: 'center', valign: 'middle', sortable: true}]; return columns; }; $(function () { var defaultColunms = MgrUser.initColumn(); var table = new BSTable("userTable", "http://localhost/abc/def", defaultColunms); table.setPaginationType("server"); MgrUser.table = table.init(); });
bootstrap-table-object.js
/** * 初始化 BootStrap Table 的封装 * * 约定：toolbar的id为 (bstableId + "Toolbar") * * @author fengshuonan */ (function () { var BSTable = function (bstableId, url, columns) { this.btInstance = null; //jquery和BootStrapTable绑定的对象 this.bstableId = bstableId; this.url = url; this.method = "post"; this.paginationType = "server"; //默认分页方式是服务器分页,可选项"client" this.toolbarId = bstableId + "Toolbar"; this.columns = columns; this.height = 665; //默认表格高度665 this.data = {}; this.queryParams = {}; // 向后台传递的自定义参数 }; BSTable.prototype = { /** * 初始化bootstrap table */ init: function () { var tableId = this.bstableId; var me = this; this.btInstance = $('#' + tableId).bootstrapTable({ contentType: "application/x-www-form-urlencoded", url: this.url, //请求地址 method: this.method, //ajax方式,post还是get ajaxOptions: { //ajax请求的附带参数 data: this.data }, /* responseHandler: function (res) {//获取多级json return res.data.records }, */ toolbar: "#" + this.toolbarId,//顶部工具条 striped: true, //是否显示行间隔色 cache: false, //是否使用缓存,默认为true sortable: true, //是否启用排序 sortOrder: "desc", //排序方式 pageNumber: 1, //初始化加载第一页，默认第一页 pageSize: 14, //每页的记录行数（*） pageList: [14, 50, 100], //可供选择的每页的行数（*） queryParamsType: 'limit', //默认值为 'limit' ,在默认情况下 传给服务端的参数为：offset,limit,sort queryParams: function (param) { return $.extend(me.queryParams, param); }, // 向后台传递的自定义参数 sidePagination: this.paginationType, //分页方式：client客户端分页，server服务端分页（*） search: false, //是否显示表格搜索，此搜索是客户端搜索，不会进服务端 strictSearch: true, //设置为 true启用 全匹配搜索，否则为模糊搜索 showColumns: true, //是否显示所有的列 showRefresh: true, //是否显示刷新按钮 minimumCountColumns: 2, //最少允许的列数 clickToSelect: true, //是否启用点击选中行 searchOnEnterKey: true, //设置为 true时，按回车触发搜索方法，否则自动触发搜索方法 columns: this.columns, //列数组 pagination: true, //是否显示分页条 height: this.height, icons: { refresh: 'glyphicon-repeat', toggle: 'glyphicon-list-alt', columns: 'glyphicon-list' }, iconSize: 'outline' }); return this; }, /** * 向后台传递的自定义参数 * @param param */ setQueryParams: function (param) { this.queryParams = param; }, /** * 设置分页方式：server 或者 client */ setPaginationType: function (type) { this.paginationType = type; }, /** * 设置ajax post请求时候附带的参数 */ set: function (key, value) { if (typeof key == "object") { for (var i in key) { if (typeof i == "function") continue; this.data[i] = key[i]; } } else { this.data[key] = (typeof value == "undefined") ? $("#" + key).val() : value; } return this; }, /** * 设置ajax post请求时候附带的参数 */ setData: function (data) { this.data = data; return this; }, /** * 清空ajax post请求参数 */ clear: function () { this.data = {}; return this; }, /** * 刷新 bootstrap 表格 * Refresh the remote server data, * you can set {silent: true} to refresh the data silently, * and set {url: newUrl} to change the url. * To supply query params specific to this request, set {query: {foo: 'bar'}} */ refresh: function (parms) { if (typeof parms != "undefined") { this.btInstance.bootstrapTable('refresh', parms); } else { this.btInstance.bootstrapTable('refresh'); } } }; window.BSTable = BSTable; }());
2.mybatisplus分页
代码片段：
Page<UserInfo> page = new PageFactory<UserInfo>().defaultPage(); EntityWrapper<UserInfo> entityWrapper = new EntityWrapper<UserInfo>(); entityWrapper.setSqlSelect("user_name","insert(mobile, 4, 4, '****') as mobile","IF(status = 0,'未签到','已签到') as status","last_login_time"); entityWrapper.eq("manager_tel",managerTel); Page<Map<String, Object>> userPages = userInfoService.selectMapsPage(page,entityWrapper); return new PageInfoBT<>(userPages);
其中，PageFactory类是用来接收bootstrap-table传递的参数：
public class PageFactory<T> { public Page<T> defaultPage() { HttpServletRequest request = HttpContext.getRequest(); int limit = Integer.valueOf(request.getParameter("limit")); //每页多少条数据 int offset = Integer.valueOf(request.getParameter("offset")); //每页的偏移量(本页当前有多少条) String sort = request.getParameter("sort"); //排序字段名称 String order = request.getParameter("order"); //asc或desc(升序或降序) if (ToolUtil.isEmpty(sort)) { Page<T> page = new Page<>((offset / limit + 1), limit); page.setOpenSort(false); return page; } else { Page<T> page = new Page<>((offset / limit + 1), limit, sort); if (Order.ASC.getDes().equals(order)) { page.setAsc(true); } else { page.setAsc(false); } return page; } } }
使用mybatisplus的好处就是可以不写sql，像这样：
EntityWrapper<UserInfo> entityWrapper = new EntityWrapper<UserInfo>(); entityWrapper.setSqlSelect("user_name","insert(mobile, 4, 4, '****') as mobile","IF(status = 0,'未签到','已签到') as status","last_login_time"); entityWrapper.eq("manager_tel",managerTel); Page<Map<String, Object>> userPages = userInfoService.selectMapsPage(page,entityWrapper);
3.spring封装对象匹配bootstrap-table格式
PageInfoBT类用来封装对象：
public class PageInfoBT<T> { // 结果集 private List<T> rows; // 总数 private long total; public PageInfoBT(Page<T> page) { this.rows = page.getRecords(); this.total = page.getTotal(); } public List<T> getRows() { return rows; } public void setRows(List<T> rows) { this.rows = rows; } public long getTotal() { return total; } public void setTotal(long total) { this.total = total; } }
返回的数据格式：
{ "rows":[ { "last_login_time":"2022-11-10 19:20:10", "user_name":"王非", "mobile":"130****2222", "status":"已签到" }, { "last_login_time":"2022-11-10 19:23:44", "user_name":"王晓晨", "mobile":"130****3333", "status":"已签到" } ], "total":"18" }
4.sql查询隐藏手机号中间四位：
SELECT username, insert(mobile, 4, 4, '****'), IF(status = 0,'未签到','已签到') as status, time FROM user
