sql解析器

最新推荐文章于 2024-05-13 19:09:35 发布

chenshuang_com

最新推荐文章于 2024-05-13 19:09:35 发布

阅读量841

点赞数 1

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/chenshuang_com/article/details/73991657

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

/**
 * Define a grammar called Hello
 */
grammar Sql;

options {language=Java;}

/**
 * Parser Rules
 */
compileUnit:
  statement
;
statement:
  selectStatement|updateStatement|insertStatement|deleteStatement
;
selectStatement:
  selectClause fromClause? whereClause? (groupByClause having?)? orderByClause? limitClause?
;
selectClause:
  SELECT columns
;
columns:
  column (COMMA column)*
;
column:
  identifier|LBRACE identifier RBRACE|function|STAR;
identifier:
  CHAR (CHAR|NUM)*
;
function:
  count|max|min|sum|avg
;
count:
  FCOUNT LB (identifier|integer|string|STAR) RB
;
max:
  FMAX LB (identifier|string) RB
;
min:
  FMIN LB (identifier|string) RB
;
sum:
  FSUM  LB (identifier|string) RB
;
avg:
  FAVG LB (identifier|string) RB
;
integer:
  NUM+
;
string:
  STRING
;
fromClause:
  FROM table
;
table:
  identifier|LBRACE identifier RBRACE
;
whereClause:
  WHERE conditions
;
conditions:
  lb condition (operator lb condition rb)*
;
lb:
  LB*
;
rb:
  RB*
;
operator:
  CONDITIONS_OPERATOR
;
condition:
  express
;
express:
  left CONDITION_OPERATOR right
;
left:
  identifier|integer|string
;
right:
  identifier|integer|string
;
start:
  identifier|integer|string
;
end:
  identifier|integer|string
;
groupByClause:
  GROUPBY groupByCondition (COMMA groupByCondition)*
;
groupByCondition:
  identifier|LBRACE identifier RBRACE
;
having:
  HAVING havingConditions
;
havingConditions:
  havingCondition (havingOperator havingCondition)*
;
havingCondition:
  havingExpress
;
havingExpress:
  left CONDITION_OPERATOR right
;
havingOperator:
  CONDITIONS_OPERATOR
;
orderByClause:
  ORDERBY order
;
order:
  orderCondition (COMMA orderCondition)*
;
orderCondition:
  orderColumn DIRECTION
;
orderColumn:
  identifier|LBRACE identifier RBRACE
;
limitClause:
  LIMIT NUM COMMA NUM
;
updateStatement:
;
insertStatement:
;
deleteStatement:
;
/*
 * Lexer Rules
 */
COMMA:',';
SELECT: 'select';
STAR:'*';
FROM:'from';
WHERE:'where';
ORDERBY:'order by';
GROUPBY:'group by';
DIRECTION:'asc'|'desc';
HAVING:'having';
LIMIT:'limit';
CHAR: 'a'..'z'|'A'..'Z'|'_';
NUM: '0'..'9';
STRING:'\'' .*? '\'';
LB:'(';
RB:')';
LBRACE:'[';
RBRACE:']';
CONDITIONS_OPERATOR    
    :'and'
    |'or'
    ;
CONDITION_OPERATOR    
    :'='
    |'>'
    |'<'
    |'<>'
    |'!='
    |'>='
    |'<='
    |'like'
    ;
FCOUNT:'count';
FSUM:'sum';
FMAX:'max';
FMIN:'min';
FAVG:'avg';
WS:[ \t\n\r]+ -> skip;

package com.demo.SqlParser;

import com.demo.SqlParser.SqlParser.ColumnContext;
import com.demo.SqlParser.SqlParser.ExpressContext;
import com.demo.SqlParser.SqlParser.GroupByConditionContext;
import com.demo.SqlParser.SqlParser.HavingExpressContext;
import com.demo.SqlParser.SqlParser.HavingOperatorContext;
import com.demo.SqlParser.SqlParser.LbContext;
import com.demo.SqlParser.SqlParser.LimitClauseContext;
import com.demo.SqlParser.SqlParser.OperatorContext;
import com.demo.SqlParser.SqlParser.OrderConditionContext;
import com.demo.SqlParser.SqlParser.RbContext;
import com.demo.SqlParser.SqlParser.TableContext;

