DataTables上移下移排序、拖拽排序

场景描述

最近做的项目，涉及到一个需求，就是需要动态的设置页面上内容的优先级，就是顺序。如下图

这个地方文章的顺序需要通过后台管理系统控制，那么问题来了，我们后台管理系统表格使用的是datatables

解决方案

那么现在就来说解决方案，首先根据需求大概想了一下思路，就是对应的表加上优先级的字段，然后上移、下移的时候交换两条数据的优先级即可。根据这个思路去官网找具体的实现方案，找了好久发现了rowreorder这个东西，发现跟这个需求比较吻合。通过查看官网的示例，发现这个是拖拽进行排序的，挺不错的，然后就开始干吧。

1. 首先前提是我们本地已经是使用的DataTables这个插件
2. 然后我们需要新添加dataTables.rowReorder.min.js(从官网下载)
   接下来就是代码了，首先是表格主体部分的代码

var table = $('#dataTables-example').DataTable({ 
        "responsive": true,
        "processing": true,
        "serverSide": true,
        "rowReorder": {
            dataSrc: 'priority'
        },
        "ajax": {
            "url":  '/admin/baseCategoryArticle/listAjax',
            "type": 'POST',
            "dataType": "json",
            "statusCode": {
                404: function() {layer.msg("您访问的页面不存在",{icon:5});},
                500: function() {layer.msg("未知错误，请稍后再试",{icon:5});}
            },
            "dataFilter": function(result, type){
                var json = jQuery.parseJSON( result );
                if(json.status !=10000){
                    layer.msg(json.message,{icon:5});
                    return false;
                }
                return result;
             },

            "data": function (d) {
                 d.baseCategoryId = $("#baseCategoryIdParam").val()
            }
        },
        "pagingType": "full_numbers",
        "stripeClasses": ["odd", "even"],
        "order": [[0, "desc"]],
        "searching": false,
        "columns": [
            {"data": "priority", className: 'reorder'},
            {"data": "article_cover_pic"},
            {"data": "article_title"},
            {"data": "sysUser"},
            {"data": "publish_time"},   
            {"data": "relation_id"}

        ], columnDefs: [
            {
                "targets": 0,
                "data": null,
                "searchable": false,
                "bSortable": false
            },
            {
                "targets": 1,
                "data": null,
                "searchable": false,
                "bSortable": false,
                "render": function (data, type, row, meta) {
                    var html = "";
                    if (data != '') {
                        html += "<img src='"+imgUrl+data+"' width='60px' height='30px'/>";
                    }
                    return html
                }
            },
            {
                "targets": 2,
                "data": null,
                "searchable": false,
                "bSortable": false,
            },
            {
                "targets": 3,
                "data": null,
                "searchable": false,
                "bSortable": false,
                "render": function (data, type, row, meta) {
                    var html = "";
                    if(row.sysUser == null || row.sysUser == '' || row.sysUser == undefined ){
                        return html;
                    }else{
                        return row.sysUser.nickname;
                    }

                }
            },{
                "targets": 5,
                "data": null,
                "searchable": false,
                "bSortable": false,
                "render": function (data, type, row, meta) {
                    var html = "";
                    html += ' <a href="javascript:void(0);" class="up btn btn-default btn-xs"><i class="fa fa-arrow-up"></i> 上升</a>'
                    html += ' <a href="javascript:void(0);" class="down btn btn-default btn-xs"><i class="fa fa-arrow-down"></i> 下降</a>'
                    return html;
                }
            },
            {
                "targets": 6,
                "data": null,
                "searchable": false,
                "bSortable": false,
                "visible": false,
            },
            {
                "targets": 7,
                "data": null,
                "searchable": false,
                "bSortable": false,
                "render": function (data, type, row, meta) {
                    var html = "";
                    html += "<button id='delete' class='btn btn-primary btn-xs' data-id=" + row.relation_id + ">删除关联</button>&nbsp";

                    return html;
                }
            }

        ],

        "language": {
            url:  '/static/js/lib/datatables/i18n/Chinese.json'
        }

    });

下面是排序核心的代码
拖拽排序代码

table.on( 'row-reorder', function ( e, diff, edit ) {
        var exchangeList = [];
        var result = 'Reorder started on row: '+eval(edit.triggerRow.data())+'<br>';

        for ( var i=0, ien=diff.length ; i<ien ; i++ ) {
            var rowData = table.row( diff[i].node ).data();

            result += rowData+' updated to be in position '+
                diff[i].newData+' (was '+diff[i].oldData+')<br>';
            exchangeList.push(rowData.relation_id+','+diff[i].newData);
        }

       console.info( 'Event result:<br>'+result );
       reOrder(exchangeList);
    } );

