package com.ycl.scheme;
|
|
|
import com.ycl.scheme.entity.BlockCode;
|
import com.ycl.scheme.entity.Keyword;
|
import com.ycl.scheme.entity.KeywordType;
|
import com.ycl.scheme.entity.TriggerType;
|
import org.apache.commons.lang3.StringUtils;
|
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
public class SqlQueryBuilder extends AbstractBlockCodeBuilder {
|
|
private static final String[] splits = {" ", "/", "\\", "(", ")", System.lineSeparator(), ":", ";", ",", "."};
|
private BlockText blockText;
|
|
private String sourceText;
|
|
private String sql;
|
|
private String where;
|
|
private String select;
|
|
private String compareScript;
|
|
private String resultText;
|
|
private List<Keyword> keywords;
|
|
private Keyword tableKeyword;
|
|
private Integer idx ;
|
|
private TriggerType triggerType;
|
|
|
private Map<String,Parameter> parameters = new HashMap<>();
|
|
|
public SqlQueryBuilder(BlockText blockText, List<Keyword> keywords, Integer idx, TriggerType triggerType) {
|
this.blockText = blockText;
|
|
this.keywords = keywords;
|
|
this.idx= idx;
|
|
this.triggerType = triggerType;
|
|
}
|
|
@Override
|
public BlockCode build() {
|
this.tableKeyword =this.keywords.stream().filter(k->k.getName().equals(blockText.getSource().trim())).findFirst().orElse(null);
|
if(this.tableKeyword == null){
|
throw new RuntimeException("没有找到数据源:" + this.blockText.getSource().trim());
|
}
|
|
this.scanWhere();
|
if(this.triggerType== TriggerType.CANAL){
|
this.addWhereRowSql();
|
}
|
this.scanResult();
|
this.buildSql();
|
BlockCode bc = this.buildCode();
|
return bc;
|
}
|
|
private void scanWhere() {
|
String inputExpression = this.blockText.getExpression();
|
int idx = 0;
|
|
String res = "";
|
String skw ="";
|
|
while (idx + 1 < inputExpression.length()) {
|
String c = inputExpression.substring(idx, idx+1);
|
|
if (isSplit(c)) {
|
Keyword kw = null;
|
if (StringUtils.isNotBlank(skw)) {
|
kw = findKeyword(skw.trim());
|
if(kw !=null){
|
MatchResult mr = matchByKeyword(skw,kw,idx);
|
res += mr.getOutput();
|
idx = mr.getIdx();
|
}else{
|
res += skw;
|
}
|
}else{
|
res +=c;
|
}
|
idx++;
|
if(kw!=null && !kw.getKeywordType().equals(KeywordType.TIME_RANGE_FUNCTION) && !c.equals("(")){
|
res+=c;
|
}
|
skw="";
|
}else{
|
skw += c;
|
idx++;
|
}
|
}
|
|
this.where = res;
|
|
}
|
|
private void addWhereRowSql(){
|
this.where += this.tableKeyword.getTableKey() + "= :rowId";
|
}
|
|
private boolean sqlTest(){
|
//测试sql执行是否正确
|
return true;
|
}
|
|
private void scanResult(){
|
int idx=0;
|
String res="";
|
String skw ="";
|
String resultText = this.blockText.getResult();
|
|
String[] ss = resultText.split(">|<|=");
|
if(ss.length < 2){
|
throw new RuntimeException("结果表达式不正确");
|
}
|
|
|
String fields = ss[0];
|
String compareStr = resultText.substring(fields.length());
|
fields+=" ";
|
while (idx+1 < fields.length()){
|
String c = fields.substring(idx, idx+1);
|
if(isSplit(c) ){
|
Keyword kw = null;
|
if (StringUtils.isNotBlank(skw)) {
|
kw = findKeyword(skw.trim());
|
if(kw !=null){
|
MatchResult mr = matchByKeyword(skw,kw,idx);
|
res += mr.getOutput();
|
|
idx = mr.getIdx();
|
}else{
|
res += skw;
|
}
|
}else{
|
res +=c;
|
}
|
idx++;
|
if(kw==null || !kw.getKeywordType().equals(KeywordType.FUNCTION) || kw.getFunctionParameterNullable()==null || !kw.getFunctionParameterNullable()){
|
res +=c;
|
}
|
|
skw="";
|
}else{
|
skw += c;
|
idx++;
|
}
|
}
|
this.select = res;
|
this.compareScript= compareStr;
|
}
|
|
private void buildQuery(){
|
|
|
|
}
|
|
private void buildSql(){
|
this.sql = "select " + this.select + " from " + this.tableKeyword.getTable() + " where " + this.where;
|
|
}
|
|
private BlockCode buildCode(){
|
BlockCode blockCode = new BlockCode();
|
String funcName = "func_" + this.idx;
|
StringBuilder csb = new StringBuilder();
|
String sql = "\"" +this.sql + "\"";
|
csb.append(" def " + funcName + "(){\n");
|
csb.append(" Map<String,Object> pars = new HashMap<>();\n");
|
csb.append(" def sql= " +sql + ";\n");
|
|
this.parameters.forEach((k,v)->{
|
String pname= "p_" + k;
|
csb.append(" def " + pname + " = " + v.getScript()+";\n");
|
csb.append(" pars.put('" +v.getName() +"'," + pname +");\n");
|
|
});
|
csb.append(" def rs = service.execQuery(sql,pars);\n");
|
csb.append(" if(rs " + this.compareScript + "){\n");
|
csb.append(" return true;\n");
|
csb.append(" }else{\n");
|
csb.append(" return false;\n");
|
csb.append(" }\n");
|
csb.append(" }\n");
|
String ol = blockText.getLogic();
|
String logic ="";
|
if(StringUtils.isNotBlank(ol)){
|
if(ol.trim().equals("并且") || ol.equals("and")){
|
logic = "&&";
|
}else if(ol.trim().equals("或者") || ol.equals("||")){
|
logic="||";
|
}
|
}
|
|
blockCode.setName(funcName);
|
blockCode.setLogic(logic);
|
blockCode.setCode(csb.toString());
|
blockCode.setSql(this.sql);
|
return blockCode;
|
}
|
private boolean isSplit(String c) {
|
return Arrays.stream(splits).filter(k -> k.equals(c)).findFirst().isPresent();
|
}
|
|
private MatchResult getMatchResult(String skw,int idx){
|
Keyword kw = findKeyword(skw);
|
MatchResult mr = new MatchResult();
|
if(kw == null){
|
mr.setInput(skw);
|
mr.setOutput(skw);
|
mr.setIdx(idx);
|
}else{
|
mr = matchByKeyword(skw,kw,idx);
|
}
|
return mr;
|
}
|
|
private MatchResult matchByKeyword(String skw,Keyword kw,int idx){
|
MatchResult mr = new MatchResult();
|
switch (kw.getKeywordType()){
|
case SYNTAX:
|
if(kw.getName().equals("包含")){
|
return matchLike(skw,kw,idx);
|
}else{
|
mr.setIdx(idx);
|
mr.setInput(skw);
|
mr.setOutput(kw.getCode());
|
}
|
break;
|
case FUNCTION:
|
|
if(kw.getCode().trim().equals("count")){
|
mr = matchCount(skw,kw,idx);
|
}else{
|
mr.setIdx(idx);
|
mr.setInput(skw);
|
mr.setOutput(kw.getCode());
|
break;
|
}
|
break;
|
case FIELD_NAME:
|
mr.setIdx(idx);
|
mr.setInput(skw);
|
mr.setOutput(kw.getField());
|
break;
|
case TIME_RANGE_FUNCTION:
|
mr = this.matchTimeRange(skw,kw,idx);
|
|
}
|
return mr;
|
}
|
|
private Keyword findKeyword(String skw) {
|
List<Keyword> kws = keywords.stream().filter(k -> k.getName().equals(skw)).collect(Collectors.toList());
|
|
if (kws.size() == 0) {
|
return null;
|
} else if (kws.size() == 1) {
|
return kws.get(0);
|
} else if (kws.size() > 1) {
|
//不同表名的字段名可能是重复的，需要再根据表名做判断
|
String tab = this.tableKeyword.getTable();
|
List<Keyword> kws2 = kws.stream().filter(k -> k.getTable().equals(this.tableKeyword.getTable())).collect(Collectors.toList());
|
if (kws2.size() == 0) {
|
throw new RuntimeException("字段[" + skw + "]在数据源[" + this.tableKeyword.getTable() + "]中不存在");
|
} else if (kws2.size() > 1) {
|
throw new RuntimeException("字段[" + skw + "]在数据源[" + this.tableKeyword.getTable() + "]中存在多个同名字段");
|
} else if (kws2.size() == 1) {
|
return kws2.get(0);
|
}
|
}
|
|
throw new RuntimeException("读取字段名称时发生错误");
|
|
}
|
|
|
|
|
private MatchResult matchLike(String skw,Keyword lkw,int idx){
|
String sub = blockText.getExpression().substring(idx);
|
|
String regex = lkw.getRegex();
|
|
Pattern pattern = Pattern.compile(regex);
|
Matcher matcher = pattern.matcher(sub);
|
|
String output="";
|
|
MatchResult rs = new MatchResult();
|
if(matcher.find()){
|
int e = matcher.end();
|
String r = matcher.group(1);
|
String out = "like '%"+ r + "%'";
|
rs.setInput(skw);
|
rs.setOutput(out);
|
rs.setIdx(idx + e);
|
}else {
|
throw new RuntimeException("没有找到[包含]操作符号对应的参数");
|
}
|
|
return rs;
|
}
|
|
private MatchResult matchCount(String skw,Keyword ckw,int idx){
|
String resultText = this.blockText.getResult();
|
|
String regex = ckw.getName() + ckw.getRegex();
|
|
Pattern p = Pattern.compile(regex);
|
Matcher matcher = p.matcher(resultText);
|
|
MatchResult mr = new MatchResult();
|
|
if(matcher.find()){
|
String f = matcher.group(0);
|
int e = matcher.end();
|
Keyword fkw = this.keywords.stream().filter(k->k.getName().equals(f)
|
&& k.getKeywordType().equals(KeywordType.FIELD_NAME)
|
&& k.getTable().equals(this.tableKeyword.getTable()))
|
.findFirst().orElse(null);
|
if(fkw==null){
|
throw new RuntimeException("没有在"+ this.tableKeyword.getTable()+"中找到对应的字段定义");
|
}
|
mr.setOutput("count("+ fkw.getField() + ")");
|
|
mr.setIdx(idx +e );
|
mr.setInput(skw);
|
}else{
|
mr.setIdx(idx);
|
mr.setOutput("count(*)");
|
mr.setInput(skw);
|
}
|
|
return mr;
|
}
|
|
private MatchResult matchTimeRange(String skw, Keyword tkw,int idx){
|
|
String sub = this.blockText.getExpression().substring(idx);
|
|
String regex = tkw.getRegex();
|
|
Pattern pattern = Pattern.compile(regex);
|
Matcher matcher = pattern.matcher(sub);
|
|
String output="";
|
|
MatchResult rs = new MatchResult();
|
|
Integer day=0;
|
|
if(matcher.find()){
|
int e = matcher.end();
|
Keyword timeRangeKw = this.keywords.stream().filter(k->k.getKeywordType().equals(KeywordType.TIME_RANGE)).findFirst().orElse(null);
|
String m = matcher.group(0);
|
|
Pattern vp = Pattern.compile(timeRangeKw.getRegex());
|
|
Matcher vm = vp.matcher(m);
|
|
if(vm.find()){
|
String d = vm.group(0);
|
day = Integer.valueOf(d);
|
|
rs.setOutput(tableKeyword.getTableTimeField() + " between :beginTime and :endTime");
|
rs.setInput(skw);
|
rs.setIdx(idx + e);
|
|
//有几个问号，就要有几个对应的参数
|
Parameter p1 = new Parameter();
|
p1.setName("beginTime");
|
p1.setScript("LocalDateTime.now()");
|
p1.setTypeName("LocalDateTime");
|
Parameter p2 = new Parameter();
|
p2.setName("endTime");
|
p2.setScript("LocalDateTime.now().minusDays("+day+ ")");
|
p2.setTypeName("LocalDateTime");
|
parameters.put(p1.getName(),p1);
|
parameters.put(p2.getName(),p2);
|
|
}else{
|
throw new RuntimeException("没有在日期范围中找到指定天数");
|
}
|
}else{
|
throw new RuntimeException("没有找到日期范围的值");
|
}
|
|
return rs;
|
}
|
|
|
|
}
|