/**
 * SQL 监听器
 * 右二叉树的构建规律（二叉树是像右下边移动的）
 * and或者or连接左右表达式的规律是取出右表达式放到and或者or的左右
 * 然后把and或者or放到原来的右表达式位置，之后把where表达式设置成刚才的and或者or
 * 接下来把表达式设置成and或者or的右表达式最后再次将where设置成刚来的表达式
 * @author 陈双
 */
public class CommonSqlListener extends SqlBaseListener {

	private SelectStatement selectStatement;
	private LBracket lb;
	private Expression temp;
	public CommonSqlListener(SelectStatement selectStatement){
		this.selectStatement= selectStatement;
	}

	@Override
	public void enterColumn(ColumnContext ctx) {
		// TODO Auto-generated method stub
		if (ctx.getText() != null) {
			if (ctx.getText().equals("*")) {
				this.selectStatement.addColumn(new AllColumns());
			} else {
				ColumnExpression column = new ColumnExpression();
				column.setName(ctx.getText());
				this.selectStatement.addColumn(column);
			}
		}
	}
	
	@Override
	public void enterExpress(ExpressContext ctx) {
		// TODO Auto-generated method stub
		
		String param = ctx.getText();
		if (param != null) {
			String[] array= null;
			if (param.contains(">=")) {
					array = ctx.getText().split(">=");
					GreaterEqualsExpression greaterEquals = new GreaterEqualsExpression();
					ColumnExpression column = new ColumnExpression();
					column.setName(array[0]);
					column.setParent(greaterEquals);
					greaterEquals.setLeft(column);
					StringExpression string = new StringExpression();
					string.setValue(array[1]);
					string.setParent(greaterEquals);//默认
					greaterEquals.setRight(string);
					if (this.selectStatement.getWhere() ==null) {
						this.selectStatement.setWhere(greaterEquals);
					} else {
						greaterEquals.setParent(this.selectStatement.getWhere());
						this.selectStatement.getWhere().setRight(greaterEquals);
						this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
					}
					if (this.lb != null) {
						column.setLb(lb);
						lb = null;
					}
					temp = greaterEquals;
			} else if (param.contains("<=")) {
					array = ctx.getText().split("<=");
					MinorEqualsExpression minorEquals = new MinorEqualsExpression();
					ColumnExpression column = new ColumnExpression();
					column.setName(array[0]);
					column.setParent(minorEquals);
					minorEquals.setLeft(column);
					StringExpression string = new StringExpression();
					string.setValue(array[1]);
					string.setParent(minorEquals);//默认
					minorEquals.setRight(string);
					if (this.selectStatement.getWhere() == null) {
						this.selectStatement.setWhere(minorEquals);
					} else {
						minorEquals.setParent(this.selectStatement.getWhere());
						this.selectStatement.getWhere().setRight(minorEquals);
						this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
					}
					if (this.lb != null) {
						column.setLb(lb);
						lb = null;
					}
					temp = minorEquals;
			} else if (param.contains("=")) {
				array = ctx.getText().split("=");
				EqualsExpression equals = new EqualsExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				column.setParent(equals);
				equals.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				string.setParent(equals);//默认
				equals.setRight(string);
				if (this.selectStatement.getWhere() ==null) {
					this.selectStatement.setWhere(equals);
				} else {
					equals.setParent(this.selectStatement.getWhere());
					this.selectStatement.getWhere().setRight(equals);
					this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
				}
				if (this.lb != null) {
					column.setLb(lb);
					lb = null;
				}
				temp = equals;
			} else if (param.contains(">")) {
				array = ctx.getText().split(">");
				GreaterExpression greater = new GreaterExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				column.setParent(greater);
				greater.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				string.setParent(greater);//默认
				greater.setRight(string);
				if (this.selectStatement.getWhere() ==null) {
					this.selectStatement.setWhere(greater);
				} else {
					greater.setParent(this.selectStatement.getWhere());
					this.selectStatement.getWhere().setRight(greater);
					this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
				}
				if (this.lb != null) {
					column.setLb(lb);
					lb = null;
				}
				temp = greater;
			} else if (param.contains("<")) {
				array = ctx.getText().split("<");
				MinorExpression minor = new MinorExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				column.setParent(minor);
				minor.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				string.setParent(minor);//默认
				minor.setRight(string);
				if (this.selectStatement.getWhere() ==null) {
					this.selectStatement.setWhere(minor);
				} else {
					minor.setParent(this.selectStatement.getWhere());
					this.selectStatement.getWhere().setRight(minor);
					this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
				}
				if (this.lb != null) {
					column.setLb(lb);
					lb = null;
				}
				temp = minor;
			} else if (param.contains("like")) {
				array = ctx.getText().split("like");
				LikeExpression like = new LikeExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				column.setParent(like);
				like.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				string.setParent(like);//默认
				like.setRight(string);
				if (this.selectStatement.getWhere() == null) {
					this.selectStatement.setWhere(like);
				} else {
					like.setParent(this.selectStatement.getWhere());
					this.selectStatement.getWhere().setRight(like);
					this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
				}
				if (this.lb != null) {
					column.setLb(lb);
					lb = null;
				}
				temp = like;
			}
		}
	}

