mysqlsql语法解析器_利用druid解析器解析SQL

小程序780 更新时间：2025-06-08 15:38:31

2023年8月3日发(作者：)

mysqlsql语法解析器_利⽤druid解析器解析SQL最近参与⼀个开源项⽬，⼀个功能的实现，⽤到了 druid 解析器来解析SQL，记录下如果使⽤ druid 来解析SQL，实现对SQL的拦截改写。1. 对 insert 语句进⾏解析：private static String convertInsertSQL(String sql){try{MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlInsertStatement insert = (MySqlInsertStatement)statement;String tableName = Backquote(leName().getSimpleName());if(!isGlobalTable(tableName))return sql;if(!isInnerColExist(tableName))return sql;List columns = umns();if(columns == null || () <= 0)return sql;if(ry() != null)// insert into tab selectreturn sql;StringBuilder sb = new StringBuilder(200)// 指定初始容量可以提⾼性能.append("insert into ").append(tableName).append("(");int idx = -1;for(int i = 0; i < (); i++) {if(i < () - 1)((i).toString()).append(",");((i).toString());String column = Backquote(umns().get(i).toString());if(IgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN))idx = i;}if(idx <= -1)(",").append(GLOBAL_TABLE_MYCAT_COLUMN);(")");(" values");List vcl = uesList();if(vcl != null && () > 1){// 批量insertfor(int j=0; jif(j != () - 1)appendValues((j).getValues(), sb, idx).append(",");elseappendValues((j).getValues(), sb, idx);}}else{// ⾮批量 insertList valuse = ues().getValues();appendValues(valuse, sb, idx);}List dku = licateKeyUpdate();if(dku != null && () > 0){(" on duplicate key update ");for(int i=0; iSQLExpr exp = (i);if(exp != null){if(i < () - 1)(ng()).append(",");(ng());}}}return ng();}catch(Exception e){ // 发⽣异常，则返回原始 (sage());return sql;}}三⾏代码就可以解析⼀条insert语句：MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlInsertStatement insert = (MySqlInsertStatement)statement;然后使⽤解析得到的 insert ，就可以获得原始insert语句的各个部分：List columns = umns(); // 获得所有列名ry(); // 如果是 insert into select 语句，则可以获取 select查询如果是批量插⼊的insert：insert into tab(id,name) values(1,'a'),(2,'b'),(3,'c');则可以使⽤：List vcl = uesList();获得素有的 values ⼦句部分。⾮批量插⼊，则可以使⽤：List valuse = ues().getValues();获得 values ⼦句。on duplicate 部分可以使⽤下⾯的语句获取：List dku = licateKeyUpdate();获得了这些，就⽽已重组得到原始SQL语句，并且对其进⾏各种改写。mysql 中的insert语法如下：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 UPDATEcol_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 UPDATEcol_name=expr[, col_name=expr] ... ]Or:INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE][INTO] tbl_name[PARTITION (partition_name,...)][(col_name,...)]SELECT ...[ ON DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ... ]2. 解析 update 语句：public static String convertUpdateSQL(String sql){try{MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement stmt = tatement();MySqlUpdateStatement update = (MySqlUpdateStatement)stmt;SQLTableSource ts = leSource();if(ts != null && ng().contains(",")){n(ng());("Do not support Multiple-table ");return sql;}String tableName = Backquote(leName().getSimpleName());if(!isGlobalTable(tableName))return sql;if(!isInnerColExist(tableName))return sql;// 没有内部列StringBuilder sb = new StringBuilder(150);SQLExpr se = re();// where中有⼦查询： update company set name='com' where id in (select id from xxx where ...)if(se instanceof SQLInSubQueryExpr){// return sql;int idx = rCase().indexOf(" SET ") + 5;(ing(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(ing(idx));return ng();}String where = null;if(re() != null)where = re().toString();SQLOrderBy orderBy = erBy();Limit limit = it();("update ").append(tableName).append(" set ");List items = ms();boolean flag = false;for(int i=0; iSQLUpdateSetItem item = (i);String col = umn().toString();String val = ue().toString();if(Backquote(col).equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN)){flag = true;(col).append("=");if(i != () - 1)(operationTimestamp).append(",");(operationTimestamp);}else{(col).append("=");if(i != () -1 )(val).append(",");(val);}}if(!flag){(",").append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp);}(" where ").append(where);if(orderBy != null && ms()!=null&& ms().size() > 0){(" order by ");for(int i=0; iSQLSelectOrderByItem item = ms().get(i);SQLOrderingSpecification os = e();(r().toString());if(i < ms().size() - 1){if(os != null)(" ").append(ng());(",");}else{if(os != null)(" ").append(ng());}}}if(limit != null){// 分为两种情况： limit 10; limit 10,10;(" limit ");if(set() != null)(set().toString()).append(",");(Count().toString());}return ng();}catch(Exception e){(sage());return sql;}}同样三⾏，解析update语句：MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement stmt = tatement();MySqlUpdateStatement update = (MySqlUpdateStatement)stmt;如果是多表 udpate 语句，可以使⽤下⾯的语句进⾏判断：SQLTableSource ts = leSource();if(ts != null && ng().contains(",")){n(ng());("Do not support Multiple-table ");return sql;}如果是单表update语句：获得 update 语句的 where 部分：SQLExpr se = re();// where中有⼦查询： update company set name='com' where id in (select id from xxx where ...)if(se instanceof SQLInSubQueryExpr){// return sql;int idx = rCase().indexOf(" SET ") + 5;(ing(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(ing(idx));return ng();}String where = null;if(re() != null)where = re().toString();如果where 部分由 select 语句，由：se instanceof SQLInSubQueryExpr 来判断。order by 和 limit 部分分别由：SQLOrderBy orderBy = erBy();Limit limit = it();获得。update 对应的列和值，有下⾯的代码获得：boolean flag = false;for(int i=0; iSQLUpdateSetItem item = (i);String col = umn().toString();String val = ue().toString();解析得到了这些部分，就可以重组出原始的 update 语句，并且按照⾃⼰的需求进⾏SQL改写。3. 解析 alter 语句：String sql = "alter table t add colomn name varchar(30)";MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlAlterTableStatement alter = (MySqlAlterTableStatement)statement;SQLExprTableSource source = leSource();String tableName = ng();解析器：adruid1.0.14

2023年8月3日发(作者：)

mysqlsql语法解析器_利⽤druid解析器解析SQL最近参与⼀个开源项⽬，⼀个功能的实现，⽤到了 druid 解析器来解析SQL，记录下如果使⽤ druid 来解析SQL，实现对SQL的拦截改写。1. 对 insert 语句进⾏解析：private static String convertInsertSQL(String sql){try{MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlInsertStatement insert = (MySqlInsertStatement)statement;String tableName = Backquote(leName().getSimpleName());if(!isGlobalTable(tableName))return sql;if(!isInnerColExist(tableName))return sql;List columns = umns();if(columns == null || () <= 0)return sql;if(ry() != null)// insert into tab selectreturn sql;StringBuilder sb = new StringBuilder(200)// 指定初始容量可以提⾼性能.append("insert into ").append(tableName).append("(");int idx = -1;for(int i = 0; i < (); i++) {if(i < () - 1)((i).toString()).append(",");((i).toString());String column = Backquote(umns().get(i).toString());if(IgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN))idx = i;}if(idx <= -1)(",").append(GLOBAL_TABLE_MYCAT_COLUMN);(")");(" values");List vcl = uesList();if(vcl != null && () > 1){// 批量insertfor(int j=0; jif(j != () - 1)appendValues((j).getValues(), sb, idx).append(",");elseappendValues((j).getValues(), sb, idx);}}else{// ⾮批量 insertList valuse = ues().getValues();appendValues(valuse, sb, idx);}List dku = licateKeyUpdate();if(dku != null && () > 0){(" on duplicate key update ");for(int i=0; iSQLExpr exp = (i);if(exp != null){if(i < () - 1)(ng()).append(",");(ng());}}}return ng();}catch(Exception e){ // 发⽣异常，则返回原始 (sage());return sql;}}三⾏代码就可以解析⼀条insert语句：MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlInsertStatement insert = (MySqlInsertStatement)statement;然后使⽤解析得到的 insert ，就可以获得原始insert语句的各个部分：List columns = umns(); // 获得所有列名ry(); // 如果是 insert into select 语句，则可以获取 select查询如果是批量插⼊的insert：insert into tab(id,name) values(1,'a'),(2,'b'),(3,'c');则可以使⽤：List vcl = uesList();获得素有的 values ⼦句部分。⾮批量插⼊，则可以使⽤：List valuse = ues().getValues();获得 values ⼦句。on duplicate 部分可以使⽤下⾯的语句获取：List dku = licateKeyUpdate();获得了这些，就⽽已重组得到原始SQL语句，并且对其进⾏各种改写。mysql 中的insert语法如下：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 UPDATEcol_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 UPDATEcol_name=expr[, col_name=expr] ... ]Or:INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE][INTO] tbl_name[PARTITION (partition_name,...)][(col_name,...)]SELECT ...[ ON DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ... ]2. 解析 update 语句：public static String convertUpdateSQL(String sql){try{MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement stmt = tatement();MySqlUpdateStatement update = (MySqlUpdateStatement)stmt;SQLTableSource ts = leSource();if(ts != null && ng().contains(",")){n(ng());("Do not support Multiple-table ");return sql;}String tableName = Backquote(leName().getSimpleName());if(!isGlobalTable(tableName))return sql;if(!isInnerColExist(tableName))return sql;// 没有内部列StringBuilder sb = new StringBuilder(150);SQLExpr se = re();// where中有⼦查询： update company set name='com' where id in (select id from xxx where ...)if(se instanceof SQLInSubQueryExpr){// return sql;int idx = rCase().indexOf(" SET ") + 5;(ing(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(ing(idx));return ng();}String where = null;if(re() != null)where = re().toString();SQLOrderBy orderBy = erBy();Limit limit = it();("update ").append(tableName).append(" set ");List items = ms();boolean flag = false;for(int i=0; iSQLUpdateSetItem item = (i);String col = umn().toString();String val = ue().toString();if(Backquote(col).equalsIgnoreCase(GLOBAL_TABLE_MYCAT_COLUMN)){flag = true;(col).append("=");if(i != () - 1)(operationTimestamp).append(",");(operationTimestamp);}else{(col).append("=");if(i != () -1 )(val).append(",");(val);}}if(!flag){(",").append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp);}(" where ").append(where);if(orderBy != null && ms()!=null&& ms().size() > 0){(" order by ");for(int i=0; iSQLSelectOrderByItem item = ms().get(i);SQLOrderingSpecification os = e();(r().toString());if(i < ms().size() - 1){if(os != null)(" ").append(ng());(",");}else{if(os != null)(" ").append(ng());}}}if(limit != null){// 分为两种情况： limit 10; limit 10,10;(" limit ");if(set() != null)(set().toString()).append(",");(Count().toString());}return ng();}catch(Exception e){(sage());return sql;}}同样三⾏，解析update语句：MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement stmt = tatement();MySqlUpdateStatement update = (MySqlUpdateStatement)stmt;如果是多表 udpate 语句，可以使⽤下⾯的语句进⾏判断：SQLTableSource ts = leSource();if(ts != null && ng().contains(",")){n(ng());("Do not support Multiple-table ");return sql;}如果是单表update语句：获得 update 语句的 where 部分：SQLExpr se = re();// where中有⼦查询： update company set name='com' where id in (select id from xxx where ...)if(se instanceof SQLInSubQueryExpr){// return sql;int idx = rCase().indexOf(" SET ") + 5;(ing(0, idx)).append(GLOBAL_TABLE_MYCAT_COLUMN).append("=").append(operationTimestamp).append(",").append(ing(idx));return ng();}String where = null;if(re() != null)where = re().toString();如果where 部分由 select 语句，由：se instanceof SQLInSubQueryExpr 来判断。order by 和 limit 部分分别由：SQLOrderBy orderBy = erBy();Limit limit = it();获得。update 对应的列和值，有下⾯的代码获得：boolean flag = false;for(int i=0; iSQLUpdateSetItem item = (i);String col = umn().toString();String val = ue().toString();解析得到了这些部分，就可以重组出原始的 update 语句，并且按照⾃⼰的需求进⾏SQL改写。3. 解析 alter 语句：String sql = "alter table t add colomn name varchar(30)";MySqlStatementParser parser = new MySqlStatementParser(sql);SQLStatement statement = tatement();MySqlAlterTableStatement alter = (MySqlAlterTableStatement)statement;SQLExprTableSource source = leSource();String tableName = ng();解析器：adruid1.0.14

本文发布于:2023-08-03，感谢您对本站的认可！

本文链接:http://torson.com.cn/chengxu/1691006191a484613.html