DML SQL解析器（支持标准的数据库SQL）

在前一家公司的时候，需要审核一些SQL语句，因此就想弄一个自动审核的工具，当时想写一个语法解析器，但一直没有完成，最近在开源代码和javacc的基础上整合了一个DML的SQL解析器，目前支持的语法关键字有：


ALL,AND,ANY,AS,ASC,AVG,BETWEEN,BY,COUNT,
DELETE,DESC,DISTINCT,EXISTS,FROM,GROUP,
HAVING,IN,INSERT,INTERSECT,INTO,IS,LIKE,
MAX,MIN,MINUS,NOT,NULL,OF,ONLY,OR,ORDER,
,SELECT,SET,SUM,UNION,UPDATE,VALUES
,WHERE
SQL解析结果包括语法解析和验证、表名获取、where语句的获取。

以下是测试代码：

public static void main(String args[]) throws ParseException {
XsqlParser p = null;
try {
String filename = "D://zhongli//parserbooks//xsql_test//test//TEST.in";// args[0]
p = new XsqlParser(new DataInputStream(
new FileInputStream(filename)));
} catch (FileNotFoundException e) {
System.out.println("/* File " + args[0]
+ " not found. Reading from stdin */");
p = new XsqlParser(System.in);
}
XStatement st = null;
while ((st = p.readStatement()) != null) {
System.out.println(st.toString() + ";");
}

System.out.println("exit;");
System.out.println("/* Parse Successful */");

// for string SQL Statement
st = null;
p
.setStatement("SELECT EMPLOYEEIDNO as NO FROM EMPLOYEESTATISTICSTABLE"
+ " WHERE POSITION = 'Manager' AND (SALARY > 50000 OR BENEFIT > 10000);");
if ((st = p.readStatement()) != null) {
System.out.println(st.toString() + ";");
System.out.println(st.getTable());
System.out.println(st.getWhere());
}
// for join
st = null;
p.setStatement("SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME"
+ " FROM ANTIQUES, ANTIQUEOWNERS"
+ " WHERE SELLERID = OWNERID"
+ " ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID;");
if ((st = p.readStatement()) != null) {
System.out.println(st.toString() + ";");
System.out.println(st.getTable());
System.out.println(st.getWhere());
}
} // main ends here
通过测试验证支持的SQL如下：

select * from TableName;
select FirstName, LastName, Address, City, State from EmployeeAddressTable;
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (SALARY >= 50000);
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (POSITION = 'Manager');
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where ((SALARY > 40000) AND (POSITION = 'Staff'));
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where ((SALARY < 40000) OR (BENEFITS < 10000));
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (((POSITION = 'Manager') AND (SALARY > 60000)) OR (BENEFITS > 12000));
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where ((POSITION = 'Manager') AND ((SALARY > 50000) OR (BENEFIT > 10000)));
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (POSITION IN ('Manager', 'Staff'));
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (SALARY BETWEEN 30000 AND 50000);
select EMPLOYEEIDNO from EMPLOYEESTATISTICSTABLE where (SALARY NOT BETWEEN 30000 AND 50000);
select EMPLOYEEIDNO from EMPLOYEEADDRESSTABLE where (LASTNAME LIKE 'L%');
select OWNERLASTNAME, OWNERFIRSTNAME from ANTIQUEOWNERS, ANTIQUES where ((BUYERID = OWNERID) AND (ITEM = 'Chair'));
select ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME from ANTIQUEOWNERS, ANTIQUES where ((ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID) AND (ANTIQUES.ITEM = 'Chair'));
select distinct SELLERID, OWNERLASTNAME, OWNERFIRSTNAME from ANTIQUES, ANTIQUEOWNERS where (SELLERID = OWNERID) order by OWNERLASTNAME ASC, OWNERFIRSTNAME ASC, OWNERID ASC;
select OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered from ORDERS ORD, ANTIQUEOWNERS OWN where ((ORD.OWNERID = OWN.OWNERID) AND (ORD.ITEMDESIRED IN (select ITEM from ANTIQUES)));
select SUM(SALARY), AVG(SALARY) from EMPLOYEESTATISTICSTABLE;
select MIN(BENEFITS) from EMPLOYEESTATISTICSTABLE where (POSITION = 'Manager');
select COUNT(*) from EMPLOYEESTATISTICSTABLE where (POSITION = 'Staff');
select SELLERID from ANTIQUES, ANTVIEW where (ITEMDESIRED = ITEM);
insert into ANTIQUES values (21, 01, 'Ottoman', 200.00);
insert into ANTIQUES(BUYERID,SELLERID,ITEM) values (01, 21, 'Ottoman');
delete from ANTIQUES where (ITEM = 'Ottoman');
delete from ANTIQUES where ((ITEM = 'Ottoman') AND (BUYERID = 01) AND (SELLERID = 21));
update ANTIQUES set PRICE=500.00 where (ITEM = 'Chair');
select BUYERID, MAX(PRICE) from ANTIQUES group by BUYERID;
select BUYERID, MAX(PRICE) from ANTIQUES group by BUYERID having (PRICE > 1000);
select OWNERID from ANTIQUES where (PRICE > (select (AVG(PRICE) + 100) from ANTIQUES));
select OWNERLASTNAME from ANTIQUEOWNERS where (OWNERID = (select distinct BUYERID from ANTIQUES));
update ANTIQUEOWNERS set OWNERFIRSTNAME='John' where (OWNERID = (select BUYERID from ANTIQUES where (ITEM = 'Bookcase')));
select OWNERFIRSTNAME, OWNERLASTNAME from ANTIQUEOWNERS where (EXISTS (select * from ANTIQUES where (ITEM = 'Chair')));
select BUYERID, ITEM from ANTIQUES where (PRICE >= ALL (select PRICE from ANTIQUES));
select BUYERID from ANTIQUEOWNERS UNION (select OWNERID from ORDERS);
select OWNERID, 'is in both Orders & Antiques' from ORDERS, ANTIQUES where (OWNERID = BUYERID) UNION (select BUYERID, 'is in Antiques only' from ANTIQUES where (BUYERID NOT IN (select OWNERID from ORDERS)));
select * from GRADES where (GRADE = (((2 + 3) - 4) + 5));
select * from GRADES where (GRADE = ((2 + (3 * 4)) - 5));
select * from table1 where (col1 IS NULL);
select * from table1 where (avg(col1) IS NOT NULL);

select EMPLOYEEIDNO NO from EMPLOYEESTATISTICSTABLE where ((POSITION = 'Manager') AND ((SALARY > 50000) OR (BENEFIT > 10000)));
EMPLOYEESTATISTICSTABLE
((POSITION = 'Manager') AND ((SALARY > 50000) OR (BENEFIT > 10000)))

select distinct SELLERID, OWNERLASTNAME, OWNERFIRSTNAME from ANTIQUES, ANTIQUEOWNERS where (SELLERID = OWNERID) order by OWNERLASTNAME ASC, OWNERFIRSTNAME ASC, OWNERID ASC;

ANTIQUES,ANTIQUEOWNERS
(SELLERID = OWNERID)

获取源码和可执行二进制jar包URL如下：
源码：
http://www.box.net/shared/2urubkpngx9vbolmp5pi

二进制jar包：

http://www.box.net/shared/0gphjv9p71x18ntrfd8o

目前一些数据库的特殊语法没有实现，如果需要实现的，可以email联系我。