java检查sql语法是否正确

由于之前做的项目 业务逻辑不能用系统定义的方案做计算 只能是让用户自己输入参数值设置比例计算规则 系统提供sql验证 在一般的项目中也很少用到这种类型的做法 如下：

 

import java.util.List;

public class RuleMain {

	/** 测试 sql语法
	 * @param args
	 */
	public static void main(String[] args) {
		//定义sql 这是由用户动态输入
String s = "ssyxz = '101' and ( CS = '1' and SZ = 0) and ZW <= 12 and CL >= 1 and CXZK < 88";
		RuleParser parser=new RuleParser();
		try {
			//检查sql语法是否存在错误
			parser.analyze(s);
			// 获取sql中的所有变量
			List<String> variables = parser.getVariables();
			for (String string : variables) {
				System.out.println("name:"+string);
			}
		} catch (RuleParserException e) {
			System.out.println(e.getMessage());
//			e.printStackTrace();
		}
	}

}

 

 执行输出

 

name:ssyxz
name:CS
name:SZ
name:ZW
name:CL
name:CXZK

 

规则解析类 RuleParser  自己实现

 

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import antlr.InputBuffer;
import antlr.LexerSharedInputState;
import antlr.ParserSharedInputState;
import antlr.RecognitionException;
import antlr.TokenBuffer;
import antlr.TokenStreamException;

public class RuleParser {
	
	//定义标准的sql查询语句
	private static String prefixCondition=new String("select * from tableName where ");
	public static int position_offset;
	static{
		position_offset=-prefixCondition.length();
	}
	private List<String>variables=new ArrayList<String>();
	
	public void analyze(String statement) throws RuleParserException {
		try {
			SqlLexer lexer = new SqlLexer(new StringReader(new StringBuffer(prefixCondition)
                        .append(statement).toString()));
			SqlParser parser = new SqlParser(lexer);
			//启动sql规则验证
			parser.start_rule();
			//获取错误集合
			List<Exception> errorPool = parser.getErrorPool();
			//是否存在错误 
			if(errorPool.size()>0){
				for (Exception ex : errorPool) {
					if(ex instanceof RecognitionException){
						throw (RecognitionException)ex;
					}
					if(ex instanceof TokenStreamException){
						throw (TokenStreamException)ex;
					}
				}
			}
			//获取所有变量
			Set<String> columnNameSet = parser.getColumnNameSet();
			variables.addAll(columnNameSet);
			
		} catch (RecognitionException e) {
			throw new RuleParserException(e);
		} catch (TokenStreamException e) {
			throw new RuleParserException(e);
		}
	}
	
	public List<String> getVariables() {
		return variables;
	}

}

 

 sql分析类 SqlLexer 指截图了一部分

 

import java.io.InputStream;
import antlr.TokenStreamException;
import antlr.TokenStreamIOException;
import antlr.TokenStreamRecognitionException;
import antlr.CharStreamException;
import antlr.CharStreamIOException;
import java.io.Reader;
import java.util.Hashtable;
import antlr.InputBuffer;
import antlr.ByteBuffer;
import antlr.CharBuffer;
import antlr.Token;
import antlr.RecognitionException;
import antlr.NoViableAltForCharException;
import antlr.TokenStream;
import antlr.ANTLRHashString;
import antlr.LexerSharedInputState;
import antlr.collections.impl.BitSet;

public class SqlLexer extends antlr.CharScanner implements SqlTokenTypes, TokenStream
 {
public SqlLexer(InputStream in) {
	this(new ByteBuffer(in));
}
public SqlLexer(Reader in) {
	this(new CharBuffer(in));
}
public SqlLexer(InputBuffer ib) {
	this(new LexerSharedInputState(ib));
}
//sql关键字定义
public SqlLexer(LexerSharedInputState state) {
	super(state);
	caseSensitiveLiterals = false;
	setCaseSensitive(false);
	literals = new Hashtable();
	literals.put(new ANTLRHashString("round", this), new Integer(40));
	literals.put(new ANTLRHashString("initcap", this), new Integer(45));
	literals.put(new ANTLRHashString("vsize", this), new Integer(82));
	literals.put(new ANTLRHashString("all", this), new Integer(20));
	literals.put(new ANTLRHashString("sqrt", this), new Integer(42));
	literals.put(new ANTLRHashString("replace", this), new Integer(49));
	literals.put(new ANTLRHashString("count", this), new Integer(61));
	literals.put(new ANTLRHashString("nvl", this), new Integer(79));
	literals.put(new ANTLRHashString("sum", this), new Integer(65));
	literals.put(new ANTLRHashString("hextoraw", this), new Integer(69));
	literals.put(new ANTLRHashString("soundex", this), new Integer(52));
	literals.put(new ANTLRHashString("chartorowid", this), new Integer(67));