Sqlparser a class that uses regular expressions to parse single sentence SQL

Time:2020-11-27

Let’s first look at the sample SQL statement to be parsed

Copy codeThe code is as follows:
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

One of the parsing effects (issingleline = false)

Copy codeThe code is as follows:
The original SQL is select * from dual
The parsed SQL is
select
*
from
dual
The original SQL is select * fromdual
The parsed SQL is
select
*
from
dual
The original SQL is select C1, C2 from TB
The parsed SQL is
select
C1,c2
from
tb
The original SQL is select C1, C2 from TB
The parsed SQL is
select
c1,c2
from
tb
The original SQL is select count (*) from T1
The parsed SQL is
select
count(*)
from
t1
The original SQL is select C1, C2, C3 from T1 where conti1 = 1
The parsed SQL is
select
c1,c2,c3
from
t1
where
condi1=1
The original SQL is select C1, C2, C3 from T1 where conti1 = 1
The parsed SQL is
select
c1,c2,c3
from
t1
where
condi1=1
The original SQL is select C1, C2, C3 from T1, T2 where conti3 = 3 or conti4 = 5 order by O1, O2
The parsed SQL is
select
c1,c2,c3
from
t1,t2
where
condi3=3 or condi4=5
order by
o1,o2
The original SQL is select C1, C2, C3 from T1, T2 where conti3 = 3 or conti4 = 5 order by O1, O2
The parsed SQL is
select
c1,c2,c3
from
t1,t2
where
condi3=3 or condi4=5
order by
o1,o2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2
The parsed SQL is
select
c1,c2,c3
from
t1,t2,t3
where
condi1=5 and condi6=6 or condi7=7
group by
g1,g2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2
The parsed SQL is
select
c1,c2,c3
from
t1,t2,t3
where
condi1=5 and condi6=6 or condi7=7
group by
g1,g2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2, G3, order by G2, G3
The parsed SQL is
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

Analysis effect 2 (issingleline = true)

Copy codeThe code is as follows:
The original SQL is select * from dual
The parsed SQL is
select
*
from
dual
The original SQL is select * fromdual
The parsed SQL is
select
*
from
dual
The original SQL is select C1, C2 from TB
The parsed SQL is
select
C1,
c2
from
tb
The original SQL is select C1, C2 from TB
The parsed SQL is
select
c1,
c2
from
tb
The original SQL is select count (*) from T1
The parsed SQL is
select
count(*)
from
t1
The original SQL is select C1, C2, C3 from T1 where conti1 = 1
The parsed SQL is
select
c1,
c2,
c3
from
t1
where
condi1=1
The original SQL is select C1, C2, C3 from T1 where conti1 = 1
The parsed SQL is
select
c1,
c2,
c3
from
t1
where
condi1=1
The original SQL is select C1, C2, C3 from T1, T2 where conti3 = 3 or conti4 = 5 order by O1, O2
The parsed SQL is
select
c1,
c2,
c3
from
t1,
t2
where
condi3=3 or
condi4=5
order by
o1,
o2
The original SQL is select C1, C2, C3 from T1, T2 where conti3 = 3 or conti4 = 5 order by O1, O2
The parsed SQL is
select
c1,
c2,
c3
from
t1,
t2
where
condi3=3 or
condi4=5
order by
o1,
o2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2
The parsed SQL is
select
c1,
c2,
c3
from
t1,
t2,
t3
where
condi1=5 and
condi6=6 or
condi7=7
group by
g1,
g2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2
The parsed SQL is
select
c1,
c2,
c3
from
t1,
t2,
t3
where
condi1=5 and
condi6=6 or
condi7=7
group by
g1,
g2
The original SQL is select C1, C2, C3 from T1, T2, T3 where conti1 = 5 and conti6 = 6 or conti7 = 7 group by G1, G2, G3, order by G2, G3
The parsed SQL is
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

Use the sqlparser class, you can copy it and use it:

