先看要解析的样例SQL语句：
select * from dual SELECT * frOm dual Select C1,c2 From tb select c1,c2 from tb select count( *) from t1 select c1,c2,c3 from t1 where condi1 = 1 Select c1,c2,c3 From t1 Where condi1 = 1 select c1,c2,c3 from t1,t2 where condi3 = 3 or condi4 = 5 order by o1,o2 Select c1,c2,c3 from t1,t2 Where condi3 = 3 or condi4 = 5 Order by o1,o2 select c1,c2,c3 from t1,t2,t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1,g2 Select c1,c2,c3 From t1,t2,t3 Where condi1 = 5 and condi6 = 6 or condi7 = 7 Group by g1,g2 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解析效果之一（isSingleLine=false）：
原SQL为select * from dual 解析后的SQL为 select * from dual 原SQL为SELECT * frOm dual 解析后的SQL为 select * from dual 原SQL为Select C1,c2 From tb 解析后的SQL为 select C1,c2 from tb 原SQL为select c1,c2 from tb 解析后的SQL为 select c1,c2 from tb 原SQL为select count( *) from t1 解析后的SQL为 select count( *) from t1 原SQL为select c1,c2,c3 from t1 where condi1 = 1 解析后的SQL为 select c1,c2,c3 from t1 where condi1 = 1 原SQL为Select c1,c2,c3 From t1 Where condi1 = 1 解析后的SQL为 select c1,c2,c3 from t1 where condi1 = 1 原SQL为select c1,c2,c3 from t1,t2 where condi3 = 3 or condi4 = 5 order by o1,o2 解析后的SQL为 select c1,c2,c3 from t1,t2 where condi3 = 3 or condi4 = 5 order by o1,o2 原SQL为Select c1,c2,c3 from t1,t2 Where condi3 = 3 or condi4 = 5 Order by o1,o2 解析后的SQL为 select c1,c2,c3 from t1,t2 where condi3 = 3 or condi4 = 5 order by o1,o2 原SQL为select c1,c2,c3 from t1,t2,t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1,g2 解析后的SQL为 select c1,c2,c3 from t1,t2,t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1,g2 原SQL为Select c1,c2,c3 From t1,t2,t3 Where condi1 = 5 and condi6 = 6 or condi7 = 7 Group by g1,g2 解析后的SQL为 select c1,c2,c3 from t1,t2,t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1,g2 原SQL为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 解析后的SQL为 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解析效果之二（isSingleLine=true）：
原SQL为select * from dual 解析后的SQL为 select * from dual 原SQL为SELECT * frOm dual 解析后的SQL为 select * from dual 原SQL为Select C1,c2 From tb 解析后的SQL为 select C1, c2 from tb 原SQL为select c1,c2 from tb 解析后的SQL为 select c1, c2 from tb 原SQL为select count( *) from t1 解析后的SQL为 select count( *) from t1 原SQL为select c1,c2,c3 from t1 where condi1 = 1 解析后的SQL为 select c1, c2, c3 from t1 where condi1 = 1 原SQL为Select c1,c2,c3 From t1 Where condi1 = 1 解析后的SQL为 select c1, c2, c3 from t1 where condi1 = 1 原SQL为select c1,c2,c3 from t1,t2 where condi3 = 3 or condi4 = 5 order by o1,o2 解析后的SQL为 select c1, c2, c3 from t1, t2 where condi3 = 3 or condi4 = 5 order by o1, o2 原SQL为Select c1,c2,c3 from t1,t2 Where condi3 = 3 or condi4 = 5 Order by o1,o2 解析后的SQL为 select c1, c2, c3 from t1, t2 where condi3 = 3 or condi4 = 5 order by o1, o2 原SQL为select c1,c2,c3 from t1,t2,t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1,g2 解析后的SQL为 select c1, c2, c3 from t1, t2, t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1, g2 原SQL为Select c1,c2,c3 From t1,t2,t3 Where condi1 = 5 and condi6 = 6 or condi7 = 7 Group by g1,g2 解析后的SQL为 select c1, c2, c3 from t1, t2, t3 where condi1 = 5 and condi6 = 6 or condi7 = 7 group by g1, g2 原SQL为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 解析后的SQL为 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使用的类SqlParser，你可以拷贝下来使用之：
package com.sitinspring.common.sqlFormatter; import java.util.ArrayList; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * SQL语句解析器类 * @author : sitinspring(junglesong@gmail.com) * @date: 2008-3-12 */ public class SqlParser{ /** * 逗号 */ private static final String Comma = ","; /** * 四个空格 */ private static final String FourSpace = " "; /** * 是否单行显示字段，表，条件的标识量 */ private static boolean isSingleLine= true; /** * 待解析的SQL语句 */ private String sql; /** * SQL中选择的列 */ private String cols; /** * SQL中查找的表 */ private String tables; /** * 查找条件 */ private String conditions; /** * Group By的字段 */ private String groupCols; /** * Order by的字段 */ private String orderCols; /** * 构造函数 * 功能：传入构造函数，解析成字段，表，条件等 * @param sql：传入的SQL语句 */ public SqlParser(String sql){ this.sql=sql.trim(); parseCols(); parseTables(); parseConditions(); parseGroupCols(); parseOrderCols(); } /** * 解析选择的列 * */ private void parseCols(){ String regex="(select)(.+)(from)"; cols=getMatchedString(regex,sql); } /** * 解析选择的表 * */ private void parseTables(){ String regex=""; if(isContains(sql,"\\s+where\\s+")){ regex="(from)(.+)(where)"; } else{ regex="(from)(.+)($)"; } tables=getMatchedString(regex,sql); } /** * 解析查找条件 * */ private void parseConditions(){ String regex=""; if(isContains(sql,"\\s+where\\s+")){ // 包括Where，有条件 if(isContains(sql,"group\\s+by")){ // 条件在where和group by之间 regex="(where)(.+)(group\\s+by)"; } else if(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的字段 * */ private void parseGroupCols(){ String regex=""; if(isContains(sql,"group\\s+by")){ // 包括GroupBy，有分组字段 if(isContains(sql,"order\\s+by")){ // group by 后有order by regex="(group\\s+by)(.+)(order\\s+by)"; } else{ // group by 后无order by regex="(group\\s+by)(.+)($)"; } } else{ // 不包括GroupBy则分组字段无从谈起，返回即可 return; } groupCols=getMatchedString(regex,sql); } /** * 解析OrderBy的字段 * */ private void parseOrderCols(){ String regex=""; if(isContains(sql,"order\\s+by")){ // 包括GroupBy，有分组字段 regex="(order\\s+by)(.+)($)"; } else{ // 不包括GroupBy则分组字段无从谈起，返回即可 return; } orderCols=getMatchedString(regex,sql); } /** * 从文本text中找到regex首次匹配的字符串，不区分大小写 * @param regex： 正则表达式 * @param text：欲查找的字符串 * @return regex首次匹配的字符串，如未匹配返回空 */ private static String getMatchedString(String regex,String text){ Pattern pattern=Pattern.compile(regex,Pattern.CASE_INSENSITIVE); Matcher matcher=pattern.matcher(text); while(matcher.find()){ return matcher.group(2); } return null; } /** * 看word是否在lineText中存在，支持正则表达式 * @param lineText * @param word * @return */ private static boolean isContains(String lineText,String word){ Pattern pattern=Pattern.compile(word,Pattern.CASE_INSENSITIVE); Matcher matcher=pattern.matcher(lineText); return matcher.find(); } public String toString(){ // 无法解析则原样返回 if(cols== null && tables== null && conditions== null && groupCols== null && orderCols== null ){ return sql; } StringBuffer sb= new StringBuffer(); sb.append("原SQL为"+sql+"\n"); sb.append("解析后的SQL为\n"); for(String str:getParsedSqlList()){ sb.append(str); } sb.append("\n"); return sb.toString(); } /** * 在分隔符后加上回车 * @param str * @param splitStr * @return */ private static String getAddEnterStr(String str,String splitStr){ Pattern p = Pattern.compile(splitStr,Pattern.CASE_INSENSITIVE); // 用Pattern类的matcher()方法生成一个Matcher对象 Matcher m = p.matcher(str); StringBuffer sb = new StringBuffer(); // 使用find()方法查找第一个匹配的对象 boolean result = m.find(); // 使用循环找出模式匹配的内容替换之,再将内容加到sb里 while (result) { m.appendReplacement(sb, m.group(0) + "\n "); result = m.find(); } // 最后调用appendTail()方法将最后一次匹配后的剩余字符串加到sb里； m.appendTail(sb); return FourSpace+sb.toString(); } /** * 取得解析的SQL字符串列表 * @return */ public List<String> getParsedSqlList(){ List<String> sqlList= new ArrayList<String>(); // 无法解析则原样返回 if(cols== null && tables== null && conditions== null && groupCols== null && orderCols== null ){ sqlList.add(sql); return sqlList; } 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); } } return sqlList; } /** * 设置是否单行显示表，字段，条件等 * @param isSingleLine */ public static void setSingleLine( boolean isSingleLine) { SqlParser.isSingleLine = isSingleLine; } /** * 测试 * @param args */ public static void main(String[] args){ List<String> ls= new ArrayList<String>(); 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( new SqlParser(sql)); // System.out.println(sql); } } }转载于:https://www.cnblogs.com/pelephone/articles/sql-parse-java.html
