dataTables服务器端分页serverSide(for django)

html

    <table class="display" id="tb">
        <thead>
        <tr>
            <th>ip_address</th>
            <th>host_name</th>
            <th>lease_expiry_time</th>
            <th>lease_duration</th>
            <th>time_span</th>
        </tr>
        </thead>
    </table>

js

    $(document).ready(function () {
        $("#tb").DataTable({
            "paging": true,
            "searching": true,
            "processing": true,
            "serverSide": true,
//            "destroy": true,
//            "pageLength": 10,
            "ordering": false,
//            "renderer": "bootstrap",//渲染样式：Bootstrap和jquery-ui
//            "pagingType": "simple_numbers",//分页样式：simple,simple_numbers,full,full_numbers
//            "autoWidth": true,
//            "stateSave": true,//保持翻页状态，和comTable.fnDraw(false);结合使用
            "ajax": {
                "url": "/example/paging/paging/",
                "type": "post"
            },
//            "columns": [          //若返回的data為字典，需要設置此屬性
//               { "data": "id" },
//               { "data": "name" },
//               { "data": "dept" },
//               { "data": "phone" }
//            ]
            "columnDefs": [  //欄位渲染
                {
                    "render": function (data, type, row) { //data:本欄位內容 row:本行內容
                        if (data == "10.199.50.125") {
                            return "<span style='color: red'>" + data + "</span>";
                        } else {
                            return data;
                        }
                    },
                    "targets": 0 //指定渲染列
                },
                {
                    "render": function (data, type, row) {
                        if (row[0] == "10.199.50.139") {  //根據IP設置本欄位顏色
                            return "<span style='color: #adff2f'>" + data + "</span>";
                        } else {
                            return data;
                        }
                    },
                    "targets": 1 //指定渲染列
                }
            ]

        });
    });

urls.py

    # 注意：進入頁面url和加載數據的url不能相同
    url(r'^example/paging/$', paging.index),
    url(r'^example/paging/paging/$', paging.paging),

views.py

def index(request):
    return render(request, 'example/paging/body01.html')

# 進入後端分頁示例頁面
def paging(request):
    draw = int(request.POST.get('draw'))  # 記錄操作次數
    start = int(request.POST.get('start'))  # 起始位置
    length = int(request.POST.get('length'))  # 每頁長度
    search_key = request.POST.get('search[value]')  # 搜索關鍵字
    order_column = request.POST.get('order[0][column]')  # 排序字段索引
    order_column = request.POST.get('order[0][dir]')  #排序規則：ase/desc

    # sql查詢出所有數據，再做分頁，速度較慢
    # if search_key:
    #     result = query(search_key)
    # else:
    #     result = select_all()
    # data = result[start:start+length]
    # count = len(result)

    # sql做分頁，速度快
    if search_key:
        result, count = query(search_key)
        data = result[start:start + length]
    else:
        data = select_by_page(start, start + length)
        count = all_count()

    dic = {
        'draw': draw,
        'recordsTotal': count,
        'recordsFiltered': count,
        'data': data,
    }

    return HttpResponse(json.dumps(dic), content_type='application/json')


# 查詢all
def select_all():
    cursor = connection.cursor()
    # Object of type 'datetime' is not JSON serializable ,用to_char轉換
    # ORA-00911: invalid character ,去掉分號
    sql = "select ip_address,host_name,to_char(lease_expiry_time),to_char(lease_duration),to_char(time_span) from example_paging order by ip_address"
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    return result

# sql做分頁
def select_by_page(start, end):
    cursor = connection.cursor()
    # Object of type 'datetime' is not JSON serializable ,用to_char轉換
    # ORA-00911: invalid character ,去掉分號
    # oracle分頁，若rownum中不包含1查詢結果為空，所以必須先取得rownum，再分頁
    sql = """
        select * from
            (select ip_address,host_name,to_char(lease_expiry_time),to_char(lease_duration),to_char(time_span),rownum as rn
            from example_paging  order by ip_address )
        where rn>=%s and rn<%s
        """
    cursor.execute(sql, [start, end])
    result = cursor.fetchall()
    cursor.close()
    return result


# 查詢總數量
def all_count():
    cursor = connection.cursor()
    # Object of type 'datetime' is not JSON serializable ,用to_char轉換
    sql = "select count(*) from example_paging "
    cursor.execute(sql)
    result = cursor.fetchone()
    cursor.close()
    return result[0]


# 搜索
def query(search_key):
    cursor = connection.cursor()
    # Object of type 'datetime' is not JSON serializable ,用to_char轉換
    # django.db.utils.DatabaseError: ORA-01036: illegal variable name/number , 採用拼接的方法
    sql = "select ip_address,host_name,to_char(lease_expiry_time),to_char(lease_duration),to_char(time_span) " \
          "from example_paging where ip_address like '%%%s%%' " % search_key
    cursor.execute(sql)
    result = cursor.fetchall()
    sql = "select count(*) from example_paging where ip_address like '%%%s%%' " % search_key
    cursor.execute(sql)
    count = cursor.fetchone()[0]
    cursor.close()
    return result, count

参考资料：

https://www.datatables.net/examples/server_side/post.html