Java 正则表达式 匹配sql参数和替换
目录
语法:
\\s 匹配空格
在动态sql查询中,需要替换sql查询语句的条件
匹配和替换sql里面的参数
要求:
如何把
select * from account WHERE field_name1 = $param.paramName and field_name2 = $global.data and field_name3 = $mean and field_name4 = $dream
参数:
Map<String,Object> single = new HashMap<>();
single.put("paramName","me");
single.put("mean","wonders");
转换为:
select * from account WHERE field_name1 = "me" and field_name2 = "wonders" and field_name3 = "wonders"
处理思路:
1,先匹配有值的情况
2,然后把参数为空的情况处理了。
3,当没有参数的时候,处理where作为后缀的情况
代码:
public static void main(String[] args) {
Map<String,Object> single = new HashMap<>();
single.put("paramName","me");
single.put("mean","wonders");
List<String> one = Lists.newArrayList("paramName","mean");
String content = "select * from account WHERE field_name1 = $param.paramName and field_name2 = $global.data and field_name3= $mean and field_name4 = $dream";
for (String e : ListUtils.emptyIfNull(one)) {
String value = MapUtils.getString(single, e);
// 先匹配现有的内容
content = matchReplaceWithCondition(content, e, value);
}
// 匹配参数内容为空的情况
content = getReplaceSql(content).trim();
if(StringUtils.endsWithIgnoreCase(content,"where")){
content = StringUtils.removeEndIgnoreCase(content,"where");
}
System.out.println("last "+content);
}
public static String matchReplaceWithCondition( String content,String condition,String value)
{
String pattern = "\\$([a-zA-Z0-9_.]*)" + condition;
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(content);
StringBuffer sb = new StringBuffer();
while (m.find()) {
String group = m.group();
m.appendReplacement(sb, group == null ? "" : ("\"").concat(value).concat("\""));
}
m.appendTail(sb);
return sb.toString();
}
public static String getReplaceSql(String content) {
String words = "([a-zA-Z0-9._]*)";
String patternReg = (words+"\\s*=\\s*\\$"+words);
// 处理遗留的逗号
String pattern = patternReg+" and|"+patternReg+"|and "+patternReg;
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(content);
StringBuffer sb = new StringBuffer();
while (m.find()) {
String value = m.group();
m.appendReplacement(sb, value == null ? "" : "");
}
m.appendTail(sb);
System.out.println("getReplaceSql: "+sb.toString());
return sb.toString();
}
结果:
getReplaceSql: select * from account WHERE field_name1 = "me" and field_name3="wonders"
last select * from account WHERE field_name1 = "me" and field_name3="wonders"
这边的匹配,使用\\s* 匹配0到N个空格,这样sql怎么写,都好处理写。匹配不出来。
思考:
如果sql后面有排序,聚合的内容,如:order by 等,参数全为空的情况下,带有where就不好处理了,这时候改怎么办?
可以换一种思路,把排序,聚合用java去处理。
总结:
动态执行sql的时候,处理sql字符串的时候,主要是空格的匹配和参数为空时,带有where的处理。