package com.ibiz.util.helper; import com.ibiz.util.SearchContext; import com.ibiz.util.SearchFieldFilter; import com.ibiz.util.SearchFilter; import com.ibiz.util.SearchGroupFilter; import com.ibiz.util.enums.SearchFieldType; import com.ibiz.util.enums.SearchGroupType; import org.apache.commons.lang3.EnumUtils; import org.springframework.stereotype.Component; import org.springframework.util.StringUtils; import java.util.List; /** * 关系型数据库SQL转换工具类 */ @Component public class QueryBuilderHelper { /** * 解析SearchContext中的field与group,返回sql * @param context * @return */ public String buildSQL(SearchContext context){ String resultCond=""; StringBuffer sbfSQL=new StringBuffer(); List<SearchFilter> conditions=context.getCondition(); String customCond=context.getCustomCond();//自定义条件查询 String defaultGroupType="AND";////组内条件的组合关系(AND/OR) for(SearchFilter cond: conditions){//条件查询 String tempCond = ""; if(cond instanceof SearchFieldFilter){ //单条件查询 tempCond=parseFieldCond((SearchFieldFilter) cond); } else if (cond instanceof SearchGroupFilter){//组条件查询 tempCond=parseGroupCond((SearchGroupFilter) cond); } if(!StringUtils.isEmpty(tempCond)) sbfSQL.append(String.format("%s %s ",defaultGroupType,tempCond)); } for (String key : context.getParams().keySet()) { String[] keys = key.split("_") ; if(keys.length ==3 && keys[0].equals("n") && EnumUtils.isValidEnumIgnoreCase(SearchFieldType.class, keys[2]) ){ SearchFieldFilter field = new SearchFieldFilter() ; field.setCondition(EnumUtils.getEnumIgnoreCase(SearchFieldType.class, keys[2])); field.setParam(keys[1]); field.setValue(context.getParams().get(key)); sbfSQL.append(String.format("%s %s ",StringUtils.isEmpty(sbfSQL.toString())?"":defaultGroupType,parseFieldCond(field))); } } if(!StringUtils.isEmpty(customCond))//自定义条件查询 sbfSQL.append(String.format("%s %s ",defaultGroupType,customCond)); resultCond=parseResult(sbfSQL,defaultGroupType); return resultCond; } /** * 字段条件解析 * @param field * @return */ private String parseFieldCond(SearchFieldFilter field){ String strSQL=""; String param=field.getParam(); SearchFieldType cond=field.getCondition(); Object value=field.getValue(); if(StringUtils.isEmpty(param)|| StringUtils.isEmpty(cond)) return strSQL; switch (cond){ case GT: strSQL=String.format(" %s > '%s'",param ,value); break; case GTANDEQ: strSQL=String.format(" %s >= '%s'",param ,value); break; case EQ: strSQL=String.format(" %s = '%s'",param ,value); break; case NOTEQ: strSQL=String.format(" %s <> '%s'",param ,value); break; case LT: strSQL=String.format(" %s < '%s'",param ,value); break; case LTANDEQ: strSQL=String.format(" %s <= '%s'",param ,value); break; case LIKE: strSQL=String.format(" %s like '%%%s%%'",param ,value); break; case LEFTLIKE: strSQL=String.format(" %s like '%s%%'",param ,value); break; case RIGHTLIKE: strSQL=String.format(" %s like '%%%s'",param ,value); break; case ISNULL: strSQL=String.format(" %s is null",param); break; case ISNOTNULL: strSQL=String.format(" %s is not null ",param); break; case IN: if(value instanceof List){ String tempValue=formatStringArr((List<String>) value); if(!StringUtils.isEmpty(tempValue)) strSQL=String.format(" %s in (%s)",param,tempValue); break; } break; case NOTIN: if(value instanceof List){ String tempValue=formatStringArr((List<String>) value); if(!StringUtils.isEmpty(tempValue)) strSQL=String.format(" %s not in (%s)",param,tempValue); break; } break; } return strSQL; } /** * 组条件解析 * @param group * @return */ private String parseGroupCond(SearchGroupFilter group){ String sql=""; List<SearchFilter> groupCond=group.getCondition();//组内条件 SearchGroupType groupType=group.getSearchGroupType();//组内条件的组合关系(AND/OR) if(groupCond.size()==0 || StringUtils.isEmpty(groupType)) return sql; String resultCond=""; String strGroupType=getGroupType(groupType); StringBuffer sbfSQL=new StringBuffer(); for(SearchFilter cond : groupCond){ String tempCond = ""; if(cond instanceof SearchFieldFilter){ //单条件查询 tempCond=parseFieldCond((SearchFieldFilter) cond); } else if (cond instanceof SearchGroupFilter){//组条件查询 tempCond=parseGroupCond((SearchGroupFilter) cond); } if(!StringUtils.isEmpty(tempCond)) sbfSQL.append(String.format("%s %s ",strGroupType,tempCond)); } resultCond=parseResult(sbfSQL,strGroupType); resultCond=String.format("(%s)",resultCond); return resultCond; } /** * 组内条件的组合关系(AND/OR) * @param groupType * @return */ private String getGroupType(SearchGroupType groupType){ String strGroupType=""; switch(groupType){ case AND: strGroupType="AND";break; case OR: strGroupType="OR"; break; } return strGroupType; } /** * 格式转换 * @param cond * @param operator * @return */ private String parseResult(StringBuffer cond,String operator){ String resultCond = cond.toString(); if (resultCond.startsWith(operator)) resultCond = resultCond.replaceFirst(operator, ""); if (resultCond.endsWith(operator)) resultCond = resultCond.substring(0, resultCond.lastIndexOf(operator)); return resultCond; } /** * 转换[a,b]格式字符串到 'a','b'格式 * * @return */ private String formatStringArr(List<String> array) { String[] arr = array.toArray(new String[array.size()]); return "'" + String.join("','",arr) + "'"; } }