jFinal中DatatablesHelper封装与调用

DatatablesHelper

1。首先我们先封装一个DatatablesHelper，这里封装了使用datatables对应的方法

import java.util.List;
import com.jfinal.json.JFinalJson;
import com.jfinal.plugin.activerecord.Record;

/** * DBHelper数据库辅助类 * @author zhengkai */
public class DatatablesHelper {

    int pageNumber=0;
    int pageSize=0;
    private String selectStr="";
    private String fromStr="";
    private String joinStr="";
    private String whereStr="";
    private String orderbyStr="";

    public int getPageNumber() {
        return pageNumber;
    }
    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    public void setPageNumber(String pageNumber) {
        if(StringUtils.isNumber(pageNumber)){
            this.pageNumber = Integer.parseInt(pageNumber);
        }
    }
    public void setPageNumberByStart(String iDisplayStart,String iDisplayLength) {
        if(StringUtils.isNumber(iDisplayStart)&&StringUtils.isNumber(iDisplayLength)){
            int s = Integer.parseInt(iDisplayStart);
            int l = Integer.parseInt(iDisplayLength);
            this.pageNumber = s/l+1;
        }
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public void setPageSize(String pageSize) {
        if(StringUtils.isNumber(pageSize)){
            this.pageSize = Integer.parseInt(pageSize);
        }
    }
    public String getSelectStr() {
        return selectStr;
    }
    public void setSelectStr(String selectStr) {
        if(StringUtils.isNotBlank(selectStr))
        this.selectStr = selectStr;
    }
    public String getFromStr() {
        return fromStr;
    }
    public void setFromStr(String fromStr) {
        if(StringUtils.isNotBlank(fromStr))
        this.fromStr = fromStr;
    }
    public String getJoinStr() {
        return joinStr;
    }
    public void setJoinStr(String joinStr) {
        if(StringUtils.isNotBlank(joinStr))
        this.joinStr = joinStr;
    }
    public String getWhereStr() {
        return whereStr;
    }
    public void setWhereStr(String whereStr) {
        if(StringUtils.isNotBlank(whereStr))
        this.whereStr = whereStr;
    }
    public void addWhereStr(String whereStr) {
        if(StringUtils.isNotBlank(whereStr))
        this.whereStr += " "+whereStr;
    }
    public String getOrderbyStr() {
        return orderbyStr;
    }
    public void setOrderbyStr(String orderbyStr) {
        if(StringUtils.isNotBlank(orderbyStr))
        this.orderbyStr = orderbyStr;
    }

    public String getSql() {
        return " "+selectStr+" "+fromStr+" "+joinStr+" "+whereStr+" "+orderbyStr+" ";
    }
    public String getSqlExceptSelect() {
        return " "+fromStr+" "+joinStr+" "+whereStr+" "+orderbyStr+" ";
    }
    public String getSqlSelect() {
        return " "+selectStr+" ";
    }

    /** * 目前仅支持Record类型 * @author zhengk */
    public ResponseDatatables getDatatables(String iTotalDisplayRecords,String iTotalRecords,List<Record> dataList){
        ResponseDatatables datatables=new ResponseDatatables();
        datatables.setiTotalDisplayRecords(iTotalDisplayRecords);
        datatables.setiTotalRecords(iTotalRecords);
        datatables.setList(dataList);
        return datatables;
    }
    /** * 封装成json * @author zhengk */
    public String getDatatablesJson(String iTotalDisplayRecords,String iTotalRecords,List<Record> dataList){
        ResponseDatatables datatables=new ResponseDatatables();
        datatables.setiTotalDisplayRecords(iTotalDisplayRecords);
        datatables.setiTotalRecords(iTotalRecords);
        datatables.setList(dataList);
        return JFinalJson.getJson().toJson(datatables);
    }
}

2。还需要一个ResponseDatatables的基础类，这是jfinal需要传输数据到前段的datatables格式的封装。

• iTotalRecords是表总数
• iTotalDisplayRecords是输入筛选条件后的计数

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Record;


/** * datatables返回json封装 * @author Moshow */
public class ResponseDatatables implements Serializable{

    private static final long serialVersionUID = 1L;

