封装一个高级查询组件

背景
- 一，前端相关代码
- 二，后端相关代码
- 三，呈现效果
- 总结

背景

业务有个按照自定义选择组合查询条件，保存下来每次查询的时候使用的需求。查了一下项目里的代码没有现成的组件可以用，于是封装了一个

一，前端相关代码

总体的设计的设想上，需要一个弹出式的对话框，可以点击增加条件，并且整体能比较容易的嵌入到界面的查询中。根据需求的查询条件需要支持常见的如输入框，下拉，时间等控件。因为使用场景不多，目前的需求范围暂时只支持三层括号。为了通用性应该做成每个界面按需配置的，譬如一个页面可以有多少个可供选择的查询条件，因此需要有个简单的协议通过配置渲染查询条件。安全起见，查询条件在后端拼成sql传给数据库中。
另外，对于有些本身在界面的增删改查的条件本身是组合的条件或者涉及到sql语句的一些逻辑的，这里使用的方法是通过反射给本来的查询vo里设值，而这些"VO_FIELD"的选项不参与界面的与或以及括号的组合，这涉及到一些特殊的修改
配置的demo如下

[{"field": "对应后端表字段1","name": "输入框查询条件","fieldType": "STRING","conditions": [{"value": "=","label": "等于=","element": {"type": "input","placeholder": "请输入查询条件1"}}]},{"field": "对应后端表字段2","name": "下拉查询条件","fieldType": "STRING","conditions": [{"value": "in","label": "在范围内in","element": {"type": "select","placeholder": "请选择下拉查询条件","options": [{"value": 1,"label": "下拉查询条件1"},{"value": 2,"label": "下拉查询条件2"}]}}]},{"field": "对应后端表字段3","name": "日期查询条件","fieldType": "DATE","conditions": [{"value": "between","label": "介于between","element": {"type": "datePickerRange"}},{"value": "=","label": "等于=","element": {"type": "datePicker"}}]}
]

这是组件的前端代码，删除一些业务相关的信息，以及查询接口的信息

