jquerydatatable服務端分頁

jquerydatatable服務端分頁,上次完成瞭客戶端的分頁,這次我們就在上一次的Demo上進行修改,來實現服務端的分頁~
js代碼:

<script type="text/javascript">
$(document).ready(function() {
    $('#table_id_example').DataTable({
        "bProcessing" : false, //是否顯示加載
        "sAjaxSource" : '/datatableDemo/user/json', //請求資源路徑
        "serverSide": true, //開啟服務器處理模式
        /*
        使用ajax,在服務端處理數據
        sSource:即是"sAjaxSource"
        aoData:要傳遞到服務端的參數
        fnCallback:處理返回數據的回調函數
        */
        "fnServerData": function(sSource, aoData, fnCallback){
            $.ajax( {   
                'type' : 'post', 
                "url": sSource,    
                "dataType": "json",   
                "data": { "aodata" : JSON.stringify(aoData) },
                "success": function(resp) {   
                    fnCallback(resp);
                }   
            }); 
        }, 
        "columns" : [ 
              { data : "id" }, 
              { data : "name" }, 
              { data : "age" }, 
        ]
    });
})
</script>

開啟服務器處理模式,這時我們對表格的每次操作,datatable都會幫我們向服務器發起請求獲取數據,默認是用GET方式傳遞參數,所以我們下面加上瞭”fnServerData”,這樣可以修改參數傳遞的方式為POST,那麼到底給服務端發送的”aoData”是個什麼鬼呢,別急,我們直接從Servlet取出來看看就知道瞭
此時我們的Servlet也要做一點修改:

public class Action extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 5957315496919679612L;

    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 編碼設置
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
        response.setCharacterEncoding("UTF-8");

        // 把傳送過來的JSON字符串轉成JSON數組
        JSONArray ja = JSONArray.fromObject(request.getParameter("aodata"));
        System.out.println(ja);

        // 從數據庫獲取數據
        List listUser = Service.getInstance().getAll();

        // 數據封裝並返回給客戶端
        DataTableJSONResponse dtjs = new DataTableJSONResponse(listUser);
        JSONObject jsonObject = JSONObject.fromObject(dtjs);
        response.getWriter().print(jsonObject.toString());
    }

此時我們運行一下,OK,發現所謂的參數長這個模樣

[{“name”:”sEcho”,”value”:1},
{“name”:”iColumns”,”value”:3},
{“name”:”sColumns”,”value”:”,,”},
{“name”:”iDisplayStart”,”value”:0},
{“name”:”iDisplayLength”,”value”:10},
{“name”:”mDataProp_0”,”value”:”id”},
{“name”:”sSearch_0”,”value”:”“},
{“name”:”bRegex_0”,”value”:false},
{“name”:”bSearchable_0”,”value”:true},
{“name”:”bSortable_0”,”value”:true},
{“name”:”mDataProp_1”,”value”:”name”},
{“name”:”sSearch_1”,”value”:”“},
{“name”:”bRegex_1”,”value”:false},
{“name”:”bSearchable_1”,”value”:true},
{“name”:”bSortable_1”,”value”:true},
{“name”:”mDataProp_2”,”value”:”age”},
{“name”:”sSearch_2”,”value”:”“},
{“name”:”bRegex_2”,”value”:false},
{“name”:”bSearchable_2”,”value”:true},
{“name”:”bSortable_2”,”value”:true},
{“name”:”sSearch”,”value”:”“},
{“name”:”bRegex”,”value”:false},
{“name”:”iSortCol_0”,”value”:0},
{“name”:”sSortDir_0”,”value”:”asc”},
{“name”:”iSortingCols”,”value”:1}]

是不是感覺有點晦澀難懂,於是本菜上網查瞭下,勉強是看懂這個鬼東西,接下來就講解一下幾個我們需要的參數:
sEcho:客戶端發送請求同時發送過來的一個標識,雖然有什麼卵用不知道,不過我們服務端返回的數據中必須帶有這個標識,哦,官網的文檔裡說,服務端取出標識最好轉一下轉成int,防止XXS攻擊(懵逼,暫且不管,我們隻知道需要這個就行)
iColumns:列數
iDisplayStart:起始索引
iDisplayLength:顯示的行數
{“name”:”mDataProp_0”,”value”:”id”},
{“name”:”sSearch_0”,”value”:”“},
{“name”:”bRegex_0”,”value”:false},
{“name”:”bSearchable_0”,”value”:true},
{“name”:”bSortable_0”,”value”:true}
每一列的參數設置,_0即是第一列,這個我們不管,到下面也是提取列名而已
sSearch:全局搜索字段
iSortCol_0:被排序的列
sSortDir_0:排序方式

完成瞭參數解讀,那麼接下來就要提取我們的參數啦~~開工加代碼

