From f0027ee7daf2c53631e974fe433147777f990ea7 Mon Sep 17 00:00:00 2001
From: shicf <shi_chongfu@163.com>
Date: 星期日, 29 三月 2026 17:43:34 +0800
Subject: [PATCH] 列表模糊查询
---
src/main/java/com/product/face/entity/FaceListEntity.java | 157 +++++++++++++++++++++++++++------------
src/main/java/com/product/face/service/FaceListSearchService.java | 39 +++++++--
2 files changed, 138 insertions(+), 58 deletions(-)
diff --git a/src/main/java/com/product/face/entity/FaceListEntity.java b/src/main/java/com/product/face/entity/FaceListEntity.java
index 8b563cc..164ec4d 100644
--- a/src/main/java/com/product/face/entity/FaceListEntity.java
+++ b/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())) {
- if (poolCache == null) {
- poolCache = DataPoolCacheImpl.getInstance();
- }
- DataTableEntity dt = poolCache.getCacheData("琛ㄥ瓧娈典俊鎭�", new String[]{getTableUuid(), faceField.getFieldName()});
- if (BaseUtil.dataTableIsEmpty(dt)) {
- } else {
- FieldSetEntity f = dt.getFieldSetEntity(0);
- 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();
+ 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(),factFieldName});
+ if (BaseUtil.dataTableIsEmpty(dt)) {
+
+ } else {
+ FieldSetEntity f = dt.getFieldSetEntity(0);
+// faceField.setFieldName(f.getString(CmnConst.FIELD_NAME));
+ faceField.setFieldReference(f.getString("field_reference"));
+ }
+ }
+
+// 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;
}
/**
diff --git a/src/main/java/com/product/face/service/FaceListSearchService.java b/src/main/java/com/product/face/service/FaceListSearchService.java
index 7a1f02e..faa980f 100644
--- a/src/main/java/com/product/face/service/FaceListSearchService.java
+++ b/src/main/java/com/product/face/service/FaceListSearchService.java
@@ -80,11 +80,13 @@
"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"]
- }
+ }
+ * @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);
+ }
}
--
Gitblit v1.9.2