jqGrid - 新行的唯一 ID

2022-01-19 00:00:00 jquery mysql jqgrid java free-jqgrid

我正在尝试为添加到 jqGrid 的新行分配一个唯一 ID.我使用免费的 jqGrid 4.11.0，一个用于服务器端的 java rest 服务和用于数据库的 MS SQL.

I am trying to asign a unique ID to the new rows i add to my jqGrid. I use free jqGrid 4.11.0, a java rest service for the server side and MS SQL for databases.

inlineEditing: {

    ajaxSaveOptions: { contentType: "application/json" },
    serializeSaveData: function (postData) {

        var idArray = grid.getCol('id');
        var count = 0;
        var k = 1;

            while(postData.id.search('jqg') != '-1'){

                for(var i =0 ; i<idArray.length;i++){

                    if(k == idArray[i]){
                        count++;
                    }
                    if(count == 0){

                        postData.id = k  ;
                        break;                              
                    }
                }                       
                k++;
            }               
        return JSON.stringify(postData);
    }
}

我注意到 jqGrid 给出的默认 ID 是 jqg + 一个数字.关于如何制作当前未在任何数据库记录上使用的唯一 ID 的任何建议?我应该从服务器端执行此操作还是可以在 jqGrid 代码中执行此操作?

I noticed the default ID given by jqGrid is jqg + a number. Any suggestions on how i could make a unique ID thats not currently in use on any of the database records? Should i do this from the server side or it can be made in the jqGrid code?

在晚上的某个时候，这段代码有点工作，并继续为所有新行提供 id 7 [即使它应该继续增加].经过一些更改[不记得有什么更改]，每次我单击保存以将新行提交到服务器时，页面冻结并且 chrome 建议我应该终止该进程.

At some point in the evening, this code was kind of working and kept on giving id 7 to all new rows [even though it should have kept on increasing]. After some changes[cant remember what changes], every time i click save to submit the new row to the server, the page freezes and chrome suggest i should kill the process.

请告诉我什么以及是否应该添加更多信息.

Please let me know what and if i should add more information.

