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包含filterif(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")){//日期的话还要解析, 而且数据库的列的类型也要是dateclause = " 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");//日期的话还要解析, 而且数据库的列的类型也要是dateclause = " 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;  }