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));

 

 

附件中有所有程序

 

 

评论

9 楼 thinkInJava_rKing 2017-07-13  

insert ... select...也不行

8 楼 thinkInJava_rKing 2017-07-13  

特别不支持各种join查询

7 楼 thinkInJava_rKing 2017-07-13  

你这个不支持嵌套 + 各种join union 查询

6 楼 duxiullf 2016-06-01  

可否提供.g文件看下，谢谢！

5 楼 baicaiyu 2015-12-01  

支持update、delete、select等校验，你需要进行设置标准sql类型就可以支持。
例如设置 update:
String prefixCondition=new String("update tableName where ");
再校验where条件，字段可以从数据库中查询该表的字段再和当前抽取的字段进行校验比对，挺好的，非常感谢！

4 楼 youfeelthat 2015-02-13  

只能是select语句吗

3 楼 jkfd 2014-06-17  

中文查询条件不支持，例如：select * from table where column = '中文'。博主是否可以修正这个问题？

2 楼 gaoran2008 2013-07-10  

可否提供g文件学习下，在下载的里面没有发现。
谢谢了

1 楼 TUIOP1234 2012-07-18