Java解析sql语句，分析出调用到的所有表

基本思想：
１。对简单的sql语句，类似select A.a, B.b from A, B left join C on b.b = C.c where ...
这种不包含子查询，没有ｕｎｉｏｎ／ｍｉｎｕｓ进行关联的语句，只要根据关键词来分析就行了。先把他们ｊｏｉｎ替换成‘，’以便统一分析。从而将问题简化为解析ｆｒｏｍ关键词后面的ｔａｂｌｅ名字，多表的话，一定是用＇，＇隔开的，这样就好分析了。
２。对于包含子查询，ｕｎｉｏｎ／ｍｉｎｕｓ来关联的语句，把他们先分解成两部分，除去子查询或者ｕｎｉｏｎ／ｍｉｎｕｓ关联的那部分，属于简单ｓｑｌ语句，用方法１就行了。对于子查询或者ｕｎｉｏｎ／ｍｉｎｕｓ关联的那部分，采用递归。最终还是化解到方法１莱解决。
３。对于子查询，需要递归到下一层，那么在当前这一层，用ｓｕｂＴａｂｌｅ来进行替代，以保证当前简单ｓｑｌ语句的完整性。最后统计ｔａｂｌｅ的时候，对于ｓｕｂＴａｂｌｅ就不算进去了。

详细见一下代码：

package com.sqlparser;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashSet;
import java.util.Iterator;

