Datatable+Springmvc+mybatis(分页+排序+搜索)_Jquery
一、简介
通过Jqury的Datatable插件，构造数据列表，并且增加或者隐藏相应的列，已达到数据显示要求。同时， jQuery Datatable 强大的功能支持：排序，分页，搜索等。
二、前台分页
1、Datatable参数讲解
<div class="container-fluid margin-md">
<table id="firm-list" class="table table-responsive table-hover table-bordered">
<thead>
<tr>
<th>订单号</th>
<th>校区</th>
<th>下单时间</th>
<th>发单人</th>
<th>带客</th>
<th>订单状态</th>
<th>订单类型</th>
<th>金额</th>
<th>操作</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script>
$(function () {
var table = $("#firm-list").DataTable({
"bStateSave": true,
"order": [[ 1, 'asc' ]],
"autoWidth": true, //表格自适应
"processing": true, //显示加载信息
"language": {
"decimal": "",
"emptyTable": "没有数据",
"info": "当前为 _START_ 至 _END_ 号记录 , 一共 _TOTAL_ 条记录",
"infoEmpty": "当前为 0 至 0 号记录 , 一共 0 条记录",
"infoFiltered": "(找到 _MAX_ 条记录)",
"infoPostFix": "",
"thousands": ",",
"lengthMenu": "显示 _MENU_ 条记录",
"loadingRecords": "加载中...",
"processing": "加载中...",
"search": "查询:",
"zeroRecords": "没有找到匹配记录",
"paginate": {
"first": "第一页",
"last": "最后一页",
"next": "下一页",
"previous": "上一页"
}
},
<!--ajax请求，不多解析-->
"ajax": function (data, fnCallback) {
$.ajax({
"url": basePath + "/Admin/loadFirms.do",
"dataType": 'json',
"type": "POST",
"success": function (result) {
fnCallback(result);
},
"error": function () {
}
});
},
<!--每一列跟后台传递过来的json数据json.data中的数据字段名要一一对应。data就是字段名、bSortable代表该字段要不要排序，render用于对数据进行处理。-->
"columns": [
{ data: "firmId" ,"bSortable": false},//data就是字段名、bSortable代表该字段要不要排序
{
"data": "order.acceptAddCode" ,
"render": function(data, type, full) {
var campusCode=data.substring(0,2);
if(campusCode=='11'){
return "南湖校区"
}else if (campusCode=='12'){
return "林园校区"
}
}
},
{
"data":'giveTime',
"render": function(data, type, full) {
var date=new Date();
date.setTime(data.time)
return format(date, "yyyy-MM-dd hh:mm:ss");
}
},
{ data: "user.name","bSortable": false },
{ data: "order.receiver","bSortable": false },
{
"data": 'orderState',
"render": function (data, type, full) {
switch (data){
case 0:
return "未接单";
break;
case 1:
return "已接单";
break;
case 2:
return "已完成"
break;
case 3:
return "已取消";
break;
case 4:
return "待付款";
break;
}
}
},
{
data: 'order.staId',
"render": function (data, type, full) {
switch (data){
case 30:
return "快递";
break;
case 31:
return "外卖";
break;
}
}
},
{
"data": 'orderMoney',
"render": function (data, type, full) {
return data*0.01;
}
}
],
"columnDefs": [
{
"targets": [8],
"data": "firmId",
"render": function(data, type, full) {
return "<a href='<%=basePath%>Admin/lookupFirmDetail?firmId=" + data + "'>查看详情</a>";
}
}
]
});
table.on( 'order.dt search.dt', function () {
table.column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
cell.innerHTML = i+1;
} );
} ).draw();
});
function format(date, fmt) {
var o = {
"M+": date.getMonth() + 1, //月份
"d+": date.getDate(), //日
"h+": date.getHours(), //小时
"m+": date.getMinutes(), //分
"s+": date.getSeconds() //秒
};
if (/(y+)/.test(fmt))
fmt = fmt.replace(RegExp.$1, (date.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt))
fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
</script>
2、根据Datatable需要的Json数据格式，提供DatatableVO实体类。
1 public class DataTableResultVO<T>{ 2 private int draw;//请求的次数 3 private int recordsTotal;//返回的记录总数 4 private int recordsFiltered; //过滤后的数据总数 5 private List<T> data; // 显示到页面的数据 6 7 public DataTableResultVO() {} 8 9 public int getDraw() { 10 return draw; 11 } 12 13 public void setDraw(int draw) { 14 this.draw = draw; 15 } 16 17 public int getRecordsTotal() { 18 return recordsTotal; 19 } 20 21 public void setRecordsTotal(int recordsTotal) { 22 this.recordsTotal = recordsTotal; 23 } 24 25 public int getRecordsFiltered() { 26 return recordsFiltered; 27 } 28 29 public void setRecordsFiltered(int recordsFiltered) { 30 this.recordsFiltered = recordsFiltered; 31 } 32 33 public List<T> getData() { 34 return data; 35 } 36 37 public void setData(List<T> data) { 38 this.data = data; 39 } 40 41 @Override 42 public String toString() { 43 return "DataTableResultVO{" + 44 "draw=" + draw + 45 ", recordsTotal=" + recordsTotal + 46 ", recordsFiltered=" + recordsFiltered + 47 ", data=" + data + 48 '}'; 49 } 50 }
3、springmvc构造Datable需要的json数据格式
@RequestMapping(value = "/Admin/loadFirms.do",method = {RequestMethod.POST})
@ResponseBody
public String loadFirms(HttpServletRequest request, HttpServletResponse response){
try {
List<Firm> firms=firmService.loadFirmsToAdmin();
DataTableResultVO<Firm> result=new DataTableResultVO<>();
result.setData(firms);//data参数。
result.setRecordsTotal(firms.size());//数据的条数
result.setRecordsFiltered(firms.size());//过滤后数据的条数
return JSONObject.fromObject(result).toString();
}catch (Exception e){
e.printStackTrace();
}
return null;
}
4、利用mybatis实现Service层与Dao层
//FirmServiceImpl、获取数据列表 public List<Firm> loadFirmsToAdmin()throws Exception{ return firmDao.selectFirmsByTableParams(); }//FirmDao接口
public Firm selectFirmDetailToAdmin(String firmId);//FirmDaoxml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD iBatis Mapper 3.0 //EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.daiba.firm.dao.FirmDao">
<!---管理员页面通过分页获取订单列表-->
<select id="selectFirmsByTableParams" resultMap="firmResultMap">
SELECT f.firm_id,o.accept_add_code,f.use_id,u.name,o.receiver,f.order_state,o.sta_id,f.give_time,f.order_money
FROM firm f,orders o,user u
WHERE f.ord_id = o.order_id
AND f.use_id=u.user_id
ORDER BY f.order_state ASC,f.give_time DESC
</select><!--mybatis常识:使用resultMap的时候记得要在mybatis.xml里面添加实体依赖注入typealis-->
<resultMap id="firmResultMap" type="Firm">
<id property="firmId" column="firm_id" jdbcType="VARCHAR"/>
<result property="briId" column="bri_id" jdbcType="INTEGER"/>
<result property="address" column="address" jdbcType="VARCHAR"/>
<result property="askTime" column="ask_time"/>
<result property="remark" column="remark" jdbcType="VARCHAR"/>
<result property="orderMoney" column="order_money" jdbcType="INTEGER"/>
<result property="giveTime" column="give_time"/>
<result property="acceptTime" column="accept_time"/>
<result property="finishTime" column="finish_time"/>
<result property="cancleTime" column="cancle_time"/>
<result property="orderState" column="order_state" jdbcType="INTEGER"/>
<result property="isApplyCancel" column="is_apply_cancel" jdbcType="INTEGER"/>
<association property="user" javaType="User">
<id property="userId" column="use_id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="portrait" column="portrait" jdbcType="VARCHAR"/>
<result property="phoneNum" column="phone_num" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="registerTime" column="register_time"/>
<result property="role" column="role" jdbcType="VARCHAR"/>
<result property="recetlyLoginTime" column="recetly_login_time"/>
<result property="orderNum" column="order_num" jdbcType="INTEGER"/>
<result property="spending" column="spending" jdbcType="DOUBLE"/>
<result property="sex" column="sex" jdbcType="INTEGER"/>
<result property="openId" column="open_id" jdbcType="VARCHAR"/>
</association>
<association property="order" javaType="Order">
<id property="orderId" column="ord_id" jdbcType="VARCHAR"/>
<result property="staId" column="sta_id" jdbcType="INTEGER"/>
<result property="acceptAddCode" column="accept_add_code" jdbcType="VARCHAR"/>
<result property="company" column="company" jdbcType="VARCHAR"/>
<result property="receiver" column="receiver" jdbcType="VARCHAR"/>
<result property="tokenNum" column="token_num" jdbcType="VARCHAR"/>
<result property="reservedPhone" column="reserved_phone" jdbcType="VARCHAR"/>
</association>
</resultMap>
</mapper>
三、后台分页
原理跟前台类似，只是数据分页、排序、查询在后台完成。
1、ajax请求重要参数(start、length、search[value])
start: 其实记录位置
length: 页面显示数量
order[0][column]: 因为是二维的表格，因此只有一维需要排序，所以 order 的下标未0. 该属性表示第几列需要排序。
order[0][dir]: 排序方式 ASC | DESC
search[value]: search 输入框中的值
2、Springmvc获取参数
1 int pageSize = 10; 2 int startRecord = 0; 3 //分页的数据条数 4 String size = request.getParameter("length"); 5 if (!"".equals(size) && size != null) { 6 pageSize = Integer.parseInt(size); 7 } 8 //分页的当前位置 9 String currentRecord = request.getParameter("start"); 10 if (!"".equals(currentRecord) && currentRecord != null) { 11 startRecord = Integer.parseInt(currentRecord); 12 } 13 //用于排序 14 String sortOrder = request.getParameter("order[0][column]"); 15 String sortDir = request.getParameter("order[0][dir]"); 16 System.out.println("sortOrder: " + sortOrder); 17 System.out.println("sortDir: " + sortDir); 18 19 // 用于搜索 20 String searchValue = request.getParameter("search[value]");
3、核心思想:组装sql，例如select * from firm where 1=1 order by sortOrder sortDir limit start，length。
4、dao层实现，利用servlet+jdbc对后台分页进行详细说明。
参考:http://www.tuicool.com/articles/NBBnum
参考:http://blog.csdn.net/tomcat_2014/article/details/50177645
四、致敬语
刚开始通过写博客来做学习笔记，若有什么不足请多多指正，谢谢！