Copy codeThe code is as follows:
package com.sitinspring.common.sqlFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
*SQL statement parser class
* @author: sitinspring([email protected])
* @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 quantity of fields, tables and conditions in a single row
*/
private static boolean isSingleLine=true;
/**
*SQL statement to be parsed
*/
private String sql;
/**
*Selected columns in SQL
*/
private String cols;
/**
*Tables found in SQL
*/
private String tables;
/**
*Search criteria
*/
private String conditions;
/**
*Field for group by
*/
private String groupCols;
/**
*Field for order by
*/
private String orderCols;
/**
*Constructor
*Function: pass in constructor, parse to field, table, condition, etc
*@ param SQL: the SQL statement passed in
*/
public SqlParser(String sql){
this.sql=sql.trim();
parseCols();
parseTables();
parseConditions();
parseGroupCols();
parseOrderCols();
}
/**
*Resolve selected columns
*
*/
private void parseCols(){
String regex=”(select)(.+)(from)”;
cols=getMatchedString(regex,sql);
}
/**
*Resolve selected table
*
*/
private void parseTables(){
String regex=””;
if(isContains(sql,”\\s+where\\s+”)){
regex=”(from)(.+)(where)”;
}
else{
regex=”(from)(.+)($)”;
}
tables=getMatchedString(regex,sql);
}
/**
*Resolve search criteria
*
*/
private void parseConditions(){
String regex=””;
if(isContains(sql,”\\s+where\\s+”)){
//Including where, conditional
if(isContains(sql,”group\\s+by”)){
//The condition is 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 from where to the end of the string
regex=”(where)(.+)($)”;
}
}
else{
//If you don’t include where, you can’t talk about the condition, just return
return;
}
conditions=getMatchedString(regex,sql);
}
/**
*Resolving fields of groupby
*
*/
private void parseGroupCols(){
String regex=””;
if(isContains(sql,”group\\s+by”)){
//Including groupby with grouping fields
if(isContains(sql,”order\\s+by”)){
//Group by followed by order by
regex=”(group\\s+by)(.+)(order\\s+by)”;
}
else{
//No order by after group by
regex=”(group\\s+by)(.+)($)”;
}
}
else{
//If groupby is not included, the grouping field is out of the question and can be returned
return;
}
groupCols=getMatchedString(regex,sql);
}
/**
*Parsing the fields of orderby
*
*/
private void parseOrderCols(){
String regex=””;
if(isContains(sql,”order\\s+by”)){
//Including groupby with grouping fields
regex=”(order\\s+by)(.+)($)”;
}
else{
//If groupby is not included, the grouping field is out of the question and can be returned
return;
}
orderCols=getMatchedString(regex,sql);
}
/**
*Find the first matching string of regex from text text, case insensitive
*@ param regex: regular expression
*@ param text: string to find
*The string that @ return regex matches for the first time. If not, it returns null
*/
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;
}
/**
*Check whether word exists in linetext and support regular expression
* @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 it cannot be resolved, it will be returned as is
if(cols==null && tables==null && conditions==null && groupCols==null && orderCols==null ){
return sql;
}
StringBuffer sb=new StringBuffer();
sb.append (“the original SQL is” + SQL + “”);
sb.append (“the parsed SQL is” ‘);
for(String str:getParsedSqlList()){
sb.append(str);
}
sb.append(“\n”);
return sb.toString();
}
/**
*Add carriage return after the separator
* @param str
* @param splitStr
* @return
*/
private static String getAddEnterStr(String str,String splitStr){
Pattern p = Pattern.compile(splitStr,Pattern.CASE_INSENSITIVE);
//Use the matcher () method of the pattern class to generate a matcher object
Matcher m = p.matcher(str);
StringBuffer sb = new StringBuffer();
//Use the find() method to find the first matching object
boolean result = m.find();
//Use the loop to find the pattern matching content, replace it, and add the content to sb
while (result) {
m.appendReplacement(sb, m.group(0) + “\n “);
result = m.find();
}
/ / finally, call the appendTail () method to add the remaining string after the last match to sb.
m.appendTail(sb);
return FourSpace+sb.toString();
}
/**
*Gets the list of parsed SQL strings
* @return
*/
public List<String> getParsedSqlList(){
List<String> sqlList=new ArrayList<String>();
//If it cannot be resolved, it will be returned as is
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;
}
/**
*Set whether to display single row table, field, condition, etc
* @param isSingleLine
*/
public static void setSingleLine(boolean isSingleLine) {
SqlParser.isSingleLine = isSingleLine;
}
/**
*Testing
* @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);
}
}
}