通过java程序抽取日志中的sql语句

今天在翻看以前的笔记时，发现自己在很早之前写过一个java程序，能够解析日志中的sql语句。
当时使用的环境是weblogic,日志目录下总是有几十上百个日志文件，有时候排查问题的时候只需要找到对应的DML语句即可。
使用linux命令固然也可以，但是解析的时候还是比较被动，不能够正确地解析出sql语句来。比如日志中出现insert的字样可能只是日志中的一段信息，不是insert语句。
这些通过linux命令来完成还是有一定的难度，记得当时问题比较多，自己也饱受这种困扰。于是写了一个java程序来。
代码如下：

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;

public class LogToSqlMain {
?private static String SELECT = "SELECT";
?private static String UPDATE = "UPDATE";
?private static String DELETE = "DELETE";
?private static String INSERT = "INSERT";
?private static String ALL = "ALL";

?public static void main(String[] args) {
??new LogToSqlMain().parse(args);
?}

?public void test(File logFile) {
??// get file
??// initialized io
??// parse log to sql

??// format sql
??// generate sql file
??// invoke jdbc
?}

?public void parse(String[] args) {
??String args0 = null;
??String args1 = null;
??if (args == null) {
???return;
??}
??if (args != null && args.length == 1) {
???args0 = args[0];
??}
??if (args != null && args.length == 2) {
???args0 = args[0];
???args1 = args[1];
???if (!args1.equalsIgnoreCase(ALL) && !args1.equalsIgnoreCase(SELECT)
?????&& !args1.equalsIgnoreCase(UPDATE) && !args1.equalsIgnoreCase(DELETE)
?????&& !args1.equalsIgnoreCase(INSERT)) {
????return;
???}
??}

??BufferedReader buffer_reader = null;
??String sql_type = null;
??try {
???File file = new File(args0);
???File[] filesOfDirs = file.listFiles();
???if (!file.isDirectory() || filesOfDirs.length == 0) {
????System.out.println("invalid path or io error");
????return;
???}
???String temp_read = null;
???String strTemp = null;
???String strTimeStamp = null;
???Long log_Line_Num = null;
???for (int i = 0; i < filesOfDirs.length; i++) {
????if (getSqlMode(args1, filesOfDirs[i].getName())) {
?????File tmp_File = filesOfDirs[i].getAbsoluteFile();
?????sql_type = getSQLType(tmp_File);
?????buffer_reader = new BufferedReader(new FileReader(tmp_File));
?????temp_read = buffer_reader.readLine();
?????while (temp_read != null) {
??????char sep_str = ‘:‘;
??????// validate every line should be SQL
??????if (validateFileLine(temp_read, sql_type)) {
???????log_Line_Num = Long.parseLong(temp_read.substring(
?????????0, temp_read.indexOf(sep_str)));
???????strTemp = temp_read.substring(temp_read
?????????.indexOf(‘:‘) + 1);
???????strTimeStamp = strTemp.substring(
?????????strTemp.indexOf(‘[‘) + 1,
?????????strTemp.indexOf(‘]‘));
???????strTemp = strTemp
?????????.substring(strTemp.indexOf(‘]‘) + 1);
???????String temp_Sql = strTemp.substring(strTemp
?????????.indexOf(sql_type + " "));
???????System.out.println(sql_type + log_Line_Num + ","
?????????+ strTimeStamp + "," + temp_Sql);
??????}
??????temp_read = buffer_reader.readLine();
?????}
?????buffer_reader.close();
????}
???}
??} catch (NumberFormatException e) {
???e.printStackTrace();
??} catch (IOException e) {
???e.printStackTrace();
??} finally {
???if (buffer_reader != null) {
????try {
?????buffer_reader.close();
????} catch (IOException e) {
?????e.printStackTrace();
????}
???}
??}

?}

?private boolean validateFileLine(String str_Line, String sql_type) {
??if (sql_type.equals(INSERT)) {
???if (str_Line.toUpperCase().contains("INTO")
?????&& str_Line.toUpperCase().contains("VALUES"))
????return true;
??} else if (sql_type.equals(SELECT)) {
???if (str_Line.toUpperCase().contains("FROM")
?????&& str_Line.toUpperCase().contains("WHERE")) {
????return true;
???}
??} else if (sql_type.equals(UPDATE)) {
???if (str_Line.toUpperCase().contains("SET")
?????&& str_Line.toUpperCase().contains("WHERE")) {
????return true;
???}
??} else if (sql_type.equals(DELETE)) {
???if (str_Line.toUpperCase().contains("WHERE")) {
????return true;
???}
??}
??return false;
?}

?private String getSQLType(File file) {
??if (file.getName().toUpperCase().contains(SELECT)) {
???return SELECT;
??} else if (file.getName().toUpperCase().contains(UPDATE)) {
???return UPDATE;
??} else if (file.getName().toUpperCase().contains(DELETE)) {
???return DELETE;
??} else if (file.getName().toUpperCase().contains(INSERT)) {
???return INSERT;
??}
??return null;
?}

?public boolean getSqlMode(String args, String fileName) {
??if (args == null || ALL.equalsIgnoreCase(args))
???return (fileName.startsWith(INSERT) || fileName.startsWith(DELETE)
?????|| fileName.startsWith(UPDATE) || fileName
??????.startsWith(SELECT));
??if ((SELECT).equalsIgnoreCase(args)) {
???return (fileName.startsWith(SELECT));
??} else if ((UPDATE).equalsIgnoreCase(args)) {
???return (fileName.startsWith(UPDATE));
??} else if ((INSERT).equalsIgnoreCase(args)) {
???return (fileName.startsWith(INSERT));
??} else if ((DELETE).equalsIgnoreCase(args)) {
???return (fileName.startsWith(DELETE));
??}
??return false;
?}
}

如果需要得到a.log中的sql语句，
就可以这样调用java程序 java LogToSqlMain a.log INSET
如果想得到所有的sql语句 java LogToSqlMain a.log ALL

文章来源 http://blog.itpub.net/23718752/viewspace-1388811/

?

获取【下载地址】

通过java程序抽取日志中的sql语句

原文：http://qq-24871163.iteye.com/blog/2174463