六灶网站建设,惠州做网站 百度优化,wordpress functions.php,网站后台修改不了封装一个高级查询组件 背景一#xff0c;前端相关代码二#xff0c;后端相关代码三#xff0c;呈现效果总结 背景 业务有个按照自定义选择组合查询条件#xff0c;保存下来每次查询的时候使用的需求。查了一下项目里的代码没有现成的组件可以用#xff0c;于是封装了一个 … 封装一个高级查询组件 背景一前端相关代码二后端相关代码三呈现效果总结 背景 业务有个按照自定义选择组合查询条件保存下来每次查询的时候使用的需求。查了一下项目里的代码没有现成的组件可以用于是封装了一个 一前端相关代码
总体的设计的设想上需要一个弹出式的对话框可以点击增加条件并且整体能比较容易的嵌入到界面的查询中。根据需求的查询条件需要支持常见的如输入框下拉时间等控件。因为使用场景不多目前的需求范围暂时只支持三层括号。为了通用性应该做成每个界面按需配置的譬如一个页面可以有多少个可供选择的查询条件因此需要有个简单的协议通过配置渲染查询条件。安全起见查询条件在后端拼成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}}]}
]这是组件的前端代码删除一些业务相关的信息以及查询接口的信息
templatedivel-row :gutter5!-- 弹窗, 高级搜索 --el-dialogv-dialogDragtitle高级搜索:visible.syncdialogFormVisiblecloseonClose()openonOpen()width60%el-form :modelform!-- 操作按钮 --el-rowel-col :span24 classpb15 pt5el-buttonclicksearchAdd()typedangersizeministylemargin-left: 10pxplain新增/el-button/el-col/el-rowel-table :datasearchConditionList stylewidth: 100%el-table-column proprelation label关联template slot-scopescopeel-select v-modelscope.row.relation placeholder请选择 :disabledscope.row.relationInputShowel-option label且 valueand/el-optionel-option label或 valueor/el-option/el-select/template/el-table-columnel-table-column propleftBracket label括号 width70template slot-scopescopeel-select v-modelscope.row.leftBracket placeholder :disabledscope.row.bracketShow clearableel-option label( value(/el-optionel-option label(( value((/el-optionel-option label((( value(((/el-option/el-select/template/el-table-columnel-table-column propfield label栏位 width180template slot-scopescopeel-select v-modelscope.row.fieldchangefieldChange(scope.row,scope.row.field)placeholder请选择el-optionv-foritem in queryConditionsConfig:keyitem.field:labelitem.name:valueitem.field/el-option/el-select/template/el-table-columnel-table-column width25template slot-scopescopespan v-ifscope.row.prompt!nullscope.row.prompt!el-tooltip placementtopdiv stylewhite-space: pre-wrap; slotcontent{{scope.row.prompt}}/divi classel-icon-question //el-tooltip/span/template/el-table-columnel-table-column propcondition label条件 width180template slot-scopescopeel-select v-modelscope.row.condition placeholder请选择changeconditionChange(scope.row.field,scope.row.condition)el-optionv-foritem in fieldConditionMap.get(scope.row.field):keyitem.value:labelitem.label:valueitem.value/el-option/el-select/template/el-table-columnel-table-column propsearchValue label搜索值 width350template slot-scopescope !-- 输入框 --el-input v-ifinputChoosed(scope.row.field,scope.row.condition)v-modelscope.row.searchValue:placeholderinputPlaceholder(scope.row.field,scope.row.condition)/el-input!-- 下拉多选框 --el-select v-modelscope.row.tempSelectOptions placeholder请选择multiplev-ifselectChoosed(scope.row.field,scope.row.condition):placeholderinputPlaceholder(scope.row.field,scope.row.condition)changecombineSelectOptions(scope.row,scope.row.tempSelectOptions);(val) this.$forceUpdate()stylewidth: 100%;el-optionv-foritem in fieldElementOptionsMap.get(scope.row.fieldscope.row.condition):keyitem.value:labelitem.label:valueitem.value/el-option/el-select!-- 下拉单选框 --el-select v-modelscope.row.searchValue placeholder请选择v-ifsingleSelectChoosed(scope.row.field,scope.row.condition):placeholderinputPlaceholder(scope.row.field,scope.row.condition)clearablestylewidth: 100%;el-optionv-foritem in fieldElementOptionsMap.get(scope.row.fieldscope.row.condition):keyitem.value:labelitem.label:valueitem.value/el-option/el-select!-- 日期范围 --el-date-picker v-ifdatePickerRangeChoosed(scope.row.field,scope.row.condition)v-modelscope.row.tempDateRangetypedaterange:default-time[00:00:00, 23:59:59]value-formatyyyy-MM-ddrange-separator至start-placeholder开始日期end-placeholder结束日期sizemini stylepadding-top: 0px;padding-bottom:0px;line-height:24px;width: 100%;changecombineDateRange(scope.row,scope.row.tempDateRange);(val) this.$forceUpdate()/el-date-picker!-- 日期 --el-date-picker v-ifdatePickerChoosed(scope.row.field,scope.row.condition)v-modelscope.row.searchValuetypedatevalue-formatyyyy-MM-ddplaceholder请输入日期sizemini stylepadding-top: 0px;padding-bottom:0px;line-height:24px;width: 100%;/el-date-picker/template/el-table-columnel-table-column proprightBracket label括号 width70template slot-scopescope el-select v-modelscope.row.rightBracket placeholder :disabledscope.row.bracketShow clearableel-option label) value)/el-optionel-option label)) value))/el-optionel-option label))) value)))/el-option/el-select/template/el-table-columnel-table-column aligncenter label操作template slot-scopescopeel-buttontypetextsizesmallclickdeleteData(scope.row.id, scope.$index)删除/el-button/template/el-table-column/el-tabletemplateel-col :span24el-col :md12 :lg16 classmargin-bottom-5div classel-input el-input--mini el-input-groupdiv 收藏夹el-select v-modelcurrentConfigIndex placeholder请选择changechangeConfigclearclearConfigclearableel-optionv-foritem in existQueryConfigs:keyitem.id:labelitem.configName:valueitem.id/el-option/el-select/div/div/el-colel-col :span8 classmargin-bottom-5el-checkbox v-modelapplicationShareFlag styledisplay:block;是否共享至应用层所有人/el-checkboxel-checkbox v-modelroleShareFlag styledisplay:block;是否共享至角色层当前角色/el-checkboxel-input v-modelcurrentConfigName placeholder输入保存名称 stylewidth:300px /el-input/el-col/el-col/template/el-formdiv slotfooter classdialog-footerel-button plain clickonClose()取 消/el-buttonel-button typeinfo clicksaveCondition保存到收藏夹/el-buttonel-button typeinfo clicksearchByConditions搜索/el-button/div/el-dialog/el-row/div/templatestyle /stylescriptimport advanceQuery from ./advance-queryimport { mixin } from /mixin/mixinimport {cloneDeep} from lodashexport 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.applicationShareFlagthis.applicationShareFlagparam.roleShareFlagthis.roleShareFlagparam.configNamethis.currentConfigNameparam.idthis.currentConfigIndexparam.roleCodethis.roleCodeparam.bizPageNamethis.bizPageNamelet tempConditionTable[]tempConditionTable cloneDeep(this.searchConditionList)//干掉两个临时变量tempConditionTable.forEach(item{if(item.tempDateRange!null){item.tempDateRangenull}if(item.tempSelectOptions!null){item.tempSelectOptionsnull}})param.configJsonJSON.stringify(tempConditionTable)if(param.configName){this.$message.warning(配置名称不可以为空);return}apAdvanceQuery.saveConfig(param).then( data {if( data.data.status0){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.searchConditionListthis.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.currentConfigNamethis.searchConditionList.length1?新建:this.currentConfigName)} else {this.$message.error(data.data.msg);}});},//切换栏目fieldChange(row,field){this.queryConditionsConfig.forEach(item{if(item.fieldfield){row.fieldTypeitem.fieldTyperow.promptitem.promptthis.fieldConditionMap.set(item.field,item.conditions)}})this.modifyRelationAndBracket()},//切换条件conditionChange(field,condition){this.currentFieldfield,this.currentConditioncondition,this.fieldConditionMap.get(field).forEach(item{if(item.valuecondition){this.fieldElementPlaceholderMap.set(fieldcondition,item.element.placeholder)this.fieldElementOptionsMap.set(fieldcondition,item.element.options)this.fieldElemenTypeChooseMap.set(fieldcondition,item.element.type)}})},inputChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(fieldcondition)input;},selectChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(fieldcondition)select;},singleSelectChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(fieldcondition)singleSelect;},datePickerRangeChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(fieldcondition)datePickerRange;},datePickerChoosed:function(field,condition){return this.fieldElemenTypeChooseMap.get(fieldcondition)datePicker;},//切换配置changeConfig(value){this.existQueryConfigs.forEach(config{if(config.idvalue){this.currentConfigNameconfig.configNamethis.currentConfigIndexconfig.idthis.applicationShareFlagconfig.applicationShareFlagthis.roleShareFlagconfig.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.currentConfigNamethis.currentConfigIndexthis.applicationShareFlagfalsethis.roleShareFlagfalsethis.searchConditionList[]},getCurrentRoleCodes(){//获取当前角色信息},getConfigs(){//获取已经保存的查询配置},combineDateRange(row,value){if(value!nullvalue![]){row.searchValuevalue[0],value[1]}else{row.searchValuenull}},combineSelectOptions(row,value){if(value!null){row.searchValuevalue.reduce((prev, curr) {return prev,curr}, )row.searchValuerow.searchValue.slice(1)}},//关联关系和括号修改modifyRelationAndBracket(){this.searchConditionList.forEach((item,index){if(index 0){item.relation anditem.relationInputShow trueif(item.fieldTypeVO_FIELD){item.bracketShowtrue}else{item.bracketShowfalse}}else if(item.fieldTypeVO_FIELD){//这种类型的字段不参与与或拼接item.relation item.relationInputShow trueitem.bracketShowtrue}else{item.relationInputShow falseitem.bracketShowfalse}})},//调用父组件实现关闭事件onClose() {this.$emit(update:dialogFormVisible, false)},//打开时候先初始化一下配置的显示onOpen(){this.changeConfig(this.currentConfigIndex);}},computed:{inputPlaceholder(){return function(field,value){return this.fieldElementPlaceholderMap.get(fieldvalue)}}},};/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(ListAdvanceSearchCondition searchConditionList,Object queryVo) {if(CollectionUtils.isEmpty(searchConditionList)){return StringUtils.EMPTY;}ListAdvanceSearchCondition validList searchConditionList.stream().filter(AdvancedSearchBizService::validate).collect(Collectors.toList());if(CollectionUtils.isEmpty(validList)){return StringUtils.EMPTY;}//有些字段不能用拼sql的方式处理因此直接作用到查询vo上ListAdvanceSearchCondition 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 11 拼上 条件关联符号在前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 leftBracketStringUtils.isNotBlank(searchCondition.getLeftBracket())?searchCondition.getLeftBracket():space;String rightBracketStringUtils.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 sqlConditionsearchParamer.toString();checkBracketMatch(sqlCondition);return sqlCondition;}private static void checkBracketMatch(String sqlCondition) {DequeCharacter 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, ListAdvanceSearchCondition decorateQueryVoList) {if(CollectionUtils.isEmpty(decorateQueryVoList)){return;}try {for (AdvanceSearchCondition one : decorateQueryVoList) {Field field ReflectionUtil.getField(queryVo.getClass(), one.getField());Type type field.getGenericType();SetObject 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());}}}
这里生成的sql片段就可以当成一个简单的查询条件追加到原来的sql上面
三呈现效果
最终效果如下
总结
接触前端也有两年了从一开始只想着调用别人的组件慢慢的也喜欢自己封装一些组件简化业务开发过程。不过由于对前端的一些高级的写法不太熟悉可能写出来的代码啰嗦了一些并且时间仓促落地的场景不多可能也有比较多的隐含的bug存在还望读者不吝赐教。 作者连
