关于sql解析的问题

[zhengwf]

1.sql where条件解析出来以后不区分是and连接，还是or连接，对子查询有支持，无法识别
例子：select name from student where id in (select sd_id from scole where scole < 60 order by scole asc) or id = 2 order by name desc
2.limit 条件不识别
3.having条件合并进where条件中了
代码例子：

public static void main(String[] args) {
		
		String DBTYPE = JdbcConstants.MYSQL;
		//String sql = "select name, course ,scole from student inner join scole on student.id = scole.sd_id where course = '数学' limit 10;";
		//sql = "select name,course,sum(scole) as total from student where student.id in (select sd_id from scole where name='aaa') and scole in (1,2,3) group by name HAVING total <60 order by scole desc limit 10 ,2 ";
		String sql = "select name from  student where id in (select sd_id from scole where scole < 60 order by scole asc) or id = 2 order by name desc";
		String format = SQLUtils.format(sql, DBTYPE);
		//System.out.println("formated sql :  " + format);
		List<SQLStatement> list = SQLUtils.parseStatements(sql, DBTYPE);
		
		for (int i = 0; i < list.size(); i++) {
			SQLStatement stmt = list.get(i);
            MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
            stmt.accept(visitor);
 
            //获取表名称
            System.out.println("当前表 : " + visitor.getCurrentTable());
            //获取操作方法名称,依赖于表名称
            System.out.println("涉及到的所有表 : " + visitor.getTables());
            Map<Name, TableStat> table_map = visitor.getTables();
            for(Entry<Name, TableStat> entry : table_map.entrySet()){
            	Name name = entry.getKey();
            	name.getName();
            	//存储表的调度次数，包括select ，update等
            	TableStat ts = entry.getValue();
            	
            }
            //获取字段名称
            System.out.println( visitor.getParameters());
            //获取列名
            System.out.println("查询的列信息 : " + visitor.getColumns());
            Collection<Column> cc = visitor.getColumns();
            //column 存储了表名，列名，以及列是出现的位置，where，select，groupby ，order
            for(Column column : cc){
            	
            }
            System.out.println("conditions : " + visitor.getConditions() );
            List<Condition> conditions = visitor.getConditions();
            System.out.println("----------------------------");
            for(Condition cond : conditions){
            	System.out.println( "column : " + cond.getColumn());
            	System.out.println( "operator : " + cond.getOperator());
            	System.out.println( "values  : " + cond.getValues());
            	
            	System.out.println("----------------------------");
            }
            System.out.println("group by : " + visitor.getGroupByColumns() );
            System.out.println("order by : " + visitor.getOrderByColumns() );
            System.out.println("relations ships  : " + visitor.getRelationships() );
            System.out.println("alias map" + visitor.getAliasMap() );
            System.out.println("Variants : " + visitor.getVariants());
		}
	}

输出

当前表 : scole
涉及到的所有表 : {student=Select, scole=Select}
[]
查询的列信息 : [student.name, student.id, scole.sd_id, scole.scole, scole.id, scole.name]
conditions : [student.id IN, scole.scole < 60, scole.id = 2]
----------------------------
column : student.id
operator : IN
values  : []
----------------------------
column : scole.scole
operator : <
values  : [60]
----------------------------
column : scole.id
operator : =
values  : [2]
----------------------------
group by : []
order by : [scole.scole, scole.name]
relations ships  : []
alias map{student=student, scole=scole, DUAL=null}
Variants : {}

不知道是我理解错误了，还是用法不对

[wenshao]

    String DBTYPE = JdbcConstants.MYSQL;
        //String sql = "select name, course ,scole from student inner join scole on student.id = scole.sd_id where course = '数学' limit 10;";
        //sql = "select name,course,sum(scole) as total from student where student.id in (select sd_id from scole where name='aaa') and scole in (1,2,3) group by name HAVING total <60 order by scole desc limit 10 ,2 ";
        String sql = "select name from  student where id in (select sd_id from scole where scole < 60 order by scole asc) or id = 2 order by name desc";
        String format = SQLUtils.format(sql, DBTYPE);
        //System.out.println("formated sql :  " + format);
        List<SQLStatement> list = SQLUtils.parseStatements(sql, DBTYPE);

        for (int i = 0; i < list.size(); i++) {
            SQLStatement stmt = list.get(i);
            MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
            stmt.accept(visitor);


            //获取操作方法名称,依赖于表名称
            System.out.println("涉及到的所有表 : " + visitor.getTables());
            Map<TableStat.Name, TableStat> table_map = visitor.getTables();
            for(Map.Entry<TableStat.Name, TableStat> entry : table_map.entrySet()){
                TableStat.Name name = entry.getKey();
                name.getName();
                //存储表的调度次数，包括select ，update等
                TableStat ts = entry.getValue();

            }
            //获取字段名称
            System.out.println( visitor.getParameters());
            //获取列名
            System.out.println("查询的列信息 : " + visitor.getColumns());
            Collection<TableStat.Column> cc = visitor.getColumns();
            //column 存储了表名，列名，以及列是出现的位置，where，select，groupby ，order
            for(TableStat.Column column : cc){

            }
            System.out.println("conditions : " + visitor.getConditions() );
            List<TableStat.Condition> conditions = visitor.getConditions();
            System.out.println("----------------------------");
            for(TableStat.Condition cond : conditions){
                System.out.println( "column : " + cond.getColumn());
                System.out.println( "operator : " + cond.getOperator());
                System.out.println( "values  : " + cond.getValues());

                System.out.println("----------------------------");
            }
            System.out.println("group by : " + visitor.getGroupByColumns() );
            System.out.println("order by : " + visitor.getOrderByColumns() );
            System.out.println("relations ships  : " + visitor.getRelationships() );

        }

输出如下：

涉及到的所有表 : {student=Select, scole=Select}
[]
查询的列信息 : [student.name, student.id, scole.sd_id, scole.scole]
conditions : [student.id IN, scole.scole < 60, student.id = 2]
----------------------------
column : student.id
operator : IN
values  : []
----------------------------
column : scole.scole
operator : <
values  : [60]
----------------------------
column : student.id
operator : =
values  : [2]
----------------------------
group by : []
order by : [scole.scole, student.name]
relations ships  : []

[zhengwf]

谢谢大佬，已经用antlr4自定义解析SQL了

[wenshao]

https://github.com/alibaba/druid/releases/tag/1.1.6
问题已修复，请用新版本