字符串一:@[id_type = '1002' and keyword1 = '324234'@] @AND @[id_type = '1001' and keyword1 = '23324234'@] or (id_type = '1007' and keyword1 = '23432423') @AND @[id_type = '1002' and keyword1 = '676765'@]字符串二:(select * from tt a where ((id_type='1002' and keyword1='qq1234567') or (id_type='1002' keyword1='qq88776'))and (select count(distinct keyword1) from tt b where a.event_num=b.event_num)>=2 );
要求得到的结果:(select * from tt a where ((id_type = '1002' and keyword1 = '324234') or (id_type = '1001' and keyword1 = '23324234')or (id_type = '1001' and keyword1 = '23324234'))and (select count(distinct keyword1) from tt b where a.event_num=b.event_num)>=3) or (id_type= '1007' and keyword1 = '23432423'));
具体需求:就是要把源串中的@[... @] 或者 @AND @[ .... @] 这样的子字串提取出来,并记录其子字符串的总数,其他的字符不处理。
然后把@AND、 @[ 、 @] 分别替换为 or 、 ( 、 )
再将替换好的字符串组成新的字符串插入( select * from tt a where ( 目标位置 )and (select count(distinct keyword1) from tt b where a.event_num=b.event_num)>= 子字符串的总数 ) + 其他未处理的字符串 ) ,这样就ok了。@[.... @]可能是零个或一个, @AND @[... @] 这样的子串的数量可能是零个,也可能是四五个,更多。
要求得到的结果:(select * from tt a where ((id_type = '1002' and keyword1 = '324234') or (id_type = '1001' and keyword1 = '23324234')or (id_type = '1001' and keyword1 = '23324234'))and (select count(distinct keyword1) from tt b where a.event_num=b.event_num)>=3) or (id_type= '1007' and keyword1 = '23432423'));
具体需求:就是要把源串中的@[... @] 或者 @AND @[ .... @] 这样的子字串提取出来,并记录其子字符串的总数,其他的字符不处理。
然后把@AND、 @[ 、 @] 分别替换为 or 、 ( 、 )
再将替换好的字符串组成新的字符串插入( select * from tt a where ( 目标位置 )and (select count(distinct keyword1) from tt b where a.event_num=b.event_num)>= 子字符串的总数 ) + 其他未处理的字符串 ) ,这样就ok了。@[.... @]可能是零个或一个, @AND @[... @] 这样的子串的数量可能是零个,也可能是四五个,更多。
再逐步对切割得到的每个元素按@]切割分出不处理的串。
然后,对含有@[的,替换成or (,当然第一个只需替换成(,追加)
不含@[的,连接到代表“其他未处理的字符串”的一个临时串。
剩下替换“目标位置”,“子字符串的总数”和“其他未处理的字符串”就简单了,自己想吧。
String temp=condition;
return temp.length()-temp.replaceAll("@[","").length();
}
private List<String> getAndLogicList(String condition){
List<String> list=new ArrayList<String>();
int indx=-1;
int indx2=-1;
if(condition.indexOf("@[")<condition.indexOf("@AND")){
indx=condition.indexOf("@[");
}else{
indx=condition.indexOf("@AND @[");
}
indx2=condition.indexOf("@]",indx);
while(-1!=indx && -1!=indx2){
list.add(condition.substring(indx, indx2));
indx=condition.indexOf("@AND @[");
indx2=condition.indexOf("@]",indx);
}
return list;
}
private String getNoAndLogicString(String condition){
String temp=condition;
List<String> list=getAndLogicList(temp);
for(String andLogic : list){
if(temp.indexOf(andLogic)!=-1){
temp.replaceAll(andLogic,"");
}
}
return temp;
}
private String replaceAndLogicMark(List<String> list){
StringBuffer sb=new StringBuffer();
for(String andLogic:list){
sb.append(andLogic).append(" ");
}
return sb.toString().replaceAll("@AND", "or").replaceAll("@[", "(").replaceAll("@]",")");
}
private String getAllString(String sourceTable,String condition){
StringBuffer sql=new StringBuffer().append("select * from ").append(sourceTable).append(" a where ( ");
sql.append(this.replaceAndLogicMark(this.getAndLogicList(condition)));
sql.append(" ) and (select count(distinct keyword1) from ");
sql.append(sourceTable);
sql.append(" b where a.event_num=b.event_num)>= ");
sql.append(this.getAndLogicCount(condition));
sql.append(this.getNoAndLogicString(condition));
return sql.toString();
}在此抛砖引玉,肯请指教。
private int getAndLogicCount(String condition){
String temp=condition;
return temp.length()-temp.replaceAll("\\@\\[","").length();
}
private List<String> getAndLogicList(String condition){
List<String> list=new ArrayList<String>();
int indx=-1;
int indx2=-1;
if(condition.indexOf("@[")<condition.indexOf("@AND")){
indx=condition.indexOf("@[");
}else{
indx=condition.indexOf("@AND @[");
}
indx2=condition.indexOf("@]",indx);
while(-1!=indx && -1!=indx2){
list.add(condition.substring(indx, indx2+2));
indx=condition.indexOf("@AND @[",indx2);
indx2=condition.indexOf("@]",indx);
}
return list;
}
private String getNoAndLogicString(String condition){
StringBuffer sb=new StringBuffer().append(condition);
List<String> list=getAndLogicList(condition);
for(String andLogic : list){
if(condition.indexOf(andLogic)!=-1){
String _sb=sb.toString().replace(andLogic,"").replaceAll("[\\s]*\\([\\s]*\\)[\\s]*","").replaceAll("[\\s]+"," ");
sb.delete(0, sb.length());
sb.append(_sb);
}
}
String result=sb.toString();
Pattern p=Pattern.compile("(\\(|and|or)\\s?(and|or|\\))");
Matcher m=p.matcher(result);
if(m.find()){
for(int i=1;i<=m.groupCount();i++){
String _group=m.group(i);
if(_group.equals("and") || _group.equals("or")){
sb.delete(0,sb.length()).append(_group+" ");
}
if(i==1){
sb.append(result.replaceAll("(\\(|and|or)\\s?(and|or|\\))",")"));
}else{
sb.append(result.replaceAll("(\\(|and|or)\\s?(and|or|\\))","("));
}
}
}
if(log.isDebugEnabled()){
log.debug("-------NoAndLogicString-------------"+sb.toString());
}
return sb.toString();
}
private String replaceAndLogicMark(List<String> list){
StringBuffer sb=new StringBuffer();
for(String andLogic:list){
sb.append(andLogic).append(" ");
}
return sb.toString().replaceAll("\\@AND", "or").replaceAll("\\@\\[", "\\(").replaceAll("\\@\\]","\\)");
}
private String getAllString(String sourceTable,String condition){
StringBuffer sql=new StringBuffer().append("( ");
sql.append(this.replaceAndLogicMark(this.getAndLogicList(condition)));
sql.append(" ) and (select count(distinct keyword1) from ");
sql.append(sourceTable);
sql.append(" D where C.event_num=D.event_num and " +this.replaceAndLogicMark(this.getAndLogicList(condition))+" )>= ");
sql.append(this.getAndLogicCount(condition)+" ");
sql.append(this.getNoAndLogicString(condition));
if(log.isDebugEnabled()){
log.debug("===============sql============== "+sql.toString());
}
return sql.toString();
}