上移、下移排序代码

 // 初始化上升按钮
    $('#dataTables-example tbody').on('click', 'a.up', function(e) {
        var index = table.row($(this).parents('tr')).index();
        if ((index - 1) >= 0) {
            var data = table.data();
            var originData = data[index];
            var targetData = data[index-1];
            var exchangeList = [];
            exchangeList.push(originData.relation_id+','+targetData.priority);
            exchangeList.push(targetData.relation_id+','+originData.priority);
            reOrder(exchangeList);
            table.draw();
        } else {
            layer.msg('亲，已经到顶了',{icon:5});
        }

    });

    // 初始化下降按钮
    $('#dataTables-example tbody').on('click', 'a.down', function(e) {
        var index = table.row($(this).parents('tr')).index();
        var max = table.rows().data().length;
        if ((index + 1) < max) {
            var data = table.data();
            var originData = data[index];
            var targetData = data[index+1];
            var exchangeList = [];
            exchangeList.push(originData.relation_id+','+targetData.priority);
            exchangeList.push(targetData.relation_id+','+originData.priority);
            reOrder(exchangeList);
            table.draw();
        } else {
            layer.msg('亲，已经到底了',{icon:5});
        }
    });

公共方法

function reOrder(param) {
    $.ajax({
        type: "POST",
        url:  '/admin/baseCategoryArticle/exchange',
        dataType: "json",
        data:{"exchangeList":param},
        "statusCode": {
            404: function() {layer.msg("您访问的链接不存在",{icon:5});},
            500: function() {layer.msg("未知错误，请稍后再试",{icon:5});}
        },
        "dataFilter": function(result, type){
            var json = jQuery.parseJSON( result );
            if(json.status !=10000){
                layer.msg(json.message,{icon:5});
                return false;
            }
            return result;
        },

        success: function (msg) {
            if (msg.status == 10000) {
                layer.msg('优先级重排成功',{icon:6});
            }
            else {
                layer.msg('优先级重排失败',{icon:5});
            }
            //console.info(msg);

        }
    });
}

后台java代码，controller

    @RequestMapping(value = "/baseCategoryArticle/exchange", method = RequestMethod.POST)
    @ResponseBody
    public String exchange(@RequestParam(required = true, value = "exchangeList[]")String[] exchangeList ,HttpServletRequest request) {
        CustomResponse customResponse = new CustomResponse();
        if(exchangeList ==null || exchangeList.length == 0){
            return customResponse.getErrorJson("必填参数不能为空");
        }
        List<BaseCategoryRelation> listParam = new ArrayList<BaseCategoryRelation>(exchangeList.length);
        for(int i= 0 ;i < exchangeList.length;i++){
            BaseCategoryRelation temp = new BaseCategoryRelation();
            String[] splitArray = exchangeList[i].split(",");
            if(splitArray.length == 2){
                temp.setId(Long.parseLong(splitArray[0]));
                temp.setPriority(Integer.parseInt(splitArray[1]));
                temp.setModifyTime(new Date());
                listParam.add(temp);
            }
        }

        baseCategoryService.batchUpdateRelation(listParam);

        return customResponse.getSuccessJson("重新排序成功");
    }

dao层mybatis的mapper.xml文件

<!-- 批量更新文章优先级 start -->
  <update id="batchUpdateRelation" parameterType="java.util.List">
        update  base_category_relation
        <trim prefix="set" suffixOverrides=",">
              <trim prefix="priority = case" suffix="end," >
                 <foreach collection="listRelation" item="item" index="index">
                     <if test="item.priority!=null">
                      when id=#{item.id} then #{item.priority}
                     </if>
                 </foreach>
              </trim>
              <trim prefix="modify_time = case" suffix="end," >
                 <foreach collection="listRelation" item="item" index="index">
                     <if test="item.priority!=null">
                      when id=#{item.id} then #{item.modifyTime}
                     </if>
                 </foreach>
              </trim>
        </trim>
        where
        <foreach collection="listRelation" separator="or" item="item" index="index" >
            id = #{item.id,jdbcType=BIGINT}
      </foreach>
  </update>
<!-- 批量更新文章优先级 end -->

效果图如下

参考:

https://datatables.net/extensions/rowreorder/
https://editor.datatables.net/examples/extensions/rowReorder.html
https://datatables.net/extensions/rowreorder/examples/initialisation/events.html