根据sql文件和传入参数得到完整的sql语句

妖狐艹你老母 2023-06-13 06:21 4阅读 0赞

今天在项目中学到的，看了好久才看懂，菜得不行

/**在spring配置文件中加入  
        <bean id="sqlMap" class="com.greatmap.estate.onemap.factorybean.QuerySqlBean"></bean>
    **/
    @Resource
        private Map<String, String> sqlMap;
    
    
    //根据传入的参数获取到完整的sql语句
    public String getBaseSql(String sqlFileName, String strJSONData, String qssj, String zzsj, String slqssj, String slzzsj) {
            String[] arrSJ = getCXSJ(slqssj, slzzsj).split(",");
            String strToDay = arrSJ[0];
            String strOldDay = arrSJ[1];
            String baseSql = "";
            String strLx = "";
            String strWhere = estateQueryService.getWhere(strJSONData);
            
            String tempSql = "";
            if (sqlFileName.indexOf(",") != -1) {
                String[] arr = sqlFileName.split(",");
                for (String str : arr) {
                    str = str.toLowerCase();
                    strLx = getLx(str);
                    if (StringUtils.isBlank(baseSql)) {
    
                        /**
                                sqlMap是自动注入的，有已经写好的sql语句和对应的sql文件名作为key
                           **
                        **/
                        baseSql = sqlMap.get(str);
                        if (strLx.indexOf(",") != -1) {
                            String[] arrLx = strLx.split(",");
                            baseSql = String.format(baseSql, arrLx[0], arrLx[1], strWhere, arrLx[0], arrLx[1], strWhere);
                        } else {
                            if (str.indexOf("bdcq") != -1 || str.indexOf("yugao") != -1) {
                                baseSql = String.format(baseSql, strLx, strWhere);
                            } else if (str.indexOf("dyaq") != -1 || str.indexOf("yydj") != -1 || str.indexOf("cfdj") != -1) {
                                baseSql = String.format(baseSql, strLx, strWhere, strLx, strWhere);
                            }
                        }
                    } else {
                        tempSql = sqlMap.get(str);
                        if (strLx.indexOf(",") != -1) {
                            String[] arrLx = strLx.split(",");
                            tempSql = String.format(tempSql, arrLx[0], arrLx[1], strWhere, arrLx[0], arrLx[1], strWhere);
                        } else {
                            if (str.indexOf("bdcq") != -1 || str.indexOf("yugao") != -1) {
                                tempSql = String.format(tempSql, strLx, strWhere);
                            } else if (str.indexOf("dyaq") != -1 || str.indexOf("yydj") != -1 || str.indexOf("cfdj") != -1) {
                                tempSql = String.format(tempSql, strLx, strWhere, strLx, strWhere);
                            }
                        }
                        baseSql = baseSql + " union " + tempSql;
                    }
                }
            } else {
                sqlFileName = sqlFileName.toLowerCase();
                strLx = getLx(sqlFileName);
                baseSql = sqlMap.get(sqlFileName);
                if (strLx.indexOf(",") != -1) {
                    String[] arrLx = strLx.split(",");
                    baseSql = String.format(baseSql, arrLx[0], arrLx[1], strWhere, arrLx[0], arrLx[1], strWhere);
                } else {
                    if (sqlFileName.indexOf("bdcq") != -1 || sqlFileName.indexOf("yugao") != -1) {
                        baseSql = String.format(baseSql, strLx, strWhere);
                    } else if (sqlFileName.indexOf("dyaq") != -1 || sqlFileName.indexOf("yydj") != -1 || sqlFileName.indexOf("cfdj") != -1) {
                        baseSql = String.format(baseSql, strLx, strWhere, strLx, strWhere);
                    }
                }
            }
            return baseSql;
        }

package com.greatmap.land.modules.oa.factorybean;
    
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    import com.greatmap.estate.common.utils.FileUtils;
    import com.greatmap.estate.common.utils.StringUtils;
    import org.apache.commons.lang3.ArrayUtils;
    import org.springframework.beans.factory.FactoryBean;
    import org.springframework.util.ResourceUtils;
    
    import java.io.File;
    import java.util.List;
    import java.util.Map;
    
    //读取类路径下sql目录中的sql文件，注入到spring容器中
    public class QuerySqlBean implements FactoryBean<Map<String, String>> {
    
    	@Override
    	public Map<String, String> getObject() throws Exception {
    		File sqlDir = ResourceUtils.getFile("classpath:sql");
    		Map<String, String> sqlMap = Maps.newHashMap();		
    		listDirFiles(sqlMap,sqlDir);		
    		return sqlMap;
    	}
    	
    	private void listDirFiles( Map<String, String> sqlMap,File sqlDir) throws Exception{
    		if(sqlMap == null||sqlDir == null||!sqlDir.isDirectory()){
    			return;
    		}
    		File[] sqlFileArr = sqlDir.listFiles();
    		if (ArrayUtils.isNotEmpty(sqlFileArr)) {
    			for (File sqlFile : sqlFileArr) {
    				if(sqlFile.isDirectory()){
    					listDirFiles(sqlMap,sqlFile);
    				}
    				else if(sqlFile.isFile()){
    					String fullFileName = sqlFile.getName();
    					String suffix = StringUtils.substringAfterLast(fullFileName, ".");
    					if ("sql".equalsIgnoreCase(suffix)) {
    						String fileName = StringUtils.substringBeforeLast(fullFileName, ".");
    						String sql = FileUtils.readFileToString(sqlFile,"UTF-8");
    						if (!StringUtils.isAnyBlank(fileName,sql) && SqlMapName.getAll().contains(fileName)) {
    								sqlMap.put(fileName, sql);
    						}
    					}
    				}
    			}
    		} 
    		
    	}
    
    	@Override
    	public Class<?> getObjectType() {
    		return Map.class;
    	}
    
    	@Override
    	public boolean isSingleton() {
    		return true;
    	}
    
    	//类路径中sql文件名的枚举对象
    	public enum SqlMapName {
    		DRAFT_LAND("draftLandQuery"), 
    		DRAFT_MULTI_LAND("draftMultiLandQuery"),
    		DRAFT_LAND_DIRECTLY("draftLandQueryDirectly"),
    		DRAFT_NYD_DIRECTLY("draftNYDQueryDirectly"),
    		DRAFT_BUILDINDG("draftZrzQuery"),
    		DRAFT_WOODLAND("draftWoodlandQuery"),
    		DRAFT_SEA("draftSeaQuery"), 
    		DRAFT_HOUSE("draftHouseQuery"),
    		DRAFT_HOUSE_DIRECTLY("draftHouseQueryDirectly"),
    		DRAFT_MULTI_HOUSE("draftMultiHouseQuery"),
    		DRAFT_FARMLAND("draftFarmlandQuery"),
    		DRAFT_STRUCTURE("draftStructureQuery"),
    		DONE("doneQuery"), 
    		TODO("todoQuery"),
    		DRAFT_BUILDING_CZ("draftBuildingQueryCZ"),
    		DRAFT_BUILDING_DIRECTLY("draftBuildingQueryDirectly"),
    		DRAFT_BUILDING_NC("draftBuildingQueryNC");
    		
    		private String content;
    
    		private SqlMapName(String sqlName) {
    			this.content = sqlName;
    		}
    
    		public String get() {
    			return this.content;
    		}
    
    		public static List<String> getAll() {
    			List<String> returnValue = Lists.newArrayList();
    			for (SqlMapName name : SqlMapName.values()) {
    				returnValue.add(name.get());
    			}
    			return returnValue;
    		}
    	}
    }