天天在写sql，一直很好奇sql到底咋解析的，于是随便整个小demo。
比如sql是：
String sql = "select name,score,sex from users where name = 'jack' and isdelete <> 1 ; ";
中间可能有1个或者多个空格，就想着怎么着也得有个分词的类，就叫做Tokenizer吧。
public class Tokenizer implements Iterator<String> { String[] tokens; int index = 0; public Tokenizer(String sql) throws BadSqlGrammarException { sql = sql.trim(); if (!sql.endsWith(";")) { throw new BadSqlGrammarException("SQL未正确结束！"); } //去除多余的空格 sql = sql.replace(";", " ;").replaceAll("\\s+", " "); //分词 tokens = sql.split(" "); } @Override public boolean hasNext() { return index < tokens.length; } @Override public String next() { return tokens[index++]; } }
实现Iterator是因为需要去遍历维护的String数组，方便后面解析。
然后是parser类，里面聚合一个Tokenizer的引用。
public class Parser { Tokenizer tokenizer; DBCmd cmd; Map<String, DBCmd> cmdMap = new HashMap<>(); public Parser(String sql) throws BadSqlGrammarException { //用查表法，代替一大堆的if else this.cmdMap.put("select", new SelectCmd()); this.tokenizer = new Tokenizer(sql); //根据第一个sql关键字来确定是什么sql命令 this.cmd = this.cmdMap.get(tokenizer.next()); if (cmd == null) throw new BadSqlGrammarException("未识别的sql命令！"); } public void query() throws BadSqlGrammarException { cmd.query(tokenizer); } }
DBCmd做成一个抽象类，不同的命令需要单独设计一个类，去继承他。DBCmd就弄一些通用的方法即可。
public abstract class DBCmd { public abstract void query(Tokenizer tokenizer) throws BadSqlGrammarException; protected String splitUntilEnd(Tokenizer tokenizer) throws BadSqlGrammarException { return splitUntil(tokenizer, ";"); } protected String splitUntil(Tokenizer tokenizer, String until) throws BadSqlGrammarException { StringBuffer sb = new StringBuffer(); boolean find = false; while (tokenizer.hasNext()) { String next = tokenizer.next(); if (!next.equals(until)) { sb.append(next).append(" "); continue; } else { find = true; break; } } if (!find) throw new BadSqlGrammarException("语法不正确"); return sb.toString(); } }
只测试一下select语法，所以只写一个select操作类。
public class SelectCmd extends DBCmd { @Override public void query(Tokenizer tokenizer) throws BadSqlGrammarException { String querys = splitUntil(tokenizer, "from"); String tableName = tokenizer.next(); String condition = null; if (tokenizer.hasNext() && tokenizer.next().equals("where")) { condition = splitUntilEnd(tokenizer); } System.out.println("查询字段：" + querys); System.out.println("查询表：" + tableName); System.out.println("查询条件：" + condition); } }
最后测试一下子：
public static void main(String[]args)throws BadSqlGrammarException{ String sql="select name,score,sex from users where name = 'jack' and isdelete <> 1 ; "; Parser parser=new Parser(sql); parser.query(); }
效果：
查询字段：name,score,sex
查询表：users
查询条件：name = 'jack' and isdelete <> 1
仔细一想，如果涉及到连表，where条件多层嵌套，甚至子查询，就GG了。实在是够复杂的。本文仅为抛砖引玉，匿了。
