java-classic-code/entity-to-sql/sql/SQLQuery.java

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 生成预览
 *
 * <pre>
 *
 * Created by zhaopx.
 * User: zhaopx
 * Date: 2020/3/25
 * Time: 17:50
 *
 * </pre>
 *
 * @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<Field> 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<Expression> 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<Expression> 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<WhereCause> 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());
    }
}