我想做个程序,把sql除了常量和参数都替换成大写,如:select /*+ index(event event_pk) */
count(1)
from event
where evtcode = 'some_game'
and evtno = #evtno#
and userid = #memberid#
and giftcode = #giftcode#
替换的结果,是
SELECT /*+ INDEX(EVENTMAST EVENTMAST_PK) */
COUNT(1)
FROM EVENTMAST
WHERE EVTCODE = #evtcode#
AND EVTNO = #evtno#
AND USERID = #memberid#
AND GIFTCODE = #giftcode##是ibatis中参数的定界符
count(1)
from event
where evtcode = 'some_game'
and evtno = #evtno#
and userid = #memberid#
and giftcode = #giftcode#
替换的结果,是
SELECT /*+ INDEX(EVENTMAST EVENTMAST_PK) */
COUNT(1)
FROM EVENTMAST
WHERE EVTCODE = #evtcode#
AND EVTNO = #evtno#
AND USERID = #memberid#
AND GIFTCODE = #giftcode##是ibatis中参数的定界符
SELECT /*+ INDEX(EVENT EVENT_PK) */
COUNT(1)
FROM EVENT
WHERE EVTCODE = 'some_game'
AND EVTNO = #evtno#
AND USERID = #memberid#
AND GIFTCODE = #giftcode#
Pattern pattern = Pattern.compile("(['#]\\S+)?");
Matcher matcher = pattern.matcher(command.toUpperCase());
StringBuffer buffer = new StringBuffer(256);
while (matcher.find()) {
matcher.appendReplacement(buffer, matcher.group().toLowerCase());
}
matcher.appendTail(buffer);
return buffer.toString();
}
String SQL = "select /*+ index(event event_pk) */\n"+
" count(1)\n" +
" from event\n" +
" where evtcode = 'some_game'\n" +
" and evtno = #evtno#\n" +
" and userid = #memberid#\n" +
" and giftcode = #giftcode#";
System.out.println(convert(SQL));SELECT /*+ INDEX(EVENT EVENT_PK) */
COUNT(1)
FROM EVENT
WHERE EVTCODE = 'some_game'
AND EVTNO = #evtno#
AND USERID = #memberid#
AND GIFTCODE = #giftcode#
String sql = "select /*+ index(event event_pk) */\n"+
" count(1)\n" +
" from event\n" +
" where evtcode = 'some_game'\n" +
" and evtno = #evtno#\n" +
" and userid = #memberid#\n" +
" and giftcode = #giftcode#";
StringBuffer sb = new StringBuffer();
Pattern p = Pattern.compile("(?s)(.*?\\s*)=(.*?(\n|$))");
Matcher m = p.matcher(sql);
while (m.find()) {
m.appendReplacement(sb, m.group(1).toUpperCase() + "=" + m.group(2));
}
m.appendTail(sb);
System.out.println(sb);
String sql = "select /*+ index(event event_pk) */\n"+
" count(1)\n" +
" from event\n" +
" where evtcode = 'some_game'\n" +
" and evtno = #evtno#\n" +
" and userid = #memberid#\n" +
" and giftcode = #giftcode#";
StringBuffer sb = new StringBuffer();
Pattern p = Pattern.compile("(?s)(.*?\\s*)(=.*?(\n|$))");
Matcher m = p.matcher(sql);
while (m.find()) {
m.appendReplacement(sb, m.group(1).toUpperCase() + m.group(2));
}
m.appendTail(sb);
System.out.println(sb);
请问正则开头那个(?s)是什么意思,从没见过这种写法。
"(?s)(.*?)(([<>!]?=|<>).*?(\n|$))"
?s是个开关,一般情况下,.是不匹配空格或换行符的,?s就是让.也能匹配这样的符号
其实正则考虑的情况未必周到,比如嵌套查询等等,可能情况很复杂,所以最好还是像做语法分析器一样,自己扫描判断
update还好,跟select差不多,都有=,但是insert情况就比较复杂了,有调系统函数或子查询什么的,正则估计比较难以胜任