我要评论复制代码 代码如下:
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);
}
}
}
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论