datatables 简单使用

html(thymeleaf)

html页面

<table class="table table-border table-bordered table-bg table-hover table-sort table-responsive">
			<thead>
				<tr class="text-c">
					<th>aaaa</th>
					<th>bbbb</th>
					<th>cccc</th>
					<th>dddd</th>
					<th>eeee</th>
					<th>ffff</th>
					<th>gggg</th>
					<th>hhhh</th>
					<th>操作</th>
				</tr>
			</thead>
			<tbody>
			</tbody>
		</table>

JS引用

<script type="text/javascript" th:src="@{/H-ui.admin/lib/My97DatePicker/4.8/WdatePicker.js}"></script> 
<script type="text/javascript" th:src="@{/H-ui.admin/lib/datatables/1.10.0/jquery.dataTables.min.js}"></script> 
<script type="text/javascript" th:src="@{/H-ui.admin/lib/laypage/1.2/laypage.js}"></script>

JS使用

$(document).ready(function() {
    $(".table-sort").dataTable({  
    	"bStateSave": true,//状态保存
    	"language": {
    		"search": "请输入aaaa名称:",
          },
    	//"pading":false,
        "bServerSide": true,//这个用来指明是通过服务端来取数据
        "sAjaxSource": "[[@{/xxxx/xxDataTables}]]",//这个是请求的地址
        "fnServerData": retrieveData, // 获取数据的处理函数
        "bDeferRender": true,// 是否启用延迟加载：当你使用AJAX数据源时，可以提升速度。默认值：False
        "iDisplayLength": 10,//初始默认10条
        "info": "请输入查询条件",           //底部文字
        "aoColumnDefs": [
        	{ 
        		"mData" : "projectName" ,
        		"aTargets" : [0]
        	},
        	{ 
        		"mData" : "address" ,
        		"aTargets" : [1]
        	},
        	{ 
        		"mData" : "roomNo" ,
        		"aTargets" : [2]
        	},
        	{ 
        		"mData" : "layer" ,
        		"aTargets" : [3]
        	},
        	{ 
        		"mData" : "houseArea" ,
        		"aTargets" : [4]
        	},
            { 
        		"mData": "unitprice" ,
        		"aTargets" : [5]
        	},  
        	{ 
        		"mData": "companyName" ,
        		"aTargets" : [6]
        	},
        	{ 
        		"mData": "companyPhone" ,
        		"aTargets" : [7]
        	},
        	
        	{
   		  		"bSortable":false,
   		  		"sClass" : "f-14 td-manage",
                "render": function (data, type, row) {
                	//console.log(row.fWqStatus);
                	console.log(row);
                	var html = "";
	                	html += "<a id='delxw' style='text-decoration:none' class='ml-5' onClick='writeChooseInfo(\""+row.houseId+"\",\""+row.areaId+"\",\""+row.fCompanyId+"\",\""+row.id+"\")' href='javascript:;' title='选择'>";
	                	html += "	选择";
	                	html += "</a>";
                	return html;
                },
   		   		"aTargets":[8]
   		  	}
        ]
        //给列赋值，这里的列名需要和data的数据名对应  
        //注意这里第六列数据指定为null，用来存放我们加入的按钮  
        
    });
});

// 3个参数的名字可以随便命名,但必须是3个参数,少一个都不行
function retrieveData( sSource,aoData, fnCallback) {
	layer.load();
    $.ajax({
        url : sSource,//这个就是请求地址对应sAjaxSource
        data : {"fHouseShowId" : '[[${id}]]', "fCompanyId":'[[${clueId}]]', "aoData":JSON.stringify(aoData)},//这个是把datatable的一些基本数据传给后台,比如起始位置,每页显示的行数
        type : 'post',
        dataType : 'json',
        async : false,
        success : function(result) {
        	layer.closeAll('loading');
            fnCallback(result);//把返回的数据传给这个方法就可以了,datatable会自动绑定数据的
        },
        error : function(msg) {
        	layer.msg('加载数据出错', {icon: 5});
        	layer.closeAll('loading');
        }
    });
}

Controller

/**
 * 
 * <p>描述 查询xxxx列表</p>
 *
 * @param form 传入的参数
 * @param aoData datetables自带参数
 * @return
 */
@ResponseBody
@PostMapping("/xxxx/xxDataTables")
public PageResult xxDataTables(@ModelAttribute ChooseForm form, @RequestParam String aoData) {
	return houseChooseService.xxDataTables(form, aoData);
}

Service