$(function () {

var grid = $('#grid'),
    checkboxEditOptionEvents = [

        {type : 'click', data: {'Yes': 'Yes'}, fn: function (e, id) {
            var checkboxCol = grid.getCol('sefDepartament'),
                ids = grid.jqGrid('getDataIDs'),
                numberOfCheckedBoxes = [],
                k;

            for (k = 0; k < ids.length; k++) {
                    if(checkboxCol[k] == 'Yes'){
                        numberOfCheckedBoxes.push(checkboxCol[k]);
                        if (numberOfCheckedBoxes.length == 1){
                            alert('Please deselect the other checked box first');
                            $(this).prop("checked",false);
                            numberOfCheckedBoxes = 0;
                        }
                    }
        }
    }}];

var experienceFunction = function(cellvalue, options ,rowObject){

            var joinYearVar =  rowObject.joinYear,
             YY = joinYearVar.slice(0,4),
             MM = joinYearVar.slice(5,7),
             DD= joinYearVar.slice(8,11);

            return moment(""" +YY+MM+DD+""" , "YYYYMMDD").fromNow();

};

var checkboxFormatFunc = function(cellvalue, options ,rowObject){
    if(cellvalue == 'Yes'){
        return 'Yes';
    }
    return 'No';
};

var  afterSaveFunction =   function(id){

            var prenumeVar = grid.getCell(id, 'prenume').trim(),
                numeVar = grid.getCell(id,'nume').trim(),
                usernameVar =  numeVar +'.'+ prenumeVar,
                emailVar = usernameVar + '@test.com';
            usernameVar =usernameVar.replace(/s/g , '').trim();
            emailVar = emailVar.replace(/s/g , '');

            grid.setCell(id, 'username', usernameVar);
            grid.setCell(id, 'email', emailVar);

};

var colModelSettings = [

    {name:'id', label:'id',key: true,hidden: true, width:10,sorttype:'number',editable: false},     
    {name:'nume',label:'Nume',width:90, align: 'center',editable:true,searchoptions: {sopt: ['eq','bw','ew','cn']}, editrules:{required:true}, editoptions: {defaultValue: ' '},formatter: 'text'},
    {name:'prenume',label:'Prenume',width:100,editable:true,searchoptions: {sopt: ['eq','bw','ew','cn']},align: 'center',editrules:{required:true},editoptions: {defaultValue: ' '},formatter: 'text'},
    {name:'username',label:'Username',searchoptions: {sopt: ['eq','bw','ew','cn']},width:125,align: 'center'  },
    {name:'email',label:'Email',width:135,searchoptions: {sopt: ['eq','bw','ew','cn']},align: 'center'},
    {name:'sefDepartament',label:'Sef Departament',width:90,editable:true,align: 'center', stype:"select", searchoptions:{sopt: ['eq','ne'],value: "Yes:Yes;No:No"},formatter: checkboxFormatFunc,edittype:'checkbox',editoptions: { dataEvents: checkboxEditOptionEvents,value:'Yes:No', defaultValue: 'No' }},
    {name:'position',label:'Position',editable:true,stype: 'select',formatter: 'select',searchoptions: {sopt: ['eq','ne'],value: ' : ;position 1:position 1;position 2:position 2;position 3:position 3;position 4:position 4;position 5:position 5'},
    align: 'center',edittype:'select',editoptions:{defaultvalue: 'P0: ',value: ' : ;position 1:position 1;position 2:position 2;position 3:position 3;position 4:position 4;position 5:position 5'},width: 75},

    {name:'joinYear',label:'Join Year',formatter:'date', formatoptions: {newformat:'d-m-Y'}, datefmt: 'dd-mm-yyyy', editable:true,searchtype: 'datepicker',align: 'center',width: 70,
    searchoptions:{dateFormat:'dd-mm-yy',dataInit: function (elem){
        $(elem).datepicker({ showButtonPanel: true, dateFormat: 'yy-mm-dd'});},sopt: ['eq','ne']},

    editoptions:{size:20,defaultValue: ' ',dataInit: function (elem) {

        $(elem).datepicker({ showButtonPanel: true, dateFormat: 'dd-mm-yy'});
    }}},

    {name:'experience', label:'Experience', formatter: experienceFunction, searchoptions:{sopt: ['eq','bw','ew','cn']}, editable:'hidden', editoptions:{defaultValue: ' '},align: 'center',width: 60},
    {name:'actiuni',label: 'Actiuni',formatter: 'actions', formatoptions: {afterSave:afterSaveFunction},editable: false,sortable: false,search: false,width: 20 }
    ];

grid.jqGrid({

    pager: '#pager', 
    url: "/RestWithDatabaseConnection/rest/fetchData",
    editurl:'/RestWithDatabaseConnection/rest/update',
    datatype: "json",
    height: 250,    
    viewrecords: true,
    scrollOffset:0,
    sortorder: 'asc', 
    caption:'Employee List' ,
    autowidth: true,
    colModel: colModelSettings,
    beforeSelectRow : function(id){ 

                        var idsArray = grid.jqGrid('getDataIDs');
                        var i;
                        for(i=0;i<idsArray.length;i++){
                            if($('#'+idsArray[i]).is('[editable="1"]') ){
                            grid.editRow(idsArray[i],true);
                            return false;
                            }
                        }   
                        return true;
                    },

    inlineEditing: {

        ajaxSaveOptions: { contentType: "application/json" },
        serializeSaveData: function (postData) {

            var idArray = grid.getCol('id');
            var count = 0;
            var k = 1;

                while(postData.id.search('jqg') != '-1'){

                    for(var i =0 ; i<idArray.length;i++){

                        if(k == idArray[i]){

                            count++;
                        }
                        if(count == 0){

                            postData.id = k  ;
                            break;
                        }
                    }
                    k++;
                }

            return JSON.stringify(postData);
        }
    }
});


grid.jqGrid('navGrid', '#pager', {edit:false, add:false, delete:true, save:false, cancel:false, search:true, searchtext: 'Search', refresh:true},

        {},{},{     
                url: '/RestWithDatabaseConnection/rest/delete',
                mtype: 'DELETE',
                reloadAfterSubmit: true,
                ajaxDelOptions: {
                    contentType: "application/json",
                },
                serializeDelData: function(postdata) {

                    return JSON.stringify(postdata);

            }},{},{},{},{} );

grid.jqGrid('inlineNav','#pager',
{
    edit:true,
    edittext: 'Edit',
    save:true,
    savetext: 'Save',
    add:true,
    cancel: true,
    canceltext: 'Cancel',
    cancelicon: 'ui-icon-cancel',
    addicon:'ui-icon-plus',
    addtext: 'Add',
    addedrow: 'last',
    addParams: {

        position: 'last',
        addRowParams: { 

            aftersavefunc : afterSaveFunction,
            keys: true,
        }
            },
    editParams:{

        url: '/RestWithDatabaseConnection/rest/update',
        mtype : "POST",
        keys: true,
        aftersavefunc : afterSaveFunction,
    }
})})

