這應該是最后一次用字符串去分析解讀SQL了，效果比上次要好些，但在函數和別名方面有點差強人意，再想前進一定要用到詞法分析，語法樹和表達式解析等，字符串解讀差不多已經到極限了。
 package com.sitinspring.common.sqlformatter2;
  /** *//**
 * SQL整形器類
* @author 何楊（heyang78@gmail.com）
*
* @since 2009-2-5 上午11:45:38
* @version 1.00
 */
class Fomatter {
// 需要整形的SQL語句
private String sql;
// 整形完畢的SQL語句
private String formatedSql;
// 空格的深度,用于嵌套Sql語句
private int spaceDepth;
// 在sql逐字讀取時當前字符所在的位置
private int currCharPos;
  /** *//**
* 構造函數，入口點
* 2009年2月5日11:52:42
* @param sql
 */
public Fomatter(String sql){
this.sql=sql;
format();
}
/** *//**
* 取得整形完畢的Sql語句，出口點
* 2009年2月5日11:52:45
* @return
*/
public String getFomattedSql(){
return formatedSql;
}
/** *//**
* 對sql進行整形，整形的結果放在FormatedSql中
* 2009年2月5日12:16:08
*/
private void format(){
formatedSql="";
Token token=getToken();
while(token!=null){
formatedSql+=token;
token=getToken();
}
}
/** *//**
* 取得下一個標識符
* 2009年2月5日12:22:41
* @return
*/
private Token getToken(){
// 初始化
String tokenText="";
//tokenType=TokenType_None;
// 結束即終止
if(currCharPos==sql.length()){
return null;
}
// 跳過空白字符
while(currCharPos<sql.length() && currCharIsWhiteSpace(currCharPos)){
currCharPos++;
}
// 結束即終止
if(currCharPos==sql.length()){
return null;
}
// 不是空白字符的話就連續往下取
while(currCharPos<sql.length() ){
char currChar=sql.charAt(currCharPos);
if(currChar=='('){
if(tokenText.trim().length()>0){
break;
}
Token token=new Token(currChar,spaceDepth);
spaceDepth++;
currCharPos++;
return token;
}
else if(currChar==')'){
if(tokenText.trim().length()>0){
break;
}
spaceDepth--;
Token token=new Token(currChar,spaceDepth);
currCharPos++;
return token;
}
else if(currChar==','){
tokenText+=currChar;
currCharPos++;
break;
}
if(currCharIsWhiteSpace(currCharPos)==true){
break;
}
tokenText+=currChar;
currCharPos++;
}
Token token=new Token(tokenText,spaceDepth);
return token;
}
/** *//**
* 判斷currentCharIndex在originalSql指向的字符是否空白字符
* @param currentCharIndex
* @return
*/
private boolean currCharIsWhiteSpace(int currentCharIndex){
return Character.isWhitespace(sql.charAt(currentCharIndex));
}
}package com.sitinspring.common.sqlformatter2;
import java.util.HashSet;
import java.util.Set;
/** *//**
* Token代表SQL語句中一個不可再分割的子單元
* @author 何楊（heyang78@gmail.com）
*
* @since 2009-2-5 上午11:46:35
* @version 1.00
*/
class Token{
// Token的類型
private int type;
// 表示這個Token是關鍵字
public static final int Type_Keyword=0;
public static final int Type_LeftKeyword=2;
public static final int Type_RightKeyword=4;
// 表示這個Token是分隔符
public static final int Type_Seperate=6;
// 表示這個Token是表達式
public static final int Type_Expression=8;
// 表示這個Token是左括號
public static final int Type_LeftBraket=10;
// 表示這個Token是右括號
public static final int Type_RightBraket=12;
// Token的文本內容
private String text;
// 這個標記所處的深度
private int depth;
private static final Set<String> keywords;
private static final Set<String> leftKeywords;
private static final Set<String> rightKeywords;
static{
keywords=new HashSet<String>();
keywords.add("select");
keywords.add("from");
keywords.add("where");
keywords.add("on");
keywords.add("having");
keywords.add("values");
keywords.add("update");
keywords.add("set");
leftKeywords=new HashSet<String>();
leftKeywords.add("order");
leftKeywords.add("group");
leftKeywords.add("delete");
leftKeywords.add("insert");
leftKeywords.add("left");
leftKeywords.add("right");
leftKeywords.add("inner");
rightKeywords=new HashSet<String>();
rightKeywords.add("by");
rightKeywords.add("into");
rightKeywords.add("join");
}
private static boolean isKeyWords(String str){
return keywords.contains(str);
}
private static boolean isLeftKeyWords(String str){
return leftKeywords.contains(str);
}
private static boolean isRightKeyWords(String str){
return rightKeywords.contains(str);
}
public Token(String text,int depth){
if("(".equals(text)){
this.type=Type_LeftBraket;
}
else if(")".equals(text)){
this.type=Type_RightBraket;
}
else if(isKeyWords(text.toLowerCase())){
this.type=Type_Keyword;
}
else if(isLeftKeyWords(text.toLowerCase())){
this.type=Type_LeftKeyword;
}
else if(isRightKeyWords(text.toLowerCase())){
this.type=Type_RightKeyword;
}
else{
this.type=Type_Expression;
}
this.text=text;
this.depth=depth;
}
public Token(char c,int depth){
this(String.valueOf(c),depth);
}
public String toString(){
StringBuffer sb=new StringBuffer();
sb.append(getDepthSpace());
sb.append(getPreTokenSpace());
sb.append(text);
sb.append(getAfterTokenWhiteSpace());
return sb.toString();
}
/** *//**
* 取得代表深度的前置空白
* @return
*/
private String getDepthSpace(){
StringBuffer sb=new StringBuffer("");
for(int i=0;i<depth;i++){
sb.append(Consts.FourSpace);
}
return sb.toString();
}
/** *//**
* 取得標識符前的空白
* @return
*/
private String getPreTokenSpace(){
if(type==Type_Expression){
return Consts.FourSpace;
}
else if(type==Type_LeftBraket){
return Consts.FourSpace;
}
else if(type==Type_RightBraket){
return Consts.FourSpace;
}
else if(type==Type_RightKeyword){
return Consts.Space;
}
return "";
}
/** *//**
* 取得標識符后的白字符
* @return
*/
private String getAfterTokenWhiteSpace(){
if(type==Type_LeftKeyword){
return "";
}
return Consts.NewLine;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public int getDepth() {
return depth;
}
public void setDepth(int depth) {
this.depth = depth;
}
}
package com.sitinspring.common.sqlformatter2;
/** *//**
* 本包諸類用到的一些常量放在此類中
* @author 何楊（heyang78@gmail.com）
*
* @since 2009-2-5 下午12:02:26
* @version 1.00
*/
class Consts{
// 新行
public final static String NewLine="\n";
// 四個空格
public final static String FourSpace=" ";
// 空格
public final static String Space=" ";
// 逗號
public final static String Comma=",";
}
package com.sitinspring.common.sqlformatter2;
import java.util.ArrayList;
import java.util.List;
/** *//**
* SQL語句整形工具類
* @author 何楊（heyang78@gmail.com）
*
* @since 2009-2-5 上午11:42:03
* @version 1.00
*/
public class sqlformatter2Util{
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 f1,(select f2 from t01) from t02 where 1=1");
ls.add("select f1,( select a from b ) from ( select f1,f2 from ( select f1,f2,f3 from tb ) ),t4 where 1=1 ");
ls.add("select f1,( select * from tb2,( select * from ( select * from ( select * from tb5 ) ) ) ) from tabl1 where 1=1");
ls.add("");
ls.add("Select c1 1,c2,c3 from t1 3,t2 4 Where condi3=3 and 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");
ls.add("select c1,c2,c3 from t1 left join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("select c1,c2,c3 from t1 right join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("select c1,c2,c3 from t1 inner join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("select c1,c2,c3 from t1 left join t2 having condi3=3 or condi4=5 group by g1,g3,g5 order by o1,o2");
ls.add("delete from table");
ls.add("delete from table where 1=1");
ls.add("delete from table where c1=1 and c2=2 or c3=3");
ls.add("update checktable set ID='' where 1=1 ");
ls.add("update checktable set ID='', NAME='' where 1=1 and 2=2");
ls.add("update checktable set ID='', NAME='', count='', remark='' where 1=1 and 2=2 or 3=3 ");
ls.add(" insert into checktable ( ID ) values ( '1' ) ");
ls.add(" insert into checktable ( ID,r ) values ( '1','' ) ");
ls.add(" insert into checktable ( ID, NAME, count, remark ) values ( '1', '2', '3', '4' ) ");
ls.add("insert into checktable select c1,c2,c3 from t1 where condi1=1 ");
ls.add("insert into checktable Select c1,c2,c3 From t1 Where condi1=1 ");
ls.add("insert into checktable select c1,c2,c3 from t1,t2 where condi3=3 or condi4=5 order by o1,o2");
ls.add("insert into checktable Select c1 1,c2,c3 from t1 3,t2 4 Where condi3=3 and condi4=5 Order by o1,o2");
ls.add("insert into checktable select c1,c2,c3 from t1,t2, t3 where condi1=5 and condi6=6 or condi7=7 group by g1,g2");
ls.add("insert into checktable Select c1,c2,c3 From t1,t2,t3 Where condi1=5 and condi6=6 or condi7=7 Group by g1,g2");
ls.add("insert into checktable 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");
ls.add("insert into checktable select c1,c2,c3 from t1 left join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("insert into checktable select c1,c2,c3 from t1 right join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("insert into checktable select c1,c2,c3 from t1 inner join t2 on condi3=3 or condi4=5 order by o1,o2");
ls.add("insert into checktable select c1,c2,c3 from t1 left join t2 having condi3=3 or condi4=5 group by g1,g3,g5 order by o1,o2");
ls.add("select (select * from dual) from dual");
ls.add("select (*) from dual");
ls.add("select count(*) from dual");
ls.add("select id,name from (select id,name from (select id,name from customer) t1 ) t2");
for(String sql:ls){
System.out.println("原始的Sql為:\n"+(sql));
System.out.println("解析后的的Sql為:\n"+getFormatedSql(sql));
}
}
/** *//**
* 取得整形完畢的Sql語句
* Entry Point：這個包的入口點
* @param sql
* @return
*/
public static String getFormatedSql(String sql){
// 去除前后空白
sql=sql.trim();
// 將Sql語句中原有的回車換行替換成空白
sql=sql.replaceAll("(\\n+|\\r+)", " ");
return (new Fomatter(sql).getFomattedSql());
}
}
整形完的效果請見：
http://www.tkk7.com/heyang/archive/2009/02/05/253411.html
|