package com.primeton.dsp.datarelease.api.sql;
import com.alibaba.druid.sql.SQLUtils;
import com.google.common.base.Preconditions;
import com.primeton.dsp.datarelease.api.model.Field;
import com.primeton.dsp.datarelease.api.model.Table;
import com.primeton.dsp.datarelease.api.model.WhereCause;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.DateValue;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.TimestampValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.MultiExpressionList;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.SelectUtils;
import org.apache.commons.lang.StringUtils;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
*
* 单表服务发布，SQL 生成预览
*
*
*
* Created by zhaopx.
* User: zhaopx
* Date: 2020/3/25
* Time: 17:50
*
*
*
* @author zhaopx
*/
public abstract class SQLQuery {
/**
* 输出 SQL
*
* @return 返回 sql str
*/
public abstract String show(boolean format);
public static class Builder {
/**
* 核心 Select
*/
Select select;
public Builder(Table table) {
net.sf.jsqlparser.schema.Table t = new net.sf.jsqlparser.schema.Table(table.getTableName());
t.setAlias(new Alias(table.getAlias(), false));
Select select = null;
// select x from t
if(table.getFields().isEmpty()) {
select = SelectUtils.buildSelectFromTable(t);
} else {
List fields = table.getFields();
SelectItem[] items = new SelectItem[fields.size()];
int i = 0;
for (Field field : fields) {
SelectExpressionItem item = new SelectExpressionItem(new Column(t, field.getFieldName()));
item.setAlias(new Alias(field.getAlias(), true));
items[i] = item;
i++;
}
select = SelectUtils.buildSelectFromTableAndSelectItems(t, items);
}
this.select = select;
}
public Builder(Table table, WhereCause... conds) {
this(table);
// 处理条件
where(conds);
}
/**
* 设置 where 调用条件。调用 where 后就不应该再调用 join 了
* @return
*/
public Builder where(WhereCause... wheres) {
if(wheres == null || wheres.length == 0) {
// 没有可加的条件
return this;
}
PlainSelect ps = (PlainSelect)select.getSelectBody();
Expression where = ps.getWhere();
net.sf.jsqlparser.schema.Table table = (net.sf.jsqlparser.schema.Table)ps.getFromItem();
if(where == null && wheres.length == 1) {
// 一个条件，就这样了。
Expression expr = buildExpression(table, wheres[0]);
ps.setWhere(expr);
} else if(where == null){
// where is null，wheres 第一个不加 and，后续都加 and。
Expression firstExpr = buildExpression(table, wheres[0]);
WhereCause[] whereCauses1toEnd = new WhereCause[wheres.length - 1];
System.arraycopy(wheres, 1, whereCauses1toEnd, 0, whereCauses1toEnd.length);
ps.setWhere(buildWhereCause(table, firstExpr, whereCauses1toEnd));
} else {
// where is not null，第一个条件就需要加 and
ps.setWhere(buildWhereCause(table, where, wheres));
}
return this;
}
/**
* 创建循环的 where 条件
* @param wheres 一个或者多个 where
* @return
*/
private Expression buildWhereCause(net.sf.jsqlparser.schema.Table table, Expression last, WhereCause... wheres) {
if(wheres.length == 1) {
Expression expression = buildExpression(table, wheres[0]);
BinaryExpression expr = null;
if("or".equalsIgnoreCase(wheres[0].getCond())){
expr = new OrExpression(last, expression);
} else {
expr = new AndExpression(last, expression);
}
return expr;
}
Expression addExpr = buildExpression(table, wheres[0]);
BinaryExpression expr = null;
if("or".equalsIgnoreCase(wheres[0].getCond())){
expr = new OrExpression(last, addExpr);
} else {
expr = new AndExpression(last, addExpr);
}
WhereCause[] whereCauses1toEnd = new WhereCause[wheres.length - 1];
System.arraycopy(wheres, 1, whereCauses1toEnd, 0, whereCauses1toEnd.length);
// 递归处理每一个表达式
return buildWhereCause(table, expr, whereCauses1toEnd);
}
/**
* 根据 where 条件，把前台选择的条件转为 sql 支持的结构。
* @param table 表名称
* @param cause 条件
* @return
*/
private Expression buildExpression(net.sf.jsqlparser.schema.Table table, WhereCause cause) {
String[] mutilValue = cause.getValue() != null ? cause.getValue().split(",") : new String[]{};
if (mutilValue.length > 1) {
// 多值的情况 select * from table where id in('a', 'b')
// 多值的必须是 in 或者 notin 的情况，如果不是，强制改变语句为 in 的条件
String opera = cause.getOpera();
if(!"in".equalsIgnoreCase(opera) && !"notin".equalsIgnoreCase(opera)) {
cause.setOpera("in");
}
// 多个值的情况
return buildSingleValue(table, cause);
}
// 单值的条件
return buildSingleValue(table, cause);
}
/**
* 编译单值的条件
* @param table
* @param cause
* @return
*/
private Expression buildSingleValue(net.sf.jsqlparser.schema.Table table, WhereCause cause) {
Expression valueExpr = null;
if(StringUtils.isNotBlank(cause.getToFieldName())) {
// 第二个表名称和表字段名都不为 null，则表达式的值为第二个表中的字段
// a.AGE > a.AGE2
valueExpr = new Column(table, cause.getToFieldName());
} else if("INT".equalsIgnoreCase(cause.getType())) {
// 表达式为常量，但是值为数值类型，SQL 中数值不加引号
valueExpr = new LongValue(cause.getValue());
} else if("DOUBLE".equalsIgnoreCase(cause.getType())) {
// 表达式为常量，但是值为浮点类型，SQL 中数值不加引号
valueExpr = new DoubleValue(cause.getValue());
} else if("DATE".equalsIgnoreCase(cause.getType())) {
// 表达式为常量，但是值为日期类型，SQL 中数值不加引号
// 日期类型为：yyyy-[M]M-[d]d
valueExpr = new DateValue(cause.getValue());
} else if("DATETIME".equalsIgnoreCase(cause.getType())) {
// 表达式为常量，但是值为日期类型，SQL 中数值不加引号
// 日期类型为：yyyy-[M]M-[d]d HH:mm:ss
valueExpr = new TimestampValue(cause.getValue());
} else {
// 表达式值为常量，字符串， NAME = 'X'
valueExpr = new StringValue(cause.getValue());
}
if("=".equals(cause.getOpera())) {
EqualsTo equals = new EqualsTo();
equals.setLeftExpression(new Column(table, cause.getFieldName()));
equals.setRightExpression(valueExpr);
return equals;
} else if(">".equals(cause.getOpera())) {
GreaterThan greaterThan = new GreaterThan();
greaterThan.setLeftExpression(new Column(table, cause.getFieldName()));
greaterThan.setRightExpression(valueExpr);
return greaterThan;
} else if(">=".equals(cause.getOpera())) {
GreaterThanEquals greaterThanEquals = new GreaterThanEquals();
greaterThanEquals.setLeftExpression(new Column(table, cause.getFieldName()));
greaterThanEquals.setRightExpression(valueExpr);
return greaterThanEquals;
} else if("<".equals(cause.getOpera())) {
MinorThan minorThan = new MinorThan();
minorThan.setLeftExpression(new Column(table, cause.getFieldName()));
minorThan.setRightExpression(valueExpr);
return minorThan;
} else if("<=".equals(cause.getOpera())) {
MinorThanEquals minorThanEquals = new MinorThanEquals();
minorThanEquals.setLeftExpression(new Column(table, cause.getFieldName()));
minorThanEquals.setRightExpression(valueExpr);
return minorThanEquals;
} else if("<>".equals(cause.getOpera()) || "!=".equals(cause.getOpera())) {
NotEqualsTo notEqualsTo = new NotEqualsTo();
notEqualsTo.setLeftExpression(new Column(table, cause.getFieldName()));
notEqualsTo.setRightExpression(valueExpr);
return notEqualsTo;
} else if("in".equalsIgnoreCase(cause.getOpera())) {
String[] mutilValue = cause.getValue().split(",");
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(new Column(table, cause.getFieldName()));
MultiExpressionList list = new MultiExpressionList();
List inVals = new ArrayList<>();
for (String val : mutilValue) {
inVals.add(new StringValue(val));
}
list.addExpressionList(new ExpressionList(inVals));
inExpression.setRightItemsList(list);
return inExpression;
} else if("notin".equalsIgnoreCase(cause.getOpera())) {
String[] mutilValue = cause.getValue().split(",");
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(new Column(table, cause.getFieldName()));
// not 否定条件
inExpression.setNot(true);
MultiExpressionList list = new MultiExpressionList();
List inVals = new ArrayList<>();
for (String val : mutilValue) {
inVals.add(new StringValue(val));
}
list.addExpressionList(new ExpressionList(inVals));
inExpression.setRightItemsList(list);
return inExpression;
} else if("l".equalsIgnoreCase(cause.getOpera())) {
Preconditions.checkNotNull(cause.getValue(), " like value must not be blank");
// 如果自带 %，则说明需要匹配值的 %，用转义
String likeValue = cause.getValue().replaceAll("%", "\\%");
valueExpr = new StringValue("%"+likeValue+"%");
LikeExpression likeExpression = new LikeExpression();
likeExpression.setLeftExpression(new Column(table, cause.getFieldName()));
likeExpression.setRightExpression(valueExpr);
return likeExpression;
}
EqualsTo equals = new EqualsTo();
equals.setLeftExpression(new Column(table, cause.getFieldName()));
equals.setRightExpression(valueExpr);
return equals;
}
/**
* 设置 where 调用条件。调用 where 后就不应该再调用 join 了
* @return
*/
public Builder where(Collection wheres) {
where(wheres.toArray(new WhereCause[wheres.size()]));
return this;
}
/**
* 创建 SQL
*
* @return
*/
public SQLQuery build() {
return new SQLQuery() {
@Override
public String show(boolean format) {
if(!format) {
return select.toString();
}
String sql = select.toString();
// 美化一下 SQL
sql = SQLUtils.formatMySql(sql);
return sql;
}
};
}
}
public static void main(String[] args) {
StringValue valueExpr = new StringValue("%王%");
LikeExpression likeExpression = new LikeExpression();
likeExpression.setLeftExpression(new Column("NAME"));
likeExpression.setRightExpression(valueExpr);
System.out.println(likeExpression.toString());
}
}