[简单]替换字符串中的参数

         这段代码估计用不到了，写的很简单，用了最直观的思路替换参数。

        

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class 替换字符串参数_S4_Test {
	public static void main(String[] args) throws Exception {
		 String str="select rownum, bb.* from (select :a, :b + 4, 4 + :c, 5 + :b, to_char(:c, 'yyy-mm-dd'), to_date(:v, 'yyyy-mm-dd') from a where a.order_id = :b and b in (:b) and c in (:b, :l) and d in (:l, 0) and e in (0, :l) and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and b.prod_spec_id = :MODEL_ID and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and a.order_type = 80 and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd') and extract(month from b.done_date) = extract(month from to_date(:b, 'yyyy-mm')) and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date) and extract(month from b.done_date) = extract(month from to_date(:b, ' yyyy - mm ')) union select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having extract(month from b.done_date) = :b and b = :b union all select extract(month from to_date(:b, ' yyyy - mm ')), to_char(:v, 'yyy-m-dd'), to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd') from b group by a having(extract(month from b.done_date) = :b and b = :b) and b = :k) bb group by aa, bb desc having(aa = :k and bb > :b + 5) ";
		 str = addSqlBlank(str);
		 List<String> sqlList = splitSqlByUnion(str);
		 str=replaceSqlNullValue(sqlList);
		 System.out.println("替换结果为="+str);
	}
	
	public static String replaceSqlNullValue(List<String> sqlList)throws Exception {
		if(sqlList==null||sqlList.size()==0){
			return "";
		}
		StringBuffer sb2=new StringBuffer();
		for (String sql : sqlList) {
			// 取真实的from 是否带where 是否带having
			int existsIndex = sql.lastIndexOf(" exists(");
			int havingIndex = sql.indexOf(" having");
			int fromIndex = sql.lastIndexOf(" from ");
			int whereIndex = sql.indexOf(" where ");
			int extractIndex = sql.lastIndexOf(" extract(");
			String selectSqlStr = null;
			if (havingIndex != -1 && fromIndex > havingIndex) {
				fromIndex = sql.substring(0, havingIndex).lastIndexOf(" from ");
			}
			if (whereIndex != -1 && extractIndex != -1) {
				fromIndex = sql.substring(0, whereIndex).lastIndexOf(" from ");
			}
			if (whereIndex != -1 && fromIndex > existsIndex) {
				while (existsIndex != -1 && fromIndex > existsIndex) {
					String tmpStr = sql.substring(0, existsIndex);
					fromIndex = tmpStr.lastIndexOf(" from ");
					existsIndex = tmpStr.lastIndexOf(" exists(");
				}
			}
			selectSqlStr = sql.substring(0, fromIndex + 6);
			sql = sql.substring(fromIndex + 6);
			sb2.append(replaceNullValueAfterSelect(selectSqlStr));
			sb2.append(removeAllErrorConnect(replaceValueAfterFrom(sql)));
		}
		return sb2.toString();
	}
	
	public static String replaceNullValueAfterSelect(String sql) {
		if (sql.indexOf(":") == -1) {
			return sql;
		}
		int position = sql.indexOf(":");
		StringBuffer before = new StringBuffer();
		StringBuffer after = new StringBuffer();
		int beforeBracket = 0;// 匹配括号数量
		boolean isConnect = false;
		char bracketBeforeChar = '\0', bracketAfterChar = '\0';
		//直接拿到字符串
		while (position != -1) {
			// 向←直到空格或者开始
			for (int i = position - 1; i >= 0; i--) {
				if (sql.charAt(i) == '(') {
					beforeBracket++;
				} else if (sql.charAt(i) == ')') {
					beforeBracket--;
				}
				if (sql.charAt(i) == ',' || sql.charAt(i) == ' ') {
					break;
				} else if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
					isConnect = true;
					before.append(sql.charAt(i));
					bracketBeforeChar = sql.charAt(i);
					break;
				} else {
					before.append(sql.charAt(i));
				}
			}
			// 向→直到空格或者最后
			for (int i = position; i < sql.length(); i++) {
				if (beforeBracket != 0) {
					if (sql.charAt(i) == '(') {
						beforeBracket++;
					} else if (sql.charAt(i) == ')') {
						beforeBracket--;
					}
					after.append(sql.charAt(i));
					continue;
				}
				if (sql.charAt(i) == '+' || sql.charAt(i) == '-') {
					bracketAfterChar = sql.charAt(i);
					after.append(sql.charAt(i));
					isConnect = true;
					break;
				}
				if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
					after.append(sql.charAt(i));
				} else {
					break;
				}
			}
			after.insert(0, before.reverse());
			if (isConnect) {
				sql = sql.replace(
						after.toString(),
						String.valueOf(
								bracketBeforeChar + "'0'" + bracketAfterChar)
								.replace('\0', ' '));
			} else {
				sql = sql.replace(after.toString(), "' '");
			}
			position = sql.indexOf(":");
			before.setLength(0);
			after.setLength(0);
			beforeBracket = 0;
			isConnect = false;
			bracketAfterChar = '\0';
			bracketBeforeChar = '\0';
		}
		after = null;
		before = null;
		sql = sql.replaceAll("(extract\\s*\\()([^)]*'\\s+')(\\s*\\))", "' '");;
		return new String(sql);
	}
	
	public static String replaceValueAfterFrom(String sql) throws Exception {
		if (sql.indexOf(":") == -1) {
			return sql;
		}
		StringBuffer result = new StringBuffer();
		//含连接符
		String[] tmpStrArr = splitByStrNormal(sql, "(?<=\\s+)(and|or)(?=\\s+|\\s*\\()");
		for (String subStr : tmpStrArr) {
			if (subStr.indexOf(":") == -1) {
				if (subStr.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "").trim().length() == 0) {
					result.append(' ');
				} else {
					result.append(subStr);
				}
			} else if (subStr.indexOf(" in ") != -1) {
				result.append(replaceNullParaWithIn(subStr));
			} else {
				result.append(replaceParaNormalWithBlank(subStr));
			}
		}
		return result.toString();
	}
	
	public static String replaceParaNormalWithBlank(String sql) throws Exception {
		int position = sql.indexOf(":");
		if (position == -1) {
			return sql;
		}
		// 忽略extract
		if (sql.indexOf("extract") != -1) {
			return removeExtract(sql);
		}
		if (sql.indexOf("to_date") == -1 && sql.indexOf("to_char") == -1) {
			if (sql.indexOf("+") != -1 || sql.indexOf("-") != -1) {
				return replaceParaWithConnect(sql);
			}
		}
		StringBuffer before = new StringBuffer();
		StringBuffer after = new StringBuffer();
		int leftBracket =0, rightBracket =0;
		// 括号匹配
		int beforeBracket = 0;// 匹配括号数量
		int leftIndex=0,rightIndex=sql.length();
		while (position != -1) {
			 leftBracket =Math.abs(sql.substring(0, position).replaceAll("\\(", "").length()-sql.substring(0, position).replaceAll("\\)", "").length());
			 rightBracket =Math.abs(sql.substring(position).replaceAll("\\(", "").length()-sql.substring(position).replaceAll("\\)", "").length());
			 if(leftBracket>rightBracket){
				for (int i = 0; i < position; i++) {
					if (sql.charAt(i) == '(') {
						beforeBracket++;
					} else if (sql.charAt(i) == ')') {
						beforeBracket--;
					}
					if(beforeBracket==leftBracket-rightBracket){
						leftIndex=i+1;
						break;
					}
				}
			 }else if(leftBracket<rightBracket){
				 for(int i=sql.length()-1;i>position;i--){
					if (sql.charAt(i) == '(') {
						beforeBracket--;
					} else if (sql.charAt(i) == ')') {
						beforeBracket++;
					}
					if(beforeBracket==rightBracket-leftBracket){
						rightIndex=i;
						break;
					}
				 }
			 }
			// 向←直到空格或者开始
			for (int i = position - 1; i >=leftIndex; i--) {
				if (sql.charAt(i) != ' ') {
					if (sql.charAt(i) == '(') {
						beforeBracket++;
					} else if (sql.charAt(i) == ')') {
						beforeBracket--;
					}
					before.append(sql.charAt(i));
				} else {
					break;
				}
			}
			// 向→直到空格或者最后
			for (int i = position; i <rightIndex; i++) {
				if (beforeBracket != 0) {
					if (sql.charAt(i) == '(') {
						beforeBracket++;
					} else if (sql.charAt(i) == ')') {
						beforeBracket--;
					}
					after.append(sql.charAt(i));
					continue;
				}
				if (sql.charAt(i) != ' ' && sql.charAt(i) != ')') {
					after.append(sql.charAt(i));
				} else {
					break;
				}
			}
			after.insert(0, before.reverse());
			sql = sql.replace(after.toString(), "");
			position = sql.indexOf(":");
			before.setLength(0);
			after.setLength(0);
			beforeBracket = 0;
			leftIndex=0;
			rightIndex=sql.length();
		}
		before.setLength(0);
		after.setLength(0);
		after = null;
		before = null;
		if(sql.indexOf("(")==-1&&sql.indexOf(")")==-1){
			return sql.replaceAll("(?<=\\s+|^)(and|or)(?=\\s+|\\)|$)", "");
		}
		return sql;
	}
	
	public static String removeExtract(String sql) throws Exception {
		int beforeBracket=0;
		int position=sql.indexOf(":");
		String subStr=sql.substring(0,position);
		int whereIndex=subStr.lastIndexOf(" where "); 
		int havingIndex=subStr.lastIndexOf(" having "); 
		int havingIndex2=subStr.lastIndexOf(" having( "); 
		StringBuffer result=new StringBuffer();
		//删除extract
		if(whereIndex!=-1){
			result.append(sql.substring(0, whereIndex+" where ".length()));
			sql=sql.substring(whereIndex+" where ".length());
		}else if(havingIndex!=-1){
			result.append(sql.substring(0, havingIndex+" having ".length()));
			sql=sql.substring(havingIndex+" having ".length());
		}else if(havingIndex2!=-1){
			result.append(sql.substring(0, havingIndex2+" having(".length()));
			sql=sql.substring(havingIndex2+" having(".length());
		}
		boolean paramFlag=false,bracketEnd=false;
		for(int i=Math.max(0,havingIndex2),len=sql.length();i<len;i++){
			if(sql.charAt(i)=='('){
				beforeBracket++;
				bracketEnd=true;
				continue;
			}else if(sql.charAt(i)==')'){
				beforeBracket--;
				continue;
			}
			if(sql.charAt(i)==':'){
				paramFlag=true;
			}
			if(bracketEnd&&paramFlag&&beforeBracket==0){
				if(sql.charAt(i)==' '||sql.charAt(i)==')'){
					result.append(sql.substring(i));
					break;
				}
			}
		}
		return replaceNullParaWithIn(result.toString());
	}
	
	// 含<>的连接符的变量替换为0
	public static String replaceParaWithConnect(String sql) {
		int position = sql.indexOf(":");
		StringBuffer before = new StringBuffer();
		StringBuffer after = new StringBuffer();
		while (position != -1) {
			if (sql.substring(0, position).indexOf(">") == -1
					&&sql.substring(0, position).indexOf("<") == -1
					&&sql.substring(0, position).indexOf("!=") == -1) {
				// 向←直到连接符
				for (int i = position - 1; i >= 0; i--) {
					if (sql.charAt(i) != '(') {
						before.append(sql.charAt(i));
					} else {
						break;
					}
				}
				// 向→直到连接符或者最后
				for (int i = position; i < sql.length(); i++) {
					if (sql.charAt(i) == ' ' || sql.charAt(i) == ')') {
						break;
					} else {
						after.append(sql.charAt(i));
					}
				}
				after.insert(0, before.reverse());
				sql = sql.replace(after.toString(), "");
			} else {
				// 向←直到连接符
				for (int i = position - 1; i >= 0; i--) {
					if (sql.charAt(i) != '=' && sql.charAt(i) != '<'
							&& sql.charAt(i) != '>' && sql.charAt(i) != '+'
							&& sql.charAt(i) != '-') {
						before.append(sql.charAt(i));
					} else {
						break;
					}
				}
				// 向→直到连接符或者最后
				for (int i = position; i < sql.length(); i++) {
					if (sql.charAt(i) == '+' || sql.charAt(i) == '-'
							|| sql.charAt(i) == ')') {
						break;
					} else {
						after.append(sql.charAt(i));
					}
				}
				after.insert(0, before.reverse());
				sql = sql.replace(after.toString(), "'0'");
			}
			position = sql.indexOf(":");
			before.setLength(0);
			after.setLength(0);
		}
		before.setLength(0);
		after.setLength(0);
		after = null;
		before = null;
		sql=sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
		sql=sql.replaceAll("(\\s*)(and|or)(\\s*\\(\\s*\\))", " ");
		return new String(sql);
	}

	// 替换in括号内的参数
	public static String replaceNullParaWithIn(String sql) throws Exception {
		int position = sql.indexOf(":");
		if (position == -1 || sql.indexOf("in") == -1) {
			return removeInErrorSem(sql);
		}
		StringBuffer before = new StringBuffer();
		StringBuffer after = new StringBuffer();
		while (position != -1) {
			// 向←直到空格或者开始
			for (int i = position - 1; i >= 0; i--) {
				if (sql.charAt(i) != ' ' && sql.charAt(i) != ',') {
					before.append(sql.charAt(i));
				} else {
					break;
				}
			}
			// 向→直到空格或者最后
			for (int i = position; i < sql.length(); i++) {
				if (sql.charAt(i) != ' ' && sql.charAt(i) != ','
						&& sql.charAt(i) != ')') {
					after.append(sql.charAt(i));
				} else {
					break;
				}
			}
			after.insert(0, before.reverse());
			sql = sql.replace(after.toString(), "");
			position = sql.indexOf(":");
			before.setLength(0);
			after.setLength(0);
		}
		before.setLength(0);
		after.setLength(0);
		after = null;
		before = null;
		sql = removeInErrorSem(sql);
		sql = sql.replaceAll(",\\s*\\)", ")");
		sql = sql.replaceAll("in\\s*\\(\\s*\\)", "in()");
		sql = removeInBlankBracket(sql);
		return new String(sql);
	}

	// 清除in括号内多余的逗号
	public static String removeInErrorSem(String str) throws Exception {
		int position = str.indexOf(" in");
		if (position == -1) {
			return str;
		}
		StringBuffer sb = new StringBuffer();
		boolean isStart = false;
		int endIndex = str.length();
		while (position != -1) {
			sb.append(str.substring(0, position + 3));
			for (int i = position + 3; i < str.length(); i++) {
				if (str.charAt(i) == '(') {
					sb.append(str.charAt(i));
					continue;
				}
				if (str.charAt(i) == ')') {
					sb.append(str.charAt(i));
					endIndex = i;
					break;
				}
				if (str.charAt(i) != ',') {
					sb.append(str.charAt(i));
					if (str.charAt(i) != ' ') {
						isStart = true;
					}
				} else if (isStart && str.charAt(i) == ',') {
					sb.append(str.charAt(i));
					isStart = false;
				}
			}
			str = str.substring(endIndex + 1);
			endIndex = str.length();
			position = str.indexOf(" in");
		}
		if (str != null) {
			sb.append(str);
		}
		str = sb.toString();
		sb.setLength(0);
		sb = null;
		return new String(str);
	}

	// 清除in空括号
	public static String removeInBlankBracket(String sql) {
		if (sql.indexOf("in()") == -1) {
			return sql;
		}
		int position = sql.indexOf("in()");
		StringBuffer sb = new StringBuffer();
		int startIndex = 0;
		boolean isParam = false, isChange = false;
		;
		while (position != -1) {
			for (int i = position - 1; i >= 0; i--) {
				if (!isParam) {
					if (sql.charAt(i) != ' ') {
						isParam = true;
						continue;
					}
				}
				if (isParam) {
					if (sql.charAt(i) == '(') {
						isParam = false;
						startIndex = i + 1;
						isChange = true;
						break;
					} else if (i >= 2 && sql.charAt(i) == 'd'
							&& sql.charAt(i - 1) == 'n'
							&& sql.charAt(i - 2) == 'a') {
						isParam = false;
						isChange = true;
						startIndex = i - 2;
						break;
					} else if (i >= 1 && sql.charAt(i) == 'r'
							&& sql.charAt(i - 1) == 'o') {
						isParam = false;
						isChange = true;
						startIndex = i - 1;
						break;
					}
				}
			}
			if (isChange) {
				sb.append(sql.substring(0, startIndex));
			} else {
				sb.append(sql.substring(0, position + 4));
			}
			startIndex = 0;
			isParam = false;
			isChange = false;
			sql = sql.substring(position + 4);
			position = sql.indexOf("in()");
		}
		if (sql != null) {
			sb.append(sql);
		}
		sql = sb.toString();
		sb.setLength(0);
		sb = null;
		sql = sql.replaceAll("(\\(\\s*)(and|or)(\\s+)", "( ");
		sql = sql.replaceAll("(\\s*|^)(and|or)(\\s*\\(\\s*\\))", " ");
		return new String(sql);
	}

	public static String removeAllErrorConnect(String sql) throws Exception {
		sql = replaceSqlWithRegex(sql,"(\\(\\s*)(and|or)(\\s*\\)?)", new int[]{1,3});
		sql = replaceSqlWithRegex(sql,"(\\s+)(and|or)(\\s*\\))", new int[]{1,3});
		sql = replaceSqlWithRegex(sql,"(\\s+and|or)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
		sql = replaceSqlWithRegex(sql, "(\\s*)(\\(\\s*,?\\s*\\))(\\s*,?$?)", new int[]{3});// 清空空括号
		sql = replaceSqlWithRegex(sql, "(where\\s+)(and|or|in)(\\s+|$)", new int[]{1});
		sql = replaceSqlWithRegex(sql,"(\\s+)(where|having|order\\s+by|group\\s+by)(\\s*$|\\s*\\)|\\s+union)",new int[]{3});
		sql = replaceSqlWithRegex(sql, "where\\s+(group|order|$)", new int[]{1});
		sql = replaceSqlWithRegex(sql, "having\\s+(order|$)", new int[]{1});
		sql = replaceSqlWithRegex(sql, "group\\s+by\\s+(order|$)", new int[]{1});
		sql = sql.replaceAll("\\s{2,}", " ");// 删除多余空格
		return new String(sql);
	}
	
	// sql添加空格
	public static String addSqlBlank(String sql) throws Exception {
		sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/|\\()(\\s+)",new int[]{2});// 去除运算符附近空格
		sql = replaceSqlWithRegex(sql, "(\\s+)(=|<|>|!|-|\\+|\\*|/)(\\s*)", new int[]{2});// 去除运算符附近空格
		sql = replaceSqlWithRegex(sql, "(\\s*)(=|<|>|!|-|\\+|\\*|/)(\\s+)", new int[]{2});// 去除运算符附近空格
		sql = replaceSqlWithRegex(sql, "(where\\s+)(,|and|or|in)(\\s+|$)", new int[]{1});// 清空where后面直接带连接符
		sql = cleanSqlComment(sql);
		sql = lowerCaseOracleKeyWord(sql);
		
		sql = sql.replaceAll("\\s*:\\s*", ":");// 去除:附近空格
		sql = sql.replaceAll("\\*from", "* from ");// from前面添加空格
		sql = sql.replaceAll("select", "select ");// from前面添加空格
		sql = sql.replaceAll("\\)", " )");// )前面添加空格
		sql = sql.replaceAll("(?<!and|or)\\s*\\(\\s*", "(");// 去除(附近空格
		sql = sql.replaceAll("\\s+in\\s*\\(", " in ( ");// in(添加空格
		sql = sql.replaceAll("\\s+having\\s*\\(", " having( ");// having(添加空格

		sql = sql.replaceAll(",", " , ");// ,后面添加空格
		// 删除不匹配的注释
		sql = sql.replace("/*", " ");
		sql = sql.replace("*/", " ");
		return new String(sql);
	}
		
	public static String replaceSqlWithRegex(String sql, String regex,
			int[] indexs) throws Exception {
		Pattern p = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sql);
		Arrays.sort(indexs);
		// 添加循环匹配失败检测
		int isFail = 0;
		while (true) {
			while (m.find()) {
				isFail++;
				if (isFail > Byte.MAX_VALUE) {
					throw new Exception("检测到匹配次数过多，请优化匹配表达式");
				}
				String tmp = new String();
				for (int i = 0; i < indexs.length; i++) {
					tmp += m.group(indexs[i]);
				}
				sql = sql.replace(m.group(), tmp);
			}
			m = p.matcher(sql);
			if (!m.find()) {
				break;
			}
			m.reset();
		}
		return new String(sql);
	}

	// 数据库关键字小写
	public static String lowerCaseOracleKeyWord(String sql) throws Exception {
		String regexStr = "(?<=\\s+|\\(|\\)|^)(select|as|from|where|and|or|in|exists|extract|to_date|to_char|not|trunc|group|by|having|order)(?=\\s+|\\(|\\)|$)";
		sql = replaceStrByAppend(regexStr, sql, true);
		return sql;
	}

	// 大小写字符串
	public static String replaceStrByAppend(String regEx, String sql,
			boolean isLower) {
		Pattern pattern = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(sql);
		StringBuffer sb = new StringBuffer();
		int lastEnd = 0;// 上一次匹配结束位置
		while (matcher.find()) {
			sb.append(sql.substring(lastEnd, matcher.start()));
			if (isLower) {
				sb.append(sql.substring(matcher.start(), matcher.end())
						.toLowerCase());
			} else {
				sb.append(sql.substring(matcher.start(), matcher.end()));
			}
			lastEnd = matcher.end();
		}
		if (lastEnd < sql.length()) {
			sb.append(sql.substring(lastEnd));
		}
		return sb.toString();
	}

	public static List<String> splitSqlByUnion(String sql){
		List<String> resultList=new ArrayList<String>();
		if(sql.indexOf(" union ")==-1){
			resultList.add(sql);
		}else{
			int position=sql.indexOf(" union ");
			while(position!=-1){
				String tmpStr=sql.substring(0,sql.indexOf(" union ")+7);
				resultList.add(tmpStr);
				sql=sql.substring(sql.indexOf(" union ")+7);
				position=sql.indexOf(" union ");
			}
			if(sql!=null){
				resultList.add(sql);
			}
		}
		return resultList;
	}
	
	// 字符串分割保留分隔符
	public static String[] splitByStrNormal(String str, String split) {
		Pattern p = Pattern.compile(split);
		Matcher m = p.matcher(str);
		String[] words = p.split(str);
		String[] strResult = new String[words.length];
		strResult[0] = words[0];
		if (words.length > 0) {
			int count = 1;
			while (count < words.length) {
				if (m.find()) {
					strResult[count] = new String(m.group() + words[count]);
				}
				count++;
			}
		}
		return strResult;
	}

	// 清除sql内的注释
	public static String cleanSqlComment(String sql) {
		if (sql.indexOf("/*") == -1) {
			return sql;
		}
		StringBuilder sb = new StringBuilder((int) (sql.length() * 1.5));
		int cursor = 0, start = -1, end = -1;
		for (; (start = sql.indexOf("/*", cursor)) != -1
				&& (end = sql.indexOf("*/", start)) != -1;) {
			sb.append(' ').append(sql.substring(cursor, start));
			cursor = end + 2;
		}
		sb.append(sql.substring(cursor, sql.length()));
		return sb.toString();
	}

	// 初步检查sql是否正确 规则1)select前面有其他字符,2)sql括号数量不匹配 3)sql中注释不匹配
	public static String checkSql(String sql) {
		StringBuffer errorInfo = new StringBuffer();
		sql = sql.trim().toLowerCase();
		if (!sql.startsWith("select")) {
			errorInfo.append(" select开始前有其他字符");
		}
		int leftBracket = sql.length() - sql.replaceAll("\\(", "").length(), rightBracket = sql
				.length() - sql.replaceAll("\\)", "").length();
		if (leftBracket != rightBracket) {
			errorInfo.append(" 括号不匹配");
		}
		sql = cleanSqlComment(sql);
		if (sql.indexOf("/*") != -1 || sql.indexOf("*/") != -1) {
			errorInfo.append(" sql中存在多余的注释");
		}
		return errorInfo.toString();
	}

	public static StringBuffer map2StringBuffer(Map<String, String> map) {
		StringBuffer result = new StringBuffer();
		for (Entry<String, String> entry : map.entrySet()) {
			result.append(entry.getKey()).append("=").append(entry.getValue())
					.append(",");
		}
		if (result.length() > 0) {
			result.deleteCharAt(result.length() - 1);
		}
		return result;
	}

}

    替换前:

   

