用正则表达式提取SQL

本文记录一个用正则解决问题的过程。较多的是思路的笔记。

 

需求:   

    分析项目中 PL/SQL的表使用状况。也就是哪个程序对那些表做了增删改查。   

 

【初步分析】

    乍一看视乎不难，INSERT/UPDATE/DELETE 语句表名比较好根据位置确定，SELECT查询 FROM 关键字后面的字符串并分析，但是实际上这种方法有很大问题，首先是子查询可能出现在from节中，这种情况几乎没办法用程序分析。其次，from节的结尾判定也有很多种，不易考虑周全。

    最终确定一个可行的方案是将完整的SQL语句抽出，再利用sql语法树进行分析。 
       
【用于抽出SQL的正则表达式分析】
    DML语句都是以 SELECT/INSERT/UPDATE/DELETE 开始，以“;”结尾，但是子查询的情况比较复杂。   
    SELECT/INSERT/UPDATE/DELETE语句中都有可能出现子查询，而INSERT/UPDATE/DELETE 中有子查询可以理解为第一个出现的表名为INSERT/UPDATE/DELETE对象表，其他为 子查询对象。   
    SELECT子查询时由“(”和“)”包裹，如果抽出会造成括号不匹配，需要防止“(”后的SELECT子句被抽出 。  
    最终得到的 抽出各语句按如下正则式进行抽出。

public enum SqlDmlEnum {
	
	SELECT("(?<!\\(\\s?)select(\\s)+.*?;"),
	INSERT("(\\s)+insert(\\s)+.*?;"),
	UPDATE("(\\s)+update(\\s)+.*?;"),
	DELETE("(\\s)+delete(\\s)+.*?;");

	private final String strReg;
	private final Pattern p;

	SqlDmlEnum(String strIn) {
		this.strReg = strIn;
		this.p = Pattern.compile(strReg, Pattern.CASE_INSENSITIVE|Pattern.DOTALL);
	}

	public List<String> extractSqls(String sqlFile) {
		Matcher m = p.matcher(sqlFile);
		List<String> list = new ArrayList<String>();
		while (m.find()) {
			list.add(m.group(0));
		}
		return list;
	}
}

注：“(?<!\\(\\s?)”左括号后面有可能有空格，本来空格数量是未定的，JAVA的逆序环视不支持不确定长度的匹配是个头痛的问题。所以简便的方法是匹配前读入阶段要将多余的空白字符删除（有多个时只留1个），就可以这样简单的匹配了。

       
【解析器选择】        
    sql的解析是最重要的一步，自己写解析树需要较多时间，还容易考虑不周。立马开始在开源项目中筛选，遗憾的是没有发现比较成熟的。最终还是选了商业化的gsp（http://www.sqlparser.com/）。作为开源控不能不说相当遗憾，今后能找到了就换。

    测试了一下功能比较强大，多重嵌套的子sql都能比较完美地解析。

    唯一发现的可能出问题的限制是 解析的SQL中的注释，需要在预处理时解决掉。   
       
【读入文本的处理（预处理）】
    这里也较多地使用了正则，首先为简化后面的处理，将回车，换行符替换为空格。

    多余的空白字符删除（有多个时只留1个）

        string.replaceAll("\\s+", " ")  
    最重要的是删除注释部分。   
    ·单行注释有两种：   
        “--”开头的字符串切除（之前前面的内容保留）

           string.replaceFirst("--.*", "")
        “REM”开头的字符串，整行忽略
    ·多行注释的删除。 如 “/*???*/”   
           string.replaceAll("/\\*(?<!\\*/).*?\\*/", "")
    ·输出消息命令 prompt，也需要删除，方法与“REM”类似。  

 

public static String readFile(String filePath) throws IOException {
	InputStream is = new FileInputStream(filePath);
	BufferedReader reader = new BufferedReader(new InputStreamReader(is));
	StringBuffer sb = new StringBuffer();
	String line = reader.readLine();
	while (line != null) {
		//注释行(以REM开头)及 输出命令（prompt）所在行跳过
		if (!(line.matches("(?i)^REM") || line.matches("(?i)^PROMPT"))){
			//单行注释删除（注意只删注释部分："--"之后的字符串）
			//为简化后面的解析处理，将换行符转为空格
			sb.append(line.replaceFirst("--.*", "")).append(" ");
		}
		line = reader.readLine();
	}
	reader.close();
	is.close();
	//使用replaceAll("\\s+", " ") 删除多余的空格
	//使用replaceAll("/\\*(?<!\\*/).*?\\*/", "") 删除多行注释
	return sb.toString().replaceAll("\\s+", " ").replaceAll("/\\*.*?\\*/", "");
}

 

 
【其他】        
    代码中表名使用变量的部分，在excel报表中标记颜色体现。    
     个别SQL解析失败的（极少数有的话），将SQL 输出到log中。    
     IO异常不是关注重点，只做了简单抛出处理。

     使用到的第三方jar包：       
        gsp.jar    (90天免费试用期 官网http://www.sqlparser.com/  )
        log4j-1.2.17.jar   
        poi-3.7-20101029.jar   

 

【总结】

    很久没这么多使用正则了，导致初期调试费了不少周折，由于使用了多个正则表达式辅助，整个程序比较精简。JAVA的逆序环视对确定长度的限制有时比较麻烦的，如果有可能用变通的做法将目标转换为到容易确定的状态，不失为一个好的解决办法。