<template><div><el-row :gutter="5"><!-- 弹窗, 高级搜索 --><el-dialogv-dialogDragtitle="高级搜索":visible.sync="dialogFormVisible"@close="onClose()"@open="onOpen()"width="60%"><el-form :model="form"><!-- 操作按钮 --><el-row><el-col :span="24" class="pb15 pt5"><el-button@click="searchAdd()"type="danger"size="mini"style="margin-left: 10px"plain>新增</el-button></el-col></el-row><el-table :data="searchConditionList" style="width: 100%"><el-table-column prop="relation" label="关联"><template slot-scope="scope"><el-select v-model="scope.row.relation" placeholder="请选择"  :disabled="scope.row.relationInputShow"><el-option label="且" value="and"></el-option><el-option label="或" value="or"></el-option></el-select></template></el-table-column><el-table-column prop="leftBracket" label="括号"   width="70"><template slot-scope="scope"><el-select v-model="scope.row.leftBracket"  placeholder="" :disabled="scope.row.bracketShow"  clearable><el-option label="(" value="("></el-option><el-option label="((" value="(("></el-option><el-option label="(((" value="((("></el-option></el-select></template></el-table-column><el-table-column prop="field" label="栏位" width="180"><template slot-scope="scope"><el-select v-model="scope.row.field"@change="fieldChange(scope.row,scope.row.field)"placeholder="请选择"><el-optionv-for="item in queryConditionsConfig":key="item.field":label="item.name":value="item.field"></el-option></el-select></template></el-table-column><el-table-column   width="25"><template slot-scope="scope"><span v-if="scope.row.prompt!=null&&scope.row.prompt!=''"><el-tooltip placement="top"><div style="white-space: pre-wrap;" slot="content">{{scope.row.prompt}}</div><i class="el-icon-question" /></el-tooltip></span></template></el-table-column><el-table-column prop="condition" label="条件" width="180"><template slot-scope="scope"><el-select v-model="scope.row.condition" placeholder="请选择"@change="conditionChange(scope.row.field,scope.row.condition)"><el-optionv-for="item in fieldConditionMap.get(scope.row.field)":key="item.value":label="item.label":value="item.value"></el-option></el-select></template></el-table-column><el-table-column prop="searchValue" label="搜索值" width="350"><template slot-scope="scope" ><!-- 输入框 --><el-input  v-if="inputChoosed(scope.row.field,scope.row.condition)"v-model="scope.row.searchValue":placeholder="inputPlaceholder(scope.row.field,scope.row.condition)"></el-input><!-- 下拉多选框 --><el-select v-model="scope.row.tempSelectOptions" placeholder="请选择"multiplev-if="selectChoosed(scope.row.field,scope.row.condition)":placeholder="inputPlaceholder(scope.row.field,scope.row.condition)"@change="combineSelectOptions(scope.row,scope.row.tempSelectOptions);(val) => this.$forceUpdate()"style="width: 100%;"><el-optionv-for="item in fieldElementOptionsMap.get(scope.row.field+scope.row.condition)":key="item.value":label="item.label":value="item.value"></el-option></el-select><!-- 下拉单选框 --><el-select v-model="scope.row.searchValue" placeholder="请选择"v-if="singleSelectChoosed(scope.row.field,scope.row.condition)":placeholder="inputPlaceholder(scope.row.field,scope.row.condition)"clearablestyle="width: 100%;"><el-optionv-for="item in fieldElementOptionsMap.get(scope.row.field+scope.row.condition)":key="item.value":label="item.label":value="item.value"></el-option></el-select><!-- 日期范围 --><el-date-picker v-if="datePickerRangeChoosed(scope.row.field,scope.row.condition)"v-model="scope.row.tempDateRange"type="daterange":default-time="['00:00:00', '23:59:59']"value-format="yyyy-MM-dd"range-separator="至"start-placeholder="开始日期"end-placeholder="结束日期"size="mini" style="padding-top: 0px;padding-bottom:0px;line-height:24px;width: 100%;"@change="combineDateRange(scope.row,scope.row.tempDateRange);(val) => this.$forceUpdate()"></el-date-picker><!-- 日期 --><el-date-picker  v-if="datePickerChoosed(scope.row.field,scope.row.condition)"v-model="scope.row.searchValue"type="date"value-format="yyyy-MM-dd"placeholder="请输入日期"size="mini" style="padding-top: 0px;padding-bottom:0px;line-height:24px;width: 100%;"></el-date-picker></template></el-table-column><el-table-column prop="rightBracket" label="括号"  width="70"><template slot-scope="scope" ><el-select v-model="scope.row.rightBracket"  placeholder="" :disabled="scope.row.bracketShow" clearable><el-option label=")" value=")"></el-option><el-option label="))" value="))"></el-option><el-option label=")))" value=")))"></el-option></el-select></template></el-table-column><el-table-column align="center" label="操作"><template slot-scope="scope"><el-buttontype="text"size="small"@click="deleteData(scope.row.id, scope.$index)">删除</el-button></template></el-table-column></el-table><template><el-col :span="24"><el-col :md="12" :lg="16" class="margin-bottom-5"><div class="el-input el-input--mini el-input-group"><div >收藏夹<el-select v-model="currentConfigIndex" placeholder="请选择"@change="changeConfig"@clear="clearConfig"clearable><el-optionv-for="item in existQueryConfigs":key="item.id":label="item.configName":value="item.id"></el-option></el-select></div></div></el-col><el-col :span="8" class="margin-bottom-5"><el-checkbox v-model="applicationShareFlag" style="display:block;">是否共享至应用层（所有人）</el-checkbox><el-checkbox v-model="roleShareFlag" style="display:block;">是否共享至角色层（当前角色）</el-checkbox><el-input  v-model="currentConfigName" placeholder="输入保存名称" style="width:300px" ></el-input></el-col></el-col></template></el-form><div slot="footer" class="dialog-footer"><el-button plain @click="onClose()">取 消</el-button><el-button type="info" @click="saveCondition">保存到收藏夹</el-button><el-button type="info" @click="searchByConditions">搜索</el-button></div></el-dialog></el-row></div></template><style ></style><script>import advanceQuery from './advance-query'import { mixin } from '@/mixin/mixin'import {cloneDeep} from 'lodash'export default {name: "advance-query",mixins: [mixin],components: {},props: {//查询条件整体配置queryConditionsConfig: {type: Object,default: {},required: true},queryDataFunc:  Function,//查主界面列表的条件condition:{type: Object,default: {}},//对话框显示dialogFormVisible: {type: Boolean,default: true},//页面名称bizPageName: {type: String,default: ""},dataList:{type: Object,default: {}},total:{type: Number,default: {}},//搜索条件，用于回传searchConditionList:{type: Object,default: {}},},data() {return {//当前选中的字段currentField:{},//field和条件的mapfieldConditionMap: new Map(),//field和控件相关的mapfieldElemenTypeChooseMap: new Map(),fieldElementPlaceholderMap: new Map(),fieldElementOptionsMap: new Map(),currentField:'',currentCondition:'',existQueryConfigs:[],applicationShareFlag:false,roleShareFlag:false,currentConfigName:"",currentConfigIndex:"",roleCode:""};},async created() {await this.getCurrentRoleCodes()this.getConfigs()},methods: {// 高级搜索-新增searchAdd() {let newsearchObject = {field: "", //栏位fieldType:"",//字段类型condition: "", //条件searchValue: "", //搜索值relation: "and", //关联relationInputShow:true,//控制关联不可选择bracketShow:false//控制括号不可选择};this.searchConditionList.push(newsearchObject);this.modifyRelationAndBracket()},//高级搜索-删除deleteData(id, index) {this.searchConditionList.splice(index, 1);this.modifyRelationAndBracket()},// 高级搜索-保存saveCondition() {let param={}param.applicationShareFlag=this.applicationShareFlagparam.roleShareFlag=this.roleShareFlagparam.configName=this.currentConfigNameparam.id=this.currentConfigIndexparam.roleCode=this.roleCodeparam.bizPageName=this.bizPageNamelet tempConditionTable=[]tempConditionTable = cloneDeep(this.searchConditionList)//干掉两个临时变量tempConditionTable.forEach(item=>{if(item.tempDateRange!=null){item.tempDateRange=null}if(item.tempSelectOptions!=null){item.tempSelectOptions=null}})param.configJson=JSON.stringify(tempConditionTable)if(param.configName==''){this.$message.warning('配置名称不可以为空！');return}apAdvanceQuery.saveConfig(param).then( data => {if( data.data.status==0){this.$message.success('保存成功')this.getConfigs()}else{this.$message.error('保存失败')}});},// 获取数据列表searchByConditions() {//   console.log("打印查询参数：",JSON.stringify(this.condition))let param = Object.assign({},this.condition)param.start= this.condition.limit * (this.condition.page - 1),param.length= this.condition.limit,param.searchConditionList=this.searchConditionListthis.queryDataFunc(param).then( data => {if (data.data.status === 0) {this.$emit('update:dataList', data.data.data)this.$emit('update:total', data.data.count)this.$emit('update:searchConditionList', this.searchConditionList)this.$emit('changeAdvanceConfigName',this.currentConfigName==""&&this.searchConditionList.length>=1?"新建":this.currentConfigName)} else {this.$message.error(data.data.msg);}});},//切换栏目fieldChange(row,field){this.queryConditionsConfig.forEach(item=>{if(item.field===field){row.fieldType=item.fieldTyperow.prompt=item.promptthis.fieldConditionMap.set(item.field,item.conditions)}})this.modifyRelationAndBracket()},//切换条件conditionChange(field,condition){this.currentField=field,this.currentCondition=condition,this.fieldConditionMap.get(field).forEach(item=>{if(item.value===condition){this.fieldElementPlaceholderMap.set(field+condition,item.element.placeholder)this.fieldElementOptionsMap.set(field+condition,item.element.options)this.fieldElemenTypeChooseMap.set(field+condition,item.element.type)}})},inputChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(field+condition)==='input';},selectChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(field+condition)==='select';},singleSelectChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(field+condition)==='singleSelect';},datePickerRangeChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(field+condition)==='datePickerRange';},datePickerChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(field+condition)==='datePicker';},//切换配置changeConfig(value){this.existQueryConfigs.forEach(config=>{if(config.id===value){this.currentConfigName=config.configNamethis.currentConfigIndex=config.idthis.applicationShareFlag=config.applicationShareFlagthis.roleShareFlag=config.roleShareFlagthis.searchConditionList=[]config.searchObjects.forEach(item=>{let newsearchObject={}Object.assign(newsearchObject, item);this.fieldChange(newsearchObject,newsearchObject.field)this.conditionChange(newsearchObject.field,newsearchObject.condition)this.searchConditionList.push(newsearchObject);})}})},//清除选中的配置clearConfig(){this.currentConfigName=""this.currentConfigIndex=""this.applicationShareFlag=falsethis.roleShareFlag=falsethis.searchConditionList=[]},getCurrentRoleCodes(){//获取当前角色信息},getConfigs(){//获取已经保存的查询配置},combineDateRange(row,value){if(value!=null&&value!=[]){row.searchValue=value[0]+','+value[1]}else{row.searchValue=null}},combineSelectOptions(row,value){if(value!=null){row.searchValue=value.reduce((prev, curr) => {return prev+','+curr}, '')row.searchValue=row.searchValue.slice(1)}},//关联关系和括号修改modifyRelationAndBracket(){this.searchConditionList.forEach((item,index)=>{if(index == 0){item.relation= "and"item.relationInputShow = trueif(item.fieldType==='VO_FIELD'){item.bracketShow=true}else{item.bracketShow=false}}else if(item.fieldType==='VO_FIELD'){//这种类型的字段不参与与或拼接item.relation= ""item.relationInputShow = trueitem.bracketShow=true}else{item.relationInputShow = falseitem.bracketShow=false}})},//调用父组件实现关闭事件onClose() {this.$emit('update:dialogFormVisible', false)},//打开时候先初始化一下配置的显示onOpen(){this.changeConfig(this.currentConfigIndex);}},computed:{inputPlaceholder(){return function(field,value){return this.fieldElementPlaceholderMap.get(field+value)}}},};</script>

二，后端相关代码

后端相关的代码主要是参与解析条件生成配置的
配置的实体类如下

@Data
public class AdvanceSearchCondition {/*** 栏位*/private String field;/*** 条件*/private String  condition;/*** 搜寻值*/private String searchValue;/*** 关联*/private  String relation;/*** 字段类型*/private  String fieldType;/*** 左括号*/private String leftBracket;/*** 右括号*/private String rightBracket;}

主要的生成sql查询条件的方法如下

public class AdvancedSearchBizService {/*** 返回生成的sql查询条件，同时部分无法拼接成sql的字段修饰到查询vo里* @param searchConditionList* @param queryVo* @return*/public static String genarateConditionSql(List<AdvanceSearchCondition> searchConditionList,Object queryVo) {if(CollectionUtils.isEmpty(searchConditionList)){return StringUtils.EMPTY;}List<AdvanceSearchCondition> validList = searchConditionList.stream().filter(AdvancedSearchBizService::validate).collect(Collectors.toList());if(CollectionUtils.isEmpty(validList)){return StringUtils.EMPTY;}//有些字段不能用拼sql的方式处理，因此直接作用到查询vo上List<AdvanceSearchCondition> decorateQueryVoList = validList.stream().filter(x-> conditionFieldTypeEnum.VO_FIELD.name().equalsIgnoreCase(x.getFieldType())).collect(Collectors.toList());validList.removeAll(decorateQueryVoList);decorateQueryVo(queryVo,decorateQueryVoList);//select *  from table where 1=1 拼上 条件（关联符号在前）StringBuilder searchParamer = new StringBuilder();String space = " ";for (AdvanceSearchCondition searchCondition : validList ) {String field = searchCondition.getField().toLowerCase();String value = searchCondition.getSearchValue();String searchVal =AdvanceSearchUtils.convertValue(searchCondition);String condition = searchCondition.getCondition();String relation = searchCondition.getRelation();String leftBracket=StringUtils.isNotBlank(searchCondition.getLeftBracket())?searchCondition.getLeftBracket():space;String rightBracket=StringUtils.isNotBlank(searchCondition.getRightBracket())?searchCondition.getRightBracket():space;// = 拼接的情况if (condition.equals(SearchConditionEnum.EQUALS.chs())) {//转一下，如果是string类型输了多个值，也用替换成inif(searchVal.contains(",")) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(SearchConditionEnum.IN.chs()).append(space).append("(").append(space).append(searchVal).append(space).append(") ").append(space).append(rightBracket).append(space);}else {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}}// <> 拼接的情况if (condition.equals(SearchConditionEnum.NOT_EQUALS.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}//  > 拼接的情况if (condition.equals(SearchConditionEnum.GREATER_THAN.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}// < 拼接的情况if (condition.equals(SearchConditionEnum.LESS_THAN.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}// >= 拼接的情况if (condition.equals(SearchConditionEnum.GREATER_EQUALS.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}// <= 拼接的情况if (condition.equals(SearchConditionEnum.LESS_EQUALS.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(searchVal).append(space).append(rightBracket).append(space);}// in 拼接的情况if (condition.equals(SearchConditionEnum.IN.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("(").append(space).append(searchVal).append(space).append(") ").append(space).append(rightBracket).append(space);}// not in  拼接的情况if (condition.equals(SearchConditionEnum.NOT_IN.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("(").append(space).append(searchVal).append(space).append(") ").append(space).append(rightBracket).append(space);}// like 拼接的情况if (condition.equals(SearchConditionEnum.LIKE.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("'%").append(searchVal.replace("'","")).append("%'").append(space).append(rightBracket).append(space);}// not like  拼接的情况if (condition.equals(SearchConditionEnum.NOT_LIKE.chs())) {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("'%").append(searchVal).append("%'").append(space).append(rightBracket).append(space);}// between 拼接的情况  between... and...// not between  拼接的情况if (condition.equals(SearchConditionEnum.BETWEEN.chs())) {String[] contentUnits = value.split(",");if (contentUnits.length != 2) {throw new BizException("搜索值栏位必须输入两个参数且用“,”隔开");}String leftContentUnit = contentUnits[0];String rightContentUnit = contentUnits[1];if(!AdvanceSearchUtils.isNumber(leftContentUnit)){searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("'").append(leftContentUnit).append("'").append(space).append("and").append(space).append("'").append(rightContentUnit).append("' ").append(space).append(rightBracket).append(space);}else {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(leftContentUnit).append(space).append("and").append(space).append(rightContentUnit).append(space).append(rightBracket).append(space);}}if (condition.equals(SearchConditionEnum.NOT_BETWEEN.chs())) {String[] contentUnits = value.split(",");if (contentUnits.length != 2) {throw new RuntimeException("搜索值栏位必须输入两个参数且用“,”隔开");}String leftContentUnit = contentUnits[0];String rightContentUnit = contentUnits[1];if(!AdvanceSearchUtils.isNumber(leftContentUnit)){searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append("'").append(leftContentUnit).append("'").append(space).append("and").append(space).append("'").append(rightContentUnit).append("' ").append(space).append(rightBracket).append(space);}else {searchParamer.append(space).append(relation).append(space).append(leftBracket).append(space).append(field).append(space).append(condition).append(space).append(leftContentUnit).append(space).append("and").append(space).append(rightContentUnit).append(space).append(rightBracket).append(space);}}}if(!CollectionUtils.isEmpty(searchConditionList) && StringUtils.isNotBlank(searchParamer)) {searchParamer.insert(4, "(");searchParamer.append(")");}String sqlCondition=searchParamer.toString();checkBracketMatch(sqlCondition);return sqlCondition;}private static void checkBracketMatch(String sqlCondition) {Deque<Character> stringStack = new LinkedList<>();char tempChar;for (int i = 0; i < sqlCondition.length(); i++) {tempChar = sqlCondition.charAt(i);switch (tempChar) {case '(': {stringStack.push(tempChar);break;}case ')':if(stringStack.isEmpty()){throw new BizException("括号不匹配，请检查");}if (stringStack.pop() != '(') {throw new BizException("括号不匹配，请检查");}break;default:}}if (!stringStack.isEmpty()) {throw new BizException("括号不匹配，请检查");}}/*** 把参数设置进vo里* @param queryVo* @param decorateQueryVoList*/private static void decorateQueryVo(Object queryVo, List<AdvanceSearchCondition> decorateQueryVoList) {if(CollectionUtils.isEmpty(decorateQueryVoList)){return;}try {for (AdvanceSearchCondition one : decorateQueryVoList) {Field field = ReflectionUtil.getField(queryVo.getClass(), one.getField());Type type = field.getGenericType();Set<Object> supportValues = new HashSet<>();if (type instanceof ParameterizedType) {Class<?> clazz = (Class<?>) ((ParameterizedType) type).getActualTypeArguments()[0];for (String val : Arrays.asList(one.getSearchValue().split(","))) {switch (clazz.getTypeName()) {case "java.lang.String":supportValues.add(val);break;default:supportValues.add(clazz.getMethod("valueOf", String.class).invoke(null, val));break;}}ReflectionUtil.setFieldValue(queryVo, one.getField(), supportValues);}else{ReflectionUtil.setFieldValue(queryVo, one.getField(), one.getSearchValue());}}}catch (Exception e){Log.error("高级查询设置vo参数失败"+e.getMessage(),e);}}private static boolean validate(AdvanceSearchCondition condition){if(!condition.getFieldType().equalsIgnoreCase(conditionFieldTypeEnum.VO_FIELD.name())) {return StringUtils.isNotBlank(condition.getField())&& StringUtils.isNotBlank(condition.getFieldType())&& StringUtils.isNotBlank(condition.getCondition())&& StringUtils.isNotBlank(condition.getSearchValue())&& StringUtils.isNotBlank(condition.getRelation());}else{return StringUtils.isNotBlank(condition.getField())&& StringUtils.isNotBlank(condition.getFieldType())&& StringUtils.isNotBlank(condition.getCondition())&& StringUtils.isNotBlank(condition.getSearchValue());}}}