    //总记录数
    String iTotalRecords="0";
    //输入筛选条件后记录数
    String iTotalDisplayRecords="0";
    //核心数据
    List<Record> list=new ArrayList<Record>();
    public String getiTotalRecords() {
        return iTotalRecords;
    }
    public void setiTotalRecords(String iTotalRecords) {
        this.iTotalRecords = iTotalRecords;
    }
    public String getiTotalDisplayRecords() {
        return iTotalDisplayRecords;
    }
    public void setiTotalDisplayRecords(String iTotalDisplayRecords) {
        this.iTotalDisplayRecords = iTotalDisplayRecords;
    }
    public List<Record> getList() {
        return list;
    }
    public void setList(List<Record> list) {
        this.list = list;
    }
    /*public ResponseDatatables<Record> getDatatables(List<Record> dataList){ ResponseDatatables<Record> datatables=new ResponseDatatables<Record>(); datatables.setiTotalDisplayRecords(iTotalDisplayRecords); datatables.setiTotalRecords(iTotalRecords); datatables.setList(dataList); return datatables; }*/
    public ResponseDatatables getDatatables(String iTotalDisplayRecords,String iTotalRecords,List<Record> dataList){
        this.iTotalRecords = iTotalRecords;
        this.iTotalDisplayRecords = iTotalDisplayRecords;
        this.list = dataList;
        return this;
    }
    /** * 空构造 */
    public ResponseDatatables() {
    }
    /** * 参数构造 */
    public ResponseDatatables(String iTotalRecords, String iTotalDisplayRecords,
            List<Record> list) {
        this.iTotalRecords = iTotalRecords;
        this.iTotalDisplayRecords = iTotalDisplayRecords;
        this.list = list;
    }
}

3。这里是controller控制器，接下来就是Controller与View的对应了

public class CertController extends Controller {
    /** * 获取个人证书列表 * 参数：searchTxt1/searchTxt2为页面传回的条件 * 返回：json * @author:zhengkai */
    public void certPersonList() {
        //分页获取
        String searchTxt1=getPara("cert_number");
        String searchTxt2=getPara("cert_name");
        DatatablesHelper  dbHelper=new DatatablesHelper();
        dbHelper.setPageNumberByStart(getPara("iDisplayStart"),getPara("iDisplayLength"));
        dbHelper.setPageSize(getPara("iDisplayLength"));
        dbHelper.setSelectStr("select *");
        dbHelper.setFromStr("from cert_person");
        dbHelper.setWhereStr("where 1=1 ");
        dbHelper.addWhereStr(StringUtils.nvlPlus(searchTxt1, " and cert_number like '%"+searchTxt1+"%'" ));
        dbHelper.addWhereStr(StringUtils.nvlPlus(searchTxt2, " and cert_name like '%"+searchTxt2+"%'" ));
        dbHelper.setOrderbyStr("order by cert_number desc ");
        Page<Record> recordPage = Db.paginate(dbHelper.getPageNumber(), dbHelper.getPageSize(), dbHelper.getSqlSelect(), dbHelper.getSqlExceptSelect());
        //计算统计数据 iTotalDisplayRecords为加搜索条件后 iTotalRecords为搜索前 
        String iTotalDisplayRecords=recordPage.getTotalRow()+"";
        dbHelper.setWhereStr("where 1=1 ");//重置搜索条件后计算总记录，记得加上固定条件，例如查询某个customer的时候
        String iTotalRecords=Db.queryLong("select count(1)"+dbHelper.getSqlExceptSelect())+"";
        renderJson(dbHelper.getDatatablesJson(iTotalDisplayRecords,iTotalRecords, recordPage.getList()));
        //renderJson(json);
    }
}

4。datatables的table定义

