packagecom.sitinspring.common.sqlFormatter;importjava.util.ArrayList;importjava.util.List;importjava.util.regex.Matcher;importjava.util.regex.Pattern;/*** SQL语句解析器类
*@author: sitinspring(junglesong@gmail.com)
* @date: 2008-3-12*/publicclassSqlParser{/*** 逗号*/privatestaticfinalString Comma=",";/*** 四个空格*/privatestaticfinalString FourSpace="";/*** 是否单行显示字段,表,条件的标识量*/privatestaticbooleanisSingleLine=true;/*** 待解析的SQL语句*/privateString sql;/*** SQL中选择的列*/privateString cols;/*** SQL中查找的表*/privateString tables;/*** 查找条件*/privateString conditions;/*** Group By的字段*/privateString groupCols;/*** Order by的字段*/privateString orderCols;/*** 构造函数
* 功能:传入构造函数,解析成字段,表,条件等
*@paramsql:传入的SQL语句*/publicSqlParser(String sql){this.sql=sql.trim();
parseCols();
parseTables();
parseConditions();
parseGroupCols();
parseOrderCols();
}/*** 解析选择的列
**/privatevoidparseCols(){
String regex="(select)(.+)(from)";
cols=getMatchedString(regex,sql);
}/*** 解析选择的表
**/privatevoidparseTables(){
String regex="";if(isContains(sql,"\\s+where\\s+")){
regex="(from)(.+)(where)";
}else{
regex="(from)(.+)($)";
}
tables=getMatchedString(regex,sql);
}/*** 解析查找条件
**/privatevoidparseConditions(){
String regex="";if(isContains(sql,"\\s+where\\s+")){//包括Where,有条件if(isContains(sql,"group\\s+by")){//条件在where和group by之间regex="(where)(.+)(group\\s+by)";
}elseif(isContains(sql,"order\\s+by")){//条件在where和order by之间regex="(where)(.+)(order\\s+by)";
}else{//条件在where到字符串末尾regex="(where)(.+)($)";
}
}else{//不包括where则条件无从谈起,返回即可return;
}
conditions=getMatchedString(regex,sql);
}/*** 解析GroupBy的字段
**/privatevoidparseGroupCols(){
String regex="";if(isContains(sql,"group\\s+by")){//包括GroupBy,有分组字段if(isContains(sql,"order\\s+by")){//group by 后有order byregex="(group\\s+by)(.+)(order\\s+by)";
}else{//group by 后无order byregex="(group\\s+by)(.+)($)";
}
}else{//不包括GroupBy则分组字段无从谈起,返回即可return;
}
groupCols=getMatchedString(regex,sql);
}/*** 解析OrderBy的字段
**/privatevoidparseOrderCols(){
String regex="";if(isContains(sql,"order\\s+by")){//包括GroupBy,有分组字段regex="(order\\s+by)(.+)($)";
}else{//不包括GroupBy则分组字段无从谈起,返回即可return;
}
orderCols=getMatchedString(regex,sql);
}/*** 从文本text中找到regex首次匹配的字符串,不区分大小写
*@paramregex: 正则表达式
*@paramtext:欲查找的字符串
*@returnregex首次匹配的字符串,如未匹配返回空*/privatestaticString getMatchedString(String regex,String text){
Pattern pattern=Pattern.compile(regex,Pattern.CASE_INSENSITIVE);
Matcher matcher=pattern.matcher(text);while(matcher.find()){returnmatcher.group(2);
}returnnull;
}/*** 看word是否在lineText中存在,支持正则表达式
*@paramlineText
*@paramword
*@return*/privatestaticbooleanisContains(String lineText,String word){
Pattern pattern=Pattern.compile(word,Pattern.CASE_INSENSITIVE);
Matcher matcher=pattern.matcher(lineText);returnmatcher.find();
}publicString toString(){//无法解析则原样返回if(cols==null&&tables==null&&conditions==null&&groupCols==null&&orderCols==null){returnsql;
}
StringBuffer sb=newStringBuffer();
sb.append("原SQL为"+sql+"\n");
sb.append("解析后的SQL为\n");for(String str:getParsedSqlList()){
sb.append(str);
}
sb.append("\n");returnsb.toString();
}/*** 在分隔符后加上回车
*@paramstr
*@paramsplitStr
*@return*/privatestaticString getAddEnterStr(String str,String splitStr){
Pattern p=Pattern.compile(splitStr,Pattern.CASE_INSENSITIVE);//用Pattern类的matcher()方法生成一个Matcher对象Matcher m=p.matcher(str);
StringBuffer sb=newStringBuffer();//使用find()方法查找第一个匹配的对象booleanresult=m.find();//使用循环找出模式匹配的内容替换之,再将内容加到sb里while(result) {
m.appendReplacement(sb, m.group(0)+"\n");
result=m.find();
}//最后调用appendTail()方法将最后一次匹配后的剩余字符串加到sb里;m.appendTail(sb);returnFourSpace+sb.toString();
}/*** 取得解析的SQL字符串列表
*@return*/publicListgetParsedSqlList(){
ListsqlList=newArrayList();//无法解析则原样返回if(cols==null&&tables==null&&conditions==null&&groupCols==null&&orderCols==null){
sqlList.add(sql);returnsqlList;
}if(cols!=null){
sqlList.add("select\n");if(isSingleLine){
sqlList.add(getAddEnterStr(cols,Comma));
}else{
sqlList.add(FourSpace+cols);
}
}if(tables!=null){
sqlList.add("\nfrom\n");if(isSingleLine){
sqlList.add(getAddEnterStr(tables,Comma));
}else{
sqlList.add(FourSpace+tables);
}
}if(conditions!=null){
sqlList.add("\nwhere\n");if(isSingleLine){
sqlList.add(getAddEnterStr(conditions,"(and|or)"));
}else{
sqlList.add(FourSpace+conditions);
}
}if(groupCols!=null){
sqlList.add("\ngroup by\n");if(isSingleLine){
sqlList.add(getAddEnterStr(groupCols,Comma));
}else{
sqlList.add(FourSpace+groupCols);
}
}if(orderCols!=null){
sqlList.add("\norder by\n");if(isSingleLine){
sqlList.add(getAddEnterStr(orderCols,Comma));
}else{
sqlList.add(FourSpace+orderCols);
}
}returnsqlList;
}/*** 设置是否单行显示表,字段,条件等
*@paramisSingleLine*/publicstaticvoidsetSingleLine(booleanisSingleLine) {
SqlParser.isSingleLine=isSingleLine;
}/*** 测试
*@paramargs*/publicstaticvoidmain(String[] args){
Listls=newArrayList();
ls.add("select * from dual");
ls.add("SELECT * frOm dual");
ls.add("Select C1,c2 From tb");
ls.add("select c1,c2 from tb");
ls.add("select count(*) from t1");
ls.add("select c1,c2,c3 from t1 where condi1=1");
ls.add("Select c1,c2,c3 From t1 Where condi1=1");
ls.add("select c1,c2,c3 from t1,t2 where condi3=3 or condi4=5 order by o1,o2");
ls.add("Select c1,c2,c3 from t1,t2 Where condi3=3 or condi4=5 Order by o1,o2");
ls.add("select c1,c2,c3 from t1,t2,t3 where condi1=5 and condi6=6 or condi7=7 group by g1,g2");
ls.add("Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and condi6=6 or condi7=7 Group by g1,g2");
ls.add("Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and condi6=6 or condi7=7 Group by g1,g2,g3 order by g2,g3");for(String sql:ls){
System.out.println(newSqlParser(sql));//System.out.println(sql);}
}
}