	@Override
	public void enterOperator(OperatorContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			if (param.equals("and")) {
				AndExpression and = new AndExpression();
				//把where的右边拿出来放到and的左边
				and.setParent(this.selectStatement.getWhere());
				this.selectStatement.getWhere().getRight().setParent(and);
				and.setLeft(this.selectStatement.getWhere().getRight());
				//把and放到where的右边
				this.selectStatement.getWhere().setRight(and);
				this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
			} else if (param.equals("or")) {
				OrExpression or = new OrExpression();
				//把where的右边拿出来放到and的左边
				or.setParent(this.selectStatement.getWhere());
				this.selectStatement.getWhere().getRight().setParent(or);
				or.setLeft(this.selectStatement.getWhere().getRight());
				//把and放到where的右边
				this.selectStatement.getWhere().setRight(or);
				this.selectStatement.setWhere(this.selectStatement.getWhere().getRight());
			}
		}
	}

	@Override
	public void enterGroupByCondition(GroupByConditionContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			ColumnExpression column = new ColumnExpression();
			column.setName(param);
			this.selectStatement.addColumn(column);
		}
	}

	
	@Override
	public void enterHavingExpress(HavingExpressContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			String[] array= null;
			if (param.contains("=")) {
				array = ctx.getText().split("=");
				EqualsExpression equals = new EqualsExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				equals.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				equals.setRight(string);
				if (this.selectStatement.getHaving() ==null) {
					this.selectStatement.setHaving(equals);
				} else {
					this.selectStatement.getHaving().setRight(equals);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			} else if (param.contains(">")) {
				array = ctx.getText().split(">");
				GreaterExpression greater = new GreaterExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				greater.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				greater.setRight(string);
				if (this.selectStatement.getHaving() ==null) {
					this.selectStatement.setHaving(greater);
				} else {
					this.selectStatement.getHaving().setRight(greater);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			} else if (param.contains("<")) {
				array = ctx.getText().split("<");
				MinorExpression minor = new MinorExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				minor.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				minor.setRight(string);
				if (this.selectStatement.getHaving() ==null) {
					this.selectStatement.setHaving(minor);
				} else {
					this.selectStatement.getHaving().setRight(minor);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			} else if (param.contains(">=")) {
				array = ctx.getText().split(">=");
				GreaterEqualsExpression greaterEquals = new GreaterEqualsExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				greaterEquals.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				greaterEquals.setRight(string);
				if (this.selectStatement.getHaving() ==null) {
					this.selectStatement.setHaving(greaterEquals);
				} else {
					this.selectStatement.getHaving().setRight(greaterEquals);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			} else if (param.contains("<=")) {
				array = ctx.getText().split("<=");
				MinorEqualsExpression minorEquals = new MinorEqualsExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				minorEquals.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				minorEquals.setRight(string);
				if (this.selectStatement.getHaving() == null) {
					this.selectStatement.setHaving(minorEquals);
				} else {
					this.selectStatement.getHaving().setRight(minorEquals);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			}else if (param.contains("like")) {
				array = ctx.getText().split("like");
				LikeExpression like = new LikeExpression();
				ColumnExpression column = new ColumnExpression();
				column.setName(array[0]);
				like.setLeft(column);
				StringExpression string = new StringExpression();
				string.setValue(array[1]);
				like.setRight(string);
				if (this.selectStatement.getHaving() == null) {
					this.selectStatement.setHaving(like);
				} else {
					this.selectStatement.getHaving().setRight(like);
					this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
				}
			}
		}
	}
	
	@Override
	public void enterHavingOperator(HavingOperatorContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			if (param.equals("and")) {
				AndExpression and = new AndExpression();
				//把where的右边拿出来放到and的左边
				and.setLeft(this.selectStatement.getHaving().getRight());
				//把and放到where的右边
				this.selectStatement.getHaving().setRight(and);
				this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
			} else if (param.equals("or")) {
				OrExpression or = new OrExpression();
				//把where的右边拿出来放到and的左边
				or.setLeft(this.selectStatement.getHaving().getRight());
				//把and放到where的右边
				this.selectStatement.getHaving().setRight(or);
				this.selectStatement.setHaving(this.selectStatement.getHaving().getRight());
			}
		}
	}

	@Override
	public void enterOrderCondition(OrderConditionContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			if (param.contains("asc")) {
				String name = param.substring(0,param.indexOf("asc"));
				OrderElement order = new OrderElement();
				ColumnExpression column = new ColumnExpression();
				column.setName(name);
				order.setExpression(column);
				order.setAsc(true);
			} else if (param.contains("desc")) {
				String name = param.substring(0,param.indexOf("desc"));
				OrderElement order = new OrderElement();
				ColumnExpression column = new ColumnExpression();
				column.setName(name);
				order.setExpression(column);
				order.setAsc(false);
			}
		}
	}
	
	@Override
	public void enterLimitClause(LimitClauseContext ctx) {
		// TODO Auto-generated method stub
		String param = ctx.getText();
		if (param != null) {
			String[] array= param.substring(5).split(",");
			Limit limit = new Limit();
			limit.setStart(Integer.parseInt(array[0]));
			limit.setRows(Integer.parseInt(array[1]));
			this.selectStatement.setLimit(limit);
		}
	}

	
	@Override
	public void enterTable(TableContext ctx) {
		// TODO Auto-generated method stub
		if (ctx.getText() != null) {
			Table table = new Table();
			table.setName(ctx.getText());
			this.selectStatement.setTable(table);
		}
	}

	@Override
	public void enterLb(LbContext ctx) {
		// TODO Auto-generated method stub
		if (ctx.getText() != null && !ctx.getText().equals("")) {
			LBracket lBracket = new LBracket();
			lBracket.setBracket(ctx.getText());
			this.lb = lBracket; 
		}
	}

	@Override
	public void enterRb(RbContext ctx) {
		// TODO Auto-generated method stub
		if (ctx.getText() != null && !ctx.getText().equals("")) {
			RBracket rBracket = new RBracket();
			rBracket.setBracket(ctx.getText());
			if (temp != null) {
				temp.getRight().setRb(rBracket);
				temp = null;
			}
			
		}
	}
	
}