datatable分页有两种方式,一是页面分页,二是服务端分页
废话不多说,直接上代码
<link rel="stylesheet" href="${app}/static/media/css/jquery.dataTables.css">
<script src="${app}/static/media/js/jquery.dataTables.js"></script>
//这个是固定列用的
<link rel="stylesheet"
href="${app}/static/media/FixedColumns-3.2.2/css/fixedColumns.dataTables.min.css">
<script src="${app}/static/media/FixedColumns-3.2.2/js/dataTables.fixedColumns.min.js"></script>
页面分页
<div class="col-sm-12">
<table class="table table-hover" id="dataTable" border="1">
<thead>
<tr>
<th class="">单元</th>
<th class="">网格</th>
<th class="">营业厅</th>
<th class="">渠道经理</th>
<th class="">渠道经理电话</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
/* numbers - 只有只有数字按钮
simple - 只有上一页、下一页两个按钮
simple_numbers - 除了上一页、下一页两个按钮还有页数按钮,Datatables默认是这个
full - 有四个按钮首页、上一页、下一页、末页
full_numbers - 除首页、上一页、下一页、末页四个按钮还有页数按钮
first_last_numbers - 除首页、末页两个按钮还有页数按钮 */
$(function() {
initTable();
})
function initTable() {
$('#dataTable').DataTable({
info : true,
destroy : true,
paging : true, //是否分页
ordering : false, //是否排序
searching : false, //结果搜索
lengthChange : false,
pagingType : "simple_numbers",
displayLength : 50,//每页显示条数
lengthMenu : [50,100],//调整每页显示条数--我隐藏了
processing : true,//显示“处理中...”
bStateSave : true,
scrollX : true,
scrollY: "500px",
scrollCollapse: true,
bAutoWidth : true,
fixedColumns : {
//固定列的配置项
leftColumns : 1//-1第一列不固定,默认固定第一列
//leftColumns : 2
//固定右边第一列
},
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" : "末页"
}
},
ajax : {
url : '/alad/test/list1',
type : 'post',
//dataType: "json",
data : function(d) {
d.stat_date = $("#stat_date").val();
},
dataSrc : '',
/* dataSrc : function(result){
return result.data1;
}, */
},
columns : [
{title:'营业厅',data:'YYT',className:''},
{title:'单元',data:'DY',className:""},
{title:'网格',data:'WG',className:''},
{title:'渠道经理',data:'QDJL',className:''},
{title:'渠道经理电话',data:'QDJLDH',className:''}
],
});
}
这种是前端分页,把所有数据查询出来,即直接把list传到前端即可;
服务端分页:
$(document).ready(function() {
$('#dataTable').DataTable({
info : true,
destroy : true,
paging : true, //是否分页
ordering : false, //是否排序
searching : false, //结果搜索
lengthChange : false,
pagingType : "full",
displayLength : 50,//每页显示条数
lengthMenu : [50,100],
processing : true,//显示“处理中...”
bStateSave : false,
scrollX : true,
scrollY: "500px",//固定表头
scrollCollapse: true,
"bAutoWidth" : false,
"sAjaxSource" : '/alad/test/list2', //请求资源路径
"serverSide" : true, //开启服务器处理模式
fixedColumns : {
//固定列的配置项
leftColumns : 1//-1第一列不固定,默认固定第一列
//leftColumns : 2
//固定右边第一列
},
language : {
url : '/alad/static/media/language.json'
},
/*
使用ajax,在服务端处理数据
sSource:即是"sAjaxSource"
aoData:要传递到服务端的参数
fnCallback:处理返回数据的回调函数
*/
"fnServerData" : function(sSource, aoData, fnCallback) {
$.ajax({
'type' : 'post',
"url" : sSource,
"dataType" : "json",
"data" : {
"aodata" : JSON.stringify(aoData),
"stat_date" : $("#stat_date").val()
},
"success" : function(resp) {
fnCallback(resp);
}
});
},
"columns" : [
{title:'营业厅',data:'YYT'},
{title:'单元',data:'DY'},
{title:'网格',data:'WG'},
{title:'渠道经理',data:'QDJL'},
{title:'渠道经理电话',data:'QDJLDH'}
],
columnDefs: [ //设置列的宽度,测试可以设置的比默认的长,减短会不行(或许我没弄对)
{
render: function (data, type, full, meta) {
return "<div class='text-wrap width-50'>" + data + "</div>";
},
targets: 3
}
]
});
})
后端代码
@RequestMapping("list2")
public void list2(HttpServletRequest request, HttpServletResponse response) {
response.setCharacterEncoding("utf-8");
PrintWriter writer = null;
try {
writer = response.getWriter();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String aodata = RequestUtils.getValue(request, "aodata").replaceAll(""", "\"");
String stat_date = RequestUtils.getValue(request, "stat_date");
JSONArray ja = JSONArray.fromObject(aodata);
String sEcho = null;
Integer iColumns = null;
Integer iDisplayStart = null;
Integer iDisplayLength = null;
List<String> mDataProp = new ArrayList<String>(); // 存放列名
for (int i = 0; i < ja.size(); i++) {
if (ja.getJSONObject(i).getString("name").equals("sEcho"))
sEcho = ja.getJSONObject(i).getString("value");
else if (ja.getJSONObject(i).getString("name").equals("iColumns"))
iColumns = Integer.valueOf(ja.getJSONObject(i).getString("value"));
else if (ja.getJSONObject(i).getString("name").equals("iDisplayStart"))
iDisplayStart = Integer.valueOf(ja.getJSONObject(i).getString("value"));
else if (ja.getJSONObject(i).getString("name").equals("iDisplayLength"))
iDisplayLength = Integer.valueOf(ja.getJSONObject(i).getString("value"));
else if (iColumns != null) {
for (int j = 0; j < iColumns; j++)
if (ja.getJSONObject(i).getString("name").equals("mDataProp_" + j))
mDataProp.add(ja.getJSONObject(i).getString("value"));
}
}
List<String> params = new ArrayList<String>();
String sql = "select b.* from ("
+ "select a.*,rownum rn from ("
+ "select * from test a where 1=1 ";
if (!stat_date.equals("")) {
sql += " and a.stat_date = ?";
params.add(stat_date);
}
sql += " order by a.sort_id)a)b " + " where b.rn between " + iDisplayStart + " and "
+ (iDisplayStart + iDisplayLength);
logger.info(sql);
List<Map<String, Object>> list = db.queryForList(sql, params);
sql = "select count(1) from test where 1=1 ";
if (!stat_date.equals("")) {
sql += " and stat_date = ? ";
}
int iTotalRecords = db.queryForInt(sql,new Object[] {stat_date});
JSONObject json = new JSONObject();
json.put("sEcho", sEcho);
json.put("iTotalRecords", iTotalRecords);
json.put("iTotalDisplayRecords", iTotalRecords);
json.put("aaData", list);
writer.print(json);
writer.close();
}