Parsing SQL with the Druid parser

Recently participated in an open source project, a function of the implementation, using the Druid parser to parse SQL, record if the use of Druid to parse SQL, the implementation of SQL interception rewrite.

1. Parse the INSERT statement:

private static string Convertinsertsql (String sql) {Try{mysqlstatementparser parser = new Mysqlstatementparser (SQL); SQLStatement statement = Parser.parsestatement ();         Mysqlinsertstatement insert = (mysqlinsertstatement) statement;        String tableName = Stringutil.removebackquote (Insert.gettablename (). Getsimplename ());        if (!isglobaltable (tableName)) return SQL;                if (!isinnercolexist (tableName)) return SQL;        list<sqlexpr> columns = Insert.getcolumns ();        if (columns = = NULL | | columns.size () <= 0) return SQL;            if (insert.getquery () = null)//INSERT INTO tab select Return SQL; StringBuilder sb = new StringBuilder (200)//Specify initial capacity to improve performance. Append ("INSERT into"). Append (TableName). Append ("("); int idx = -1;for (int i = 0; i < columns.size (); i++) {if (I < columns.size ()-1) sb.append (Columns.get (i). toString ()). Append ( ","); Elsesb.append (Columns.get (i). toString ()); String column = Stringutil.removebackquote (insert.gEtcolumns (). get (i). toString ()); if (Column.equalsignorecase (global_table_mycat_column)) idx = i;} if (idx <=-1) sb.append (","). Append (Global_table_mycat_column); Sb.append (")"); Sb.append ("values"); List<valuesclause> VCL = Insert.getvalueslist (); if (VCL! = null && vcl.size () > 1) {//batch insertfor (int j=0 ; J<vcl.size ();   J + +) {if (J! = Vcl.size ()-1) appendvalues (Vcl.get (j). GetValues (), SB, IDX). Append (","); Else Appendvalues (Vcl.get (j). GetValues (), SB, IDX);}} else{//non-bulk insertlist<sqlexpr> valuse = Insert.getvalues (). GetValues (); Appendvalues (Valuse, SB, IDX);} list<sqlexpr> dku = Insert.getduplicatekeyupdate (); if (dku! = null && dku.size () > 0) {sb.append ("on DUPL Icate key Update "); for (int i=0; I<dku.size (); i++) {sqlexpr exp = dku.get (i); if (exp! = NULL) {if (I < dku.size ()-1) sb.append (exp.tostring ()). Append (","); Elsesb.append (Exp.tostring ());}}} return sb.tostring ();} catch (Exception e) {//exception occurred, return original Sqllogger.warn (E.getmessage ()); return SQL;}} 

Three lines of code can parse an INSERT statement:

Mysqlstatementparser parser = new Mysqlstatementparser (SQL);
SQLStatement statement = Parser.parsestatement ();
Mysqlinsertstatement insert = (mysqlinsertstatement) statement;

You can then use the parsed insert to get the parts of the original INSERT statement:

list<sqlexpr> columns = Insert.getcolumns (); Get all column names

Insert.getquery (); If it is an INSERT INTO SELECT statement, you can get the select query

If it is a bulk insert of insert:insert into tab (Id,name) VALUES (1, ' a '), (2, ' B '), (3, ' C ');

You can use:

List<valuesclause> VCL = Insert.getvalueslist ();

Gets the part of the values clause that is known.

Non-bulk inserts, you can use:

list<sqlexpr> valuse = Insert.getvalues (). GetValues ();

Gets the values clause.

The on duplicate section can be obtained using the following statement:

list<sqlexpr> dku = Insert.getduplicatekeyupdate ();

With this, it's just a reorganization to get the original SQL statement and rewrite it.

The insert syntax in MySQL is as follows:

Mysql>? Insertname: ' INSERT ' Description:Syntax:INSERT [low_priority | DELAYED | High_priority] [IGNORE]    [into] tbl_name    [PARTITION (Partition_name,...)]    [(Col_name,...)]    {VALUES | VALUE} ({expr | DEFAULT},...), (...),...    [on DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ...] Or:insert [Low_priority | DELAYED | High_priority] [IGNORE]    [into] tbl_name    [PARTITION (Partition_name,...)]    SET col_name={expr | DEFAULT}, ...    [on DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ...] Or:insert [Low_priority | High_priority] [IGNORE]    [into] tbl_name    [PARTITION (Partition_name,...)]    [(Col_name,...)]    SELECT ...    [on DUPLICATE KEY UPDATE      col_name=expr        [, col_name=expr] ...]

2. Parse the UPDATE statement:

public static string Convertupdatesql (String sql) {Try{mysqlstatementparser parser = new Mysqlstatementparser (SQL); SQLStatement stmt = Parser.parsestatement (); Mysqlupdatestatement update = (mysqlupdatestatement) stmt; Sqltablesource ts = Update.gettablesource (); if (ts! = null && ts.tostring (). Contains (",")) {System.out.println ( Ts.tostring ()); Logger.warn ("Do not support multiple-table udpate syntax ..."); return SQL;}        String tableName = Stringutil.removebackquote (Update.gettablename (). Getsimplename ());        if (!isglobaltable (tableName)) return SQL; if (!isinnercolexist (tableName)) return sql;//has no inner column StringBuilder sb = new StringBuilder (150); sqlexpr se = Update.getwhere ();//Where there is a subquery: Update company set name= ' COM ' where ID in (SELECT id from xxx where ...) if (se instanceof sqlinsubqueryexpr) {//return Sql;int idx = Sql.touppercase (). IndexOf ("SET") + 5;sb.append (Sql.substrin G (0, idx)). Append (Global_table_mycat_column). Append ("="). Append (Operationtimestamp). Append(","). Append (sql.substring (IDX)); return sb.tostring ();} String where = Null;if (update.getwhere () = null) where = Update.getwhere (). toString (); Sqlorderby = Update.getorderby (); Limit limit = Update.getlimit (); Sb.append ("Update"). Append (TableName). Append ("set"); list<sqlupdatesetitem> items = Update.getitems (); Boolean flag = false;for (int i=0; i<items.size (); i++) { Sqlupdatesetitem item = items.get (i); String col = Item.getcolumn (). toString (); String val = Item.getvalue (). toString (); if (Stringutil.removebackquote (col). Equalsignorecase (Global_table_mycat_ COLUMN) {flag = True;sb.append (col). Append ("="); if (i! = Items.size ()-1) sb.append (Operationtimestamp). Append (","); Elsesb.append (Operationtimestamp);} Else{sb.append (COL). Append ("="); if (i! = Items.size ()-1) sb.append (val). Append (","); Elsesb.append (val);}} if (!flag) {sb.append (","). Append (Global_table_mycat_column). Append ("="). Append (Operationtimestamp);} Sb.append ("where"). append (where); if (= = = NULL && orderby.geTitems ()!=null && orderby.getitems (). Size () > 0) {sb.append ("Order By"), for (int i=0; I<orderby.getitems (). Size (); i++) {Sqlselectorderbyitem item = Orderby.getitems (). get (i); sqlorderingspecification OS = Item.gettype (); Sb.append (item.getexpr (). toString ()); if (I < Orderby.getitems (). Size ()-1) {if (OS! = null) sb.append (""). Append (Os.tostring ()); Sb.append (",");} else{if (OS = null) sb.append (""). Append (Os.tostring ());}}}   if (limit! = null) {//is divided into two cases: limit 10; Limit 10,10;sb.append ("limit"); if (limit.getoffset () = null) Sb.append (Limit.getoffset (). toString ()). Append (","); Sb.append (Limit.getrowcount (). toString ());} return sb.tostring ();} catch (Exception e) {Logger.warn (E.getmessage ()); return SQL;}}

The same three rows parse the UPDATE statement:

Mysqlstatementparser parser = new Mysqlstatementparser (SQL);
SQLStatement stmt = Parser.parsestatement ();
Mysqlupdatestatement update = (mysqlupdatestatement) stmt;

If it is a multi-table udpate statement, you can use the following statement to determine:

Sqltablesource ts = Update.gettablesource ();
if (ts! = null && ts.tostring (). Contains (",")) {
System.out.println (Ts.tostring ());
Logger.warn ("Do not support multiple-table udpate syntax ...");
return SQL;
}

If it is a single-table UPDATE statement:

Get the where part of the UPDATE statement:

sqlexpr se = Update.getwhere ();
In where there is a subquery: Update company set name= ' COM ' where ID in (SELECT id from xxx where ...)
if (se instanceof sqlinsubqueryexpr) {
return SQL;
int idx = Sql.touppercase (). IndexOf ("SET") + 5;
Sb.append (sql.substring (0, idx)). Append (Global_table_mycat_column)
. Append ("="). Append (Operationtimestamp)
. Append (","). Append (sql.substring (idx));
return sb.tostring ();
}
String where = null;
if (update.getwhere () = null)
where = Update.getwhere (). toString ();

If the where part is determined by the SELECT statement, by: SE instanceof sqlinsubqueryexpr.

The order by and limit sections are divided by:

Sqlorderby = Update.getorderby ();
Limit limit = Update.getlimit ();

Get.

Update corresponds to the column and value, with the following code obtained:

Boolean flag = false;
for (int i=0; i<items.size (); i++) {
Sqlupdatesetitem item = items.get (i);
String col = Item.getcolumn (). toString ();
String val = Item.getvalue (). toString ();

By parsing these parts, you can reorganize the original UPDATE statement and make the SQL rewrite as you want.

Parser:

        <dependency>            <groupId>com.alibaba</groupId>            <artifactid>druid</artifactid >             <version>1.0.  </version>         

Parsing SQL with the Druid parser