Java tutorial
/* * Copyright(C) 2011-2012 Alibaba Group Holding Limited * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License version 2 as * published by the Free Software Foundation. * * Authors: * wentong <wentong@taobao.com> */ package com.taobao.tdhs.jdbc.sqlparser; import com.taobao.tdhs.jdbc.util.StringUtil; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.jetbrains.annotations.NotNull; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * function:??parse * * @author danchen */ public class ParseSQL { // log4j private static Logger logger = Logger.getLogger(ParseSQL.class); // SQL? private String oriSql; private String sql; // SQL insert 0,update 1,delete 2, select 3 private SQLType sqlType; // ?,?,: private String errmsg; // private String tip; // ?? private String tablename; // ?? private String alias_tablename; // where? private TreeNode whereNode; // private String select_column; // ???? private List<Entry<String, String>> columns; // group by private String groupbycolumn; // ? private String orderbycolumn; // Muti-table sql statement tag private int tag; // index hint private HintStruct hint; // ?OperationStruct??? private List<OperationStruct> listOperationStructs; private Map<String, List<OperationStruct>> mapOperationStructs; // ?? private OrderByType sortMethod; // insertcolumnvalue private List<Entry<String, String>> insertEntries; // updateset columnvalue private List<Entry<String, String>> updateEntries; // limit? private int limitStart; private int limit; // public ParseSQL(@NotNull String sql) { this.oriSql = sql; sql = sql.trim(); if (StringUtils.endsWith(sql, ";")) { sql = sql.substring(0, sql.length() - 1); } this.sql = sql; this.errmsg = ""; this.tip = ""; this.tablename = ""; this.groupbycolumn = ""; this.orderbycolumn = ""; this.limitStart = 0; this.limit = 0; // ?0:?;1: this.tag = 0; this.columns = new ArrayList<Entry<String, String>>(); listOperationStructs = new LinkedList<OperationStruct>(); this.updateEntries = new ArrayList<Entry<String, String>>(); } public String getSql() { return oriSql; } public SQLType getSqlType() { return sqlType; } public String getErrmsg() { return errmsg; } public String getTableName() { return tablename; } public String getAlias_tablename() { return alias_tablename; } public TreeNode getWhereNode() { return whereNode; } public List<OperationStruct> getListOperationStructs() { if (listOperationStructs.isEmpty()) { getListOperationStructsFromWhereTree(this.whereNode); } return listOperationStructs; } public List<Entry<String, String>> getUpdateEntries() { return updateEntries; } public Map<String, List<OperationStruct>> getMapOperationStructs() { if (mapOperationStructs == null) { mapOperationStructs = new HashMap<String, List<OperationStruct>>(); for (OperationStruct o : getListOperationStructs()) { List<OperationStruct> value; String key = o.getColumnName(); key = StringUtil.escapeField(key); if (!mapOperationStructs.containsKey(key)) { value = new ArrayList<OperationStruct>(); mapOperationStructs.put(key, value); } else { value = mapOperationStructs.get(key); } value.add(o); } } return mapOperationStructs; } // ??where tree private void getListOperationStructsFromWhereTree(TreeNode node) { if (node == null) { return; } if (node.node_type == 4) { if (node.node_content.equalsIgnoreCase("or")) { errmsg = "where is not support or!"; return; } getListOperationStructsFromWhereTree(node.left_node); getListOperationStructsFromWhereTree(node.right_node); } else { if (node.node_type == 2) { OperationStruct operationStruct = new OperationStruct(); operationStruct.setOper(node.node_content); if (node.left_node != null) { operationStruct.setColumnName(node.left_node.node_content); } if (node.right_node != null) { operationStruct.setValue(node.right_node.node_content); } listOperationStructs.add(operationStruct); } else { errmsg = "where tree has some error."; } } } public List<String> getSelect_column() { return changeToList(select_column); } public List<Entry<String, String>> getColumns() { return columns; } public List<String> getGroupbycolumn() { return changeToList(groupbycolumn); } public List<String> getOrderByColumn() { return changeToList(orderbycolumn); } public HintStruct getHint() { return hint; } public OrderByType getSortMethod() { return sortMethod; } public List<Entry<String, String>> getInsertEntries() { return insertEntries; } public int getLimitOffset() { return limitStart; } public int getLimit() { return limit; } private void analyzeSQLHint() { int addr = StringUtils.indexOfIgnoreCase(sql, "/*"); if (addr < 0) { return; } int addr_right = StringUtils.indexOfIgnoreCase(sql, "*/"); if (addr < 0 || addr_right < addr) { errmsg = "hint systax is not right."; return; } String hintString = sql.substring(addr, addr_right + 2); this.hint = new HintStruct(hintString); this.hint.AnalyzeHint(); this.sql = sql.substring(0, addr) + sql.substring(addr_right + 2); } private void analyzeSortMethod() { if (StringUtils.indexOfIgnoreCase(sql, "order by") > 0 && StringUtils.indexOfIgnoreCase(sql, " asc") > 0) { this.sortMethod = OrderByType.ASC; } else if (StringUtils.indexOfIgnoreCase(sql, "order by") > 0 && StringUtils.indexOfIgnoreCase(sql, " desc") > 0) { this.sortMethod = OrderByType.DESC; } else if (StringUtils.indexOfIgnoreCase(sql, "order by") > 0) { // ?? this.sortMethod = OrderByType.ASC; } else { this.sortMethod = null; } } /* * limit a,b??SQL? limit offset,count */ private void analyzeLimit() { int addr = StringUtils.indexOfIgnoreCase(sql, " limit "); if (addr < 0) { return; } addr = addr + 7; String limitstr = sql.substring(addr).trim(); String[] array_limit = limitstr.split(","); if (array_limit.length == 1) { this.limitStart = 0; this.limit = Integer.valueOf(array_limit[0]); if (this.limit < 0) { this.errmsg = "limitOffset should larger than 0"; } } else if (array_limit.length == 2) { this.limitStart = Integer.valueOf(array_limit[0]); this.limit = Integer.valueOf(array_limit[1]); if (this.limitStart < 0) { this.errmsg = "limitStart should larger than 0"; return; } if (this.limit < 0) { this.errmsg = "limitOffset should larger than 0"; return; } } else { this.errmsg = "wrong limit systax."; } } // ,??list public List<String> changeToList(String str) { if (StringUtils.isBlank(str)) { return null; } String[] array = str.split(","); List<String> list = new LinkedList<String>(); Collections.addAll(list, array); return list; } /* * parameter sqlstring?searchStr,?,searchStr?'' */ private boolean checkSpecialStr(String sqlstring, String searchStr) { //????searchStr Stack<String> stack = new Stack<String>(); boolean exist_danyinhao = false; for (int i = 0; i < sqlstring.length(); i++) { // if (sqlstring.substring(i, i + 1).equals("'") == false) { stack.push(sqlstring.substring(i, i + 1)); } //' if (sqlstring.substring(i, i + 1).equals("'")) { //?\,?,\,,?? int count = 0; int k = i; boolean real_danyinhao; while (k - 1 >= 0 && sqlstring.substring(k - 1, k).equals("\\") == true) { k--; count++; } //System.out.println("\\:"+count); if (count % 2 == 0) { //?? real_danyinhao = true; } else { //???,value real_danyinhao = false; stack.push(sqlstring.substring(i, i + 1)); } if (real_danyinhao == true) { if (exist_danyinhao == false) { exist_danyinhao = true; stack.push(sqlstring.substring(i, i + 1)); } else { boolean find_real_danyinhao = false; while (find_real_danyinhao == false) { while (!stack.pop().equals("'")) { ; } //???,??\ if (stack.isEmpty() == false && stack.peek().equals("\\")) { //?,??? count = 0; while (stack.peek().equals("\\")) { stack.pop(); count++; } if (count % 2 == 0) { //? find_real_danyinhao = true; } else { //? find_real_danyinhao = false; } } else { // find_real_danyinhao = true; } } exist_danyinhao = false; } } } } //end for logger.debug(stack.toString()); if (stack.isEmpty() == false && stack.search(searchStr) > -1) { stack.clear(); return true; } else { return false; } } // ?SQL public boolean analyzeSqlInjection(String sqlstring) { //SQL if (sqlstring.indexOf(";") == -1) { return false; } return checkSpecialStr(sqlstring, ";"); } // ?SQLselect,insert,update,delete public void sqlDispatch() { if (analyzeSqlInjection(sql)) { errmsg = "too many \";\""; return; } if (sql.substring(0, 6).equalsIgnoreCase("select")) { // ?select analyzeSQLHint(); analyzeSortMethod(); analyzeLimit(); parseSQLSelect(); sqlType = SQLType.SELECT; } else if (sql.substring(0, 6).equalsIgnoreCase("insert")) { // ?insert analyzeSQLHint(); parseSQLInsert(); sqlType = SQLType.INSERT; } else if (sql.substring(0, 6).equalsIgnoreCase("update")) { // ?update analyzeSQLHint(); analyzeSortMethod(); analyzeLimit(); parseSQLUpdate(); sqlType = SQLType.UPDATE; } else if (sql.substring(0, 6).equalsIgnoreCase("delete")) { // ?delete analyzeSQLHint(); analyzeSortMethod(); analyzeLimit(); parseSQLDelete(); sqlType = SQLType.DELETE; } else // ?SQL? sqlType = null; } /* * where??,?or ,?DBA */ private void checkWhereTreeRootNode(TreeNode treeRootNode) { if (treeRootNode == null) { this.errmsg = "where tree root node is empty."; logger.warn(this.errmsg); return; } if (treeRootNode.node_content.equalsIgnoreCase("or")) { this.errmsg = "where tree root node appears or key word,this is not allowed."; logger.error(this.errmsg); } } private void parseSQLDelete() { // delete?SQL auto review,???where??? logger.debug("SQL at parsing:" + this.sql); int i = 0; int loop = 0; // if (i + 6 < sql.length() && sql.substring(0, 6).equalsIgnoreCase("delete")) i = i + 6; else { this.errmsg = "not delete SQL statement."; return; } // ,? while (i + 1 < sql.length() && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // ,from if (i + 4 < sql.length() && sql.substring(i, i + 4).equalsIgnoreCase("from")) i = i + 4; else { this.errmsg = "not find from key word."; return; } // ,? while (i + 1 < sql.length() && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // ,tablename while (i + 1 < sql.length() && !sql.substring(i, i + 1).equalsIgnoreCase(" ")) { tablename = tablename + sql.substring(i, i + 1); i++; } logger.debug("table name:" + this.tablename); // ,? while (i + 1 < sql.length() && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // ,where if (i + 5 <= sql.length() && sql.substring(i, i + 5).equalsIgnoreCase("where")) i = i + 5; else { this.errmsg = "not find where key word."; return; } // ? if (i > sql.length()) { this.errmsg = "not find where condition."; logger.warn(this.errmsg); return; } else { if (sql.substring(i).trim().length() == 0) { this.errmsg = "not find where condition."; logger.warn(this.errmsg); return; } } int addrOderBy = StringUtils.indexOfIgnoreCase(sql, "order by"); int addrLimit = StringUtils.indexOfIgnoreCase(sql, " limit "); String whereStr; if (addrOderBy > 0) { whereStr = sql.substring(i, addrOderBy); } else if (addrLimit > 0) { whereStr = sql.substring(i, addrLimit); } else { whereStr = sql.substring(i); } whereNode = parseWhere(null, whereStr, loop); // ???? whereNode = parseWhere(null, whereStr.trim(), loop); // check whereNode tree checkWhereTreeRootNode(whereNode); analyzeOrderByStr(); logger.debug("where condition:" + whereStr.trim()); } // order by columns? public void analyzeOrderByStr() { logger.debug("enter function AnalyzeOrderByStr"); String orderbycolumns = ""; int addr_order_by = StringUtils.indexOfIgnoreCase(sql, "order by"); if (addr_order_by < 0) return; if (StringUtils.indexOfIgnoreCase(sql, " limit ", addr_order_by) > addr_order_by) { orderbycolumns = sql.substring(addr_order_by + 8, StringUtils.indexOfIgnoreCase(sql, " limit ", addr_order_by)); } else { // no limit key word orderbycolumns = sql.substring(addr_order_by + 8).trim(); } orderbycolumns = orderbycolumns.replace(" asc", " "); orderbycolumns = orderbycolumns.replace(" desc", " "); this.orderbycolumn = orderbycolumns; logger.debug("order by columns:" + orderbycolumn); } private void parseSQLUpdate() { // update?SQL auto review,???tablename,?where??? // update??select logger.debug("SQL at parsing:" + this.sql); int addr_where = 0; int loop = 0; tablename = ""; int i = 0; // if (i + 6 < sql.length() && sql.substring(0, 6).equalsIgnoreCase("update")) i = i + 6; else { this.errmsg = "not update SQL statement."; return; } // ,? while (i + 1 < sql.length() && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // ,tablename while (i + 1 < sql.length() && !sql.substring(i, i + 1).equalsIgnoreCase(" ")) { tablename = tablename + sql.substring(i, i + 1); i++; } logger.debug("table name:" + this.tablename); int addrSet = StringUtils.indexOfIgnoreCase(sql, " set "); if (addrSet < 0) { this.errmsg = "not find set key word."; logger.warn(this.errmsg); return; } // check where key word addr_where = StringUtils.indexOfIgnoreCase(sql, " where"); if (addr_where < 0) { this.errmsg = "not find where key word."; logger.warn(this.errmsg); return; } analyzeUpdateSetColumns(sql.substring(addrSet + 5, addr_where)); // values?sysdate(),?? if (StringUtils.indexOfIgnoreCase(sql, "sysdate()", i) > 0 && StringUtils.indexOfIgnoreCase(sql, "sysdate()", i) < addr_where) { errmsg = "use sysdate() function,this not allowed,you should use now() replace it."; logger.warn(errmsg); return; } if (addr_where + 6 >= sql.length()) { this.errmsg = "not find where condition."; logger.warn(this.errmsg); return; } int addrOderBy = StringUtils.indexOfIgnoreCase(sql, "order by"); int addrLimit = StringUtils.indexOfIgnoreCase(sql, " limit "); String whereStr; if (addrOderBy > 0) { whereStr = sql.substring(addr_where + 6, addrOderBy); } else if (addrLimit > 0) { whereStr = sql.substring(addr_where + 6, addrLimit); } else { whereStr = sql.substring(addr_where + 6); } whereNode = parseWhere(null, whereStr.trim(), loop); // check whereNode tree checkWhereTreeRootNode(whereNode); analyzeOrderByStr(); logger.debug("where condition:" + whereStr); } public boolean checkRealEqual(Stack<String> stack) { logger.debug("checkRealEqual:" + stack.toString()); String tmp_str = ""; @SuppressWarnings("unchecked") Stack<String> tmpStack = (Stack<String>) stack.clone(); while (tmpStack.isEmpty() == false) { tmp_str = tmpStack.pop() + tmp_str; } //?',?,?,value boolean result = !checkSpecialStr(tmp_str, "'"); logger.debug(result ? "=?" : "?=?"); return result; } /* * ?update set?column,?value,??value, * ,? */ private void analyzeUpdateSetColumns(String substring) { if (substring == null) return; /*String[] array_setColumn = substring.split(","); for (String setColumn : array_setColumn) { int addr = StringUtils.indexOfIgnoreCase(setColumn, "="); String column = setColumn.substring(0, addr).trim(); String value = setColumn.substring(addr + 1).trim(); this.updateEntries.add(new Entry<String, String>(column, value)); }*/ //Stack?? Stack<String> updateColumnValueStack = new Stack<String>(); for (int i = 0; i < substring.length(); i++) { updateColumnValueStack.push(substring.substring(i, i + 1)); } String column = ""; String value = ""; while (updateColumnValueStack.isEmpty() == false) { column = ""; value = ""; //value String while (updateColumnValueStack.peek().equals("=") == false || checkRealEqual(updateColumnValueStack) == false) { value = updateColumnValueStack.pop() + value; } //= updateColumnValueStack.pop(); //column String try { while (updateColumnValueStack.peek().equals(",") == false) { column = updateColumnValueStack.pop() + column; } } catch (EmptyStackException e) { //? this.updateEntries.add(new Entry<String, String>(column, value)); break; } //, updateColumnValueStack.pop(); //? this.updateEntries.add(new Entry<String, String>(column, value)); } } /* * ?? */ private void selectColumnCheckValid(String columnsString) { if (columnsString.equalsIgnoreCase("*")) this.errmsg = "can't support select * !"; } /* * ???,?????? */ public String getNextToken(String str, int from_addr) { String token = ""; // ? if (str == null || str.length() < from_addr) { return null; } // while (from_addr < str.length() && str.substring(from_addr, from_addr + 1).equalsIgnoreCase(" ")) { from_addr++; } // ? if (from_addr > str.length()) { return null; } // token while (from_addr < str.length() && str.substring(from_addr, from_addr + 1).equalsIgnoreCase(" ") == false) { token = token + str.substring(from_addr, from_addr + 1); from_addr++; } return token; } /* * ????select SQL?? */ private void parseSQLSelect() { // where word check if (StringUtils.indexOfIgnoreCase(sql, " where ") < 0) { this.errmsg = "don't have where!"; return; } // and word check if (getNextToken(sql, StringUtils.indexOfIgnoreCase(sql, " where ") + 7).equalsIgnoreCase("and")) { this.errmsg = "and after where,syntax error"; return; } // > <SQLMAP?? if (StringUtils.indexOfIgnoreCase(sql, ">") > 0 || StringUtils.indexOfIgnoreCase(sql, "<") > 0) { this.errmsg = "error in < and > , syntax error"; return; } // join???? if (StringUtils.indexOfIgnoreCase(sql, " join ") > 0 && StringUtils.indexOfIgnoreCase(sql, " on ") > 0) { this.errmsg = "join or left join or right join is not supported now."; return; } // ?SQL? if (StringUtils.indexOfIgnoreCase(sql, ".") < 0) { parseSQLSelectBase(); return; } // select,?? if (StringUtils.indexOfIgnoreCase(sql, "select ", 7) > 0) { this.errmsg = "don't support multi-select."; return; } // ???? int is_mutiple_table = checkMutipleTable(sql); // ??db.tablename if (is_mutiple_table == 0) { parseSQLSelectBase(); return; } // ??? if (is_mutiple_table == 1) { parseSQLSelectBase(); return; } // ? if (is_mutiple_table == 2) { this.tag = 1; return; } this.errmsg = "don't support this select"; return; } // ???? // ?from?? // from tablename t where // -1 // 0,??? // 1,??? // 2, private int checkMutipleTable(String sql) { int addr; int length = sql.length(); int i; int start; boolean is_find_as = false; String alias_name = ""; addr = StringUtils.indexOfIgnoreCase(sql, " from "); if (addr < 0) return -1; i = addr + 6; // space while (i + 1 < length && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // table name while (i + 1 < length && !sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // space while (i + 1 < length && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; // tablename as t1? if (i + 3 < sql.length() && sql.substring(i, i + 3).equalsIgnoreCase("as ")) { i = i + 3; is_find_as = true; } // token=where? start = i; while (i + 1 < length && !sql.substring(i, i + 1).equalsIgnoreCase(" ") && !sql.substring(i, i + 1).equalsIgnoreCase(",")) i++; alias_name = sql.substring(start, i).trim(); if (alias_name.equalsIgnoreCase("where")) { return 0; } else { // ??,? while (i + 1 < length && sql.substring(i, i + 1).equalsIgnoreCase(" ")) i++; if (sql.substring(i, i + 1).equalsIgnoreCase(",")) { logger.debug("mutiple tables,this is not support now."); return 2; } // ???,?? logger.debug("alias name:" + alias_name); this.sql = this.sql.replace(" " + alias_name + " ", " "); alias_name = alias_name + "."; this.sql = this.sql.replace(alias_name, ""); if (is_find_as == true) { this.sql = this.sql.replace(" as ", " "); } return 1; } } /* * select column_name,[column_name] from table_name where ? order by * column_name limit #endnum ??select SQL */ private void parseSQLSelectBase() { int i = 0, tmp = 0; int addr_from; int addr_where; int addr_group_by; int addr_order_by; int addr_limit; String wherestr = ""; int loop = 0; logger.debug("SQL at parsing:" + sql); // select if (i + 6 < sql.length() && sql.substring(0, 6).equalsIgnoreCase("select")) { i = i + 6; } else { this.errmsg = "not select SQL statement."; return; } // ?? addr_from = StringUtils.indexOfIgnoreCase(sql, " from "); if (addr_from == -1) { this.errmsg = "not find from key word."; return; } this.select_column = sql.substring(i, addr_from).trim(); selectColumnCheckValid(this.select_column); // ????? addToColumnHashMap(this.select_column, this.columns); logger.debug("select columns:" + this.select_column); // ?table name i = addr_from + 6; addr_where = StringUtils.indexOfIgnoreCase(sql, " where ", i); if (addr_where == -1) { this.errmsg = "don't have where!"; return; } this.tablename = sql.substring(i, addr_where); logger.debug("table name:" + this.tablename); // ?where? i = addr_where + 7; addr_group_by = StringUtils.indexOfIgnoreCase(sql, "group by"); addr_order_by = StringUtils.indexOfIgnoreCase(sql, "order by"); addr_limit = StringUtils.indexOfIgnoreCase(sql, "limit "); if (addr_group_by < 0 && addr_order_by < 0 && addr_limit < 0) { wherestr = sql.substring(i); } else { for (tmp = i; tmp < sql.length() - 8; tmp++) { if (!sql.substring(tmp, tmp + 8).equalsIgnoreCase("group by") && !sql.substring(tmp, tmp + 8).equalsIgnoreCase("order by") && !sql.substring(tmp, tmp + 6).equalsIgnoreCase("limit ")) wherestr = wherestr + sql.substring(tmp, tmp + 1); else { break; } } } // ?where string int wherestr_len = wherestr.length(); wherestr = handleBetweenAnd(wherestr); this.whereNode = this.parseWhere(null, wherestr, loop); // check whereNode tree checkWhereTreeRootNode(whereNode); logger.debug("where condition:" + wherestr); // ??,?handleBetweenAnd??wherestr i = i + wherestr_len; if (i < sql.length()) { if (sql.substring(i, i + 8).equalsIgnoreCase("group by")) { // ????,order by,?? // group by,??having if (StringUtils.indexOfIgnoreCase(sql, "having", i + 8) > 0) { this.groupbycolumn = sql.substring(i + 8, StringUtils.indexOfIgnoreCase(sql, "having", i + 7)) .trim(); } else if (StringUtils.indexOfIgnoreCase(sql, "order by", i + 8) > 0) { this.groupbycolumn = sql.substring(i + 8, StringUtils.indexOfIgnoreCase(sql, "order by", i + 8)) .trim(); } else if (StringUtils.indexOfIgnoreCase(sql, "limit", i + 8) > 0) { this.groupbycolumn = sql.substring(i + 8, StringUtils.indexOfIgnoreCase(sql, "limit", i + 8)) .trim(); } } logger.debug("group by columns:" + this.groupbycolumn); if (StringUtils.indexOfIgnoreCase(sql, "order by", i) >= i) { if (StringUtils.indexOfIgnoreCase(sql, "limit ", i) > StringUtils.indexOfIgnoreCase(sql, "order by", i)) { // ?limit,????,?limit if (this.orderbycolumn.length() > 0) this.orderbycolumn = this.orderbycolumn + "," + sql.substring(StringUtils.indexOfIgnoreCase(sql, "order by") + 8, StringUtils.indexOfIgnoreCase(sql, "limit")); else { this.orderbycolumn = sql.substring(StringUtils.indexOfIgnoreCase(sql, "order by", i) + 8, StringUtils.indexOfIgnoreCase(sql, "limit ")); } } else { // ??limit, if (this.orderbycolumn.length() > 0) this.orderbycolumn = this.orderbycolumn + "," + sql.substring(StringUtils.indexOfIgnoreCase(sql, "order by", i) + 8); else { this.orderbycolumn = sql.substring(StringUtils.indexOfIgnoreCase(sql, "order by", i) + 8); } } this.orderbycolumn = this.orderbycolumn.replace(" asc", " "); this.orderbycolumn = this.orderbycolumn.replace(" desc", " "); } this.orderbycolumn = this.orderbycolumn.replace(" ", ""); logger.debug("order by columns:" + this.orderbycolumn); } } /* * ????? column as alias_column,column as alias_column or * function(column) as alias_column : SELECT CONCAT(last_name,', * ',first_name) AS full_name FROM mytable ORDER BY full_name; * select_expr??AS????? SELECT CONCAT(last_name,', * ',first_name) full_name FROM mytable ORDER BY full_name; */ public static void addToColumnHashMap(String select_exprs, List<Entry<String, String>> entries) { // ? if (select_exprs == null) { return; } select_exprs = select_exprs.toLowerCase(); logger.debug("addToColumnHashMap select_exprs:" + select_exprs); // ?? if (StringUtils.indexOfIgnoreCase(select_exprs, "(") < 0) { String[] array_columns = select_exprs.split(","); for (String array_column : array_columns) { dealSingleSelectExpr(array_column, entries); } return; } // ,??,? int i = 0; int start = 0; int addr_douhao = 0; int douhao_before_left_kuohao; int douhao_before_right_kuohao; String select_expr; while (i < select_exprs.length()) { addr_douhao = StringUtils.indexOfIgnoreCase(select_exprs, ",", i); if (addr_douhao < 0) { // ?select_expr select_expr = select_exprs.substring(start); dealSingleSelectExpr(select_expr, entries); break; } // ???,?? douhao_before_left_kuohao = getWordCountInStr(select_exprs, "(", addr_douhao); douhao_before_right_kuohao = getWordCountInStr(select_exprs, ")", addr_douhao); if (douhao_before_left_kuohao == douhao_before_right_kuohao) { // select_expr select_expr = select_exprs.substring(start, addr_douhao); dealSingleSelectExpr(select_expr, entries); start = addr_douhao + 1; i = start; } else { // ?,? i = addr_douhao + 1; } } } /* * ? */ private static int getWordCountInStr(String str, String symbol, int addr_douhao) { int count = 0; if (str == null || symbol == null || str.length() <= addr_douhao) { return -1; } for (int i = 0; i < addr_douhao; i++) { if (str.substring(i, i + 1).equalsIgnoreCase(symbol)) { count++; } } return count; } /* * ??select_expr column as alias_column or function(column) as * alias_column */ private static void dealSingleSelectExpr(String select_expr, List<Entry<String, String>> entries) { String alias_column_name = ""; String column_name = ""; String word = ""; if (select_expr == null || select_expr.trim().equalsIgnoreCase("")) { return; } logger.debug("dealSingleSelectExpr select_expr:" + select_expr); int k = select_expr.length(); // ?? while (k - 1 >= 0 && !select_expr.substring(k - 1, k).equalsIgnoreCase(" ")) { alias_column_name = select_expr.substring(k - 1, k) + alias_column_name; k--; } if (k == 0) { // ?????? column_name = alias_column_name; entries.add(new Entry<String, String>(alias_column_name, column_name)); logger.debug("column_name:" + column_name + " alias_column_name:" + alias_column_name); return; } // ? while (k - 1 >= 0 && select_expr.substring(k - 1, k).equalsIgnoreCase(" ")) { k--; } // ?as,???? while (k - 1 >= 0 && !select_expr.substring(k - 1, k).equalsIgnoreCase(" ")) { word = select_expr.substring(k - 1, k) + word; k--; } if (!word.equalsIgnoreCase("as")) { column_name = word; logger.debug("column_name:" + column_name + " alias_column_name:" + alias_column_name); entries.add(new Entry<String, String>(alias_column_name, column_name)); return; } // ? while (k - 1 >= 0 && select_expr.substring(k - 1, k).equalsIgnoreCase(" ")) { k--; } // ???? column_name = select_expr.substring(0, k); logger.debug("column_name:" + column_name + " alias_column_name:" + alias_column_name); entries.add(new Entry<String, String>(alias_column_name, column_name)); } /* * ?where?between and */ public String handleBetweenAnd(String wherestr) { String tmp_wherestr = wherestr; String resultString = ""; String column_name; int start = 0; String matchString; int addr, len; if (StringUtils.indexOfIgnoreCase(tmp_wherestr, " between ") < 0) { resultString = tmp_wherestr; } else { // between #value# and#value#? tmp_wherestr = removeSpace(tmp_wherestr); Pattern pattern = Pattern .compile("\\s+[a-zA-Z][0-9_a-zA-Z\\.]+\\s+between\\s+[',:#+\\-0-9_a-zA-Z\\(\\)]+\\sand\\s+"); Matcher matcher = pattern.matcher(tmp_wherestr); while (matcher.find()) { matchString = matcher.group(); len = matchString.length(); addr = StringUtils.indexOfIgnoreCase(tmp_wherestr, matchString); column_name = matchString.trim().substring(0, matchString.trim().indexOf(" ")); // between??>=? matchString = matchString.replace(" between ", " >= "); // and???<=? matchString = matchString + column_name + " <= "; // ?resultString resultString = resultString + tmp_wherestr.substring(start, addr) + matchString; // start? start = addr + len; } // end while // ??SQL if (start < tmp_wherestr.length()) { resultString = resultString + tmp_wherestr.substring(start); } } return resultString; } /* * between #value# and#value#?,# */ public String removeSpace(String tmp_wherestr) { String tmpString = ""; int addr_between = StringUtils.indexOfIgnoreCase(tmp_wherestr, " between "); int addr_and; int start = 0; while (addr_between > -1) { addr_and = StringUtils.indexOfIgnoreCase(tmp_wherestr, " and ", addr_between); tmpString = tmpString + tmp_wherestr.substring(start, addr_between) + " between " + tmp_wherestr.substring(addr_between + 9, addr_and).trim().replaceAll(" ", "#") + " and "; addr_between = StringUtils.indexOfIgnoreCase(tmp_wherestr, " between ", addr_and + 5); start = addr_and + 5; } if (start < tmp_wherestr.length()) { tmpString = tmpString + tmp_wherestr.substring(start); } return tmpString; } private void parseSQLInsert() { // insert SQL logger.debug(sql); int i = 0; int addr_values; String columns; String values; // insert if (sql.substring(0, 6).equalsIgnoreCase("insert")) { i = i + 6; } else { errmsg = "it is not a insert SQL"; return; } // ?into while (sql.substring(i, i + 1).equalsIgnoreCase(" ")) { i++; } if (!sql.substring(i, i + 4).equalsIgnoreCase("into")) { errmsg = "insert sql miss into,syntax error!"; return; } else { i = i + 4; } // ???? while (sql.substring(i, i + 1).equalsIgnoreCase(" ")) { i++; } while (!sql.substring(i, i + 1).equalsIgnoreCase(" ") && !sql.substring(i, i + 1).equalsIgnoreCase("(")) { tablename = tablename + sql.substring(i, i + 1); i++; } logger.debug(tablename); // (col1,col2)values(#col1#,#col2#) addr_values = StringUtils.indexOfIgnoreCase(sql, "values", i); if (addr_values < 0) { errmsg = "not find values key word."; logger.warn(errmsg); return; } // ??,???,? int kuohao_left = StringUtils.indexOfIgnoreCase(sql, "(", i); int kuohao_right = StringUtils.indexOfIgnoreCase(sql, ")", i); if (kuohao_left >= i && kuohao_right > kuohao_left && kuohao_right < addr_values) { columns = sql.substring(kuohao_left + 1, kuohao_right); } else { errmsg = "between tablename and values key word,you must write columns clearly."; logger.warn(errmsg); return; } // values?sysdate(),?? if (StringUtils.indexOfIgnoreCase(sql, "sysdate()", addr_values) > 0) { errmsg = "use sysdate() function,this not allowed,you should use now() replace it."; logger.warn(errmsg); return; } kuohao_left = StringUtils.indexOfIgnoreCase(sql, "(", addr_values); kuohao_right = StringUtils.lastIndexOfIgnoreCase(sql, ")"); values = sql.substring(kuohao_left + 1, kuohao_right); // ??value?,?map<String,String>? String[] array_columns = columns.split(","); String[] array_values = dealInsertValues(values); if (array_columns.length != array_values.length) { errmsg = "insert sql columns is not map with values."; return; } List<Entry<String, String>> entries = new ArrayList<Entry<String, String>>(array_columns.length); for (int j = 0; j < array_columns.length; j++) { entries.add(new Entry<String, String>(array_columns[j], array_values[j])); } this.insertEntries = entries; } private String[] dealInsertValues(String values) { List<String> list_values = new LinkedList<String>(); int addr_douhao; int last_position = 0; addr_douhao = values.indexOf(","); while (addr_douhao > 0) { String tmp_str = values.substring(last_position, addr_douhao); logger.debug("dealInsertValues function:" + tmp_str); if (checkSpecialStr(tmp_str, "'")) { //',value,? //?,? addr_douhao = values.indexOf(",", addr_douhao + 1); } else { //??,? list_values.add(tmp_str); last_position = addr_douhao + 1; addr_douhao = values.indexOf(",", last_position); } } //??value list_values.add(values.substring(last_position)); // String[] str_array = new String[list_values.size()]; for (int i = 0; i < str_array.length; i++) { str_array[i] = list_values.get(i); } return str_array; } /* * ?,a=5 build? parseBase() */ private TreeNode buildTree(TreeNode rootnode, String str, int addr, int offset) { TreeNode node = new TreeNode(); TreeNode left_child_node = new TreeNode(); TreeNode right_child_node = new TreeNode(); // ???? node.node_content = str.substring(addr, addr + offset).trim(); node.node_type = 2; node.parent_node = rootnode; node.left_node = left_child_node; node.right_node = right_child_node; // ? left_child_node.node_content = str.substring(0, addr).trim(); left_child_node.node_type = 1; left_child_node.parent_node = node; left_child_node.left_node = null; left_child_node.right_node = null; // ?? right_child_node.node_content = str.substring(addr + offset).trim(); right_child_node.node_type = 3; right_child_node.parent_node = node; right_child_node.left_node = null; right_child_node.right_node = null; return node; } /* * ??,a=5 a>#abc# */ private TreeNode parseBase(TreeNode rootnode, String str) { int addr; addr = StringUtils.indexOfIgnoreCase(str, ">="); if (addr > 0) { return buildTree(rootnode, str, addr, 2); } addr = StringUtils.indexOfIgnoreCase(str, "<="); if (addr > 0) { return buildTree(rootnode, str, addr, 2); } addr = StringUtils.indexOfIgnoreCase(str, ">"); if (addr > 0) { return buildTree(rootnode, str, addr, 1); } addr = StringUtils.indexOfIgnoreCase(str, "<"); if (addr > 0) { return buildTree(rootnode, str, addr, 1); } addr = StringUtils.indexOfIgnoreCase(str, "!="); if (addr > 0) { return buildTree(rootnode, str, addr, 2); } addr = StringUtils.indexOfIgnoreCase(str, "="); if (addr > 0) { return buildTree(rootnode, str, addr, 1); } addr = StringUtils.indexOfIgnoreCase(str, " in "); if (addr > 0) { // ?in,???,?? // ??? return buildTree(rootnode, str, addr, 4); } addr = StringUtils.indexOfIgnoreCase(str, " like "); if (addr > 0) { return buildTree(rootnode, str, addr, 6); } addr = StringUtils.indexOfIgnoreCase(str, " is "); if (addr > 0) { return buildTree(rootnode, str, addr, 4); } return null; } public TreeNode parseWhere(TreeNode rootnode, String str_where, int loop) { // loop++; if (loop > 10000) return null; String str = str_where.trim(); TreeNode node = new TreeNode(); int addr_and; int addr_or; // ??,?? if (str.substring(0, 1).equalsIgnoreCase("(")) { // ??? // SQL??in,??? Stack<String> stack = new Stack<String>(); int k = 0; String tmp_s; while (k < str.length()) { tmp_s = str.substring(k, k + 1); if (!tmp_s.equalsIgnoreCase(")")) // stack.push(tmp_s); else { // ,?? while (!stack.pop().equalsIgnoreCase("(")) { ; } // ?,,? if (stack.isEmpty()) break; } k++; } // end while if (k == str.length() - 1) { // ?? return parseWhere(rootnode, str.substring(1, k), loop); } else { // ??,and or, if (str.substring(k + 1, k + 6).equalsIgnoreCase(" and ")) { node.node_content = "and"; node.node_type = 4; node.left_node = parseWhere(node, str.substring(1, k), loop); node.right_node = parseWhere(node, str.substring(k + 6), loop); node.parent_node = rootnode; } else if (str.substring(k + 1, k + 5).equalsIgnoreCase(" or ")) { node.node_content = "or"; node.node_type = 4; node.left_node = parseWhere(node, str.substring(1, k), loop); node.right_node = parseWhere(node, str.substring(k + 5), loop); node.parent_node = rootnode; } return node; } } else { addr_and = StringUtils.indexOfIgnoreCase(str, " and "); addr_or = StringUtils.indexOfIgnoreCase(str, " or "); if (addr_and > 0 && addr_or > 0) if (addr_and < addr_or) { // and node.node_content = "and"; node.node_type = 4; node.parent_node = rootnode; node.left_node = parseBase(node, str.substring(0, addr_and).trim()); node.right_node = parseWhere(node, str.substring(addr_and + 5), loop); return node; } else { // or node.node_content = "or"; node.node_type = 4; node.parent_node = rootnode; node.left_node = parseBase(node, str.substring(0, addr_or).trim()); node.right_node = parseWhere(node, str.substring(addr_or + 4), loop); return node; } else if (addr_and > 0) { node.node_content = "and"; node.node_type = 4; node.parent_node = rootnode; node.left_node = parseBase(node, str.substring(0, addr_and).trim()); node.right_node = parseWhere(node, str.substring(addr_and + 5), loop); return node; } else if (addr_or > 0) { node.node_content = "or"; node.node_type = 4; node.parent_node = rootnode; node.left_node = parseBase(node, str.substring(0, addr_or).trim()); node.right_node = parseWhere(node, str.substring(addr_or + 4), loop); return node; } else { // ?? return parseBase(rootnode, str); } } } /* * ? */ public void printTree(TreeNode rootnode) { if (rootnode != null) { System.out.println("NODE ID:" + rootnode.hashCode() + ", NODE CONTENT:" + rootnode.node_content); } if (rootnode.left_node != null) { System.out.println("My PARENT NODE CONTENT:" + rootnode.node_content + ", NODE ID:" + rootnode.hashCode() + ", LEFT CHILD "); printTree(rootnode.left_node); } if (rootnode.right_node != null) { System.out.println("My PARENT NODE CONTENT:" + rootnode.node_content + ", NODE ID:" + rootnode.hashCode() + ", RIGHT CHILD "); printTree(rootnode.right_node); } } }