说明:整个项目使用了maven管理,服务器使用jetty.后台分页使用pagehelper-可在github上找到,dao使用mybatis,并结合spring和springmvc一起使用。
- 简单介绍下后台代码
//**1.mybatis mapper.xml文件,一个简单查询**
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jel.tech.dao.DeptDao">
<resultMap type="Dept" id="deptMap">
<id column="dept_id" property="deptId"/>
<result property="deptName" column="dept_name" />
<result property="parentId" column="parent_id" />
<result property="icon" column="icon" />
<result property="rank" column="rank" />
</resultMap>
<select id="queryDeptByName" resultMap="deptMap" parameterType="java.util.Map">
select * from t_dept
<where>
<if test="deptName != null">
dept_name LIKE CONCAT('%',#{deptName},'%')
</if>
</where>
</select>
</mapper>
//**2.DeptDao.java代码,service层代码省略,你懂的!**
package com.jel.tech.dao;
import java.util.List;
import java.util.Map;
import com.jel.tech.model.Dept;
public interface DeptDao {
public List<Dept> queryDeptByName(Map<String, Object> map);
}
//**3.mybatis-config.xml,注意pagehelper配置**
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 别名 -->
<typeAliases>
<package name="com.jel.tech.model"/>
</typeAliases>
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 4.0.0以后版本可以不设置该参数 -->
<property name="dialect" value="mysql"/>
<!--
当该参数设置为`true`时,如果`pageSize=0`或者`RowBounds.limit = 0`
就会查询出全部的结果(相当于没有执行分页查询,
但是返回结果仍然是`Page`类型)
-->
<property name="pageSizeZero" value="true"/>
<property name="reasonable" value="true"/>
</plugin>
</plugins>
</configuration>
//**4.controller层代码**
package com.jel.tech.controller;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jel.tech.common.json.JsonUtils;
import com.jel.tech.model.Dept;
import com.jel.tech.model.datatables.DatatableOrder;
import com.jel.tech.model.datatables.DatatableRequest;
import com.jel.tech.model.datatables.DatatableResponse;
import com.jel.tech.service.DeptService;
@RequestMapping("/datatables")
@Controller
public class DatatableHandler {
private static final Logger logger = LoggerFactory.getLogger(DatatableHandler.class);
@Autowired
private DeptService deptService;
/*
*需要produces,不然页面可能中文乱码,
*callback是DataTables框架请求内部自带过来的,返回数据的时需要它
前端的请求以json形式发送到后台,把其中的数据封装到Model中,
代码中具体实现原理请查看封装的Datatable Model.
*数据一般是以json形式返回.
*/
@ResponseBody
@RequestMapping(value = "/getJsonData4.do", method = RequestMethod.POST,produces="application/json; charset=UTF-8")
public String getDataTables(String callback,@RequestBody DatatableRequest request) {
DatatableResponse<Dept> response = new DatatableResponse<Dept>();
response.setDraw(request.getDraw());
//分页
Integer start = request.getStart();
Integer length = request.getLength();
PageHelper.startPage(start, length);
//对应数据库中的列名称
String [] columnNames = {"dept_id","dept_name","parent_id","icon","rank"};
//排序
/*
* request.getOrder()中的数据可能如下:
* [DatatableOrder [column=0, dir=asc], DatatableOrder [column=2, dir=desc]]
* 经过for循环处理后:
* [DatatableOrder [column=0, dir=dept_id asc], DatatableOrder [column=2, dir=parent_id desc]]
* 此时,orderBy = dept_id asc, parent_id desc
* 至此组成完整的sql语句:
* select * from tableName
* where condition
* limit start, length
* order by dept_id asc, parent_id desc
*/
for(DatatableOrder order : request.getOrder()) {
order.setDir(StringUtils.join(Arrays.asList(columnNames[order.getColumn()], order.getDir()), " "));
}
String orderBy = StringUtils.join(request.getOrder().stream().map(DatatableOrder::getDir).toArray(), ",");
PageHelper.orderBy(orderBy);
List<Dept> depts = deptService.queryDeptByName(request.getParamMap());
PageInfo<Dept> pageInfo = new PageInfo<Dept>(depts);
response.setRecordsTotal((int)pageInfo.getTotal()); response.setRecordsFiltered((int)pageInfo.getTotal());
response.setData(pageInfo.getList());
String json = JsonUtils.toJson(response);
logger.info(json);
/*
*返回数据类似如下格式:
jQuery111305123673508038207_1482069843425(
{
"draw":1,
"recordsTotal":12,
"recordsFiltered":12,
"data":[
{
"deptId":1,
"deptName":"大学",
"parentId":-1,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":1
},
{
"deptId":10,
"deptName":"综合大学",
"parentId":1,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":10
},
{
"deptId":11,
"deptName":"专业大学",
"parentId":1,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":11
},
{
"deptId":101,
"deptName":"清华大学",
"parentId":10,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":101
},
{
"deptId":102,
"deptName":"北京大学",
"parentId":10,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":102
},
{
"deptId":103,
"deptName":"华北电力大学",
"parentId":11,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":103
},
{
"deptId":104,
"deptName":"河海大学",
"parentId":10,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":104
},
{
"deptId":105,
"deptName":"野鸡大学",
"parentId":11,
"icon":"http://localhost:8080/tech-web/images/cart.gif",
"rank":105
},
{
"deptId":110,
"deptName":"耶鲁大学",
"parentId":11,
"icon":"/tech-web/images/cart.gif",
"rank":17
},
{
"deptId":111,
"deptName":"剑桥大学",
"parentId":11,
"icon":"/tech-web/images/cart.gif",
"rank":24
}
]
})*/
return callback.concat("(").concat(json).concat(")");
}
}
//**4.DataTables Model代码**
package com.jel.tech.model.datatables;
import java.util.Collections;
import java.util.List;
import java.util.Map;
/**
*
* @author Jelex.xu DataTables' server-side processing:Request Model
*
*/
public class DatatableRequest {
/**
* Draw counter. This is used by DataTables to ensure that the Ajax returns
* from server-side processing requests are drawn in sequence by DataTables
*/
private Integer draw;
/**
* Paging first record indicator. This is the start point in the current
* data set (0 index based, i.e. 0 is the first record)
*/
private Integer start;
/**
* Number of records that the table can display in the current draw. It is
* expected that the number of records returned will be equal to this
* number, unless the server has fewer records to return. Note that this can
* be -1 to indicate that all records should be returned(although that
* negates any benefits of server-side processing!)
*/
private Integer length;
/**
* Means global search function model here.
*/
DatatableSearch search;
/**
* an array defining all columns in the table
*/
private List<DatatableColumn> columns = Collections.<DatatableColumn>emptyList();
/**
* an array defining how many columns are being ordered upon - i.e. if the
* array length is 1, then a single column sort is being performed,
* otherwise a multi-column sort is being performed.
*/
private List<DatatableOrder> order = Collections.<DatatableOrder>emptyList();
/**
* request params(search conditions) a user input to search.
* key:param name,
* value:param value.
*/
private Map<String, Object> paramMap = Collections.emptyMap();
// getter/setter 省略
}
package com.jel.tech.model.datatables;
import java.util.Collections;
import java.util.List;
/**
*
* @author Jelex.xu DataTables Response Model
*
* The actually response data might be like this:
{
"draw": 1,
"recordsTotal": 57,
"recordsFiltered": 57,
"data": [
[
"Angelica",
"Ramos",
"System Architect",
"London",
"9th Oct 09",
"$2,875"
],
[
"Ashton",
"Cox",
"Technical Author",
"San Francisco",
"12th Jan 09",
"$4,800"
],
...
]
}
*/
public class DatatableResponse<T> {
/**
* The draw counter that this object is a response to - from the draw
* parameter sent as part of the data request. Note that it is strongly
* recommended for security reasons that you cast this parameter to an
* integer, rather than simply echoing back to the client what it sent in
* the draw parameter, in order to prevent Cross Site Scripting (XSS)
* attacks.
*/
private Integer draw;
/**
* Total records, before filtering (i.e. the total number of records in the
* database)
*/
private Integer recordsTotal;
/**
* Total records, after filtering (i.e. the total number of records after
* filtering has been applied - not just the number of records being
* returned for this page of data).
*/
private Integer recordsFiltered;
/**
* The data to be displayed in the table. This is an array of data source
* objects, one for each row, which will be used by DataTables. Note that
* this parameter's name can be changed using the ajax option's dataSrc
* property.
*/
private List<T> data = Collections.<T>emptyList();
/**
* Optional: If an error occurs during the running of the server-side
* processing script, you can inform the user of this error by passing back
* the error message to be displayed using this parameter. Do not include if
* there is no error.
*/
// private String error;
// getter/setter 省略
/*public String getError() {
return error;
}
public void setError(String error) {
this.error = error;
}*/
}
package com.jel.tech.model.datatables;
/**
*
* @author Jelex.xu
* DataTables Ordering Model
*
*/
public class DatatableOrder {
/**
* Column to which ordering should be applied. This is an index reference to
* the columns array of information that is also submitted to the server.
*/
private Integer column;
/**
* Ordering direction for this column. It will be asc or desc to indicate
* ascending ordering or descending ordering, respectively. You may want to
* get the value through the use of OrderDirection class.
*/
private String dir;
// getter/setter 省略
}
package com.jel.tech.model.datatables;
/**
*
* @author Jelex.xu
* Column Model in the table.
* a request example at the front-end might be like this:
var t = $('#example').DataTable( {
"columnDefs": [ {
"searchable": false,
"orderable": false,
"targets": 0
} ],
"order": [[ 1, 'asc' ]]
} );
*/
public class DatatableColumn {
/**
* Column's data source,as defined by columns.data. Which can be used to
* read and write data to and from any data source property, including
* deeply nested objects / properties.
*/
private String data;
/**
* Column's name, as defined by columns.name. Since: DataTables 1.10 Set a
* descriptive name for a column.
*/
private String name;
/**
* Flag to indicate if this column is searchable (true) or not (false). This
* is controlled by columns.searchable.
*/
private boolean searchable;
/**
* Flag to indicate if this column is orderable (true) or not (false). This
* is controlled by columns.orderable :Enable or disable ordering on this
* column
*/
private boolean orderable;
/**
* Here used as a model to search specific column, which is
* different to the usage situation in DatatableReq class,
* and I don't think you would like to use it for performance reason.
*/
private DatatableSearch search;
// getter/setter 省略
}
package com.jel.tech.model.datatables;
/**
*
* @author Jelex.xu
*DataTables Search function model
*/
public class DatatableSearch {
/**
* Search value to apply to this specific column
*/
private String value;
/**
* Flag to indicate if the search term for this column
* should be treated as regular expression (true) or not (false).
* As with global search, normally server-side processing scripts
* will not perform regular expression searching for performance reasons
* on large data sets, but it is technically possible
* and at the discretion of your script.
*/
private boolean regex;
// getter/setter省略
}
package com.jel.tech.model.datatables;
/**
* 列排序枚举类
*/
public enum OrderDirection {
Direction_ASC("asc","升序"),
Direction_DESC("desc","降序");
/**
* ascending ordering OR descending ordering
*/
private String order;
/**
* ordering description
*/
private String description;
private OrderDirection(String order, String description) {
this.order = order;
this.description = description;
}
public String getOrder() {
return order;
}
public String getDescription() {
return description;
}
}
package com.jel.tech.common.json;
import java.lang.reflect.Type;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
/**
* JSON 工具类
* @author Jelex
*
*/
public class JsonUtils {
private static final Gson gsonWithPrettyPrinting = new GsonBuilder().serializeNulls().setPrettyPrinting().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
private static final Gson gson = new GsonBuilder().serializeNulls().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
private JsonUtils() {}
public static <E> String toJson(E obj) {
return gson.toJson(obj);
}
public static <E> String toJsonWithPrettyPrint(E obj) {
return gsonWithPrettyPrinting.toJson(obj);
}
/**
* note:you can build the typeOfSrc in this way:
* Type fooType = new TypeToken<Foo<Bar>>() {}.getType();
* where class Bar means the generic class type.
* Actually,the toJson with one param is sufficient.
*
* @param obj:the generic class
* @param typeOfSrc
* @return
*/
public static <E> String toJson(E obj,Type typeOfSrc) {
return gson.toJson(obj,typeOfSrc);
}
public static <E> E fromJson(String jsonStr,Class<E> clazz) {
return gson.fromJson(jsonStr, clazz);
}
/**
* 可从json格式转换为javabean,Map,List,for example:
* @param jsonStr
* @param typeOfSrc
* @return
*
*An sexample:
public class Point {
private double x;
private double y;
}
Map<String, Point> map = new HashMap<String,Point>();
map.put("start", new Point(1.0,5.0));
map.put("end", new Point(3.0,9.0));
map.put("circle", new Point(2.0,6.0));
String json = JsonUtils.toJson(map);
System.out.println(json);
Type typeOfSrc = new TypeToken<Map<String,Point>>(){}.getType();
Map<String,Point> retMap = JsonUtils.fromJson(json, typeOfSrc );
System.out.println(retMap);
System.out.println(MapUtils.getObject(retMap, "circle"));
System.out.println(retMap.size());
and the total result would be like this:
{"start":{"x":1.0,"y":5.0},"end":{"x":3.0,"y":9.0},"circle":{"x":2.0,"y":6.0}}
{start=Point [x=1.0, y=5.0], end=Point [x=3.0, y=9.0], circle=Point [x=2.0, y=6.0]}
Point [x=2.0, y=6.0]
3
*/
public static <E> E fromJson(String jsonStr,Type typeOfSrc) {
return gson.fromJson(jsonStr, typeOfSrc);
}
}
//**至此,后台代码结束!**
2.前端部分:
js文件:除了DataTables官网所要求的js文件外,还需要引入我封装的一个js文件,以及一个语言json文件-language.json
截图如下:
css文件截图:
- **页面代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主页</title>
<link rel="stylesheet" href="css/bootstrap-3.3.5/fonts/glyphicons-halflings-regular.woff" />
<link rel="stylesheet" href="css/bootstrap-3.3.5/fonts/glyphicons-halflings-regular.woff2" />
<link rel="stylesheet" href="css/bootstrap-3.3.5/fonts/glyphicons-halflings-regular.ttf" />
<link rel="stylesheet" href="css/datatables.min.css" />
<link rel="stylesheet" href="css/bootstrap.min.css" />
<link rel="stylesheet" href="css/bootstrap-theme.min.css" />
<script type="text/javascript" src="js/datatables.min.js"></script>
<script type="text/javascript" src="js/ajax-datatables.js"></script>
<script type="text/javascript">
function callback() {
alert('yes!');
}
$(function() {
$("#datatables").click(function() {
var paramMap = {
/* deptName : '华北电力大学' */
};
var dataColumns = [
{'data':'deptId'},
{'data':'deptName'},
{'data':'parentId'},
{'data':'icon'},
{'data':'rank'},
{'data':"deptId","render": function (data, type, row) {
return '<div class="dropdown text-center">'
+' <span class="glyphicon glyphicon-cog" data-toggle="dropdown"'
+' aria-haspopup="true" aria-expanded="false" style="cursor:pointer;"></span>'
+' <ul class="dropdown-menu" aria-labelledby="dLabel">'
+' <li><a href="#" data-toggle="modal" data-target="#deptModal" onclick="deptMain.edit(\''+row.deptId+'\')">修改</a></li> '
+' <li><a href="#" onclick="deptMain.deleteInfo(\''+row.deptId+'\')">删除</a></li> '
+' </ul>'
+'</div>';
}
}
];
var dataColumnDefs = [
{ targets: [0, 1], visible: true},
{ targets: 3, "searchable": false },
{ targets: 4, visible: false},
]
console.log(dataTable);
dataTable.initTable(
'example',
'/tech-web/datatables/getJsonData4.do',
paramMap,
dataColumns,
/* dataColumnDefs, */
null,
callback);
});
});
</script>
</head>
<body>
<div class="container">
<button id="datatables" class="btn btn-success" type="button">datatables</button>
<form action="" id="form" method="POST">
<table id="example" class="table table-bordered table-hover">
<thead>
<tr>
<th>部门编号</th>
<th>部门名称</th>
<th>上级部门</th>
<th>校徽</th>
<th>排名</th>
<th>操作</th>
</tr>
</thead>
<tbody></tbody>
</table>
</form>
</div>
</body>
</html>
[ajax-datatables.js]
/*
two ways to add your params to request the data you need:
1.Add data to the request, returnng an object by extending the default data:
$('#example').dataTable( {
"ajax": {
"url": "data.json",
"data": function ( d ) {
return $.extend( {}, d, {
"extra_search": $('#extra').val()
} );
}
}
} );
2.Add data to the request by manipulating the data object:
$('#example').dataTable( {
"ajax": {
"url": "data.json",
"data": function ( d ) {
d.extra_search = $('#extra').val();
}
}
} );
*/
/*note:
the js is used to make it convenient to fire an ajax request to get data so as to
show it in the table in a jQuery-DataTables way.
tableId: the document id element name;
for example:
<table id="exampleTableId" class="table table-bordered table-hover">
<thead>
<tr>
<th>Property1</th>
<th>Property2</th>
<th>Property3</th>
<th>Property4</th>
<th>Property5</th>
<th>Operation</th>
</tr>
</thead>
<tbody></tbody>
</table>
That's it,a typical table, and the id attribute value 'exampleTableId' is what we need.
url: the url to which you post the request;
paramMap: request params you pass:
think a situation like this,
if you want to query your physical exercise(running) plan at day 2016-12-18,and you can compose
you request param in this way:
var paramMap = {taskName:'running',taskDate:'2016-12-18'};
in which, taskName and taskDate are the input query condition,
with 'running' and '2016-12-18' be their values respectively,
by the way,the taskDate can be a datetimepicker selected value and that's the sugguested way;
dataColumns: the columns you would like to show in the DataTable,actually,they are corresponding to the position
at the table->thread->tr>th element and it's an array filled with js object. below is an example:
columns = [
{'data':'deptId'},
{'data':'deptName'},
{'data':'parentId'},
{'data':'icon'},
{'data':'rank'},
{'data':"deptId","render": function (data, type, row) {
return '<div class="dropdown text-center">'
+' <span class="glyphicon glyphicon-cog" data-toggle="dropdown"'
+' aria-haspopup="true" aria-expanded="false" style="cursor:pointer;"></span>'
+' <ul class="dropdown-menu" aria-labelledby="dLabel">'
+' <li><a href="#" data-toggle="modal" data-target="#deptModal" onclick="deptMain.edit(\''+row.deptId+'\')">修改</a></li> '
+' <li><a href="#" onclick="deptMain.deleteInfo(\''+row.deptId+'\')">删除</a></li> '
+' </ul>'
+'</div>';
}
}
];
dataColumnDefs: user's own columns' settings can be set here,for example:
var columnDefs = [
{
"targets": 2,
"bSortable": false,
"searchable": false,
"render": function ( data, type, row ) {
//you extra process goes here.
}
},
{
targets: [3,5],
visible: false
}
];
callback: as the name says,it's a callback function.
If you want to run some javascript on the contents of the table after its initial draw,
and after every redraw / page, try using draw Event or drawCallback options.
draw Event:
var table = $('#example').DataTable();
table.on( 'draw.dt', function () {
console.log( 'Table draw event' );
})
drawCallback:
$('#example').dataTable( {
"drawCallback": function( settings ) {
var api = this.api();
// Output the data for the visible rows to the browser's console
console.log( api.rows( {page:'current'} ).data() );
}
} );
finally,important:
for it to be working,the language.json comes with this js,you should bind them together,put them in the same position.
*/
var dataTable = {
initTable : function(tableId,url,paramMap,dataColumns,dataColumnDefs,callback) {
//get the DataTable object
var otable =$("#"+tableId).dataTable();
//destroy the DataTable if exists so as to reinitialize it.
if(otable) {
otable.fnDestroy();
}
/*$("#"+tableId).on('order.dt', callback())
.on('page.dt', callback())*/
$("#"+tableId).dataTable({
"processing": true,
'serverSide': true,
"pagingType": "full_numbers",
"searching":false,
"lengthMenu": [ [10,15,20,50,100], [10,15,20,50,100] ],
"language": {
"url": "js/luaguage.json"
},
'ajax' : {
'url' : url,
'dataType': 'jsonp',
'type': 'POST',
'contentType': 'application/json; charset=utf-8',
'data': function (d) {
console.log(d);
d.paramMap = paramMap;
return JSON.stringify(d);
}
},
columns:dataColumns,
columnDefs:dataColumnDefs,
order: [[ 0, 'asc' ]],
"drawCallback": function( settings ) {
callback();
}
});
/*.on( 'draw.dt', callback());*/
}
};
[luaguage.json]
{
"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": "上页"
},
"aria": {
"sortAscending": ": activate to sort column ascending",
"sortDescending": ": activate to sort column descending"
}
}
//前端代码结束!
运行截图:
注:支持分页、排序,和bootstrap样式完美结合。