本文介绍了一个Java类SqlParser，用于使用正则表达式解析和美化SQL语句。该类可以将多行SQL转换为标准格式，支持单行和多行显示模式，适用于SQL的格式化和整理。示例展示了不同类型的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
+
"
"
);
sb.append(
"
解析后的SQL为
"
);
for
(String str:getParsedSqlList()){
sb.append(str);
}
sb.append(
"
"
);
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
)
+
"
"
);
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
"
);
if
(isSingleLine){
sqlList.add(getAddEnterStr(cols,Comma));
}
else
{
sqlList.add(FourSpace
+
cols);
}
}
if
(tables
!=
null
){
sqlList.add(
"
from
"
);
if
(isSingleLine){
sqlList.add(getAddEnterStr(tables,Comma));
}
else
{
sqlList.add(FourSpace
+
tables);
}
}
if
(conditions
!=
null
){
sqlList.add(
"
where
"
);
if
(isSingleLine){
sqlList.add(getAddEnterStr(conditions,
"
(and|or)
"
));
}
else
{
sqlList.add(FourSpace
+
conditions);
}
}
if
(groupCols
!=
null
){
sqlList.add(
"
group by
"
);
if
(isSingleLine){
sqlList.add(getAddEnterStr(groupCols,Comma));
}
else
{
sqlList.add(FourSpace
+
groupCols);
}
}
if
(orderCols
!=
null
){
sqlList.add(
"
order by
"
);
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)
注：以上文章由网友0bW6gZtg分享，仅用学习使用，不代表本站的观点，如有侵犯你的版权或违法信息，请联系站长删除。
扫一扫
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
