项目里需要扫代码的sql,主要是想找出所有用到的sql。 将相关的sql 提交给DBA 来分析,希望在最早的时间发现潜在的查询性能问题。
想想eclipse 里面用到的JDT 能分析java 源代码, 如果我们能分析项目里的源代码利用ASTParser 就可以找到相关的SQL 定义了。
其实已经有人想到这个 http://www.programcreek.com/2011/01/a-complete-standalone-example-of-astparser
,已经自己整理过所有会依赖的jar,机器重装本地的 repository给丢了。 所有就直接用这个链接里面给的一个zip 包, 但是在我现在用到的eclipse 还需要 一个 org.eclipse.text_3.5.0.jar 。
为了这个代码不要弄丢了,直接放一份 当然没有项目里面的信息
package com.bwang.jdt;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.List;
import org.eclipse.jdt.core.dom.AST;
import org.eclipse.jdt.core.dom.ASTNode;
import org.eclipse.jdt.core.dom.ASTParser;
import org.eclipse.jdt.core.dom.AbstractTypeDeclaration;
import org.eclipse.jdt.core.dom.BodyDeclaration;
import org.eclipse.jdt.core.dom.CompilationUnit;
import org.eclipse.jdt.core.dom.FieldDeclaration;
public class SqlExtractor {
private BufferedWriter writer;
private long counter = 0;
private final String fileRoot;
private final String logFileName;
public static void main (String[] args) {
if (args.length < 2) {
System.out.println("Please provide the parameters:");
System.out.println("SqlExtractor codeOfRoot logFileName");
System.exit(-1);
}
SqlExtractor sqlExtractor = new SqlExtractor(args);
sqlExtractor.extractSql();
}
SqlExtractor(String[] args) {
fileRoot = args[0];
logFileName = args[1];
try {
writer = new BufferedWriter(new FileWriter(new File(logFileName)));
} catch(IOException e) {
}
}
private void analyzeOneFile(String sourceFile, Writer writer) {
String source = getFileContent(sourceFile);
ASTParser parser = ASTParser.newParser(AST.JLS3);
parser.setSource(source.toCharArray());
CompilationUnit unit = (CompilationUnit)parser.createAST(null);
unit.recordModifications();
StringBuilder sb = new StringBuilder();
List types = unit.types();
for (AbstractTypeDeclaration type : types) {
if (type.getNodeType() == ASTNode.TYPE_DECLARATION) {
// Class def found
List bodies = type.bodyDeclarations();
for (BodyDeclaration body : bodies) {
if (body.getNodeType() == ASTNode.FIELD_DECLARATION /*&&
((FieldDeclaration)body).getType().equals(Type.S) */) {
FieldDeclaration field = (FieldDeclaration)body;
if(field.toString().toLowerCase().indexOf("select") >=0) {
sb.append(field.toString());
counter++;
}
}
}
}
}
if (sb.length() > 0) {
try {
if (counter > 0) {
writer.append("\n\n");
}
sourceFile = sourceFile.replace(fileRoot, "");
sourceFile = sourceFile.replace("\\", "/");
writer.append(sourceFile + "\n");
String content = sb.toString();
content = content.replace("\" +", "\"\n\t\t+ ");
content = content.replace("\"+", "\"\n\t\t+ ");
writer.append(content);
}catch(IOException ioe) {
}
}
}
private void extractSql() {
File folder = new File(fileRoot);
List poms = new ArrayList();
handleOneFolder(folder, poms);
System.out.println("begin to analyze files of :" + poms.size());
try {
int index = 1;
for(File f : poms) {
System.out.println("begin to analyze file : " + index + " " + f.getName());
analyzeOneFile(f.getPath(), writer);
index++;
}
}
finally {
try {
writer.flush();
writer.close();
} catch(IOException e) {
}
}
System.out.println("successfully find sql count:" + counter);
}
private void handleOneFolder(File folder, List poms) {
if(!folder.isDirectory()) {
return;
}
String[] files = folder.list();
for(String oneFile : files) {
String fullFileName = folder.getPath() + "/" + oneFile;
if (fullFileName.endsWith(".java") && fullFileName.indexOf("src\\test\\java\\") < 0) {
poms.add(new File(fullFileName));
continue;
}
File f = new File(fullFileName);
if (f.isDirectory()) {
handleOneFolder(f, poms);
}
}
}
private String getFileContent(String fName) {
try {
File file = new File(fName);
BufferedReader in = new BufferedReader(new FileReader(file));
StringBuffer buffer = new StringBuffer();
String line = null;
while (null != (line = in.readLine())) {
buffer.append("\t" + line);
buffer.append("\n");
}
return buffer.toString();
}
catch (IOException e) {
throw new RuntimeException(e);
}
}
}
思路也简单就是遍历 codeOfRoot 下面的所有java 文件, 找到 所有的field 定义的值里面包含 select 关键字的 field, 然后将所有这些满足条件的都写到一个log 文件。
到时候将log 文件发给DBA 就行了。 当然还可以更进一步来个CI, 可以定期去扫项目的代码, 然后比较产生的log 文件的变化。 然后DBA 定期看 变化的SQL 就好了, 是不是很酷。