Edit2 - 服务器对 fetchData 的响应:

Edit2 - Server Responce to fetchData : 

[{"id":"3","nume":"Aladin","prenume":"Zoro","username":"Aladin.Zoro","email":"Aladin.Zoro@test.com","sefDepartament":"Yes","position":"position 4","joinYear":"2015-11-08","experience":"2 months"},

{"id":"2","nume":"Harap","prenume":"Alb","username":"Harap.Alb","email":"Harap.Alb@test.com","sefDepartament":"No","position":"position 1","joinYear":"2016-01-03","experience":"9 days  "},

{"id":"4","nume":"Don","prenume":"Homa","username":"Don.Homa","email":"Don.Homa@test.com","sefDepartament":"No","position":"position 4","joinYear":"2015-09-06","experience":"4 months"},

{"id":"5","nume":"Dorel","prenume":"Gigel","username":"Dorel.Gigel","email":"Dorel.Gigel@test.com","sefDepartament":"No","position":"position 4","joinYear":"2016-01-10","experience":"2 days"},

{"id":"1","nume":"Ivan","prenume":"Stefan","username":"Ivan.Stefan","email":"Ivan.Stefan@test.com","sefDepartament":"No","position":"position 2","joinYear":"2016-01-10","experience":"2 days"}]

推荐答案

以下是解决您的主要问题并改进您发布的 JavaScript 代码的一些建议.

Below are some advises to solve your main problem and to improve the JavaScript code which you posted.

首先，本地编辑场景需要locally生成新的rowids.应该在服务器上生成新的 rowids ，以防将后端的数据保存在数据库中.典型的实现包括在 every 表中将 PRIMARY KEY 定义为 int IDENTITY.它使 id 唯一且固定.删除某些行并创建新行永远不会被解释为对旧行的编辑，因为新行将始终获得新的 id，它以前从未使用过(在表中).

First of all, the generation of new rowids locally is required for local editing scenario. One should generate the new rowids on the server in case of saving the data on the backend in the database. Typical implementation consist on having PRIMARY KEY defined as int IDENTITY in every table. It makes the ids unique and fixed. Deleting of some row and creating the new one will never be interpreted as editing of the old row because the new row will get always new id, which was never used before (in the table).

要利用在服务器端生成的 id，主要有两种选择:

To have advantage of ids generated on the server side one have two main choices:

  1. 在每次添加行操作后重新加载网格.
  2. 在编辑时扩展与服务器的通信，以便服务器将在数据库表中生成的新 id 返回给 jqGrid.在服务器上成功创建行后，可以使用 aftersavefunc 回调(仅用于添加新行)来更新 rowid.RESTful 服务的许多标准实现会在添加或编辑时返回全行数据包含的 id.可以使用 aftersavefunc 回调中的数据并使用 $("#" + rowid).attr("id", newRowid); 之类的东西来更新新行.它一个将 id 保存在一些额外的列中(比如你使用隐藏的 id 列)然后一个人应该另外使用 setCell 方法来更新单元格.
  1. reloading the grid after every Add row operation.
  2. extending the communication with the server on editing so, that the server returns new id, generated in the database table, back to jqGrid. One can use aftersavefunc callback (for Add new row only) for updating the rowid after successful creating the row on the server. Many standard implementations of RESTful services returns back full row data inclusive id on both Add or Edit. One can use the data inside of aftersavefunc callback and use something like $("#" + rowid).attr("id", newRowid); to update the new row. It one saved the id in some additional columns (like you use hidden id column) then one should use setCell method additionally to update the cell too.

