从零开始搭建自己的网站二十五：使用datatables进行表格分页查询

对于网站后台，肯定会有很多列表。我们这里采用了datatables框架，进行页面列表展示，我采用的分页查询列表数据，每页单独查询一次，这样可以避免一次性查询数据过大，导致速度过慢的问题。下图是做好之后的样式

首先得引入 dataTables.min.css 和 dataTables.min.js ，js得放在jquery.js的后面。

html代码部分

<table id="myTable" class="table table-striped table-hover">
    <thead>
    <tr>
        <th class="col-md-6">
            <span class="glyphicon glyphicon-file"></span>
            <span class="visible-lg">标题</span>
        </th>
        <th class="col-md-1">
            <span class="glyphicon glyphicon-list"></span>
            <span class="visible-lg">类别</span>
        </th>
        <th class="col-md-1">
            <span class="glyphicon glyphicon-tag"></span>
            <span class="visible-lg">关键词</span>
        </th>
        <th class="col-md-1">
            <span class="glyphicon glyphicon-comment"></span>
            <span class="visible-lg">评论</span>
        </th>
        <th class="col-md-2">
            <span class="glyphicon glyphicon-time"></span>
            <span class="visible-lg">日期</span>
        </th>
        <th class="col-md-1">
            <span class="glyphicon glyphicon-pencil"></span>
            <span class="visible-lg">操作</span>
        </th>
    </tr>
    </thead>
    <tbody>
    </tbody>
</table>

js代码部分

$(document).ready(function(){
    var table = $('#myTable').DataTable({
        "lengthChange": false,
        "searching": true,          //搜索框
        "ordering": true,           //排序
        "info": true,
        "autoWidth": false,
        "bLengthChange": true,      //改变每页显示数据数量
        "pageLength": 10,
        "serverSide": true,         //开启服务端请求模式，也就是分页
        "scrollCollapse": true,
        "bInfo": true,              //页脚信息
        "ajax": {
            "url": "/manager/getArticles",
            "type":"POST"
        },
        "columns": [
            {"data": "title",
                "render" : function(data, type, row, meta) {
                    return '<a target="_blank" href="/article/'+ row.contentId +'.html">' + row.title + '</a>';
                }
            },
            { "data": "categoryValue" },
            { "data": "keyValue" },
            { "data": "comment" },
            { "data": "createDate" },
            { "data": "id",
                "render" : function(data, type, row, meta) {
                    return '<a href="/manager/updateArticle?contentId='+ row.contentId +'">修改</a>' + " | " +
                        "<a rel='6' name='delete' onclick='deleteComment("+ meta.row +")'>删除</a>";
                }
            }
        ],
        "columnDefs": [ {
            "searchable": true,
            "orderable": false,
            "targets": 0
        } ],
        "order": [[ 1, 'asc' ]]
    });

    $('#myTable tbody').on( 'click', 'tr', function () {
        $(this).toggleClass('selected');
    });

    $('#button').click( function () {
        alert( table.rows('.selected').data().length +' row(s) selected');
    });
});

Java后台代码

//controller层
/**
 * 分页获取文章列表
 */
@RequestMapping(value = "/getArticles", method = RequestMethod.POST)
@ResponseBody
public DataTables getArticles(HttpServletRequest request) {
    DataTables dataTables = new DataTables();
    String draw1 = request.getParameter("draw");
    String start1 = request.getParameter("start");            //开始数
    String length1 = request.getParameter("length");          //每页的数量
    String search = request.getParameter("search[value]");    //查询条件
    int draw = Integer.parseInt(draw1);
    int start = Integer.parseInt(start1);
    int length = Integer.parseInt(length1);
    List<Article> articleList = articleService.getArticleByPages(start, length, search);
    int articleSize = articleService.getArticleSize(search);
    dataTables.setDraw(draw);
    dataTables.setStart(start);
    dataTables.setLength(length);
    dataTables.setData(articleList);
    dataTables.setRecordsTotal(articleSize);
    dataTables.setRecordsFiltered(articleSize);
    return dataTables;
}

//service层
@Override
public List<Article> getArticleByPages(int start, int length, String search) {
    return articleDao.getArticleByPages(start, length, search);
}

//dao层
List<Article> getArticleByPages(int start, int length, String search);

//datatables实体
public class DataTables {

    private int draw;

    private int start;

    private int end;

    private int length;

    private int recordsTotal;

    private int recordsFiltered;

    private List data;

    public int getDraw() {
        return draw;
    }

    public void setDraw(int draw) {
        this.draw = draw;
    }

    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public int getLength() {
        return length;
    }

    public void setLength(int length) {
        this.length = length;
    }

    public int getRecordsTotal() {
        return recordsTotal;
    }

    public int getEnd() {
        return end;
    }

    public void setEnd(int end) {
        this.end = end;
    }

    public void setRecordsTotal(int recordsTotal) {
        this.recordsTotal = recordsTotal;
    }

    public int getRecordsFiltered() {
        return recordsFiltered;
    }

    public List getData() {
        return data;
    }

    public void setData(List data) {
        this.data = data;
    }

    public void setRecordsFiltered(int recordsFiltered) {
        this.recordsFiltered = recordsFiltered;
    }
}

//sqlmap
<!--分页获取文章列表-->
<select id="getArticleByPages" resultMap="article">
    select  a.* , count( c.content ) AS comment from article a LEFT JOIN  comment c on a.id = c.articleId
    where a.title like CONCAT('%',#{2},'%')
    GROUP BY a.id
    order by a.createDate desc limit #{0},#{1}
</select>

 

欢迎转载，转载请注明出处 http://www.dingyinwu.com/article/70.html

如果文章中有任何问题或者可以改进的地方，请大家多提提意见，我会非常感激。