SqlParser 一个利用正则表达式解析单句SQL的类

125 阅读 0 评论 83 点赞

我是靠谱客的博主负责高跟鞋，最近开发中收集的这篇文章主要介绍SqlParser 一个利用正则表达式解析单句SQL的类，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

先看要解析的样例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); 
} 
} 
} 