最近接了个项目,客户要求项目支持高级搜索,他可以自选字段,然后自选运算符,然后输入值,字段可随意组合,类似于下图。
![](https://img-blog.csdnimg.cn/img_convert/1c20dc454024dfd1d2c788f173e80fc9.png)
1 前端处理
遇到问题首先抽象:
字段类型一般有4种,第一种普通输入框,第二种日期,第三种数字,第四种下拉。
如果是下拉的话,只支持精确匹配 =, 其他的支持所有的运算符。
按照我的性格,这个高级搜索功能肯定是要配置出来的,而不是傻乎乎的写html代码。
首先确定配置JSON 文件格式,然后做通用页面,解析配置,配置比较简单,就不多解释了
1 2 3 4 5 | var advanceConfig = [{name:'name',title:'人员姓名',type:'input'}, {name:'sex',title:'性别',type:'book',code:'sex'}, {name:'birthday',title:'出生年月',type:'date',formart:'yyyy-MM'}, {name:'primaryClassification',title:'一级分类',type:'book',code:'primary_classification'}, ]; |
然后写公共代码。
<script type="text/javascript" src="${fhs_static_url}js/baidutemplate.js"></script> <script type="text/javascript" src="${fhs_static_url}js/My97DatePicker/WdatePicker.js"></script> <script>
//记录每个index是类型是什么 var indexType = {};
var advnaceIsInit= false;
var filter_index = 0;
function openAdvance(){ advanceConfig = advanceConfig.filter(function(val){ return !(!val || val === ""); }); for(i=0;i<advanceConfig.length;i++){ if(advanceConfig[i]){ advanceConfig[i].index = (i+1); } } if(!advnaceIsInit){ addFilter(); advnaceIsInit = true; } $('#advanceDialog').dialog('open').dialog('setTitle', '高级搜索'); }
//添加一个过滤条件 function addFilter(){ var _html = baidu.template('rowTemplate',{filterIndex:filter_index}); $('#advanceFormDiv').append(_html); $.parser.parse('#filter' + filter_index); filter_index = filter_index + 1; }
//修改右侧内容 function filterFieldChange(_tempIndex,_record){ var _tempHtml = ''; indexType[_tempIndex] = _record; var _needSetFilterTypeReadonly = false; if(_record.type=='input' || _record.type=='number'){ _tempHtml = '<input type="text" id="advanceFilterVal' + _tempIndex + '"/>'; } else if(_record.type=='date'){ if(!_record.formart){ _record.formart = 'yyyy-MM-dd'; } console.log(_record.formart); _tempHtml = '<input type="text" id="advanceFilterVal' + _tempIndex + '" readonly οnclick="WdatePicker({dateFmt:\'' + _record.formart + '\'})"/>'; } else if(_record.type=='book'){ _tempHtml = '<input type="text" id="advanceFilterVal' + _tempIndex + '" class="easyui-combobox"' + ' url="${fhs_basics_url}/webApi/wordbook/getData?wordbookGroupCode=' + _record.code + '&jsonpCallback=?" valueField="wordbookCode" textField="wordbookDesc"/>'; _needSetFilterTypeReadonly = true; } else if(_record.type=='select'){ _tempHtml = '<input type="text" id="advanceFilterVal' + _tempIndex + '" class="easyui-combobox"' + ' url="' + _record.url + '" valueField="'+_record.valueField+'" textField="'+_record.textField+'"/>'; _needSetFilterTypeReadonly = true; } if(_record.name=='searchKey'){ _needSetFilterTypeReadonly = true; } if(_needSetFilterTypeReadonly){ $('#advanceFilterType' + _tempIndex).combobox('setValue','0'); $('#advanceFilterType' + _tempIndex).combobox('readonly',true); }else{ $('#advanceFilterType' + _tempIndex).combobox('readonly',false); } if(_record.searchKeyType =='str'){ $('#advanceFilterType' + _tempIndex).combobox('setValue','1'); } $('#advanceFilterValLable' + _tempIndex).html(_tempHtml); $.parser.parse('#advanceFilterValLable' + _tempIndex); }
var extAdvanceFilterParam = []; function execAdvanceSearch(){ extAdvanceFilterParam = []; for(i=0;i<filter_index;i++){ if($('#advanceFilterField' + i).length>0 && $('#advanceFilterField' + i).combobox('getValue')){ var _val = ''; if(indexType[i].type!=='book' && indexType[i].type!=='select'){ _val = $('#advanceFilterVal' + i).val(); }else{ _val = $('#advanceFilterVal' + i).combobox('getValue'); } if(_val){ extAdvanceFilterParam.push({name:indexType[i].name,val:_val, filterType:$('#advanceFilterType' + i).combobox('getValue'), searchKeyType:indexType[i].searchKeyType, fieldName:indexType[i].fieldName}); } } } $('#listGrid').datagrid('load', { extAdvanceFilterParam:JSON.stringify({filterType:$('#advanceFilterType').combobox('getValue'),extAdvanceFilterParamArray:extAdvanceFilterParam}) }); //reload(); console.log(extAdvanceFilterParam); }
</script>
<script id="rowTemplate" type="text/html"> <div class="fitem" id="filter<@=filterIndex@>"> <div class='bigLabelDiv'><label>选择字段:</label></div> <div class='bigContent'> <select class="easyui-combobox" id="advanceFilterField<@=filterIndex@>" data-options=" onSelect:function(_record){ var _tempIndex = <@=filterIndex@>; filterFieldChange(_tempIndex,_record); }, data:advanceConfig, valueField:'index', textField:'title', "> </select> <select class="easyui-combobox" id="advanceFilterType<@=filterIndex@>"> <option value="0" checked="checked">等于</option> <option value="1">包含</option> <option value="2">不等于</option> <option value="3">大于等于</option> <option value="4">小于等于</option> <option value="5">大于</option> <option value="6">小于</option> <option value="7">以什么开始</option> <option value="8">以什么结尾</option> </select> <lable id="advanceFilterValLable<@=filterIndex@>">
</lable> <a href="javascript:void(0)" class="easyui-linkbutton" οnclick="$('#filter<@=filterIndex@>').remove()">删除</a> </div> </div> </script> <div class="easyui-dialog" id = "advanceDialog" style="width: 80%; height: 40%; padding: 10px" closed="true"> <div id="advanceFormDiv"> <div class="fitem"> <div class='bigLabelDiv'><label>过滤条件匹配:</label></div> <div class='bigContent'> <select class="easyui-combobox" id="advanceFilterType"> <option value="and" checked="checked">AND(满足所有)</option> <option value="or">OR(满足一个)</option> </select> <a href="javascript:void(0)" class="easyui-linkbutton" οnclick="addFilter()">添加</a> </div> </div> </div> <div class="fitem"> <center> <a href="javascript:void(0)" class="easyui-linkbutton" οnclick="execAdvanceSearch()">搜索</a> <a href="javascript:void(0)" class="easyui-linkbutton" οnclick="closeAdvanceSearch()">关闭</a></center> </div> </div>
<script>
function closeAdvanceSearch(){ $('#advanceDialog').dialog('close'); }
</script> |
经过上面的代码,前段基本处理完成,下面讲解后端代码如何处理。
2 后端处理
首先在VO中创建接收前段搜索条件的字段。
1 2 3 4 5 | /** * 高级搜索过滤条件 */ @Transient private String extAdvanceFilterParam; |
然后写格式化sql的代码。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | private static final Map<String, String> SIMPLE_OPERATOR = new HashMap();
static { SIMPLE_OPERATOR.put(POJOConstant.EQ, " = "); SIMPLE_OPERATOR.put(POJOConstant.LIKE, " LIKE "); SIMPLE_OPERATOR.put(POJOConstant.NEQ, " != "); SIMPLE_OPERATOR.put(POJOConstant.BIGGER_EQ, " >= "); SIMPLE_OPERATOR.put(POJOConstant.LESS_EQ, " <= "); SIMPLE_OPERATOR.put(POJOConstant.LESS, " < "); SIMPLE_OPERATOR.put(POJOConstant.START_WITH, " LIKE "); SIMPLE_OPERATOR.put(POJOConstant.END_WITH, " LIKE "); SIMPLE_OPERATOR.put(POJOConstant.BIGGER, " > "); }
/** * 获取高级搜索的where条件 * * @return */ public String getAdvanceSearchSql() { if (extAdvanceFilterParam == null) { return null; } JSONObject extAdvanceFilterParamJson = JSON.parseObject(extAdvanceFilterParam); String filterType = " OR "; boolean isOr = true; if (extAdvanceFilterParamJson.getString("filterType").equals("and")) { filterType = " AND "; isOr = false; } JSONArray extAdvanceFilterParamArray = extAdvanceFilterParamJson.getJSONArray("extAdvanceFilterParamArray"); JSONObject tempAFilter = null; Field field = null; String sqlField = null; String tempVal = null; StringBuilder whereSql = new StringBuilder(isOr ? " AND (" : " AND "); boolean isHashWhere = false; for (int i = 0; i < extAdvanceFilterParamArray.size(); i++) { tempAFilter = extAdvanceFilterParamArray.getJSONObject(i); field = ReflectUtils.getDeclaredField(this.getClass(), tempAFilter.getString("name")); if (field == null) { log.error("字段不存在:" + field); continue; } sqlField = getSqlField(field); tempVal = formartVal(field, tempAFilter.get("val"), tempAFilter.getString("filterType"), tempAFilter.getString("searchKeyType") , tempAFilter.getString("fieldName")); if (sqlField == null || tempVal == null || !SIMPLE_OPERATOR.containsKey(tempAFilter.getString("filterType"))) { log.error("条件不满足,无法拼接此字段,详情请打断点:" + field); continue; } if (whereSql.length() > 6) { whereSql.append(filterType); } whereSql.append(sqlField + ("searchKey".equals(field.getName()) ? " LIKE " : SIMPLE_OPERATOR.get(tempAFilter.getString("filterType"))) + tempVal + " "); isHashWhere = true; } if(!isHashWhere){ return ""; } whereSql.append(isOr ? ")" : ""); return whereSql.toString(); }
/** * 格式化值 * * @param field lambdaSett * @param val 值 * @param filterType * @return 值的sql格式 */ protected String formartVal(Field field, Object val, String filterType, String searchKeyType, String fieldName) { if (val == null || "null".equals(val)) { return "null"; } Class<?> type = field.getType(); String result = null; // 字符串直接是字段名 if (!CheckUtils.isNullOrEmpty(searchKeyType)) { if ("str".equals(searchKeyType)) { return " CONCAT('%','"" + fieldName + ""','%','" + val + "','%') "; } if ("streq".equals(searchKeyType)) { return " CONCAT('%','"" + fieldName + "":"" + val + ""','%') "; } else if ("date".equals(searchKeyType)) { return " CONCAT('%','"" + fieldName + "":"" + val + "','%') "; } else if ("int".equals(searchKeyType)) { return " CONCAT('%','"" + fieldName + "":"" + val + ""','%') "; } } //只有字符串才有like 需要特殊处理 if (type == String.class) { if (POJOConstant.LIKE.equals(filterType)) { result = " CONCAT('%','" + val + "','%') "; } else if (POJOConstant.START_WITH.equals(filterType)) { result = " CONCAT('" + val + "','%') "; } else if (POJOConstant.END_WITH.equals(filterType)) { result = " CONCAT('%','" + val + "') "; } else { result = "'" + val + "'"; } } else if (type == Number.class || Number.class.isAssignableFrom(type)) { result = ConverterUtils.toString(val); } else if (type == Date.class || Date.class.isAssignableFrom(type)) { Date dateVal = DateUtils.parseStr(ConverterUtils.toString(val)); return "FROM_UNIXTIME(" + (dateVal.getTime() / 1000) + ")"; } else { log.warn("格式不支持:" + field + val); return null; } return result; }
public String getSqlField(Field field) { if (field.isAnnotationPresent(TableField.class)) { TableField tableField = field.getAnnotation(TableField.class); if (tableField.exist()) { return tableField.value(); } else {
return null; } } else if (field.isAnnotationPresent(Column.class)) { Column column = field.getAnnotation(Column.class); return column.name(); } return null; } |
通过上面代码,基本上已经把sql拼接出来了,如果觉得不安全可以加个sql的过滤,如果有人故意黑,可以抛异常。
有了sql 之后拼到查询语句中就可以了。