bootstrap-table实现后端分页
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<%--
User: chaui
Date: 2018/10/05 0028
Time: 16:30
--%>
<!DOCTYPE html>
<html>
<head>
<base href="<%=basePath%>">
<meta charset="UTF-8">
<link href="assets/bootstrap/css/bootstrap.css" rel="stylesheet">
<link rel="stylesheet" href="assets/bootstraptable/bootstrap-table.css">
<link rel="stylesheet" href="assets/bootstrap/css/bootstrap-theme.css">
<link rel="stylesheet" href="assets/font-awesome/css/font-awesome.css">
<link rel="stylesheet" href="assets/layui/css/layui.css">
<link rel="stylesheet" href="assets/css/jquery.validator.min.css">
<link rel="stylesheet" type="text/css" href="assets/css/usermgr.css"/>
</head>
<body>
<div class="container-fluid">
<div class="SysUsermgr_container">
<div class="panel panel-default">
<div class="panel-body">
<div id="toolbar">
<div class="form-inline" role="form" style="">
<div class="form-group">
<label for="begintime">注册时间:</label>
<input readonly="readonly" type="text" name="begintime"
class="form-control"
id="begintime"
placeholder="开始时间">至
<input readonly="readonly" type="text" name="endtime"
class="form-control" id="endtime"
placeholder="结束时间">
</div>
<div class="form-group">
<label for="seachs">用户名:</label>
<input type="text" name="users.userName" class="form-control" id="seachs">
</div>
<button id="searchBtn" class="btn btn-primary btn-md">查询</button>
</div>
</div>
<div class="table-responsive">
<table id="table" class="table text-nowrap"
data-toolbar="#toolbar"
data-show-refresh="true"
data-show-columns="true"
data-query-params="queryParams"></table>
</div>
</div>
</div>
</div>
</div>
</body>
<script src="assets/js/jquery2.1.0.js"></script>
<script src="assets/bootstrap/js/bootstrap.js"></script>
<script src="assets/laydate/laydate.js"></script>
<script src="assets/layer/layer.js"></script>
<script src="assets/bootstraptable/bootstrap-table.js"></script>
<script src="assets/bootstraptable/locale/bootstrap-table-zh-CN.js"></script>
<script src="assets/js/jquery.validator.min.js"></script>
<script src="assets/js/zh-CN.min.js"></script>
<script>
$(function () {
var table = $('#table');
//更新状态
window.enableEvents = {
'click button': function (e, value, row, index) {
layer.confirm('确认' + (value ? "禁用" : "启用") + '?', {icon: 3, title: '请确认'}, function (index) {
var loadIndex = layer.load();
$.ajax({
type: "get",
dataType: "json",
url: "user/toggleEnable.action?users.userId=" + row.userId + "&users.enables=" + row.enables,
success: function (ret) {
if (ret == "true") {
layer.close(loadIndex);
layer.msg('操作成功', {icon: 1, time: 1000});
row['enables'] = !row['enables'];
table.bootstrapTable('updateRow', {
index: index,
row: row
});
refreshThisTable();
}
}
})
});
}
};
/*每次查询后刷新到第一页*/
$("#searchBtn").click(function () {
table.bootstrapTable('refresh');
table.bootstrapTable('selectPage', 1);
});
/*携带的参数,包括查询的输入框的值,还有最重要的是limit和offset*/
function queryParams(params) {
var param = {};
$('#toolbar').find('[name]').each(function () {
var value = $(this).val();
if (value != '') {
param[$(this).attr('name')] = value;
}
});
param['limit'] = params.limit; //页面大小(每页多少个)
param['rowoffset'] = params.offset; //页码,偏移量,相当于页数
return param;
}
/*刷新表格*/
function refreshThisTable() {
table.bootstrapTable('refresh');
}
/*表格显示*/
table.bootstrapTable({
pagination: true,
sidePagination: "server",
height: $(window).height() - 20,
queryParams: queryParams,
pageList: [10, 15, 20, 25, 30, 35, 40, 45, 50],
method: 'post', // 请求方式(*)post/get
contentType: "application/x-www-form-urlencoded",
url: "user/queryAllUserList.action",
showToggle: false, //是否显示详细视图和列表视图的切换按钮
columns: [{
field: "index",
title: '编号',
align: 'center',
formatter: formatIndex
}, {
field: 'userName',
title: '用户名',
align: 'center'
}, {
field: 'registertime',
title: '注册日期',
align: 'center'
}, {
field: 'score',
title: '积分',
align: 'center'
}, {
field: 'score',
title: '上传文档数',
align: 'center'
}, {
field: 'enables',
title: '用户状态',
align: 'center',
events: enableEvents, //用来做启用和禁用的事件
formatter: formatEnable
}],
onResetView: function () {
$("[data-toggle='tooltip']").tooltip();
$("#addBtn").parent().find("[title]").tooltip({
placement: "bottom"
});
}
});
// 以下是针对表格的事件
// 启用和禁用(状态)
function formatEnable(value, row, index) {
var btn;
if (row.enables == 1) {
btn = '<button data-toggle="tooltip" class="btn btn-success btn-xs" data-placement="left" title="已启用,点击可切换状态为未启用"><span class="glyphicon glyphicon-eye-open"></span></button>';
} else {
btn = '<button data-toggle="tooltip" class="btn btn-danger btn-xs" data-placement="left" title="未启用,点击可切换状态为启用"><span class="glyphicon glyphicon-eye-close"></span></button>';
}
return [btn].join('');
}
//自定义显示,这三个参数分别是:value该行的属性,row该行记录,index该行下标
function formatIndex(value, row, index) {
return [index + 1].join('');
}
setTimeout(function () {
table.bootstrapTable('resetView');
}, 200);
});
//执行一个laydate实例
laydate.render({
elem: '#begintime'
//,format: 'yyyy-MM-dd HH:mm:ss' //可任意组合
//,type:'datetime'
});
//执行一个laydate实例
laydate.render({
elem: '#endtime'
//,format: 'yyyy-MM-dd HH:mm:ss' //可任意组合
//,type:'datetime'
});
</script>
</html>
后端代码
/**
* 用户列表
*/
public String queryAllUserList() {
//得到全部用户的列表
//得到末尾
int endWith = limit + rowoffset;
//得到起始位置
int startWith = rowoffset;
usersList = userService.queryAllUserList(endWith, startWith, users, begintime, endtime);
int totalCount = userService.findAllTotalCount(users, begintime, endtime);
//页码
int pageNumber = rowoffset / limit + 1;
pages.setLimit(limit);
pages.setRowoffset(rowoffset);
pages.setPageNumber(pageNumber);
pages.setTotal(totalCount);
pages.setRows(usersList);
return SUCCESS;
}
翻页实体类
package com.crh.bean;
import java.io.Serializable;
import java.util.List;
/**
* @author Chrui
* @date 2018/10/15__13:23
*/
public class BootStrapPage implements Serializable {
//每页多少个 pageSize,表示分页后,每页的信息个数
private Integer limit;
//偏移量,从数据库第几条记录开始
private Integer rowoffset;
//总数
private Integer total;
//页码
private Integer pageNumber;
//数据
private List rows;
public BootStrapPage() {
}
public BootStrapPage(Integer limit, Integer rowoffset, Integer total, Integer pageNumber, List rows) {
this.limit = limit;
this.rowoffset = rowoffset;
this.total = total;
this.pageNumber = pageNumber;
this.rows = rows;
}
public Integer getLimit() {
return limit;
}
public void setLimit(Integer limit) {
this.limit = limit;
}
public Integer getRowoffset() {
return rowoffset;
}
public void setRowoffset(Integer rowoffset) {
this.rowoffset = rowoffset;
}
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}
public Integer getPageNumber() {
return pageNumber;
}
public void setPageNumber(Integer pageNumber) {
this.pageNumber = pageNumber;
}
public List getRows() {
return rows;
}
public void setRows(List rows) {
this.rows = rows;
}
}
mapper
<mapper namespace="com.crh.mapper.UserMapper">
<select id="queryAllUserList" resultType="com.crh.bean.Users">
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (select * from t_users where 1=1
<if test="users!= null">
<if test="users.userName and users.userName !=''">
and USERNAME like concat(concat('%','${users.userName}'),'%')
</if>
</if>
<if test="begintime != null and begintime != ''">
and to_char(REGISTERTIME, 'yyyy-MM-dd') >= #{begintime,jdbcType=VARCHAR}
</if>
<if test="endtime != null and endtime != ''">
and to_char(REGISTERTIME, 'yyyy-MM-dd') <= #{endtime,jdbcType=VARCHAR}
</if>
order by registertime desc,enables
desc) a WHERE ROWNUM <= #{endWith}) WHERE rn > #{startwith}
</select>
<select id="findAllTotalCount" resultType="integer">
SELECT COUNT(*) FROM T_USERS where 1=1
<if test="users!= null">
and USERNAME like concat(concat('%','${users.userName}'),'%')
</if>
<if test="begintime != null and begintime != ''">
and to_char(REGISTERTIME, 'yyyy-MM-dd') >= #{begintime,jdbcType=VARCHAR}
</if>
<if test="endtime != null and endtime != ''">
and to_char(REGISTERTIME, 'yyyy-MM-dd') <= #{endtime,jdbcType=VARCHAR}
</if>
</select>
</mapper>