Question

怎么用正则表达式解析sql语句

Answer 1

先看要解析的样例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 wher www.hnnedu.com e 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();

Answer 2

import java.util.*;import java.text.*;
class Testi { public static void main(String[] args) { String span="select aaaa.id name ,hello ,type t,h from datas aaaa,city b where a.id=b.id and c like 'e%' and name is null"; 
span=span.toUpperCase();//测试用sql语句 System.out.println(span); 
String column="(<a href="file://w+//s*(//w+//s*)%7B0,1%7D)" ;="" "="" 
style="text-decoration: none; color: rgb(49, 139, 146);">\\w+\\s*(\\w+\\s*){0,1})";//一列的正则表达式 
product p String columns=column+"(,\\s*"+column+")*"; //多列正则表达式 匹配如 product p,category c,warehouse w String ownerenable="((<a href="file://w+//.)%7B0,1%7D//w+//s*(//w+//s*)%7B0,1%7D)" ;="" "="" style="text-decoration: none; color: rgb(49, 139, 146);">\\w+\\.){0,1}\\w+\\s*(\\w+\\s*){0,1})";//一列的正则表达式
a.product p String ownerenables=ownerenable+"(,\\s*"+ownerenable+")*";//多列正则表达式 匹配如 a.product p,a.category c,b.warehouse w String from="FROM\\s+"+columns; String condition="(\\w+\\.){0,1}\\w+\\s*(=|LIKE|IS)\\s*'?(\\w+\\.){0,1}[\\w%]+'?";//条件的正则表达式
a=b 或 a is b.. String conditions=condition+"(<a href="file://s+%28and%7Cor%29//s*" +condition+"="" s*)*";="" "="" style="text-decoration: none; color: rgb(49, 139, 146);">\\s+(AND|OR)\\s*"+condition+"\\s*)*";//多个条件
a=b and c like 'r%' or d is null String where="(WHERE\\s+"+conditions+"){0,1}"; String pattern="SELECT\\s+(\\*|"+ownerenables+"\\s+"+from+")\\s+"+where+"\\s*"; //匹配最终sql的正则表达式 System.out.println(pattern);//输出正则表达式// System.out.println(span.matches(pattern));//是否比配 System.out.println(" a='b' and c='1'".matches(conditions));//是否比配}}