package com.alimama.mdrill.jdbc;
import java.io.StringReader;
import java.util.Arrays;
import java.util.regex.Pattern;
import java.lang.reflect.Method;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import com.alimama.mdrill.json.JSONArray;
import com.alimama.mdrill.json.JSONException;
import com.alimama.mdrill.json.JSONObject;
public class SqlParser {
private String sql;
public String tablename;
public String fl;
public String groupby;
public String sort;
public String order;
public String queryStr;
public String start;
public String rows;
public String[] colsAliasNames;
public String[] colsNames;
public static void main(String[] args) {
System.out.println("###");
SqlParser p=new SqlParser();
// p.parse("select myn,abc from test where _higopartions_ = '20130101,20120303' ");
// System.out.println(p.toString());
// p.parse("select count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' ");
// System.out.println(p.toString());
//
// p.parse("select count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' and myn eq 123 and username neq 'yannian'");
// System.out.println(p.toString());
//
// p.parse("select count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' and price gt 20 and buycnt lt 30 ");
// System.out.println(p.toString());
//
p.parse("select count(pv) as pv,sum(pv) as sumpv from test where thedate >=20130101 and thedate <=20120303 and userid not in (abc,dec,a,e,f,'13','45') ");
// System.out.println(p.toString());
//
// p.parse("select myn,abc,count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' group by myn,abc");
// System.out.println(p.toString());
//
// p.parse("select myn,abc,count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' group by myn,abc order by pv");
// System.out.println(p.toString());
//
// p.parse("select myn,abc,count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' group by myn,abc order by pv desc");
// System.out.println(p.toString());
//
// p.parse("select myn,abc,count(pv) as pv,sum(pv) as sumpv from test where _higopartions_ = '20130101,20120303' group by myn,abc order by sum(pv) limit 5,60");
// System.out.println(p.toString());
//
// p.parse("select thedate,category_level1_name,user_id,count(*) from rpt_hitfake_auctionall_d where thedate >'20130625' and thedate <'20130705' and (thedate ='20130704' or thedate ='20130705' or thedate<='20130702') and ((custid='1104981405' and user_id='136018175') or user_id='932280506' or user_id like '%9999%') and category_level1_name='3C数码配件' group by thedate,user_id,category_level1_name limit 0,100");
//select thedate,category_level1_name,count(*),count(suit_sum) as cnt,sum(suit_sum) as sam from rpt_hitfake_auctionall_d where thedate >'20130625' and thedate <'20130705' and (thedate ='20130704' or thedate ='20130705' or thedate<='20130702') and ((custid='1104981405' and user_id='136018175') or user_id='932280506' or user_id like '%9999%') and category_level1_name like '%电%' group by thedate,category_level1_name order by sam desc limit 0,100
System.out.println(p.toString());
}
public void parse(String sql)
{
try {
} catch (Exception e) {
e.printStackTrace();
}
this.sql=sql;
String otherSql=sql;
otherSql=this.parseColumns(otherSql);
otherSql=this.parseTableName(otherSql);
otherSql=this.parselimit(otherSql);
otherSql=this.parseOrderBy(otherSql);
otherSql=this.parseGroupBy(otherSql);
try {
this.parseFq(otherSql);
} catch (Exception e) {
e.printStackTrace();
}
}
public String toString() {
return "SqlParser [\n sql=" + sql + ",\n tablename=" + tablename + ",\n fl="
+ fl + ",\n groupby=" + groupby + ",\n sort=" + sort + ",\n order="
+ order + ",\n queryStr=" + queryStr + ",\n start=" + start + ",\n rows=" + rows + ",\n colsAliasNames="
+ Arrays.toString(colsAliasNames) + ",\n colsNames="
+ Arrays.toString(colsNames) + "\n]";
}
Pattern fromReg=Pattern.compile("from", Pattern.CASE_INSENSITIVE);
Pattern selectReg=Pattern.compile(".*select[ ]*", Pattern.CASE_INSENSITIVE);
Pattern columnAliasReg=Pattern.compile("as", Pattern.CASE_INSENSITIVE);
private String parseColumns(String otherSql)
{
String[] fromSplit=fromReg.split(otherSql);
String[] cols=selectReg.matcher(fromSplit[0]).replaceAll("").trim().split(",");
StringBuffer flBuffer=new StringBuffer();
this.colsAliasNames=new String[cols.length];
this.colsNames=new String[cols.length];
for(int i=0;i<cols.length;i++)
{
String[] alinames=columnAliasReg.split(cols[i]);
String realColumn=alinames[0].trim();
this.colsNames[i]=realColumn;
if(i!=0)
{
flBuffer.append(",");
}
flBuffer.append(realColumn);
if(alinames.length>1){
colsAliasNames[i]=alinames[1].trim();
}else{
colsAliasNames[i]=realColumn;
}
}
this.fl=flBuffer.toString();
return fromSplit[1];
}
private String parseTableName(String otherSql)
{
String tblname=otherSql.trim();
int end=tblname.length();
int index=tblname.indexOf(" ");
if(index>0)
{
end=index;
}
this.tablename=tblname.substring(0, end);
return tblname.substring(end);
}
Pattern groupbyReg=Pattern.compile("group[ ]+by", Pattern.CASE_INSENSITIVE);
private String parseGroupBy(String otherSql)
{
String[] group=groupbyReg.split(otherSql);
if(group.length>1)
{
this.groupby=group[1].trim();
}
return group[0];
}
Pattern limitReg=Pattern.compile("limit", Pattern.CASE_INSENSITIVE);
private String parselimit(String otherSql)
{
String[] limit=limitReg.split(otherSql);
this.start="0";
this.rows="20";
if(limit.length>1)
{
String[] cols=limit[1].trim().split(",");
this.start=cols[0].trim();
this.rows=cols[1].trim();
}
return limit[0];
}
Pattern orderbyReg=Pattern.compile("order[ ]+by", Pattern.CASE_INSENSITIVE);
private String parseOrderBy(String otherSql)
{
String[] split=orderbyReg.split(otherSql);
if(split.length>1)
{
String[] sort=split[1].trim().split("[ ]+");
this.setSort(sort[0].trim());
this.order="asc";
if(sort.length>1)
{
this.order=sort[1].trim().toLowerCase();
}
}
return split[0];
}
private void setSort(String sort)
{
for(int i=0;i<this.colsAliasNames.length;i++)
{
if(sort.equals(this.colsAliasNames[i]))
{
this.sort=this.colsNames[i];
return ;
}
}
this.sort=sort;
}
Pattern whereReg=Pattern.compile("where[ ]*", Pattern.CASE_INSENSITIVE);
protected Expression getExpressionWithoutParenthesis(Expression ex){
if(ex instanceof Parenthesis){
Expression child = ((Parenthesis)ex).getExpression();
return getExpressionWithoutParenthesis(child);
}else{
return ex;
}
}
public JSONArray generateList(Expression ex , JSONArray linkList,JSONObject parent) throws JSONException{
if(ex==null){
return linkList;
}
if(ex instanceof OrExpression||ex instanceof AndExpression){
parent.put("subQuery", "1");
parent.put("filter", (ex instanceof OrExpression)?"OR":"AND");
BinaryExpression be = (BinaryExpression)ex;
generateList(be.getLeftExpression(), linkList,parent);
generateList(be.getRightExpression(), linkList,parent);
}else if(ex instanceof Parenthesis){
JSONArray sublist = new JSONArray();//{colname:{operate:1,value:xxxx}}
JSONObject subQuery=new JSONObject();
subQuery.put("subQuery", "1");
subQuery.put("filter", "AND");
subQuery.put("list", sublist);
Expression exp = getExpressionWithoutParenthesis(ex);
linkList.put(subQuery);
generateList(exp,sublist,subQuery);
}else{
JSONObject item=new JSONObject();//
processExpression(ex,item);
linkList.put(item);
}
return linkList;
}
private Object invokeMethod(Object obj, String methodFunc){
try {
Method method = obj.getClass().getMethod(methodFunc, null);
return method.invoke(obj, null);
} catch (Exception e) {
return null;
}
}
private JSONArray toJSONArray(String str,String split)
{
String[] list=str.split(split);
JSONArray rtn=new JSONArray();
for(String s:list)
{
rtn.put(s.trim().replaceAll("^'", "").replaceAll("'$", ""));
}
return rtn;
}
protected ObjectExpression processExpression(Expression e,JSONObject item) throws JSONException{
ObjectExpression oe = new ObjectExpression();
Object columnObj = invokeMethod(e, "getLeftExpression");
if(columnObj instanceof LongValue){
LongValue longValue = (LongValue)columnObj;
oe.setColumnname(longValue.getStringValue());
}else{
Column column = (Column)invokeMethod(e, "getLeftExpression");
oe.setColumnname(column.getColumnName());
}
if (e instanceof BinaryExpression) {
BinaryExpression be = (BinaryExpression) e;
oe.setExp(be.getStringExpression());
if(be.getRightExpression() instanceof Function){
oe.setValue(invokeMethod(be.getRightExpression(), "toString"));
}else{
oe.setValue(invokeMethod(be.getRightExpression(), "getValue"));
}
}else{
oe.setExp((String)invokeMethod(e, "toString"));
}
JSONObject subitem=new JSONObject();
String op=oe.getExp();
String colname=oe.getColumnname();
String val=String.valueOf(oe.getValue());
JSONArray rtn=new JSONArray();
rtn.put(val);
subitem.put("value", rtn);
if(op.equals("="))
{
subitem.put("operate", "1");
}else if(op.equals("<>"))
{
subitem.put("operate", "2");
}
else if(op.equals(">="))
{
subitem.put("operate", "3");
}
else if(op.equals(">"))
{
subitem.put("operate", "13");
}
else if(op.equals("<="))
{
subitem.put("operate", "4");
}
else if(op.equals("<"))
{
subitem.put("operate", "14");
}
else if(op.toLowerCase().equals("like"))
{
subitem.put("operate", "1000");
subitem.put("value", colname+":"+transValue(val.replaceAll("%", "*")));
colname=String.valueOf(Math.random());
}
else if(op.indexOf("NOT IN")>=0 )
{
String[] cols=op.split("NOT IN");
String list=cols[1].replaceAll("^[ ]*\\(", "").replaceAll("\\)[ ]*$", "");
subitem.put("operate", "7");
subitem.put("value", toJSONArray(list,","));
}
else if(op.indexOf("IN")>=0 )
{
String[] cols=op.split("IN");
String list=cols[1].replaceAll("^[ ]*\\(", "").replaceAll("\\)[ ]*$", "");
subitem.put("operate", "5");
subitem.put("value", toJSONArray(list,","));
}else{
subitem.put("operate", op);
subitem.put("value", toJSONArray(val,","));
}
item.put(colname, subitem);
return oe;
}
private void parseFq(String otherSql) throws JSONException, JSQLParserException
{
String[] split=whereReg.split(this.tablename +" "+otherSql.trim());
if(split.length>1)
{
String where=split[1].trim();
CCJSqlParserManager pm = new CCJSqlParserManager();
PlainSelect plainSelect = (PlainSelect)((Select) pm.parse(new StringReader("select * from abc where "+where))).getSelectBody();
Expression e = getExpressionWithoutParenthesis(plainSelect.getWhere());
JSONArray jsonObj = new JSONArray();//{colname:{operate:1,value:xxxx}}
JSONObject subQuery=new JSONObject();
subQuery.put("subQuery", "1");
subQuery.put("filter", "AND");
subQuery.put("list", jsonObj);
this.queryStr=generateList(e, jsonObj, subQuery).toString();
}
}
private String transValue(String val)
{
return val.trim().replaceAll("'", "");
}
}