使用正则表达式解析SQL语句

来源：互联网发布：数据质量自查报告编辑：程序博客网时间：2024/04/27 02:19

代码：

本文乃原创，转载请注明出处。

先看要解析的样例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中选择的列
     */

使用正则表达式解析SQL语句

使用正则表达式解析语句

使用正则表达式解析一般sql语句（C++）