From d02571d59633367ac76b7f58ab38584698b1aa1b Mon Sep 17 00:00:00 2001
From: ludc
Date: 星期四, 17 十月 2024 09:55:37 +0800
Subject: [PATCH] 高级查询中增加范围查询功能
---
Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java | 121 +++++++++++++++++------------
Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/MybatisParameterUtil.java | 72 +++++++++++++++++
Source/UBCS-WEB/src/components/advanced-query/advancedQuery.vue | 3
3 files changed, 144 insertions(+), 52 deletions(-)
diff --git a/Source/UBCS-WEB/src/components/advanced-query/advancedQuery.vue b/Source/UBCS-WEB/src/components/advanced-query/advancedQuery.vue
index cb35a69..a7309cb 100644
--- a/Source/UBCS-WEB/src/components/advanced-query/advancedQuery.vue
+++ b/Source/UBCS-WEB/src/components/advanced-query/advancedQuery.vue
@@ -144,6 +144,9 @@
}, {
value: "_notlike",
label: "涓嶅寘鍚�",
+ },{
+ value: "_in",
+ label: "鑼冨洿(,闂撮殧)",
}
],
// 瀛楁绫诲瀷鏄崟閫夋垨涓嬫媺妗嗙被鍨嬬殑鏉′欢鏁扮粍
diff --git a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/MybatisParameterUtil.java b/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/MybatisParameterUtil.java
index 4c644ef..5f9e2f6 100644
--- a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/MybatisParameterUtil.java
+++ b/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/MybatisParameterUtil.java
@@ -14,7 +14,7 @@
import java.util.Map;
/**
- * in 鍙傛暟鎴彇锛岃繑鍥瀢rapper
+ * in 鍙傛暟鎴彇锛岃繑鍥瀢rapper/鎴栬�厀here鏉′欢瀛楃涓�
*
* @author ludc
* @date 2023/5/5
@@ -22,6 +22,76 @@
public class MybatisParameterUtil {
/**
+ * Oracle IN 鏌ヨ鐨勬渶澶у弬鏁伴檺鍒�
+ */
+ private static final int MAX_IN_CLAUSE_SIZE = 1000;
+
+ /**
+ * 灏嗙粰瀹氱殑 ID 鍒楄〃杞崲涓洪�傚悎 Oracle 鐨勬煡璇㈡潯浠�
+ *
+ * @param ids ID 鍒楄〃
+ * @return 閫傚悎 Oracle 鐨勬煡璇㈡潯浠�
+ */
+ public static String convertToOrConditions(String filed,List<String> ids) {
+ if (ids == null || ids.isEmpty()) {
+ return "";
+ }
+
+ StringBuilder queryBuilder = new StringBuilder();
+ int size = ids.size();
+
+ // 鍒嗘壒澶勭悊锛岄伩鍏嶈秴杩� Oracle IN 鏌ヨ鐨勬渶澶ч檺鍒�
+ for (int i = 0; i < size; i += MAX_IN_CLAUSE_SIZE) {
+ int end = Math.min(i + MAX_IN_CLAUSE_SIZE, size);
+ List<String> subList = ids.subList(i, end);
+ if (queryBuilder.length() > 0) {
+ queryBuilder.append(" OR ");
+ }
+ queryBuilder.append("(")
+ .append(getInClause(filed,subList)).append(")");
+ }
+
+ return queryBuilder.toString();
+ }
+
+ /*
+ * 鑾峰彇 IN 鏌ヨ鏉′欢瀛楃涓�
+ * @param ids ID 鍒楄〃
+ * @return IN 鏌ヨ鏉′欢瀛楃涓�
+ */
+ private static String getInClause(String filed,List<String> ids) {
+ StringBuilder inClauseBuilder = new StringBuilder();
+ inClauseBuilder.append(filed + " IN (");
+
+ for (int i = 0; i < ids.size(); i++) {
+ inClauseBuilder.append("'").append(ids.get(i)).append("'");
+ if (i < ids.size() - 1) {
+ inClauseBuilder.append(", ");
+ }
+ }
+ inClauseBuilder.append(")");
+
+ return inClauseBuilder.toString();
+ }
+
+ /**
+ * 鑾峰彇 IN 鏌ヨ鏉′欢瀛楃涓诧紙涓嶅寘鍚玦n锛�
+ *
+ * @param ids ID 鍒楄〃
+ * @return IN 鏌ヨ鏉′欢瀛楃涓�
+ */
+ public static String getInClause(List<String> ids) {
+ StringBuilder inClauseBuilder = new StringBuilder();
+ for (int i = 0; i < ids.size(); i++) {
+ inClauseBuilder.append("'").append(ids.get(i)).append("'");
+ if (i < ids.size() - 1) {
+ inClauseBuilder.append(", ");
+ }
+ }
+ return inClauseBuilder.toString();
+ }
+
+ /**
* in浣滀负鏌ヨ鏉′欢鏃讹紝闃叉澶т簬1000鍑虹幇鎶ラ敊锛屽鏉′欢杩涜鎴彇锛岃繛琛ㄦ煡璇㈢殑wrapper
* @param wrapper MPJLambdaWrapper,涓嶈繘琛岃繛琛ㄦ煡璇㈡椂浣跨敤
* @param column 浣滀负in鐨勬潯浠跺垪
diff --git a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
index ab9f999..9fff05b 100644
--- a/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
+++ b/Source/UBCS/ubcs-service/ubcs-code/src/main/java/com/vci/ubcs/code/service/impl/MdmEngineServiceImpl.java
@@ -2050,7 +2050,7 @@
@Override
public DataGrid<Map<String, String>> queryGrid(String btmType, CodeClassifyTemplateVO templateVO, Map<String, String> conditionMap, PageHelper pageHelper) {
CodeTemplateAttrSqlBO sqlBO = getSqlByTemplateVO(btmType, templateVO, conditionMap, pageHelper);
-// List<Map> maps = boService.queryByOnlySqlForMap(sqlBO.getSqlHasPage());
+ // List<Map> maps = boService.queryByOnlySqlForMap(sqlBO.getSqlHasPage());
List<Map<String, String>> maps = null;
try {
maps = commonsMapper.queryByOnlySqlForMap(sqlBO.getSqlHasPage());
@@ -2070,39 +2070,39 @@
for (String s : map.keySet()) {
data.put(s.toLowerCase(Locale.ROOT), map.get(s) == null ? null : String.valueOf(map.get(s)));
}
-// map.forEach((key, value) -> {
-// data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
-// });
+ // map.forEach((key, value) -> {
+ // data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
+ // });
dataList.add(data);
});
wrapperData(dataList, templateVO, sqlBO.getSelectFieldList(), false);
-// maps.stream().forEach(map -> {
-// Map<String, String> data = new HashMap<>();
-//// map.forEach((key, value) -> {
-//// data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
-//// });
-// for (String s : map.keySet()) {
-// data.put(((String) s).toLowerCase(Locale.ROOT), String.valueOf(map.get(s)));
-// }
-// dataList.add(data);
-// });
+ // maps.stream().forEach(map -> {
+ // Map<String, String> data = new HashMap<>();
+ // map.forEach((key, value) -> {
+ // data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
+ // });
+ // for (String s : map.keySet()) {
+ // data.put(((String) s).toLowerCase(Locale.ROOT), String.valueOf(map.get(s)));
+ // }
+ // dataList.add(data);
+ // });
}
-// dataGrid.setData(dataList);
-// if (!CollectionUtils.isEmpty(maps)) {
-// wrapperData(maps, templateVO, sqlBO.getSelectFieldList(), false);
-// maps.stream().forEach(map -> {
-// Map<String, String> data = new HashMap<>();
-//// map.forEach((key, value) -> {
-//// data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
-//// });
-// for (String s : map.keySet()) {
-// data.put(((String) s).toLowerCase(Locale.ROOT), String.valueOf(map.get(s)));
-// }
-// dataList.add(data);
-// });
+ // dataGrid.setData(dataList);
+ // if (!CollectionUtils.isEmpty(maps)) {
+ // wrapperData(maps, templateVO, sqlBO.getSelectFieldList(), false);
+ // maps.stream().forEach(map -> {
+ // Map<String, String> data = new HashMap<>();
+ //// map.forEach((key, value) -> {
+ //// data.put(((String) key).toLowerCase(Locale.ROOT), String.valueOf(value));
+ //// });
+ // for (String s : map.keySet()) {
+ // data.put(((String) s).toLowerCase(Locale.ROOT), String.valueOf(map.get(s)));
+ // }
+ // dataList.add(data);
+ // });
dataGrid.setData(dataList);
dataGrid.setTotal(Long.parseLong(commonsMapper.selectBySql(sqlBO.getSqlCount()).get(0).values().toArray()[0].toString()));
-// }
+ // }
return dataGrid;
}
@@ -2179,7 +2179,7 @@
userVOMap.put(String.valueOf(data.getId()), data);
}
}
-// Map<String, SmUserVO> userVOMap = Optional.ofNullable(userQueryService.listUserByUserIds(userIds)).orElseGet(() -> new ArrayList<>()).stream().collect(Collectors.toMap(s -> s.getId().toLowerCase(Locale.ROOT), t -> t));
+ // Map<String, SmUserVO> userVOMap = Optional.ofNullable(userQueryService.listUserByUserIds(userIds)).orElseGet(() -> new ArrayList<>()).stream().collect(Collectors.toMap(s -> s.getId().toLowerCase(Locale.ROOT), t -> t));
dataMap.stream().forEach(data -> {
String creator = data.getOrDefault("creator", null);
if (StringUtils.isNotBlank(creator) && userVOMap.containsKey(creator.toLowerCase(Locale.ROOT))) {
@@ -2569,8 +2569,19 @@
if (referFieldMap.containsKey(field)) {
return VciBaseUtil.toInSql(referFieldMap.get(field), value); //referFieldMap.get(field) + SPACE + "= '" + value + "'" + SPACE;
} else {
- return (field.contains(".") ? "" : "t.") + field + SPACE + "= " + getStringValueInWhere(field, value, attrVOMap) + "" + SPACE;
+ //TODO:鏆傛椂娌¤�冭檻IN>1000鏉＄殑闂
+ //return (field.contains(".") ? "" : "t.") + field + SPACE + "= " + getStringValueInWhere(field, value, attrVOMap) + "" + SPACE;
+ return (field.contains(".") ? "" : "t.") + field + SPACE + "IN (" + getStringValueInWhere(field, "IN//"+value, attrVOMap) + ")" + SPACE;
}
+ } else if (key.endsWith("_notin")) {
+ String field = UBCSSqlKeyword.getColumn(key, "_in");
+ if (referFieldMap.containsKey(field)) {
+ return VciBaseUtil.toInSql(referFieldMap.get(field), value); //referFieldMap.get(field) + SPACE + "= '" + value + "'" + SPACE;
+ } else {
+ //return (field.contains(".") ? "" : "t.") + field + SPACE + "not in (" + getStringValueInWhere(field, "IN//"+value, attrVOMap) + ")" + SPACE;
+ return (field.contains(".") ? "" : "t.") + field + SPACE + "IN (" + getStringValueInWhere(field, "IN//"+value, attrVOMap) + ")" + SPACE;
+ }
+
} else {
// if (referFieldMap.containsKey(key)) {
// //璇存槑鏄弬鐓х殑锛屾垜浠弬鐓х殑鏌ヨ閮借涓烘槸瀛楃涓诧紝濡傛灉鏄椂闂存牸寮忕殑鏌ヨ鑲畾鏈夐棶棰橈紝
@@ -2667,13 +2678,21 @@
|| VciFieldTypeEnum.VTInteger.equals(fieldTypeEnum)) {
return value;
} else {
+ if(value.contains("IN//")){
+ return MybatisParameterUtil.getInClause(Func.toStrList(value.replace("IN//","")));
+ }
return "'" + value + "'";
}
} else {
if ((value.startsWith("(") && value.endsWith(")")) || (value.startsWith("'") && value.endsWith("'"))) {
return value;
+ }else if(value.contains("IN//")){
+ //鏆傛椂涓嶈�冭檻>1000鐨勬儏鍐碉紝鍥犱负鎼滅储涓�鑸篃涓嶄細鍑虹幇鎼滅储1000鏉＄殑鎯呭喌
+ //return MybatisParameterUtil.convertToOrConditions(field, Func.toStrList(value.replace("IN//","")));
+ return MybatisParameterUtil.getInClause(Func.toStrList(value.replace("IN//","")));
+ }else{
+ return "'" + value + "'";
}
- return "'" + value + "'";
}
}
@@ -3042,26 +3061,26 @@
}
});
return mapDataGrid;
-// List<String> selectFieldList = templateVO.getAttributes().stream().map(CodeClassifyTemplateAttrVO::getId).collect(Collectors.toList());
-// //鍙傜収璁╁钩鍙扮洿鎺ユ煡璇㈠氨琛�
-// List<String> finalSelectFieldList = selectFieldList;
-// List<CodeClassifyTemplateAttrVO> referAttrVOs = templateVO.getAttributes().stream().filter(
-// s -> StringUtils.isNotBlank(s.getReferbtmid())
-// &&
-// (finalSelectFieldList.size() ==0 || finalSelectFieldList.contains(s.getId().toLowerCase(Locale.ROOT)))
-// ).collect(Collectors.toList());
-// if(!CollectionUtils.isEmpty(referAttrVOs)){
-// for (int i = 0; i < referAttrVOs.size(); i++) {
-// selectFieldList.add(referAttrVOs.get(i).getId() + ".name");
-// }
-// }
-// //鎴戜滑浣跨敤鍜屼笟鍔＄被鍨嬬殑鏉ユ煡璇�
-// DataGrid<Map<String,String>> dataGrid = boService.queryGridByBo(btmTypeId, conditionMap, pageHelper, selectFieldList);
-// //鎴戜滑闇�瑕佷娇鐢ㄦā鏉挎潵杞崲
-// if(!CollectionUtils.isEmpty(dataGrid.getData())){
-// wrapperData(dataGrid.getData(),templateVO,finalSelectFieldList,false);
-// }
-// return dataGrid;
+ // List<String> selectFieldList = templateVO.getAttributes().stream().map(CodeClassifyTemplateAttrVO::getId).collect(Collectors.toList());
+ // //鍙傜収璁╁钩鍙扮洿鎺ユ煡璇㈠氨琛�
+ // List<String> finalSelectFieldList = selectFieldList;
+ // List<CodeClassifyTemplateAttrVO> referAttrVOs = templateVO.getAttributes().stream().filter(
+ // s -> StringUtils.isNotBlank(s.getReferbtmid())
+ // &&
+ // (finalSelectFieldList.size() ==0 || finalSelectFieldList.contains(s.getId().toLowerCase(Locale.ROOT)))
+ // ).collect(Collectors.toList());
+ // if(!CollectionUtils.isEmpty(referAttrVOs)){
+ // for (int i = 0; i < referAttrVOs.size(); i++) {
+ // selectFieldList.add(referAttrVOs.get(i).getId() + ".name");
+ // }
+ // }
+ // //鎴戜滑浣跨敤鍜屼笟鍔＄被鍨嬬殑鏉ユ煡璇�
+ // DataGrid<Map<String,String>> dataGrid = boService.queryGridByBo(btmTypeId, conditionMap, pageHelper, selectFieldList);
+ // //鎴戜滑闇�瑕佷娇鐢ㄦā鏉挎潵杞崲
+ // if(!CollectionUtils.isEmpty(dataGrid.getData())){
+ // wrapperData(dataGrid.getData(),templateVO,finalSelectFieldList,false);
+ // }
+ // return dataGrid;
}
/**
--
Gitblit v1.9.3