@Override
public PageResult linksDataTables(ChooseForm form, String aoData) {
	PageRequest page = DataTableUtil.dataPackag(aoData);

	PageResult result = new PageResult();

	PageInfo<xxxShowDTO> pages = this.pageShowLinks(form, page);

	result.setiTotalRecords(pages.getTotal());
	result.setiTotalDisplayRecords(pages.getTotal());
	result.setAaData(this.xxListToJson(pages.getList()));
	result.setsEcho(page.getsEcho() + 1);
	return result;
}

/**
* mybatis分页查询
*/
private PageInfo<xxxShowDTO> pageShowLinks(ChooseForm form, PageRequest page) {
	PageHelper.startPage(page.getiDisplayStart() / page.getiDisplayLength() + 1 , page.getiDisplayLength());
	List<xxxShowDTO> shows = xxxShowMapper.pagexxxShow(form, page);
	return new PageInfo<>(shows);
}

/**
 * <p>描述 这里将查询出来的对象封装成另一个对象</p>
 *
 * @param list
 * @return
 */
private JSONArray xxListToJson(List<xxxShowDTO> list) {
	List<ShowXxxVO> vos = new ArrayList<>();
	for (xxxShowDTO dto : list) {
		ShowXxxVOvo = new ShowXxxVO();
		BeanUtils.copyProperties(dto, vo);
		//状态,时间之内的可以直接在这里转成字符串,页面直接显示就好了
		vos.add(vo);
	}
	//这里必须要加SerializerFeature.WriteMapNullValue,否则空的值会被清掉
	return JSONArray.parseArray(JSON.toJSONString(vos,SerializerFeature.WriteMapNullValue));
}

工具类

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xqx.dhfygs.form.vo.PageRequest;

public class DataTableUtil {

	/*
	 * 将dataTables传入的参数转换成对象
	 */
	public static PageRequest dataPackag(String aoData) {
		PageRequest page = new PageRequest();
		JSONArray alldata = JSONObject.parseArray(aoData);
	    for (int i = 0; i <alldata.size() ; i++) {
	        JSONObject obj = (JSONObject) alldata.get(i);
	        
	        if (obj.get("name").equals("sEcho")) {
	        	page.setsEcho(obj.getIntValue("value"));
	        }else if (obj.get("name").equals("iDisplayStart")) {
	        	page.setiDisplayStart(obj.getIntValue("value"));
	        }else if (obj.get("name").equals("iDisplayLength")) {
	        	page.setiDisplayLength(obj.getIntValue("value"));
	        }else if (obj.get("name").equals("sSearch")) {
	        	page.setsSearch(StringUtils.isNull(obj.get("value")) ? "" : obj.get("value").toString().trim());
	        }else if(obj.get("name").equals("iSortCol_0")) {
	        	page.setiSortCol_0(obj.getIntValue("value"));
	        }else if(obj.get("name").equals("sSortDir_0")) {
	        	page.setsSortDir_0(obj.getString("value"));
	        }
	    }
		return page;
	}

}

Mapper

/**
* <p>描述</p>
 *
 * @param form 查询条件参数
 * @param page 分页参数
 * @return
 */
List<xxxShowDTO> pagexxxShow(@Param("form") ChooseForm form, @Param("pageReq") PageRequest pageReq);

Mapper.xml

<select id="pagexxxShow" parameterType="map" resultMap="showXxxResultMap">
  	select distinct a.*
	from show 
	a.f_state = '有效'
	<if test="pageReq.sSearch != null and pageReq.sSearch != ''">
		<bind name="search" value="'%' +pageReq.sSearch + '%'" />
        and a.f_name like #{search,jdbcType=VARCHAR}
    </if>
    order by 
    <choose>
    	<when test="pageReq.iSortCol_0 == 0">
    		f_name 
    	</when>
    	<when test="pageReq.iSortCol_0 == 1">
    		f_address
    	</when>
    	<when test="pageReq.iSortCol_0 == 2">
    		F_RoomNo
    	</when>
    	<when test="pageReq.iSortCol_0 == 3">
    		F_Layer
    	</when>
    	<when test="pageReq.iSortCol_0 == 4">
    		f_Area
    	</when>
    	<when test="pageReq.iSortCol_0 == 5">
    		F_unitprice
    	</when>
    	<when test="pageReq.iSortCol_0 == 6">
    		f_company
    	</when>
    	<when test="pageReq.iSortCol_0 == 7">
    		f_phone
    	</when>
    	<otherwise>
    		id
    	</otherwise>
    </choose>
    <choose>
    	<when test="pageReq.sSortDir_0 == 'asc'">
    		ASC
    	</when>
    	<otherwise>
    		DESC
    	</otherwise>
    </choose>
  </select>