第一个选择是最简单的，我建议你首先实现它.只有当重新加载网格不能满足用户，不断添加许多行时，你才应该多写一点代码来实现第二种情况.

The first choice is the mostly simple and I would recommend you to implement it first of all. Only if reloading of the grid will not satisfy the users, who add many rows one after another, then you should write a little more code and to implement the second scenario.

您当前的代码使用 inlineNav 进行添加和编辑操作，使用内联编辑实现，使用方法 navGrid 进行删除操作，使用表单编辑实现.表单编辑，包括删除，默认使用 reloadAfterSubmit: true 选项.这意味着在删除每一行后，将从服务器重新加载网格(来自 url: "/RestWithDatabaseConnection/rest/fetchData").您可以通过将 afterSaveFunction 替换为以下内容来解决您的主要问题:

You current code use inlineNav for Add and Edit operations, implemented using inline editing, and the method navGrid for Delete operation, implemented using form editing. The form editing, inclusive Delete, uses reloadAfterSubmit: true option by default. It means that the grid will be reloaded from the server (from url: "/RestWithDatabaseConnection/rest/fetchData") after deleting of every row. You can solve your main problem by replacing afterSaveFunction to the following:

var afterSaveFunction = function () {
        $(this).trigger("reloadGrid", [{current: true, fromServer: true}]);
    };

选项 current 用于在重新加载后保持当前选择和选项 fromServer: true 仅在您使用 loadonce: true 额外选项.您可以使用 navGrid 的 reloadGridOptions: {fromServer: true} 选项来强制重新加载数据从服务器点击刷新/重新加载导航栏的按钮.如果您没有太多需要在网格中显示的数据(例如少于 1000 行)，那么建议您使用这种行为.

The option current to hold the current selection after reloading and the option fromServer: true have sense only in case if you use loadonce: true option additionally. You can just use reloadGridOptions: {fromServer: true} option of navGrid to force reloading the data from the server on click on the Refresh/Reload button of the navigator bar. If you have not so much data which you need to display in the grid (for example less as 1000 of rows) then such behavior would be recommended.

一些更常见的建议来改进你的代码:

Some more common advices to you to improve your code:

您可以考虑使用 height: "auto" 而不是 height: 250 并通过指定 rowNum 来管理网格的最大高度> 价值.在这种情况下将不需要选项 scrollOffset: 0.

You can consider to use height: "auto" instead of height: 250 and to manage the maximal height of the grid by specifying rowNum value. The option scrollOffset: 0 will be unneeded in the case.

从服务器返回的数据格式看起来如此，您没有实现服务器端的分页、排序和过滤.您应该使用 loadonce: true 和 forceClientSorting: true 选项.loadonce: true 通知jqGrid 将all 从服务器返回的数据本地保存在内部data 参数中.您可以随时使用 $('#grid').jqGrid("getGridParam", "data") 访问数组.rowNum 的值(默认值为 20)将用于 local 分页.sortname 和 sortorder 将用于 local 排序.您将使用搜索对话框(由 navGrid 添加)或过滤器工具栏(由 filterToolbar 添加)进行 local 搜索/过滤.它简化了服务器代码，从用户的角度提高了网格的性能，并简化了服务器和客户端之间的接口.您可以在服务器上使用经典的 RESTful 接口，无需任何扩展.

The format of the data returned from the server looks so, that you don't implemented server side paging, sorting and filtering. You should use loadonce: true and forceClientSorting: true options. The loadonce: true informs jqGrid to save all the data returned from the server locally in internal data parameter. You can any time access the array by usage $('#grid').jqGrid("getGridParam", "data"). The value of rowNum (the default value is 20) will be used for local paging. The sortname and the sortorder will be used for local sorting. And you will use searching dialog (added by navGrid) or the filter toolbar (added by filterToolbar) for local searching/filtering. It simplify the server code, improve the performance of the grid from the user's point of view, and simplifies the interface between the server and the client. You can use the classical RESTful interface on the server without any extensions.

