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

        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);