列表模糊查询

shicf

2026-03-29 f0027ee7daf2c53631e974fe433147777f990ea7

列表模糊查询

 src/main/java/com/product/face/entity/FaceListEntity.java

@@ -348,6 +348,18 @@
     * @return
     */
    public Map<String, List<String>> getFilters(Map<String, List<String>> tableAlias, Map<String, List<String>> aliasField,Map<String, List<String>> tableAndField) {
        return getFilters(tableAlias, aliasField,tableAndField,false);
    }
    /**
     * 新的方法，根据查询语句的中的表别名，生成查询条件
     *
     * @param tableAlias 表别名
     * @param aliasField 字段别名
     * @param  tableAndField 表中字段
     * @return
     */
    public Map<String, List<String>> getFilters(Map<String, List<String>> tableAlias, Map<String, List<String>> aliasField,Map<String, List<String>> tableAndField,boolean unionSql) {

        DataPoolCacheImpl poolCache = null;
        //所有字段集合
        List<FaceField> faceFields = this.getFaceFields();
@@ -365,38 +377,65 @@
            if (!faceField.isSearch()) {
                continue;
            }
            if (StringUtils.isEmpty(faceField.getFieldName()) || StringUtils.isEmpty(faceField.getFieldReference())) {
            //字段名称,可能是字段别名
            String fieldName = faceField.getFieldName();
            String factFieldName =fieldName;//真实的字段名
            //需要去查出真实字段名
            if (aliasField != null) {
				
                for(String key:aliasField.keySet()) {
                    List<String> fieldAlias = aliasField.get(key);
                    if (fieldAlias != null && fieldAlias.size() > 0) {
                        for(int j=0;j<fieldAlias.size();j++){
                            String alias = fieldAlias.get(j);
                            if(fieldName.equals(alias)) {
                                factFieldName = key.trim();
                                if(factFieldName.indexOf('.')>0) {
                                    factFieldName=factFieldName.substring(factFieldName.indexOf('.')+1);
                                }
                            }
                        }
                    }
                }
            }
            //取查询条件中的字段，如果有别名，则显示【别名.字段名】
            String aliasTableField=getFieldName(tableAlias,aliasField, tableAndField, fieldName,factFieldName,unionSql);
			
            //查出字段对应的参照配置
            if (//StringUtils.isEmpty(faceField.getFieldName()) || 
                    StringUtils.isEmpty(faceField.getFieldReference())) {
                if (poolCache == null) {
                    poolCache = DataPoolCacheImpl.getInstance();
                }
                DataTableEntity dt = poolCache.getCacheData("表字段信息", new String[]{getTableUuid(), faceField.getFieldName()});
                DataTableEntity dt = poolCache.getCacheData("表字段信息", new String[]{getTableUuid(),factFieldName});
                if (BaseUtil.dataTableIsEmpty(dt)) {
					
                } else {
                    FieldSetEntity f = dt.getFieldSetEntity(0);
                    faceField.setFieldName(f.getString(CmnConst.FIELD_NAME));
//                    faceField.setFieldName(f.getString(CmnConst.FIELD_NAME));
                    faceField.setFieldReference(f.getString("field_reference"));
                }
            }
            //字段名称
            String fieldName = faceField.getFieldName();
            if (!StringUtils.isEmpty(fieldName)) {
                fieldName.trim();
            }
            if (aliasField != null && !StringUtils.isEmpty(fieldName)) {
                List<String> fieldAlias = aliasField.get(getTableName() + "." + fieldName);
                if (fieldAlias != null && fieldAlias.size() > 0) {
                    String alias = fieldAlias.get(0);
                    if (!StringUtils.isEmpty(alias)) {
                        fieldName = alias.trim();
                    }
                }
            }
			
//            if (!StringUtils.isEmpty(fieldName)) {
//                fieldName.trim();
//            }
//            if (aliasField != null && !StringUtils.isEmpty(fieldName)) {
//                List<String> fieldAlias = aliasField.get(getTableName() + "." + fieldName);
//                if (fieldAlias != null && fieldAlias.size() > 0) {
//                    String alias = fieldAlias.get(0);
//                    if (!StringUtils.isEmpty(alias)) {
//                        fieldName = alias.trim();
//                    }
//                }
//            }
            //参照处理
            String field_reference = faceField.getFieldReference();
            if (!StringUtils.isEmpty(field_reference)) {
                if (field_reference.indexOf("《") == 0 && field_reference.indexOf("》") > 0) {
                    //普通参照
                    filters.add(getDictFilter(getFieldName(tableAlias,aliasField, tableAndField, fieldName), field_reference.replace("《", "").replace("》", "")));
//                    filters.add(getDictFilter(getFieldName(tableAlias,aliasField, tableAndField, fieldName), field_reference.replace("《", "").replace("》", "")));
                    filters.add(getDictFilter(aliasTableField, field_reference.replace("《", "").replace("》", "")));
                    continue;
                } else {
                    if (poolCache == null) {
@@ -409,16 +448,18 @@
                    }
                    FieldSetEntity ff = prompt.getFieldSetEntity(0);
                    StringBuilder p = new StringBuilder();
                    p.append(getFieldName(tableAlias,aliasField, tableAndField, fieldName)).append(" in ").append(" ( SELECT ").append(ff.getString("value_field")).append(" FROM ").append(ff.getString("source_table"));
//                    p.append(getFieldName(tableAlias,aliasField, tableAndField, fieldName)).append(" in ").append(" ( SELECT ").append(ff.getString("value_field")).append(" FROM ").append(ff.getString("source_table"));
                    p.append(aliasTableField).append(" in ").append(" ( SELECT ").append(ff.getString("value_field")).append(" FROM ").append(ff.getString("source_table"));
                    p.append(" WHERE ").append(ff.getString("view_fields")).append(" like concat('%','~val~','%') )");
                    filters.add(p.toString());
                    continue;
                }
            }

            if (!StringUtils.isEmpty(faceField.getFieldName())) {
                fieldNames.add(getFieldName(tableAlias,aliasField, tableAndField, fieldName));
            }
//            if (!StringUtils.isEmpty(faceField.getFieldName())) {
//                fieldNames.add(getFieldName(tableAlias,aliasField, tableAndField, fieldName););
//            }
            fieldNames.add(aliasTableField);
        }
        //获取redis 起始key
        String faceListKey = SystemFaceService.FACE_LIST_KEY;
@@ -435,17 +476,35 @@
        return fieldName;
    }
    //新方法
    private String getFieldName(Map<String, List<String>> tableAlias,Map<String, List<String>> aliasField, Map<String, List<String>> tableAndField, String fieldName) {
    //获取【表名.字段名】，哪果没有表别名，只返回字段名
    private String getFieldName(Map<String, List<String>> tableAlias,Map<String, List<String>> aliasField, Map<String, List<String>> tableAndField, String fieldName,String factFieldName) {
        return getFieldName(tableAlias,aliasField, tableAndField, fieldName,factFieldName,false);
    }
    //新方法
    /**
     * 获取【表名.字段名】，哪果没有表别名，只返回字段名
     * @param tableAlias
     * @param aliasField
     * @param tableAndField
     * @param fieldName
     * @param factFieldName
     * @param unionSql 是否是由union 组成的多表查询，且是放在最外层的查询条件，是则不能在字段前面加表别名，否则要添加表别名,适用sql如：
     * select * from (select* from xxx a union all select * from xxx b) where 条件
     * @return
     */
    private String getFieldName(Map<String, List<String>> tableAlias,Map<String, List<String>> aliasField, Map<String, List<String>> tableAndField, String fieldName,String factFieldName,boolean unionSql) {
	
        if(unionSql) return fieldName;
		
        String table=null;
        boolean b=false;
        if(tableAndField!=null && !tableAndField.isEmpty()) {
            for(String key:tableAndField.keySet()) {
                for(String field:tableAndField.get(key)) {
                    if(fieldName.equals(field)) {

                        List<String>alias=tableAlias.get(key);
                        if(alias!=null && !alias.isEmpty()) {
                            table=alias.get(0);//取第一个别名
                            table=alias.get(0);//取第一个表别名
                        }
                        b=true;
                        break;
@@ -455,28 +514,28 @@
            }
        }
        b=false;
        //找到字段的真实名字
        for(String f:aliasField.keySet()) {
            List<String> fs=aliasField.get(f);
            if(!fs.isEmpty()) {
                for(String af:fs) {
                    //字段别名有可能是带 表别名
                    int c=af.indexOf('.');
                    if(c>0)af=af.substring(c+1);
                    if(fieldName.equals(af)) {
                        fieldName=f;
                        b=true;
                        break;
                    }
                }
            }
            if(b)break;
        }
//        //找到字段的真实名字
//        for(String f:aliasField.keySet()) {
//            List<String> fs=aliasField.get(f);
//            if(!fs.isEmpty()) {
//                for(String af:fs) {
//                    //字段别名有可能是带 表别名
//                    int c=af.indexOf('.');
//                    if(c>0)af=af.substring(c+1);
//                    if(fieldName.equals(af)) {
//                        fieldName=f;
//                        b=true;
//                        break;
//                    }
//                }
//            }
//            if(b)break;
//        }

        if (fieldName.indexOf('.')<0 && !StringUtils.isEmpty(table)) {
            return "`" + table + "`." + fieldName;
            return "`" + table + "`." + factFieldName;
        }
        return fieldName;
        return factFieldName;
    }

    /**

 src/main/java/com/product/face/service/FaceListSearchService.java

@@ -81,10 +81,12 @@
        "product_sys_job_posts": ["job_post_uuid"],
        "product_sys_org_levels": ["uuid2", "uuid3", "org_level_uuid", "dept_uuid"]
    }
     * @param unionSql 是否是由union 组成的多表查询，且是放在最外层的查询条件，是则不能在字段前面加表别名，否则要添加表别名,适用sql如：
     * select * from (select* from xxx a union all select * from xxx b) where 条件
     * @return
     * @throws BaseException
     */
    public String getFaceListSearchFilter(Map<String, List<String>> aliasTable, Map<String, List<String>> aliasField, String faceUuid, String faceNumber, String val, Map<String, List<String>> tableOfField) throws BaseException {
    public String getFaceListSearchFilter(Map<String, List<String>> aliasTable, Map<String, List<String>> aliasField, String faceUuid, String faceNumber, String val, Map<String, List<String>> tableOfField,Boolean unionSql) throws BaseException {
        if (StringUtils.isEmpty(val)) {
            return "";
        }
@@ -101,24 +103,43 @@
//            if (t != null && t.size() > 0) {
//                tableAlias = t.get(0);
//            }
            Map<String, List<String>> params = face.getFilters(aliasTable, aliasField,tableOfField);
            Map<String, List<String>> params = face.getFilters(aliasTable, aliasField,tableOfField,unionSql);
            List<String> fieldNames = params.get("fields");
            List<String> filters = params.get("filter");
            String filter = "";
            StringBuilder filter = new StringBuilder();
            for (int i = 0; i < fieldNames.size(); i++) {
                if (i > 0) {
                    filter += " or ";
                    filter.append(" or ");
                }
                filter += " " + fieldNames.get(i) + " LIKE BINARY concat('%','" + val + "','%')";
                filter.append(" ").append(fieldNames.get(i)).append( " LIKE BINARY concat('%','").append( val).append("','%')");
            }
            for (int i = 0; i < filters.size(); i++) {
                if (!"".equals(filter)) {
                    filter += " or ";
                    filter.append(" or ");
                }
                filter += filters.get(i).replace("~val~", val);
                filter.append(filters.get(i).replace("~val~", val));
            }
            return "".equals(filter) ? "" : "( " + filter + " )";
            return "".equals(filter) ? "" :filter.insert(0, "( ").append(" )").toString();
        }
        return "";
    }
    /**
     * 列表关键字搜索
     * @param aliasTable  {"product_sys_staffs":["a"],"product_sys_users":["b"],"product_sys_job_posts":["e"],"product_sys_org_levels":["c","d"]}
     * @param aliasField
     * @param faceUuid
     * @param faceNumber
     * @param val
     * @param tableOfField  表对应字段 如：table_relate_fields = {
        "product_sys_staffs": ["uuid", "staff_status", "staff_avatar", "show_name", "mobile_phone", "staff_status_save_value", "status_save_value"],
        "product_sys_users": ["uuid1", "user_name", "user_id", "user_account", "status"],
        "product_sys_job_posts": ["job_post_uuid"],
        "product_sys_org_levels": ["uuid2", "uuid3", "org_level_uuid", "dept_uuid"]
        }
    * @return
     * @throws BaseException
     */
    public String getFaceListSearchFilter(Map<String, List<String>> aliasTable, Map<String, List<String>> aliasField, String faceUuid, String faceNumber, String val, Map<String, List<String>> tableOfField) throws BaseException {
        return getFaceListSearchFilter(aliasTable, aliasField,  faceUuid,  faceNumber,  val, tableOfField,false);
    }
}