????????在处理sql参数的时候，替换圆括号里面只处理了一种情况。而没有从整体上进行处理！！！
这是一个思考问题上严重的偏向。 考虑问题时候，要先从整体开始考虑，逐步分解，再细分到局部！
要求：
替换sql里面的参数：
SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME ,
b.REGION_NAME, b.operatingsystem, b.renduoxuan
FROM table_basic a, table_177 b WHERE a.table_id = b.table_id
AND a.table_serial in ($formSerial) and a.create_date >= $createStartDate
AND a.create_date <= $createEndDate or a.state like '%$state%'
AND b.tickettitle in ($tickettitle)
GROUP BY a.table_id ORDER BY state_date DESC
参数：
Map<String, Object> sqlParam = new HashMap<>();
sqlParam.put("state", "A,F");
sqlParam.put("tableSerial", "COM, tt");对于参数里面有的值进行替换，没有的值，替换为空。
分析：
? ? sql 里面支持大小写，替换的时候，要处理大小写。
常见的sql条件里面有： where、in、or、like?
如果where 后面的条件都为空 设置为 1 = 1
| \s | 匹配任何空白字符，包括空格、制表符、换页符等等。等价于 [ \f\n\r\t\v]。 |
前面条件的正则：
\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)
String condition = "\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)";后面条件的正则：
带like:? \s*\(*'%\$([a-zA-Z_.]*)%'\)*
其它：?\s*\(*\$([a-zA-Z_.]*)\)*
两个不好合并在一起处理，就分开来。
代码：
public static String replaceConditionStr(String content, Map<String, Object> param) {
String likeCondition = "\\s*\\(*\'%\\$([a-zA-Z_.]*)%\'\\)*";
content = replaceOtherConditionStr(content, param, likeCondition);
String otherCondition = "\\s*\\(*\\$([a-zA-Z_.]*)\\)*";
return replaceOtherConditionStr(content, param, otherCondition);
}
public static String replaceOtherConditionStr(String content, Map<String, Object> param, String paramConfition) {
String condition = "\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)";
String pattern = condition + paramConfition;
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(content);
while (m.find()) {
String group = m.group();
String operator = m.group(4);
String key = m.group(5);
if (key.contains(".")) {
key = StringUtils.substringAfter(key, ".");
}
String value = MapUtils.getString(param, key);
if (StringUtils.isBlank(value)) {
if (group.contains("WHERE") || group.contains("where")) {
content = content.replace(group, " where 1 = 1 ");
}
content = content.replace(group, "");
} else {
if ("like".equals(operator) || "LIKE".equals(operator)) {
if (!group.contains("%")) {
value = ("\'%").concat(value).concat("%\'");
}
} else {
if ("in".equals(operator) || "IN".equals(operator)) {
List<String> valueArr = Arrays.asList(value.split(","));
value = ListUtils.emptyIfNull(valueArr).stream().map(s -> "\'" + s.trim() + "\'")
.collect(Collectors.joining(","));
} else {
value = ("\'").concat(value).concat("\'");
}
}
content = content.replace("$" + key, value);
}
}
return content;
}测试：
?
public static void main(String[] args) {
testSqlEmpty();
testSqlReplace();
}
public static void testSqlEmpty() {
String sqlContent = "SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, "
+ " b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE "
+ " a.table_serial in ($formSerial) and a.create_date >= $createStartDate "
+ "AND a.create_date <= $createEndDate or a.state like '%$state%' " +
"AND b.tickettitle in ($tickettitle) GROUP BY a.table_id ORDER BY state_date DESC";
Map<String, Object> sqlParam = new HashMap<>();
System.out.println("empty param: "+replaceConditionStr(sqlContent, sqlParam));
}
public static void testSqlReplace() {
String sqlContent = "SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, "
+ " b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE a.table_id = b.table_id "
+ "AND a.table_serial in ($formSerial) and a.create_date >= $createStartDate "
+ "AND a.create_date <= $createEndDate or a.state like '%$state%' " +
"AND b.tickettitle in ($tickettitle) GROUP BY a.table_id ORDER BY state_date DESC";
Map<String, Object> sqlParam = new HashMap<>();
sqlParam.put("state", "A,F");
sqlParam.put("tableSerial", "COM, tt");
System.out.println("have param: "+replaceConditionStr(sqlContent, sqlParam));
}empty param: SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b where 1 = 1 GROUP BY a.table_id ORDER BY state_date DESC
have param: SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE a.table_id = b.table_id or a.state like '%A,F%' GROUP BY a.table_id ORDER BY state_date DESC
总结：
? ?处理问题的时候，先从整体考虑，全面把握，再细化到局部。如果完全没头绪，再从局部开始寻找突破。
?