public class Action extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 5957315496919679612L;

    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 編碼設置
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
        response.setCharacterEncoding("UTF-8");

        // 把傳送過來的JSON字符串轉成JSON數組
        JSONArray ja = JSONArray.fromObject(request.getParameter("aodata"));
        System.out.println(ja);

        // 獲取需要的參數值
        String sEcho = null;
        Integer iColumns = null;
        Integer iDisplayStart = null;
        Integer iDisplayLength = null;
        List mDataProp = new ArrayList();   //存放列名
        String sSearch = null;
        Integer iSortCol_0 = null;
        String iSortCol = null;
        String sSortDir_0 = null;
        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 (ja.getJSONObject(i).getString("name").equals("sSearch"))
                sSearch = ja.getJSONObject(i).getString("value");
            else if (ja.getJSONObject(i).getString("name").equals("iSortCol_0"))
                iSortCol_0 = Integer.valueOf(ja.getJSONObject(i).getString("value"));
            else if (ja.getJSONObject(i).getString("name").equals("sSortDir_0"))
                sSortDir_0 = 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"));
            }
        }
        iSortCol = mDataProp.get(iSortCol_0);
        System.out.println(sEcho);
        System.out.println(iColumns);
        System.out.println(iDisplayStart);
        System.out.println(iDisplayLength);
        System.out.println(sSearch);
        System.out.println(iSortCol);
        System.out.println(sSortDir_0);

        // 從數據庫獲取數據
        List listUser = Service.getInstance().getAll();

        // 數據封裝並返回給客戶端
        DataTableJSONResponse dtjs = new DataTableJSONResponse(listUser);
        JSONObject jsonObject = JSONObject.fromObject(dtjs);
        response.getWriter().print(jsonObject.toString());
    }

好的,加工完畢,這時我們跑一跑看看是不是打出我們要的參數,這裡我就不貼出來瞭,你們自己跑瞭看,好的發現拿到瞭我們要的參數,那麼接下來可以拼接我們的sql語句瞭,同時我們要對我們的DataTableJSONResponse進行一下小小的修改,因為這裡對於返回的數據也是有參數要求的,那我們就把需要的參數放進去,代碼如下:

public class DataTableJSONResponse {
    Object sEcho;
    Object iTotalRecords;   //查詢的記錄數
    Object iTotalDisplayRecords;    //過濾後的記錄數
    Object aaData;

    public DataTableJSONResponse() {
        super();
    }

    public DataTableJSONResponse(Object aaData) {
        super();
        this.aaData = aaData;
    }

    public DataTableJSONResponse(Object sEcho, Object iTotalRecords, Object iTotalDisplayRecords, Object aaData) {
        super();
        this.sEcho = sEcho;
        this.iTotalRecords = iTotalRecords;
        this.iTotalDisplayRecords = iTotalDisplayRecords;
        this.aaData = aaData;
    }

    public Object getAaData() {
        return aaData;
    }

    public void setAaData(Object aaData) {
        this.aaData = aaData;
    }

    public Object getsEcho() {
        return sEcho;
    }

    public void setsEcho(Object sEcho) {
        this.sEcho = sEcho;
    }

    public Object getiTotalRecords() {
        return iTotalRecords;
    }

    public void setiTotalRecords(Object iTotalRecords) {
        this.iTotalRecords = iTotalRecords;
    }

    public Object getiTotalDisplayRecords() {
        return iTotalDisplayRecords;
    }

    public void setiTotalDisplayRecords(Object iTotalDisplayRecords) {
        this.iTotalDisplayRecords = iTotalDisplayRecords;
    }
}

完整的Servlet:

public class Action extends HttpServlet {

    /**
     * 
     */
    private static final long serialVersionUID = 5957315496919679612L;

    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 編碼設置
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
        response.setCharacterEncoding("UTF-8");

        // 把傳送過來的JSON字符串轉成JSON數組
        JSONArray ja = JSONArray.fromObject(request.getParameter("aodata"));

        // 獲取需要的參數值
        String sEcho = null;
        Integer iColumns = null;
        Integer iDisplayStart = null;
        Integer iDisplayLength = null;
        List mDataProp = new ArrayList();   //存放列名
        String sSearch = null;
        Integer iSortCol_0 = null;
        String iSortCol = null;
        String sSortDir_0 = null;
        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 (ja.getJSONObject(i).getString("name").equals("sSearch"))
                sSearch = ja.getJSONObject(i).getString("value");
            else if (ja.getJSONObject(i).getString("name").equals("iSortCol_0"))
                iSortCol_0 = Integer.valueOf(ja.getJSONObject(i).getString("value"));
            else if (ja.getJSONObject(i).getString("name").equals("sSortDir_0"))
                sSortDir_0 = 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"));
            }
        }
        iSortCol = mDataProp.get(iSortCol_0);

        String sql = null;
        if(sSearch.equals(""))
            sql = "select * from(select id,name,age,rownum rn from dtdemo_xxx)"
            +"where rn between " + iDisplayStart + " and " + (iDisplayStart+iDisplayLength)
            +" order by " + iSortCol + " " + sSortDir_0;
        else
            sql = "select * from(select id,name,to_char(age,999) age,rownum rn from dtdemo_xxx where id like '%"
            + sSearch +"%' or name like '%"+ sSearch +"%' or age like '%"+ sSearch +"%')"
            +"where rn between " + iDisplayStart + " and " + (iDisplayStart+iDisplayLength)
            +" order by " + iSortCol + " " + sSortDir_0;
        System.out.println(sql);

        // 從數據庫獲取數據
        List listUser = Service.getInstance().getAll(sql);
        //獲取記錄數
        int size = Service.getInstance().getAll().size();

        // 數據封裝並返回給客戶端
        DataTableJSONResponse dtjs = new DataTableJSONResponse(sEcho,size,size,listUser);
        JSONObject jsonObject = JSONObject.fromObject(dtjs);
        response.getWriter().print(jsonObject.toString());
    }

}

至此,服務端分頁完成~

發佈留言