SqlParser: a class that uses regular expressions to parse a single SQL statement.

First, let's 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

Resolution effect (isSingleLine = false ):Copy codeThe Code is as follows: the original SQL statement is select * from dual
The parsed SQL is
Select
*
From
Dual
The original SQL statement is SELECT * frOm dual
The parsed SQL is
Select
*
From
Dual
The original SQL statement is Select C1, c2 From tb
The parsed SQL is
Select
C1, c2
From
Tb
The original SQL statement is select c1, c2 from tb
The parsed SQL is
Select
C1, c2
From
Tb
The original SQL statement is select count (*) from t1
The parsed SQL is
Select
Count (*)
From
T1
The original SQL statement 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 statement 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 statement is 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
O1, o2
The original SQL statement is 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
O1, o2
The original SQL statement 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
G1, g2
The original SQL statement 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
G1, g2
The original SQL statement 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
The parsed SQL is
Select
C1, c2, c3
From
T1, t2, t3
Where
Condi1 = 5 and condi6 = 6 or condi7 = 7
Group
G1, g2, g3
Order
G2, g3

Resolution result 2 (isSingleLine = true ):Copy codeThe Code is as follows: the original SQL statement is select * from dual
The parsed SQL is
Select
*
From
Dual
The original SQL statement is SELECT * frOm dual
The parsed SQL is
Select
*
From
Dual
The original SQL statement is Select C1, c2 From tb
The parsed SQL is
Select
C1,
C2
From
Tb
The original SQL statement is select c1, c2 from tb
The parsed SQL is
Select
C1,
C2
From
Tb
The original SQL statement is select count (*) from t1
The parsed SQL is
Select
Count (*)
From
T1
The original SQL statement 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 statement 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 statement is 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
O1,
O2
The original SQL statement is 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
O1,
O2
The original SQL statement 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
G1,
G2
The original SQL statement 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
G1,
G2
The original SQL statement 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
The parsed SQL is
Select
C1,
C2,
C3
From
T1,
T2,
T3
Where
Condi1 = 5 and
Condi6 = 6 or
Condi7 = 7
Group
G1,
G2,
G3
Order
G2,
G3

You can copy the SqlParser class to 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 (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, and condition identification quantity in a single row
*/
Private static boolean isSingleLine = true;
/**
* SQL statements to be parsed
*/
Private String SQL;
/**
* Columns selected in SQL
*/
Private String cols;
/**
* Tables searched in SQL
*/
Private String tables;
/**
* Search criteria
*/
Private String conditions;
/**
* Group By field
*/
Private String groupCols;
/**
* Order by field
*/
Private String orderCols;
/**
* Constructor
* Function: Imports constructor and parses it into fields, tables, and conditions.
* @ Param SQL: the input SQL statement
*/
Public SqlParser (String SQL ){
This. SQL = SQL. trim ();
ParseCols ();
ParseTables ();
ParseConditions ();
ParseGroupCols ();
ParseOrderCols ();
}
/**
* Parse 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 );
}
/**
* Parse search conditions
*
*/
Private void parseConditions (){
String regex = "";
If (isContains (SQL, "\ s + where \ s + ")){
// Including Where, conditional
If (isContains (SQL, "group \ s + ")){
// The condition is between where and group.
Regex = "(where) (. +) (group \ s + )";
}
Else if (isContains (SQL, "order \ s + ")){
// The condition is between where and order.
Regex = "(where) (. +) (order \ s + )";
}
Else {
// The condition is from where to the end of the string
Regex = "(where) (. +) ($ )";
}
}
Else {
// If the where clause is not included, the condition cannot be mentioned. Return.
Return;
}
Conditions = getMatchedString (regex, SQL );
}
/**
* Parse the GroupBy Field
*
*/
Private void parseGroupCols (){
String regex = "";
If (isContains (SQL, "group \ s + ")){
// Includes GroupBy and group Fields
If (isContains (SQL, "order \ s + ")){
// Order by after group
Regex = "(group \ s + by) (. +) (order \ s + )";
}
Else {
// No order by after group
Regex = "(group \ s + by) (. +) ($ )";
}
}
Else {
// If GroupBy is not included, the group field cannot be mentioned. You can return the group field.
Return;
}
GroupCols = getMatchedString (regex, SQL );
}
/**
* Parse the OrderBy Field
*
*/
Private void parseOrderCols (){
String regex = "";
If (isContains (SQL, "order \ s + ")){
// Includes GroupBy and group Fields
Regex = "(order \ s + by) (. +) ($ )";
}
Else {
// If GroupBy is not included, the group field cannot be mentioned. You can return the group field.
Return;
}
OrderCols = getMatchedString (regex, SQL );
}
/**
* Find the first matching string of regex in text, which is case insensitive.
* @ Param regex: Regular Expression
* @ Param text: string to be searched
* @ Return regex indicates the string that is matched for the first time. If the string is not matched, null is returned.
*/
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. Regular Expressions are supported.
* @ 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 (){
// Returns the value as is if the parsing fails.
If (cols = null & tables = null & conditions = null & groupCols = null & orderCols = null ){
Return SQL;
}
StringBuffer sb = new StringBuffer ();
Sb. append ("the original SQL is" + SQL + "\ n ");
Sb. append ("the 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 );
// 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 matched object
Boolean result = m. find ();
// Replace the content that matches the pattern in a loop and add the content to the sb.
While (result ){
M. appendReplacement (sb, m. group (0) + "\ n ");
Result = m. find ();
}
// Call the appendTail () method to add the remaining strings after the last match to sb;
M. appendTail (sb );
Return FourSpace + sb. toString ();
}
/**
* Retrieve the parsed SQL string list
* @ Return
*/
Public List <String> getParsedSqlList (){
List <String> sqlList = new ArrayList <String> ();
// Returns the value as is if the parsing fails.
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 tables, fields, and conditions in a single row.
* @ 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 order by g2, g3 ");
For (String SQL: ls ){
System. out. println (new SqlParser (SQL ));
// System. out. println (SQL );
}
}
}