select rownum, bb.*
  from (select :a,
               :b + 4,
               4 + :c,
               5 + :b,
               to_char(:c, 'yyy-mm-dd'),
               to_date(:v, 'yyyy-mm-dd')
          from a
         where a.order_id = :b
           and b in (:b)
           and c in (:b, :l)
           and d in (:l, 0)
           and e in (0, :l)
           and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
           and b.prod_spec_id = :MODEL_ID
           and extract(month from b.done_date) =
               extract(month from to_date(:b, ' yyyy - mm '))
           and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
           and a.order_type = 80
           and extract(month from b.done_date) = to_date(:b, 'yyyy-mm-dd')
           and extract(month from b.done_date) =
               extract(month from to_date(:b, 'yyyy-mm'))
           and to_date(b, 'yyyy-mm-dd') = extract(month from :done_date)
           and extract(month from b.done_date) =
               extract(month from to_date(:b, ' yyyy - mm '))
        union
        select extract(month from to_date(:b, ' yyyy - mm ')),
               to_char(:v, 'yyy-m-dd'),
               to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
          from b
         group by a
        having extract(month
          from b.done_date) = :b and b = :b
        union all
        select extract(month from to_date(:b, ' yyyy - mm ')),
               to_char(:v, 'yyy-m-dd'),
               to_char(to_date(:b, 'yyy-mm-dd'), 'yyyy-mm-dd')
          from b
         group by a
        having(extract(month
          from b.done_date) = :b and b = :b) and b = :k) bb
 group by aa, bb desc
having(aa = :k and bb > :b + 5)

   替换后:

  

替换结果为=select  rownum ,  bb.* from(select ' ' ,  '0'+4 ,  4+'0'  ,  5+'0'  ,  ' ' ,  ' ' from a where d in ( 0 ) and e in ( 0 ) and a.order_type=80 union select  ' ' ,  ' ' ,  ' ' from b group by a union all select  ' ' ,  ' ' ,  ' ' from b group by a ) bb group by aa , bb desc having( bb>'0'+5 ) 

  

select rownum, bb.*
  from (select ' ', '0' + 4, 4 + '0', 5 + '0', ' ', ' '
          from a
         where d in (0)
           and e in (0)
           and a.order_type = 80
        union
        select ' ', ' ', ' '
          from b
         group by a
        union all
        select ' ', ' ', ' ' from b group by a) bb
 group by aa, bb desc
having(bb > '0' + 5)

    全文完。