Springboot + easyui + mybatis 高级搜索功能实现

最近接了个项目，客户要求项目支持高级搜索，他可以自选字段，然后自选运算符，然后输入值，字段可随意组合，类似于下图。

1 前端处理 

 

遇到问题首先抽象：

 字段类型一般有4种，第一种普通输入框，第二种日期，第三种数字，第四种下拉。

 如果是下拉的话，只支持精确匹配  =， 其他的支持所有的运算符。

 按照我的性格，这个高级搜索功能肯定是要配置出来的，而不是傻乎乎的写html代码。

 首先确定配置JSON 文件格式，然后做通用页面，解析配置，配置比较简单，就不多解释了

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 onclick="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" onclick="$('#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"  onclick="addFilter()">添加</a>
            </div>
        </div>
    </div>
    <div class="fitem">
        <center>  <a href="javascript:void(0)" class="easyui-linkbutton"  onclick="execAdvanceSearch()">搜索</a>
            <a href="javascript:void(0)" class="easyui-linkbutton"  onclick="closeAdvanceSearch()">关闭</a></center>
    </div>
</div>


<script>

    function closeAdvanceSearch(){
        $('#advanceDialog').dialog('close');
    }


</script>

 经过上面的代码，前段基本处理完成，下面讲解后端代码如何处理。

 

2 后端处理 

     首先在VO中创建接收前段搜索条件的字段。

    

 /**
     * 高级搜索过滤条件
     */
    @Transient
    private String extAdvanceFilterParam;

  然后写格式化sql的代码。

  

 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 之后拼到查询语句中就可以了。