下面是我在自己的一个项目中用来分析嵌套SQL语句的两个类,它们的基本做法就是靠括号来区别嵌套语句的层,然后用类似进栈的方法将复杂SQL分离成一个个小的简单SQL语句,最好再用类似出栈的方法将整个SQL整形好.
代码如下:
SqlFormatter类:
package com.junglesong.common.sqlFormatter;
import java.util.Hashtable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.junglesong.common.sqlFormatter.Exception.CantParseSqlException;
import com.junglesong.common.sqlFormatter.Exception.SingleKHException;
public class SqlFormatter 
{
private static final char LeftKH = '(';
private static final char RightKH = ')';
private static final String ReplacePart = "ReplacePart";
private Hashtable partialSqlMap;
public SqlFormatter() {
partialSqlMap = new Hashtable();
}
private void checkPairKuoHao(String sql) throws SingleKHException {
int retval = 0;
for (int i = 0; i < sql.length(); i++) {
char tmp = sql.charAt(i);
if (tmp == LeftKH) {
retval++;
} else if (tmp == RightKH) {
retval--;
}
}
if (retval != 0) {
throw new SingleKHException(sql);
}
}
public String formatSql(String originalSql) throws SingleKHException,
CantParseSqlException {
checkPairKuoHao(originalSql);
int index = 0;
while (isFindKuohao(originalSql)) {
int leftKHPos = getDeepestLeftKHPos(originalSql);
int rightKHPos = getNearestRightKHPos(leftKHPos, originalSql);
int layer = getLeftKHLayer(leftKHPos, originalSql);
String replacePart = originalSql.substring(leftKHPos,
rightKHPos + 1);
String replacePartIndex = ReplacePart + index;
partialSqlMap.put(replacePartIndex, new PartialSql(replacePart,
layer));
originalSql = originalSql.replace(replacePart, replacePartIndex);
index++;
}
String middleSql = new PartialSql(originalSql).getSql();
String patternStr = "(" + ReplacePart + "[0-9]+)";
Pattern pattern = Pattern.compile(patternStr);
Matcher matcher = pattern.matcher(middleSql);
while (matcher.find()) {
String replaceStr = matcher.group();
PartialSql replacePart = (PartialSql) partialSqlMap.get(replaceStr);
middleSql = middleSql.replace(replaceStr, replacePart.getSql());
matcher = pattern.matcher(middleSql);
}
String finalSql = middleSql;
return finalSql;
}
private boolean isFindKuohao(String text) {
return isFindLeftKh(text) || isFindRightKh(text);
}
private boolean isFindLeftKh(String text) {
return text.indexOf(LeftKH) >= 0;
}
private boolean isFindRightKh(String text) {
return text.indexOf(RightKH) >= 0;
}
private int getDeepestLeftKHPos(String text) {
int retval = 0;
for (int i = 0; i < text.length(); i++) {
char tmp = text.charAt(i);
if (tmp == LeftKH) {
retval = i;
}
}
return retval;
}
private int getNearestRightKHPos(int leftKHPos, String text)
throws SingleKHException {
int retval = 0;
for (int i = leftKHPos; i < text.length(); i++) {
char tmp = text.charAt(i);
if (tmp == RightKH) {
retval = i;
return retval;
}
}
throw new SingleKHException(text);
}
private int getLeftKHLayer(int leftKHPos, String text) {
int retval = 0;
for (int i = 0; i < leftKHPos; i++) {
char tmp = text.charAt(i);
if (tmp == LeftKH) {
retval++;
} else if (tmp == RightKH) {
retval--;
}
}
return retval;
}
public static void main(String[] args) {
try {
// String text = "select f1,(select a from b) from (select f1,f2
// from (select f1,f2,f3 from tb)),t4 where 1=1 ";
// String text = "select f1,f2,f3,f4,f5 from tabl1 where 1=1 ";
// String text = "select f1,(select f2 from t01) from t02 where 1=1
// ";
//String text = "select f1,(select * from tb2,(select * from (select * from tb4))) from tabl1 where 1=1 ";
String text = "select f1,(select * from tb2,(select * from (select * from (select * from tb5)))) from tabl1 where 1=1 ";
SqlFormatter testSqlParser = new SqlFormatter();
System.out.println("Final Text=\n" + testSqlParser.formatSql(text));
} catch (SingleKHException ex) {
System.out.print(ex.getErrMsg());
} catch (CantParseSqlException ex) {
System.out.print(ex.getErrMsg());
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
PartialSql类:
package com.junglesong.common.sqlFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
import com.junglesong.common.sqlFormatter.Exception.CantParseSqlException;
public class PartialSql {
private String sql;
private int layer;
private List sqlLines;
private static final String CRLF = "\n";
private static final String TAB = " "; // Four Spaces
public PartialSql(String sql, int layer) throws CantParseSqlException {
this.sql = removeKH(sql);
this.layer = layer;
sqlLines = new ArrayList();
splitSql(this.sql);
}
public PartialSql(String sql) throws CantParseSqlException {
this(sql, -1);
}
private void splitSql(String sql) throws CantParseSqlException {
if(sql.toLowerCase().indexOf("select")<0){
throw new CantParseSqlException(sql);
}
try {
Pattern p = Pattern.compile("(select)|(from)|(where)",
Pattern.CASE_INSENSITIVE);
String[] results = p.split(sql);
String[] fields = null;
String[] tables = null;
String wherePart = "";
if (results.length == 4) {
fields = getSplitDotArray(results[1]);
tables = getSplitDotArray(results[2]);
wherePart = results[3];
} else if (results.length == 3) {
fields = getSplitDotArray(results[1]);
tables = getSplitDotArray(results[2]);
wherePart = "";
}
String tab = TAB;
sqlLines.add(" Select " + tab + CRLF);
int n = fields.length;
for (int i = 0; i < n; i++) {
if (i != n - 1) {
sqlLines.add(tab + tab + fields[i].trim() + "," + CRLF);
} else {
sqlLines.add(tab + tab + fields[i].trim() + CRLF);
}
}
sqlLines.add(" from " + tab + CRLF);
n = tables.length;
for (int i = 0; i < n; i++) {
if (i != n - 1) {
sqlLines.add(tab + tab + tables[i].trim() + "," + CRLF);
} else {
sqlLines.add(tab + tab + tables[i].trim() + CRLF);
}
}
if (wherePart.length() > 0) {
sqlLines.add(" where " + tab + CRLF);
Pattern pattern = Pattern.compile("(and)",
Pattern.CASE_INSENSITIVE);
String[] wheres = pattern.split(wherePart);
n = wheres.length;
for (int i = 0; i < n; i++) {
if (i != n - 1) {
sqlLines.add(tab + tab + wheres[i].trim() + " and " + CRLF);
} else {
sqlLines.add(tab + tab + wheres[i].trim() + CRLF);
}
}
//sqlLines.add(tab + tab + wherePart);
}
} catch (Exception ex) {
throw new CantParseSqlException(sql);
}
}
private String[] getSplitDotArray(String text) {
Pattern p = Pattern.compile("[,]");
String[] results = p.split(text);
return results;
}
private String removeKH(String sql) {
String retval = sql;
retval = retval.replace("(", "");
retval = retval.replace(")", "");
return retval;
}
public String getSql() {
String retval = "";
if (layer != -1