First look at the sample SQL statement to parse:
Copy Code code 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
One of the parsing effects (Issingleline=false):
Copy Code code as follows:
Original SQL is select * from dual
The parsed SQL is
Select
*
From
Dual
Original SQL is select * from dual
The parsed SQL is
Select
*
From
Dual
Original SQL is select C1,C2 from TB
The parsed SQL is
Select
C1,c2
From
Tb
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 condi1=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 condi1=1
The parsed SQL is
Select
C1,c2,c3
From
T1
where
Condi1=1
The original SQL is a select c1,c2,c3 from t1,t2 where condi3=3 or condi4=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 a select c1,c2,c3 from T1,t2 Where condi3=3 or condi4=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 condi1=5 and condi6=6 or condi7=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 condi1=5 and condi6=6 or condi7=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 condi1=5 and condi6=6 or condi7=7 Group by g1,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
Parse effect bis (issingleline=true):
Copy Code code as follows:
Original SQL is select * from dual
The parsed SQL is
Select
*
From
Dual
Original SQL is select * from dual
The parsed SQL is
Select
*
From
Dual
Original SQL is select C1,C2 from TB
The parsed SQL is
Select
C1,
C2
From
Tb
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 condi1=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 condi1=1
The parsed SQL is
Select
C1,
C2,
C3
From
T1
where
Condi1=1
The original SQL is a select c1,c2,c3 from t1,t2 where condi3=3 or condi4=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 a select c1,c2,c3 from T1,t2 Where condi3=3 or condi4=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 condi1=5 and condi6=6 or condi7=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 condi1=5 and condi6=6 or condi7=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 condi1=5 and condi6=6 or condi7=7 Group by g1,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
Using the class Sqlparser, you can copy it down and use it:
Copy Code code 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 (junglesong@gmail.com)
* @date: 2008-3-12
*/
public class sqlparser{
/**
* Comma
*/
private static final String Comma = ",";
/**
* Four spaces
*/
private static final String fourspace = "";
/**
* Whether to display the field, table, the label of the condition
*/
private static Boolean issingleline=true;
/**
* Pending resolution of SQL statements
*/
private String SQL;
/**
* Selected Columns in SQL
*/
Private String cols;
/**
* Tables found in SQL
*/
private String tables;
/**
* Find conditions
*/
private String conditions;
/**
* Group by field
*/
Private String Groupcols;
/**
* Field by
*/
Private String Ordercols;
/**
* Constructor
* Function: Incoming constructor, parse into field, table, condition, etc.
* @param sql: Incoming SQL statements
*/
Public sqlparser (String sql) {
This.sql=sql.trim ();
Parsecols ();
Parsetables ();
Parseconditions ();
Parsegroupcols ();
Parseordercols ();
}
/**
* Parse the selected columns
*
*/
private void Parsecols () {
String regex= "(select) (. +) (from);
Cols=getmatchedstring (Regex,sql);
}
/**
* Parse the selected table
*
*/
private void Parsetables () {
String regex= "";
if (Iscontains (SQL, "\\s+where\\s+")) {
Regex= "(from) (. +) (where)";
}
else{
Regex= "(from) (. +) ($)";
}
Tables=getmatchedstring (Regex,sql);
}
/**
* Resolution Lookup conditions
*
*/
private void Parseconditions () {
String regex= "";
if (Iscontains (SQL, "\\s+where\\s+")) {
Include where, conditional
if (Iscontains (SQL, "Group\\s+by")) {
Condition between where and group by
Regex= "(where) (. +) (group\\s+by)";
}
else if (iscontains (SQL, "Order\\s+by")) {
condition between where and order by
Regex= "(where) (. +) (order\\s+by)";
}
else{
The condition is where to the end of the string
Regex= "(where) (. +) ($);
}
}
else{
Does not include where the condition cannot be discussed, return can
Return
}
Conditions=getmatchedstring (Regex,sql);
}
/**
* Parse the GroupBy field
*
*/
private void Parsegroupcols () {
String regex= "";
if (Iscontains (SQL, "Group\\s+by")) {
Include GroupBy, with grouped fields
if (Iscontains (SQL, "Order\\s+by")) {
Group BY IS followed by
Regex= "(Group\\s+by) (. +) (order\\s+by)";
}
else{
Group BY without ORDER by
Regex= "(Group\\s+by) (. +) ($)";
}
}
else{
Do not include GroupBy the Group field is not available, you can return
Return
}
Groupcols=getmatchedstring (Regex,sql);
}
/**
* Parse the field by
*
*/
private void Parseordercols () {
String regex= "";
if (Iscontains (SQL, "Order\\s+by")) {
Include GroupBy, with grouped fields
Regex= "(Order\\s+by) (. +) ($)";
}
else{
Do not include GroupBy the Group field is not available, you can return
Return
}
Ordercols=getmatchedstring (Regex,sql);
}
/**
* To find the first matching regex string from text texts, case-insensitive
* @param regex: Regular expression
* @param text: string to find
* @return The first time a regex matches a string, such as an unmatched return 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;
}
/**
* See if Word exists in Linetext, support regular expressions
* @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 () {
Cannot resolve then return as-is
if (cols==null && tables==null && conditions==null && groupcols==null && ordercols== NULL) {
return SQL;
}
StringBuffer sb=new StringBuffer ();
Sb.append ("Original SQL" +sql+ "\ n");
Sb.append ("parsed SQL is \ n");
For (String str:getparsedsqllist ()) {
Sb.append (str);
}
Sb.append ("\ n");
return sb.tostring ();
}
/**
* Add a 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);
Generates a Matcher object using the Matcher () method of the Pattern class
Matcher m = p.matcher (str);
StringBuffer sb = new StringBuffer ();
Finds the first matching object using the Find () method
Boolean result = M.find ();
Use loops to find patterns to match the contents of the replacement, and then add the content to SB
while (result) {
M.appendreplacement (SB, M.group (0) + "\ n");
result = M.find ();
}
Finally, the Appendtail () method is invoked to add the remaining string after the last match to SB;
M.appendtail (SB);
return fourspace+sb.tostring ();
}
/**
* Get a list of parsed SQL strings
* @return
*/
Public list<string> getparsedsqllist () {
List<string> sqllist=new arraylist<string> ();
Cannot resolve then return 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 a single display table, fields, conditions, etc.
* @param issingleline
*/
public static void Setsingleline (Boolean issingleline) {
Sqlparser.issingleline = Issingleline;
}
/**
* Test
* @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");
for (String Sql:ls) {
System.out.println (new Sqlparser (SQL));
SYSTEM.OUT.PRINTLN (SQL);
}
}
}