另外一句话:我建议你删除不需要的隐藏 id 列(name:'id', label:'id', key: true, hidden: true, ...).rowid 的信息将保存在行的 id 属性中(<tr> 元素)，并且不需要在隐藏的 <td> 元素在每一行.

Another remark: I'd recommend you to remove unneeded hidden id column (name:'id', label:'id', key: true, hidden: true, ...). The information about the rowid will be saved in id attribute of the rows (<tr> element) and one don't need to hold duplicate information in the hidden <td> element in every row.

您的代码还有许多其他部分可以改进.例如，您在服务器端使用的 DELETE 操作似乎很奇怪.您使用 mtype: 'DELETE'，但您将请求的 body 内已删除行的 id 发送到服务器，而不是将其附加到 URL.对应于标准，HTTP DELETE 应该包含 no body.您可以使用 jqGrid 选项 formDeleting 指定所有删除选项，您可以将 url 参数定义为函数:

There are many other parts of your code, which could be improved. For example the DELETE operation which you use on the server side seems be strange. You use mtype: 'DELETE', but you send the id of deleted row inside of body of the request to the server instead of appending it to the URL. Corresponds to the standards, the HTTP DELETE should contains no body. You can use jqGrid option formDeleting to specify all Delete options and you can define url parameter as function:

formDeleting: {
    mtype: "DELETE",
    url: function (rowid) {
        return "/RestWithDatabaseConnection/rest/delete/" + rowid;
    },
    ajaxDelOptions: { contentType: "application/json" },
    serializeDelData: function () {
        return "";
    }
}

您需要修改您的服务器代码 /RestWithDatabaseConnection/rest/delete/ 以使用相同的通信协议并从 URL 中获取删除的 id.

You need of cause modify your server code of /RestWithDatabaseConnection/rest/delete/ to use the same communication protocol and to get the id of deleted from from the URL.

你可以使用free jqGrid的navOptions参数来指定navGrid的选项:

You can use navOptions parameter of free jqGrid to specify the options of navGrid:

navOptions: { edit: false, add: false }

(searchtext: 'Search' 和您使用的其他选项似乎有默认值，我在那里删除了).

(searchtext: 'Search' and other options which you use seems to have default values and I removed there).

为了更接近 REST 标准，可以使用 HTTP PUT 操作进行行编辑，使用 HTTP POST 添加新行.您应该在后端为这两个操作实现不同的入口点.您已经使用 /RestWithDatabaseConnection/rest/update 并且可以实现 /RestWithDatabaseConnection/rest/create 来添加新行.例如，您可以使用以下 inlineEditing 更改来实现场景:

To be closer to REST standards one can use HTTP PUT operation for row editing and HTTP POST for adding new rows. You should implement different entry points for both operation on the backend. You use /RestWithDatabaseConnection/rest/update already and you can implement /RestWithDatabaseConnection/rest/create for adding new rows. You can use the following inlineEditing changes for example to implement the scenario:

inlineNavOptions: { add: true, edit: true },
inlineEditing: {
    url: function (id, editOrAdd) {
        return "/RestWithDatabaseConnection/rest/" +
            (editOrAdd === "edit" ? "update" : "create");
    },
    mtype: function (editOrAdd) {
        return editOrAdd === "edit" ? "PUT" : "POST";
    },
    keys: true,
    serializeSaveData: function (postData) {
        return JSON.stringify(dataToSend);
    },
    aftersavefunc: function () {
        $(this).trigger("reloadGrid", [{current: true, fromServer: true}]);
    },
    addParams: {
        addRowParams: {
            position: "last",
            serializeSaveData: function (postData) {
                var dataToSend = $.extend({}, postData);
                // don't send any id in case of creating new row
                // or to send `0`:
                delete dataToSend.id; // or dataToSend.id = 0; 
                return JSON.stringify(dataToSend);
            }
        }
    }
}

相关文章