    <table id="datatables" class="table table-border table-bordered table-bg table-hover" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>证书编码</th>
                        <th>个人姓名</th>
                        <th>认证课程</th>
                        <th>开始日期</th>
                        <th>结束日期</th>
                        <th>详情</th>
                    </tr>
                </thead>
            </table>

5。关于datatables引入，
官网https://datatables.net/
中文网http://www.datatables.club/

开始使用DataTables很简单，只需要引入两个文件， 一个css样式文件和DataTables本身的脚本文件。在DataTables CDN上，可以使用下面这两个文件
http://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css
http://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js

6。datatables的js定义，难得地方应是这里， 有很多种配置的，可以去官方看，记得当年研究了半天后来自己写了这个。

<script type="text/javascript"> $(function(){ }); var iDisplayLength=10; var iDisplayStart=1; var oTable; /** * 空值转换 */ function nvl(data){ if (data == undefined||data==null||$.trim(data)=="") { return " "; } else { return data; } } function deleteDate(item){ $.post( "${basePath}cert/deleteCertPerson", //url地址 "cert_number="+item, //序列化表单 function(data) { //回调 alert("删除成功！"); } ); } function datatables_init(){ //初始化datatables oTable=$('#datatables') .dataTable({ "bProcessing" : true, "searching" :false, "bDestory" : true, "bRetrieve" : true, "bPaginate" : true, //显示分页器 //前端自动分页模式，适用少量数据 /* "ajax" : { "url" : "${basePath}erpbill/purorderQuery", //默认为data，这里定义为demo "dataSrc" : "list" }, */ //服务端分页模式，适用大量数据 "bServerSide": true, "sAjaxDataProp" : "list", "sAjaxSource" : "${basePath}cert/certPersonList", "fnServerParams" : //向服务器发送数据 function (aoData) { aoData.push( { "name": "cert_number", "value": $("#cert_number").val() }, { "name": "cert_name", "value": $("#cert_name").val() } ); }, "columnDefs" : [//列加工模式,下标从0~n为左边到右边正数，-n为右边倒数第几个 { "targets" : [ 0 ], "data" : "cert_number",//证书名称 "orderable":false, "render" : function(data, type, full) { //return nvl(data); return '<a target="_blank" href="${basePath}cert/certPersonDisplay?cert_number='+nvl(data)+'">'+nvl(data)+'</a>'; //return '<input type="checkbox" id="'+data+'">'; } }, { "targets" : [ 1 ], "data" : "cert_name",//公司名称 "render" : function(data, type, full) { return nvl(data); } }, { "targets" : [ 2 ], "data" : "cert_lesson",//公司地址 "render" : function(data, type, full) { return nvl(data); } }, { "targets" : [ 3 ], "data" : "cert_validate_start",//开始日期 "render" : function(data, type, full) { return nvl(data); } },{ "targets" : [ 4 ], "data" : "cert_validate_end",//结束日期 "render" : function(data, type, full) { return nvl(data); } }, { "targets" : [ 5 ], "data" : "cert_number", "orderable":false, "render" : function(data, type, full) { return ''//'<a class="btn btn-primary-outline radius"><span class="row-details" id="'+data+'"><i class="Hui-iconfont Hui-iconfont-arrow3-bottom">详情</i></span></a>&nbsp;' <#if isAdmin??> +'<a class="btn btn-primary-outline radius" onclick="layer_show(\'证书编辑\',\'${basePath}cert/certPersonEdit?oid='+nvl(data)+'\',\'\',\'550\')"><i class="Hui-iconfont Hui-iconfont-edit2"></i>编辑</a>' </#if> +'<a class="btn btn-primary-outline radius" onclick="layer_show(\'证书详情\',\'${basePath}cert/certPersonDisplay?cert_number='+nvl(data)+'\',\'\',\'550\')"><i class="Hui-iconfont Hui-iconfont-edit2"></i>查看</a>' ; } } ] }); oTable.fnDraw(); } //页面初始化 datatables_init(); //重新加载，可以按钮触发 function reloadDatatables(){ oTable.fnClearTable(); //清空一下table oTable.fnDestroy(); //还原初始化了的datatable datatables_init(); //重新加载 } //监听点击详情 $('.table').on('click', ' tbody td .row-details', function() { var nTr = $(this).parents('tr')[0]; if (oTable.fnIsOpen(nTr)) //判断是否已打开 { /* This row is already open - close it */ $(this).children().addClass("Hui-iconfont-arrow3-bottom").removeClass("Hui-iconfont-arrow3-top"); oTable.fnClose(nTr); } else { /* This row is opened*/ $(this).children().addClass("Hui-iconfont-arrow3-top").removeClass("Hui-iconfont-arrow3-bottom"); // 调用方法显示详细信息 data_id为自定义属性 存放配置ID fnFormatDetails(nTr, $(this).attr("id")); } }); function fnFormatDetails(nTr, pdataId) { //根据配置Id 异步查询数据 $.get("${basePath}cert/certPersonDetail?cert_number="+pdataId, function(data) { var sOut = ''; if(data!=""){ //绘制开始样式 sOut = '<table class="table table-border table-bordered table-hover"><thead><tr class="warning"><th>证书编码</th><th>个人姓名</th><th>认证课程</th></tr></thead><tbody>'; //循环输出列表样式 sOut += '<tr class="danger"><td>' + data.cert_number +'</td><td>'+ data.cert_name +'</td><td>'+ data.cert_lesson+'</td></tr>'; //绘制结束样式 sOut += '</tbody></table>'; oTable.fnOpen(nTr, sOut, 'details'); }else{ sOut = '<center> <p style="width:70%"><i class="Hui-iconfont Hui-iconfont-face-ku">没有'+pdataId+'详细信息</i></p></center>'; oTable.fnOpen(nTr, sOut, 'details'); } }); } /*弹出窗口*/ function window_pop(title,url,w,h){ layer_show(title,url,w,h); } </script>