第java正則替換sql中的參數(shù)實(shí)例代碼目錄前言要求：分析：代碼：測試：總結(jié)：

前言

在處理sql參數(shù)的時(shí)候，替換圓括號(hào)里面只處理了一種情況。而沒有從整體上進(jìn)行處理?。?！

這是一個(gè)思考問題上嚴(yán)重的偏向?？紤]問題時(shí)候，要先從整體開始考慮，逐步分解，再細(xì)分到局部！

要求：

替換sql里面的參數(shù)：

SELECTa.table_id,a.table_title,a.table_name,a.table_serial,b.STAFF_NAME,

b.REGION_NAME,b.operatingsystem,b.renduoxuan

FROMtable_basica,table_177bWHEREa.table_id=b.table_id

ANDa.table_serialin($formSerial)anda.create_date=$createStartDate

ANDa.create_date=$createEndDateora.statelike'%$state%'

ANDb.tickettitlein($tickettitle)

GROUPBYa.table_idORDERBYstate_dateDESC

參數(shù)：

MapString,ObjectsqlParam=newHashMap();

sqlParam.put("state","A,F");

sqlParam.put("tableSerial","COM,tt");

對(duì)于參數(shù)里面有的值進(jìn)行替換，沒有的值，替換為空。

分析：

sql里面支持大小寫，替換的時(shí)候，要處理大小寫。

常見的sql條件里面有：where、in、or、like

如果where后面的條件都為空設(shè)置為1=1

\s匹配任何空白字符，包括空格、制表符、換頁符等等。等價(jià)于[\f\n\r\t\v]。

前面條件的正則：

\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|=|||=|like|LIKE)+)

Stringcondition="\\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_.]*)\)*

兩個(gè)不好合并在一起處理，就分開來。

代碼：

publicstaticStringreplaceConditionStr(Stringcontent,MapString,Objectparam){

StringlikeCondition="\\s*\\(*\'%\$([a-zA-Z_.]*)%'\\)*";

content=replaceOtherConditionStr(content,param,likeCondition);

StringotherCondition="\\s*\\(*\\$([a-zA-Z_.]*)\\)*";

returnreplaceOtherConditionStr(content,param,otherCondition);

publicstaticStringreplaceOtherConditionStr(Stringcontent,MapString,Objectparam,StringparamConfition){

Stringcondition="\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|=|||=|like|LIKE)+)";

Stringpattern=condition+paramConfition;

Patternp=Ppile(pattern);

Matcherm=p.matcher(content);

while(m.find()){

Stringgroup=m.group();

Stringoperator=m.group(4);

Stringkey=m.group(5);

if(key.contains(".")){

key=StringUtils.substringAfter(key,".");

Stringvalue=MapUtils.getString(param,key);

if(StringUtils.isBlank(value)){

if(group.contains("WHERE")||group.contains("where")){

content=content.replace(group,"where1=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)){

ListStringvalueArr=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);

returncontent;

}

測試：

publicstaticvoidmain(String[]args){

testSqlEmpty();

testSqlReplace();

publicstaticvoidtestSqlEmpty(){

StringsqlContent="SELECTa.table_id,a.table_title,a.table_name,a.table_serial,b.STAFF_NAME,b.REGION_NAME,"

+"b.operatingsystem,b.renduoxuanFROMtable_basica,table_177bWHERE"

+"a.table_serialin($formSerial)anda.create_date=$createStartDate"

+"ANDa.create_date=$createEndDateora.statelike'%$state%'"+

"ANDb.tickettitlein($tickettitle)GROUPBYa.table_idORDERBYstate_dateDESC";

MapString,ObjectsqlParam=newHashMap();

System.out.println("emptyparam:"+replaceConditionStr(sqlContent,sqlParam));

publicstaticvoidtestSqlReplace(){

StringsqlContent="SELECTa.table_id,a.table_title,a.table_name,a.table_serial,b.STAFF_NAME,b.REGION_NAME,"

+"b.operatingsystem,b.renduoxuanFROMtable_basica,table_177bWHEREa.table_id=b.table_id"

+"ANDa.table_serialin($formSerial)anda.create_date=$createStartDate"

+"ANDa.create_date=$createEndDateora.statelike'%$state%'"+

"ANDb.tickettitlein($tickettitle)GROUPBYa.table_idORDERBYstate_dateDESC";

MapString,ObjectsqlParam=newHashMap();

sqlParam.put("state","A,F");

sqlParam.put("tableSerial","COM,tt");

System.out.println("haveparam:"+replaceConditionStr(sqlContent,sqlParam));

}

emptyparam:SELECTa.table_id,a.table_title,a.table_name,a.table_serial,b.STAFF_NAME,b.REGION_NAME,b.operatingsystem,b.renduoxuanFROMtable_basica,table_177bwhere1=1GROUPBYa.table_idORDERBYstate_dateDESC

haveparam:SELECTa.table_id,a.table_title,a.table_name,a.table_serial,b.STAFF_