java 解析ExtJS的GridPanel的Filter请求服务器的过滤条件

之前做的一个项目用的ExtJS 3.3.1, 现在做个新的换4.1了, GridPanel的Filter在ExtJS 3.3.1里面是plugin, 在4.1里是feature, 前台配置上有些变化, 不过后台处理是一样的

影响后台处理关键的config option是decode, 为false的时候, 一个列的过滤条件是对应多个parameter的, 例如

filter[0][data][type]: string

filter[0][data][value]: chen

filter[0][field]: login_name

为true的时候, 传递给后台的过滤条件, 仅仅就是一个parameter了, 以JSONObject的形式, 容易解析一些, 推荐后者

filter:[{"type":"string","value":"SYS","field":"login_name"}]

JSON解析使用的是net.sf.json相关的类

java代码如下, 写成了一个静态方法

第2个参数是columnMap, 代表前台的GridPanel的Column的dataIndex与数据库的列名之间的映射关系, 如果一致就设置为null即可

数据库是oracle, 主要是日期格式上要注意

其实编码和不编码的后半部分组织SQL的where条件的代码是一样的, 可以合并, 当初复制的 呵呵

	public static String generateFilterWhereCondition(HttpServletRequest request, Map<String, String> columnMap){
		//decode:false的情况
		String regex = "filter\\[(\\d+)\\]\\[(\\w+)\\](\\[(\\w+)\\])?";
//		boolean requestContainFilter = false;
		Enumeration params = request.getParameterNames();
		//未转义的参数列表
		List<String> paramNames = new ArrayList<String>();
		while(params.hasMoreElements()){
			String paramName = (String)params.nextElement();
			if(paramName.matches(regex)){
//				requestContainFilter = true;	//确定request包含filter
				if(columnMap != null && columnMap.get(paramName) != null){
					paramNames.add(columnMap.get(paramName));
				}else{
					paramNames.add(paramName);
				}
			}
		}
		//这里判断Filter的encode是false还是true
		//未编码, 悲剧解析
		if(paramNames.size() > 0){
			Collections.sort(paramNames);
			Map<Integer, Map> paramList = new HashMap<Integer, Map>();
			int index = -1;
			for(Iterator<String> iteratori = paramNames.iterator(); iteratori.hasNext();){
				Pattern p = Pattern.compile(regex);
				String paramName = iteratori.next();
				Matcher m = p.matcher(paramName);
				System.out.println(paramName + ": " + request.getParameter(paramName) + ", group count: " + m.groupCount());
				if(m.matches() && m.groupCount() > 0){
	//				for(int i = 1; i <= m.groupCount(); i ++){
	//					System.out.println("group " + i + ": " + m.group(i));
	//				}
					int currentIndex = Integer.valueOf(m.group(1));
	//				System.out.println("current index: " + currentIndex);

					Map paramMap = null;
					if(currentIndex != index){
						index = currentIndex;
						paramMap = new HashMap();
						paramList.put(currentIndex, paramMap);
					}else{
						paramMap = paramList.get(currentIndex);
					}
					String key = m.group(2);
					//有可能是ComboBox的一组值
					String value = request.getParameter(paramName);
					String[] values = request.getParameterValues(paramName);
					if(key.equals("data")){
						key = m.group(4);
					}
					if(key.equals("value")){
						paramMap.put(key, values);
					}else{
						paramMap.put(key, value);
					}
				}else{
					System.out.println("not matched");
				}
			}
			System.out.println(paramList.size());
			String condition = "";
			for(Map paramInfo : paramList.values()){
				String clause = "";
				String columnName = (String)paramInfo.get("field");
				String type = (String)paramInfo.get("type");
				String comparison = (String)paramInfo.get("comparison");
				String[] values = (String[])paramInfo.get("value");
				System.out.println("clause info: " + paramInfo);
				//根据数据类型的不同组织SQL的WHERE子句
				String compare = "";
				if(comparison == null){
					compare = " LIKE ";
				}else if(comparison.equals("eq")){
					compare = " = ";
				}else if(comparison.equals("lt")){
					compare = " < ";	//用不用<=呢
				}else if(comparison.equals("gt")){
					compare = " > ";
				}
				if(type.equals("numeric")){
					clause = " AND " + columnName + compare + values[0];
				}else if(type.equals("string")){
					if(compare.equals(" LIKE ")){
						clause = " AND " + columnName + compare + "'%" + values[0] + "%'";
					}
				}else if(type.equals("date")){
					//日期的话还要解析, 而且数据库的列的类型也要是date
					clause = " AND " + columnName + compare + "TO_DATE('" + values[0] + "', 'mm/dd/yyyy hh24:mi:ss')";
				}else if(type.equals("list")){
					//这里的判断比较复杂, 应该是用IN, 不会用LIKE OR吧
					clause = " AND " + columnName + " IN(";
					for(String value : values){
						clause += "'" + value + "', ";
					}
					clause = clause.substring(0, clause.length() - 2) + ")";
				}else if(type.equals("boolean")){
					//布尔值, 这还要对照数据库的类型, 后来我用转换算是搞定了
					compare = " = ";
					boolean value = Boolean.valueOf(values[0]);
//					String value = values[0];
					if(value){
						clause = " AND (UPPER(TO_CHAR(" + columnName + ")) = '1' OR UPPER(TO_CHAR(" + columnName + ")) = 'Y')";
					}else{
						clause = " AND (UPPER(TO_CHAR(" + columnName + ")) = '0' OR UPPER(TO_CHAR(" + columnName + ")) = 'N' OR " + columnName + " IS NULL)";
					}
				}
				System.out.println("clause: " + clause);
				condition += clause;
			}
			return condition.length() > 5 ? condition.substring(5) : condition;
			
			
		}else{
			//编码, 头顶青天
			String json = request.getParameter("filter");
			if(json != null){
				String whereCondition = "";
//				JSONObject object = JSONObject.fromObject(map, Constant.JSON_DATE_TIMESTAMP);
				JSONArray conditionArray = JSONArray.fromObject(json, configJson("mm/dd/yyyy hh24:mi:ss"));
				for(Object condition : conditionArray.toArray()){
					System.out.println(condition.toString());
					String clause = "";
					Map rowMap = (Map)JSONObject.toBean((JSONObject)condition, Map.class);
					String columnName = (String)rowMap.get("field");
					//查看是否需要转义
					if(columnMap != null && columnMap.get(columnName) != null){
						columnName = columnMap.get(columnName);
					}
					String jsType = (String)rowMap.get("type");
					String comparison = (String)rowMap.get("comparison");
					String compare = "";
					if(comparison == null){
						compare = " LIKE ";
					}else if(comparison.equals("eq")){
						compare = " = ";
					}else if(comparison.equals("lt")){
						compare = " < ";	//用不用<=呢
					}else if(comparison.equals("gt")){
						compare = " > ";
					}
					if(jsType.equals("list")){
						//"value":["ext-record-1","ext-record-2"] 是ArrayList
//						Object[] values = (Object[])rowMap.get("value");
						List values = (List)rowMap.get("value");
						clause = " AND " + columnName + " IN(";
						for(Object value : values){
							clause += "'" + value.toString() + "', ";
						}
						clause = clause.substring(0, clause.length() - 2) + ")";
					}else{
						if(jsType.equals("numeric")){
							Object value = rowMap.get("value");
							clause = " AND " + columnName + compare + String.valueOf(value);
						}else if(jsType.equals("string")){
							String value = (String)rowMap.get("value");
							if(compare.equals(" LIKE ")){
								clause = " AND " + columnName + compare + "'%" + value + "%'";
							}
						}else if(jsType.equals("date")){
							String value = (String)rowMap.get("value");
							//日期的话还要解析, 而且数据库的列的类型也要是date
							clause = " AND " + columnName + compare + "TO_DATE('" + value + "', 'mm/dd/yyyy hh24:mi:ss')";
						}else if(jsType.equals("boolean")){
							//布尔值, 这还要对照数据库的类型, 后来我用转换算是搞定了
							compare = " = ";
							boolean value = (Boolean)rowMap.get("value");
							if(value){
								clause = " AND (UPPER(TO_CHAR(" + columnName + ")) = '1' OR UPPER(TO_CHAR(" + columnName + ")) = 'Y')";
							}else{
								clause = " AND (UPPER(TO_CHAR(" + columnName + ")) = '0' OR UPPER(TO_CHAR(" + columnName + ")) = 'N' OR " + columnName + " IS NULL)";
							}
						}
					}
					System.out.println("clause: " + clause);
					whereCondition += clause;
				}
				return whereCondition.length() > 5 ? whereCondition.substring(5) : whereCondition;
			}
			return "";
		}
	}

public static JsonConfig configJson(String format){  
	    JsonConfig jcf = new JsonConfig();  
	    //对Map的value有效, 但value里面还有Map, 这个Map里有Date的value, 就不起作用了
	    if(format == null || format.equals("")){
	    	jcf.registerJsonValueProcessor(Date.class, new JsonDateValueProcessor());  
	    }else if(format.equals("timestamp")){
	    	jcf.registerJsonValueProcessor(Date.class, new JsonTimestampValueProcessor());  
	    }else{
	    	jcf.registerJsonValueProcessor(Date.class, new JsonDateValueProcessor(format));  
	    }
	    return jcf;  
	}