package ;
import ;
import ;
import ;
import ;
/**
* SQL statement parser class
* @author: sitinspring(junglesong@)
* @date: 2008-3-12
*/
public class SqlParser{
/**
* Comma
*/
private static final String Comma = ",";
/**
* Four spaces
*/
private static final String FourSpace = " ";
/**
* Whether to display the identification amount of fields, tables, and conditions in a single row
*/
private static boolean isSingleLine=true;
/**
* SQL statement to be parsed
*/
private String sql;
/**
* Columns selected in SQL
*/
private String cols;
/**
* Tables found in SQL
*/
private String tables;
/**
* Find criteria
*/
private String conditions;
/**
* Group By field
*/
private String groupCols;
/**
* Order by field
*/
private String orderCols;
/**
* Constructor
* Function: Pass in constructor, parse into fields, tables, conditions, etc.
* @param sql: Incoming SQL statement
*/
public SqlParser(String sql){
=();
parseCols();
parseTables();
parseConditions();
parseGroupCols();
parseOrderCols();
}
/**
* parse selected columns
*
*/
private void parseCols(){
String regex="(select)(.+)(from)";
cols=getMatchedString(regex,sql);
}
/**
* parse selected tables
*
*/
private void parseTables(){
String regex="";
if(isContains(sql,"\\s+where\\s+")){
regex="(from)(.+)(where)";
}
else{
regex="(from)(.+)($)";
}
tables=getMatchedString(regex,sql);
}
/**
* parse the search criteria
*
*/
private void parseConditions(){
String regex="";
if(isContains(sql,"\\s+where\\s+")){
// Including Where, conditions
if(isContains(sql,"group\\s+by")){
// The conditions are between where and group by
regex="(where)(.+)(group\\s+by)";
}
else if(isContains(sql,"order\\s+by")){
// The condition is between where and order by
regex="(where)(.+)(order\\s+by)";
}
else{
// The condition is at where to end of the string
regex="(where)(.+)($)";
}
}
else{
// If there is no need to discuss the conditions, just return
return;
}
conditions=getMatchedString(regex,sql);
}
/**
* parse the fields of GroupBy
*
*/
private void parseGroupCols(){
String regex="";
if(isContains(sql,"group\\s+by")){
// Including GroupBy, with grouped fields
if(isContains(sql,"order\\s+by")){
// group by
regex="(group\\s+by)(.+)(order\\s+by)";
}
else{
// group by no order by
regex="(group\\s+by)(.+)($)";
}
}
else{
// If GroupBy is not included, there is no way to talk about grouping fields, just return
return;
}
groupCols=getMatchedString(regex,sql);
}
/**
* Parsing OrderBy fields
*
*/
private void parseOrderCols(){
String regex="";
if(isContains(sql,"order\\s+by")){
// Including GroupBy, with grouped fields
regex="(order\\s+by)(.+)($)";
}
else{
// If GroupBy is not included, there is no way to talk about grouping fields, just return
return;
}
orderCols=getMatchedString(regex,sql);
}
/**
* Find the string that regex matches for the first time from the text text, case-insensitive
* @param regex: Regular expression
* @param text: string to be found
* @return regex first matched string, if not matched, return empty
*/
private static String getMatchedString(String regex,String text){
Pattern pattern=(regex,Pattern.CASE_INSENSITIVE);
Matcher matcher=(text);
while(()){
return (2);
}
return null;
}
/**
* Check whether word exists in lineText and supports regular expressions
* @param lineText
* @param word
* @return
*/
private static boolean isContains(String lineText,String word){
Pattern pattern=(word,Pattern.CASE_INSENSITIVE);
Matcher matcher=(lineText);
return ();
}
public String toString(){
// If it cannot be parsed, return as it is
if(cols==null && tables==null && conditions==null && groupCols==null && orderCols==null ){
return sql;
}
StringBuffer sb=new StringBuffer();
("The original SQL is "+sql+"\n");
("The parsed SQL is \n");
for(String str:getParsedSqlList()){
(str);
}
("\n");
return ();
}
/**
* Add a carriage return after the delimiter
* @param str
* @param splitStr
* @return
*/
private static String getAddEnterStr(String str,String splitStr){
Pattern p = (splitStr,Pattern.CASE_INSENSITIVE);
// Use the matcher() method of the Pattern class to generate a Matcher object
Matcher m = (str);
StringBuffer sb = new StringBuffer();
// Use the find() method to find the first matching object
boolean result = ();
// Use loop to find the pattern matching content to replace it, and then add the content to sb
while (result) {
(sb, (0) + "\n ");
result = ();
}
// Finally, call the appendTail() method to add the remaining string after the last match to sb;
(sb);
return FourSpace+();
}
/**
* Get the parsed SQL string list
* @return
*/
public List<String> getParsedSqlList(){
List<String> sqlList=new ArrayList<String>();
// If it cannot be parsed, return as it is
if(cols==null && tables==null && conditions==null && groupCols==null && orderCols==null ){
(sql);
return sqlList;
}
if(cols!=null){
("select\n");
if(isSingleLine){
(getAddEnterStr(cols,Comma));
}
else{
(FourSpace+cols);
}
}
if(tables!=null){
(" \nfrom\n");
if(isSingleLine){
(getAddEnterStr(tables,Comma));
}
else{
(FourSpace+tables);
}
}
if(conditions!=null){
(" \nwhere\n");
if(isSingleLine){
(getAddEnterStr(conditions,"(and|or)"));
}
else{
(FourSpace+conditions);
}
}
if(groupCols!=null){
(" \ngroup by\n");
if(isSingleLine){
(getAddEnterStr(groupCols,Comma));
}
else{
(FourSpace+groupCols);
}
}
if(orderCols!=null){
(" \norder by\n");
if(isSingleLine){
(getAddEnterStr(orderCols,Comma));
}
else{
(FourSpace+orderCols);
}
}
return sqlList;
}
/**
* Set whether to display tables, fields, conditions, etc. in a single row
* @param isSingleLine
*/
public static void setSingleLine(boolean isSingleLine) {
= isSingleLine;
}
/**
* test
* @param args
*/
public static void main(String[] args){
List<String> ls=new ArrayList<String>();
("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");
for(String sql:ls){
(new SqlParser(sql));
//(sql);
}
}
}