public class TableQuerier {
	public static String startKey = "SELECT ";
	public static String fromKey = " FROM ";
	public static String joinKey = " JOIN ";
	public static String onKey = " ON ";
	public static String endKey = " WHERE ";
	public static String unionKey = " UNION ";
	public static String minusKey = " MINUS ";
	public String subTable = "subTable";
	private HashSet tableSet;
	private String fileName;

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TableQuerier tq = new TableQuerier("c://data.txt");
		HashSet s = tq.getTableSet();
		tq.parseTableFromFile();
		Iterator i = s.iterator();
		System.out.println("The following tables are included in this sql statement.");
		int num = 1;
		System.out.println("Number#     tableName");
		while(i.hasNext()){
			System.out.println(num+" "+i.next());
			num++;
		}
		
	}
	public void parseTableFromFile(){
		BufferedReader buf = null;
		try {
			buf = new BufferedReader(new FileReader(fileName));
			
			String line = buf.readLine();
			
			StringBuilder sb = new StringBuilder();
			
			while(line != null){
				sb.append(line.toUpperCase()+" ");
				line = buf.readLine();
			}
			//System.out.println(sb.toString());
			
			getLevelOneTables(sb.toString(), tableSet);
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				buf.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	public TableQuerier(String fileName){
		tableSet = new HashSet();
		this.fileName = fileName;
	}
	
	public void getLevelOneTables(String str, HashSet s){
		if("".equals(str)){
			return;
		}
		//remove sub query
		int firstLeftBlockPos = str.indexOf("(");
		int lastRightBlockPos = -1;
		
		while(firstLeftBlockPos != -1){
			//get matched ')', remove the substring between them
			int leftBlockNum = 1;
			for(int start = firstLeftBlockPos +1; start < str.length(); start++){
				if(str.charAt(start) == ')'){
					if(leftBlockNum == 1){
						lastRightBlockPos = start;
						break;
					}else{
						leftBlockNum --;
					}
				}else if(str.charAt(start) == '('){
					leftBlockNum ++;
				}
			}
			if(lastRightBlockPos < 0){
				try {
					throw new Exception("block mismatch");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			String blockContent = str.substring(firstLeftBlockPos+1, lastRightBlockPos);
			
			
			//parse the content in block
			
			if(blockContent.contains(startKey)){
				getLevelOneTables(blockContent, s);
				str = str.substring(0,firstLeftBlockPos)+" "+subTable+" "+str.substring(lastRightBlockPos+1);
			}else{//remove '(' and ')' at level one
				str = str.substring(0,firstLeftBlockPos)
						+str.substring(firstLeftBlockPos+1,lastRightBlockPos)
				 		+str.substring(lastRightBlockPos +1);
			}
			firstLeftBlockPos = str.indexOf("(");
		}
		//if the statement is joined with 'UNION' or 'MINUS', 
		//parse the first segement left of the key word('UNION' or 'MINUS')
		int unionPos = -1;
		int minusPos = -1;
		int breakPos = -1;
		unionPos = str.indexOf(unionKey);
		minusPos = str.indexOf(minusKey);
		if(unionPos != -1 ||minusPos != -1){
			if(unionPos != -1 && minusPos != -1){
				breakPos = minusPos < unionPos ? minusPos:unionPos;
			}else{
				breakPos = minusPos < unionPos ? unionPos:minusPos;
			}
		}
		
		if(breakPos != -1){
			String rightContent = str.substring(breakPos+unionKey.length()).trim();
			rightContent = rightContent.startsWith("ALL")?rightContent.substring(3):rightContent;
			getLevelOneTables(rightContent, s);
			str = str.substring(0, breakPos).trim();
		}
		//change join to ','
        //		if join table
		int joinKeyPos = str.indexOf(joinKey);
		if(joinKeyPos != -1){
			str = str.replace(joinKey, ",");
		}
		
		//now, we can look for tables in a simple statement
		int fromPos = str.indexOf(fromKey);
		int endPos = str.indexOf(endKey);
		if(endPos == -1){
			endPos = str.length();
		}
		if(fromPos > 0){
			str = str.substring(fromPos+fromKey.length(), endPos).trim();
		}else{
			return;
		}
		//System.out.println("modified str  = "+str);
		String tableName = str.indexOf(" ") > 0?str.substring(0,str.indexOf(" ")).trim():str.trim();//the first word after 'from' is a table
		if(!subTable.equals(tableName)){
			s.add(tableName);
		}
		
		str = str.substring(str.indexOf(tableName)+tableName.length()).trim();
		//if multiple table after 'from'
		int dotIndex = str.indexOf(",");
		while(dotIndex != -1){
			str = str.substring(dotIndex+1).trim();
			tableName = str.indexOf(" ")>0?str.substring(0,str.indexOf(" ")).trim():str.trim();//the first word after ',' must be a table name
			if(!subTable.equals(tableName)){
				s.add(tableName);
			}
			str = str.substring(str.indexOf(tableName)+tableName.length()).trim();
			dotIndex = str.indexOf(",");
		}
	}
	
	
	/**
	 * @return the fileName
	 */
	public String getFileName() {
		return fileName;
	}
	/**
	 * @param fileName the fileName to set
	 */
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
	/**
	 * @return the tableSet
	 */
	public HashSet getTableSet() {
		return tableSet;
	}
	/**
	 * @param tableSet the tableSet to set
	 */
	public void setTableSet(HashSet tableSet) {
		this.tableSet = tableSet;
	}
}

这是针对一条ｓｑｌ语句的解析方法。下次有时间可以改进成针对存储过程，函数的解析方法。

未经大量测试，如果有ｂｕｇ请告知。谢谢：）

评论

4 楼 mail_j 2013-04-07  

不是很好用，很多情况都不能处理
1、没有区分关键字的大小写
2、关键字前后必须是空格，对于\t \n 等情况都不能匹配

3 楼 幻影之蚀 2011-08-25  

其实有一个包叫JSQLParser……

2 楼 seya 2010-04-21  

lxr0530 写道

请问
String url2 ="select ISNB,title,sale_id from book,sales" +     "where book.book_id=sales.book_id";
String url2 ="select ISNB,title,sale_id from book,sales" +     "on book.book_id=sales.book_id";
这样写有什么问题？总是出现如下错误
找了很多资料都没搞明白，谢谢回答

晕，“where”前面要空格的。。。不然跟sales连在一起了。

1 楼 lxr0530 2010-04-21  

请问
String url2 ="select ISNB,title,sale_id from book,sales" +     "where book.book_id=sales.book_id";
String url2 ="select ISNB,title,sale_id from book,sales" +     "on book.book_id=sales.book_id";
这样写有什么问题？总是出现如下错误
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 1 行: '.' 附近有语法错误。
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery(Unknown Source)
at TestSQLServer.main(TestSQLServer.java:15)

找了很多资料都没搞明白，谢谢回答