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:ssyxzname:CSname:SZname:ZWname:CLname: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));?
?
附件中有所有程序
?
?