jqgrid columnChooser列的自定义及存储和获取

来源：互联网发布：2016淘宝服装销售排行编辑：程序博客网时间：2024/06/05 02:10

jggrid可以通过setcolumns设置列的自定义显示，不过这种方法已经被否决了,现在的方法是columnChooser。我们的问题是既然用户可以选择需要显示和隐藏那些列，那么是否可以把这些信息都存储起来，在用户再次打开这些表时，可以显示对应的列。有很多种方式存储这个数据，可以用浏览器本地存储localstorage,但是问题是当用户换了浏览器或者电脑时就不行了，现在以用mysql存储作为例子。selectColumn用于获取需要隐藏的列，并存入数据库。getHiddencolumn用于获取该表的信息和对表的部分列进行隐藏。

/** * Multiselect save and get cloumn information  * * @author cc_fys */ function selectColumn(tableName,pageName,myID)    {    console.log(tableName);   $(tableName).jqGrid('navGrid',pageName,{add:false,del:false,edit:false,search:false,refresh:false});    $(tableName).jqGrid('navButtonAdd', pageName, {         caption: "隐藏显示列",         buttonicon: "ui-icon-calculator",         title: "选择列",          onClickButton: function () {             $(tableName).jqGrid('columnChooser',{width: 550, dialog_opts: {modal: true,minWidth: 470,height: 470,show: 'blind',hide: 'explode',dividerLocation: 0.5}, done : function (perm) {      if (perm) {          //点击确定           this.jqGrid("remapColumns", perm, true);          var width = $(window).width()-150;             $(tableName).setGridWidth(width,true);           //要存的就json,已经 隐藏部分的部分            //获取列名                      var colModel=$(tableName).jqGrid('getGridParam','colModel');                      var newColumnName = [];                      for (var i=0;i<colModel.length;i++)                       {                          var columnHidden = colModel[i].hidden;                           if(columnHidden==true)                          {                             // newColumnName.push(colNames[i]);                          newColumnName.push(i);                          }                      }                    var strHidden=JSON.stringify(newColumnName);                   console.log(strHidden);                   //数据库存储                   $.ajax({   type : "POST",   url : "myurl",       data:{serverID:myID,hiddenC:strHidden},   success : function(data) {   console.log('finish');   }});         } }});         }  });          };    function getHiddencolumn(tableName,myID)    {     $.ajax({ type : "POST", url : "myurl",     data:{serverID:myID}, success : function(data) { console.log(data);  if(data&&data!=''&&data!='[]')//这里做隐藏列 {     var jsonData=JSON.parse(data);     var colModel=$(tableName).jqGrid('getGridParam','colModel');      jsonData.forEach(function(item,index)     {     //console.log(colModel[item].name);     $(tableName).setGridParam().hideCol(colModel[item].name).trigger("reloadGrid");     });     //调整宽度     var width = $(window).width()-150;            $(tableName).setGridWidth(width,true);  }  }});        };

package ciss.controller.savecolumn;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.cxf.common.classloader.FireWallClassLoader;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import ciss.web.jdbc.MySQL;@Controller@RequestMapping("/mysql")public class SaveColume {@ResponseBody@RequestMapping(value = "/setHiddenColumn")//这个改为分以下两步，//没有列话就先添加一列之后再存数据//没有用户的话添加一行用户//有用户有行之后才更新public String setHiddenColumn(HttpServletRequest request, HttpServletResponse response)    {        String ID=request.getParameter("serverID");        String userName=request.getSession().getAttribute("userName").toString();        String hiddenColume=request.getParameter("hiddenC");        //String sql= "insert into hide_column(username,c"+ID+") values(?,?)";         String sql="UPDATE hide_column set c"+ID+"='"+hiddenColume+"' where username='"+userName+"'";          //System.out.println(sql);        Connection conn = MySQL.getConnection();        if(addUserAndRow(conn,userName,ID))        {   try {         PreparedStatement ps = conn.prepareStatement(sql);         ps.executeUpdate();         System.out.println("添加成功！");         return "finish"; }catch (SQLException e) {  e.printStackTrace(); return "更改数据失败！";     }        }        else        {return "创建列或用户出错啦！";        }}@ResponseBody@RequestMapping(value = "/getHiddenColumn")public String getHiddenColumn(HttpServletRequest request, HttpServletResponse response)    {   ResultSet resultSet;    String result="[]";    String ID=request.getParameter("serverID");        String userName=request.getSession().getAttribute("userName").toString();        System.out.println(ID);        System.out.println(userName);        String sql= "select * from hide_column where username='"+userName+"'";         System.out.println(sql);        Connection conn = MySQL.getConnection();       try {        PreparedStatement ps = conn.prepareStatement(sql);          resultSet = ps.executeQuery(sql);        if(isExistColumn(resultSet,"c"+ID))        {        resultSet.first();        System.out.println(resultSet.getString("c"+ID));        result=resultSet.getString("c"+ID);        }            resultSet.close();            //conn.close();          }catch (Exception e) {        e.printStackTrace();        }return result; }//这个是用来创建表的，一般情况下不要调用！public String addHiddenColumn(HttpServletRequest request, HttpServletResponse response)    {Connection conn = MySQL.getConnection();for(int i=200;i<=1000;i++){String sql="alter table hide_column add c"+i+" varchar(255) default '[]'";          System.out.println(sql);   try {         PreparedStatement ps = conn.prepareStatement(sql);         ps.executeUpdate();   }catch (SQLException e) { e.printStackTrace();     }}return "finish";   }//查看 是否有某一列public boolean isExistColumn(ResultSet rs, String columnName) {      try {          if (rs.findColumn(columnName) > 0 ) {              return true;          }       }      catch (SQLException e) {          return false;      }            return false;  } //添加指定用户 或者 指定字段public boolean addUserAndRow(Connection conn,String userName,String rowName) {      try {      String sql= "select * from hide_column where username='"+userName+"'";     PreparedStatement ps = conn.prepareStatement(sql);          ResultSet rs= ps.executeQuery(sql);        if(isExistColumn(rs,"c"+rowName)==false)        {//添加新列        String sql1="alter table hide_column add c"+rowName+" varchar(255) default '[]'";          PreparedStatement ps1 = conn.prepareStatement(sql1);      ps1.executeUpdate();         }        if(rs.next())        {         System.out.println("不用添加用户");        }        else         {//添加新用户        String sql2= "insert into hide_column(username,c"+rowName+") values(?,?)";         System.out.println(sql2);        PreparedStatement ps2 = conn.prepareStatement(sql2);          ps2.setString(1, userName);        ps2.setString(2, "[]");          ps2.executeUpdate();         }             return true;        }      catch (SQLException e)        {          return false;         }  } }

存储的数据像这样，前端比较好处理

最后只需要在表格初始化的地方调用就可以了，非常简便。其中需要传递表格ID，page的ID，以及该表的标识符。

getHiddencolumn('#tables0',1367);selectColumn('#tables0','#page0',1367);

0 0