mysql数据导入elasticsearch并支持SQL查询

package cn.rmt.support.es.mysql; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang.StringUtils; import org.elasticsearch.search.sort.SortOrder; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.alibaba.fastjson.JSON; import cn.rmt.support.bean.SQLWhereBean; import cn.rmt.support.constants.WhereOperateMode; /** * <br> * <b>功能：</b>SQL 语句解析 根据关键字获取条件信息<br> * <b>作者：</b>zhouzm<br> * <b>日期：</b>2017-2-7<br> * <b>版权所有：<b>前海乘势科技有限公司版权所有(C) 2015<br> */ public class SQLAnalysis { private static Logger log = LoggerFactory.getLogger(SQLAnalysis.class); /** * 获取需要的表字段() * key:filed(名称) => value:转义的名称 * 支持AS转义语法 * @param sql * @return */ public static Map<String, String> getColumn(String sql){ Map<String, String> clomunMaps = new HashMap<String,String>(); String columnStr = parseCols(sql).trim(); //*解析 if(columnStr.equals("*")){ return clomunMaps; } //解析是否包含 AS String []columns = columnStr.split(","); for(int i=0; i< columns.length; i++){ //column存在别名 if(isContains(columns[i], "\\sas\\s")){ //解析别名 String regex="(.+)(\\sas\\s)"; String regexAlias = "(\\sas\\s)(.+)"; clomunMaps.put(getMatchedString(regex,columns[i],1), getMatchedString(regexAlias,columns[i],2)); }else{ clomunMaps.put(columns[i], columns[i]); } } return clomunMaps; } /** * 获取搜索索引 * @param sql * @return */ public static String getIndex(String sql){ String indexStr = parseTables(sql); String regex=""; if(isContains(sql,"/")){ regex="(.+)(/)"; } else{ regex="(.+)($)"; } return getMatchedString(regex,indexStr,1); } /** * 获取搜索索引类型 * Types(_type) * @param sql * @return */ public static List<String> getTypes(String sql){ List<String> typeList = new ArrayList<String>(); String typeStr = parseTables(sql); //判断是否定位搜索为_type if(isContains(typeStr, "/")){ String[] types = typeStr.split(","); String regex="(/)(.+)"; for(int i=0; i< types.length; i++){ typeList.add(getMatchedString(regex,types[i],2) ); } } return typeList; } /** * 获取sql where查找 * 支持(=,>,<,like) 可以复合查找 * @param sql * @return */ public static SQLWhereBean getWhere(String sql){ String sqlStr = parseConditions(sql); if(StringUtils.isBlank(sqlStr) ){ return null; } SQLWhereBean bean = getWhere(sqlStr, null, null); return bean; } private static void getWhereEntity(SQLWhereBean bean,String sql){ String left = ""; String right = ""; WhereOperateMode mode = null; if(isContains(sql, WhereOperateMode.GREAT_EQUAL.CODE ) ){ String regex = "(.+)("+WhereOperateMode.GREAT_EQUAL.CODE+")(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.GREAT_EQUAL; }else if(isContains(sql, WhereOperateMode.LESS_EQUAL.CODE ) ){ String regex = "(.+)("+WhereOperateMode.LESS_EQUAL.CODE+")(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.LESS_EQUAL; }else if(isContains(sql, WhereOperateMode.EQUAL.CODE )){ String regex = "(.+)("+WhereOperateMode.EQUAL.CODE+")(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.EQUAL; }else if(isContains(sql, WhereOperateMode.GREAT.CODE )){ String regex = "(.+)("+WhereOperateMode.GREAT.CODE+")(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.GREAT; }else if(isContains(sql, WhereOperateMode.LESS.CODE )){ String regex = "(.+)("+WhereOperateMode.LESS.CODE+")(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.LESS; }else if(isContains(sql, "\\s"+WhereOperateMode.LIKE.CODE+"\\s" )){ String regex = "(.+)(\\s"+WhereOperateMode.LIKE.CODE+"\\s)(.+)"; left = getMatchedString(regex,sql,1); right = getMatchedString(regex,sql,3); mode = WhereOperateMode.LIKE; }else{ log.error("sql param error not support mode is "+ sql); //抛出异常 } bean.setLeft(left.trim()); bean.setRight(right.trim()); bean.setMode(mode); } private static SQLWhereBean getWhere(String left,String rigth,WhereOperateMode mode){ SQLWhereBean bean = new SQLWhereBean(); bean.setMode(mode); //左边表达式存在 if( !StringUtils.isBlank(left) ){ //脱括号 String tmpStr = offBracket(left); tmpStr = tmpStr.toLowerCase(); //and 优先级大于 or 先判断是否存在and表达式 if(isContains(tmpStr, "and")){ int index = tmpStr.indexOf("and"); //添加括号 String nextLeft = left.substring(0, index).trim(); String nextRight = left.substring(index+3).trim(); if(nextLeft.charAt(0) != '('){ nextLeft = "("+nextLeft+")"; } if(nextRight.charAt(0) != '('){ nextRight = "("+nextRight+")"; } bean.setLeftBean(getWhere(nextLeft, nextRight,WhereOperateMode.AND) ); }else if(isContains(tmpStr, "or")){ int index = tmpStr.indexOf("or"); //添加括号 String nextLeft = left.substring(0, index).trim(); String nextRight = left.substring(index+2).trim(); if(nextLeft.charAt(0) != '('){ nextLeft = "("+nextLeft+")"; } if(nextRight.charAt(0) != '('){ nextRight = "("+nextRight+")"; } bean.setLeftBean(getWhere(nextLeft, nextRight ,WhereOperateMode.OR ) ); }else{ //表层有括号 去括号 if(left.charAt(0) == '(' ){ bean.setLeftBean(getWhere(left.substring(1, left.lastIndexOf(")") ) ,null ,null) ); } else{ //获取where条件(</>/=/<=/>=) getWhereEntity(bean,left); } } } if(!StringUtils.isBlank(rigth)){ //脱括号 String tmpStr = offBracket(rigth); tmpStr = tmpStr.toLowerCase(); //and 优先级大于 or 先判断是否存在and表达式 if(isContains(tmpStr, "and")){ int index = tmpStr.indexOf("and"); //添加括号 String nextLeft = rigth.substring(0, index).trim(); String nextRight = rigth.substring(index+3).trim(); if(nextLeft.charAt(0) != '('){ nextLeft = "("+nextLeft+")"; } if(nextRight.charAt(0) != '('){ nextRight = "("+nextRight+")"; } bean.setRightBean(getWhere(nextLeft, nextRight , WhereOperateMode.AND) ); }else if(isContains(tmpStr, "or")){ int index = tmpStr.indexOf("or"); //添加括号 String nextLeft = rigth.substring(0, index).trim(); String nextRight = rigth.substring(index+2).trim(); if(nextLeft.charAt(0) != '('){ nextLeft = "("+nextLeft+")"; } if(nextRight.charAt(0) != '('){ nextRight = "("+nextRight+")"; } bean.setRightBean(getWhere(nextLeft, nextRight , WhereOperateMode.OR) ); }else{ //表层有括号 去括号 if(rigth.charAt(0) == '(' ){ bean.setRightBean(getWhere(rigth.substring(1, rigth.lastIndexOf(")")) ,null ,null) ); } else{ //获取where条件(</>/=/<=/>=) getWhereEntity(bean,rigth); } } } return bean; } private static String offBracket(String sql){ if( sql.contains("(") ){ String newSQL = ""; String prefixStr = ""; for(int i=0; i<sql.length(); i++){ if(sql.charAt(i) == ')'){ break; }else{ prefixStr += sql.charAt(i); } } int start = prefixStr.length(); String braketStr = ""