Java tutorial
/** * (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: * danchen <danchen@taobao.com> * */ package com.taobao.sqlautoreview; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.Iterator; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.log4j.Logger; import org.dom4j.Comment; import org.dom4j.Document; import org.dom4j.DocumentException; import org.dom4j.Element; import org.dom4j.Node; import org.dom4j.io.SAXReader; /* * function:xmlsql, */ public class XmlToSQL { //log4j private static Logger logger = Logger.getLogger(XmlToSQL.class); //SQL MAP FILE id int sqlmap_file_id; //SQL MAP FILE String sqlmapfilename; // HashMap<String, String> hash; // IHandleDB wsdb; // public XmlToSQL(IHandleDB iHandleSQLReviewDB, int sqlmap_file_id, String sqlmapfilename) { this.sqlmap_file_id = sqlmap_file_id; this.sqlmapfilename = sqlmapfilename; this.hash = new HashMap<String, String>(); this.wsdb = iHandleSQLReviewDB; } // public XmlToSQL() { HandleXMLConf sqlmapfileconf = new HandleXMLConf("sqlmapfile.xml"); this.sqlmap_file_id = sqlmapfileconf.getSQLMapFileID(); this.sqlmapfilename = sqlmapfileconf.getSQLMapFileName(); this.hash = new HashMap<String, String>(); this.wsdb = new HandleSQLReviewDB(); } //SQL public static String delByPattern(String str) { Pattern p = Pattern.compile(" {2,}"); Matcher m = p.matcher(str); String result = m.replaceAll(" "); return result; } public static String formatSql(String unFormatSql) { String newSql = unFormatSql.trim().replace("\n", " ").replace("\t", " ").replace("\r", " ").replace("\f", " "); return delByPattern(newSql); } /** * XML * */ private Document loadXml(String path) throws DocumentException, FileNotFoundException { //FileInptStreamFileReader InputStream input = new FileInputStream(path); SAXReader reader = new SAXReader(); Document doc = reader.read(input); return doc; } /* * xml,SQL * 1.<![CDATA[<=]]> * 2.include * 3.prepend */ private String getRealSQL(Element sqlElement) { String sql_xml = sqlElement.asXML(); // int sql_length = sql_xml.length(); //SQL String last_sql = ""; //> int addr_right_kuohao; //prepend int addr_prepend; //<> String sub_sql_xml; //ID String refid; // int skip_step = 0; for (int i = 0; i < sql_length; i++) { if (skip_step > 0) { skip_step--; continue; } if (sql_xml.substring(i, i + 1).equals("<") == true) { addr_right_kuohao = sql_xml.indexOf(">", i); sub_sql_xml = sql_xml.substring(i, addr_right_kuohao); // if (sub_sql_xml.indexOf("![CDATA[") > 0) { addr_right_kuohao = sql_xml.indexOf(">", sql_xml.indexOf("]]", i)); sub_sql_xml = sql_xml.substring(i, addr_right_kuohao); } //, if (sub_sql_xml.indexOf("include") > 0 && sub_sql_xml.indexOf("refid") > 0) { //include refid = sub_sql_xml.substring(sub_sql_xml.indexOf("\"") + 1, sub_sql_xml.lastIndexOf("\"")) .trim(); String refsql = hash.get(refid); if (refsql == null) { if (refid.indexOf(".") > 0 && (hash.get(refid.substring(refid.indexOf(".") + 1))) != null) { refsql = hash.get(refid.substring(refid.indexOf(".") + 1)); last_sql = last_sql + refsql; logger.warn("SQL,."); } else { logger.error("SQL."); } } else { last_sql = last_sql + refsql; } } else if (sub_sql_xml.indexOf("![CDATA[") > 0) { //CDATA last_sql = last_sql + sub_sql_xml.substring(sub_sql_xml.indexOf("![CDATA[") + 8, sub_sql_xml.indexOf("]]")); } else if (sub_sql_xml.indexOf("prepend") > 0) { //prepend addr_prepend = sub_sql_xml.indexOf("prepend"); int addr_first_yinhao = sub_sql_xml.indexOf("\"", addr_prepend); int addr_last_yinhao = sub_sql_xml.indexOf("\"", addr_first_yinhao + 1); last_sql = last_sql + sub_sql_xml.substring(addr_first_yinhao + 1, addr_last_yinhao); } // skip_step = sub_sql_xml.length(); } else { last_sql = last_sql + sql_xml.substring(i, i + 1); } } //end for return last_sql; } /* * SQL,followxml * <sql id=> * SQLhashmap */ @SuppressWarnings("unchecked") private void dealInclude(Element root) { String refid; String refsql; Element sqlElement; for (Iterator<Element> iterator = root.elementIterator(); iterator.hasNext();) { sqlElement = iterator.next(); if (sqlElement.getName().equals("sql") && sqlElement.attributeValue("id") != null) { refid = sqlElement.attributeValue("id"); refsql = getRealSQL(sqlElement); hash.put(refid, refsql); } else { continue; } } } /* * , */ private 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).equals(" ")) { from_addr++; } // if (from_addr > str.length()) { return null; } //token while (from_addr < str.length() && str.substring(from_addr, from_addr + 1).equals(" ") == false) { token = token + str.substring(from_addr, from_addr + 1); from_addr++; } return token; } /* * SQL,, */ private String preDealSql(String sqlString) { int addr_where; int addr_first_and; if (sqlString == null) return null; sqlString = sqlString.toLowerCase(); //whereand addr_where = sqlString.indexOf(" where "); if (addr_where > 0 && addr_where + 7 < sqlString.length()) { if (getNextToken(sqlString, addr_where + 7).equals("and")) { //whereand addr_first_and = sqlString.indexOf("and", addr_where + 7); sqlString = sqlString.substring(0, addr_first_and) + sqlString.substring(addr_first_and + 3); } } sqlString = sqlString.replace(">", ">"); sqlString = sqlString.replace("<", "<"); return sqlString; } /** * SQL MAP,SQL * * @throws DocumentException */ private void readSqlMap() throws DocumentException { Element root; if (sqlmapfilename == null) return; try { Document dom = loadXml(sqlmapfilename); root = dom.getRootElement(); if (root == null) { logger.error("can not find sql map file xml root node,sqlautoreview program exit."); return; } } catch (FileNotFoundException e) { logger.error("the sql-map-file don't exist,please check the path."); return; } // SQL dealInclude(root); //Element Element sqlElement = null; // int max_loop_count = 0; //SQL String real_sql; //SQL String commentString = ""; // for (int i = 0; i < root.nodeCount(); i++) { //,100000, max_loop_count++; if (max_loop_count > 100000) { logger.error( "the sql map file has more than 100000 sqls.Sql reveiw exit. Please check the sql map file."); break; } Node node = root.node(i); if (node instanceof Element) { sqlElement = (Element) node; } else { continue; } //SQL:select,update,delete,insert if (sqlElement.getName().equals("select") || sqlElement.getName().equals("update") || sqlElement.getName().equals("delete") || sqlElement.getName().equals("insert")) { real_sql = formatSql(getRealSQL(sqlElement)); //SQL real_sql = preDealSql(real_sql); logger.info("java class id=" + sqlElement.attributeValue("id") + " sql=" + real_sql); //, real_sql = real_sql.replace("'", "''"); //sql_xml String sql_xml = sqlElement.asXML(); sql_xml = sql_xml.replace("'", "''"); //SQL MAPcomment if (root.node(i - 1) != null && root.node(i - 1) instanceof Comment) { commentString = root.node(i - 1).asXML(); commentString = commentString.replace("'", "''"); } // wsdb.insertDB(sqlmap_file_id, sqlElement.attributeValue("id"), sql_xml, real_sql, commentString); } commentString = ""; } } /* * */ public void xmlToSqlService() throws DocumentException, IOException { readSqlMap(); } /** * @param args * @throws DocumentException * @throws IOException */ public static void main(String[] args) throws DocumentException, IOException { XmlToSQL xts = new XmlToSQL(); if (xts.wsdb.checkConnection() == false) { logger.error("sqlreviewdb,."); return; } logger.info("SQLMAP FILE"); xts.readSqlMap(); logger.info("SQLMAP FILE"); } }