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

package com.primeton.dsp.datarelease.api.sql;

import com.google.common.collect.Sets;
import com.primeton.dsp.datarelease.api.model.JoinPair;
import com.primeton.dsp.datarelease.api.model.Table;
import com.primeton.dsp.datarelease.api.model.WhereCause;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 
 * 服务发布多表关联，和单表  服务发布时生成预览SQL。
 * 
 * <pre>
 *
 * Created by zhaopx.
 * User: zhaopx
 * Date: 2020/3/27
 * Time: 17:15
 *
 * </pre>
 *
 * @author zhaopx
 */

@Slf4j
public abstract class SQLCreatorFactory {


    /**
     * 单表生成 SQL
     * @return
     */
    public abstract String singleTable(Table table, WhereCause[] conds);


    /**
     * 多表生成 SQL
     *
     * @param tables
     * @param joins
     * @param conds
     * @return
     */
    public abstract String mutilTable(Table[] tables, JoinPair[] joins, WhereCause[] conds);


    /**
     * 生成一个实例
     * @return
     */
    public static SQLCreatorFactory newInstance() {
        return new SQLCreatorFactory() {
            @Override
            public String singleTable(Table table, WhereCause[] conds) {
                //单表生成SQL预览
                SQLQuery sqlQuery = new SQLQuery.Builder(table)
                        .where(conds)
                        .build();

                //生成的SQL要预览，这里美化一下
                String showSQL = sqlQuery.show(true);
                log.info("c sql {}", showSQL);
                return showSQL;
            }

            @Override
            public String mutilTable(Table[] tables, JoinPair[] joins, WhereCause[] conds) {
                Map<String, SQLJoin.JoinTable> tableMapping = new HashMap<>();
                for(Table table : tables) {
                    tableMapping.put(table.getTableName(), new SQLJoin.JoinTable(table));
                }

                // join 条件，如果量表 join 多次也当做 where
                List<JoinPair> joinList = new ArrayList<>();
                // where 条件，把多余的 join 条件放到 where 条件中
                Set<WhereCause> condSet = Sets.newHashSet(conds);

                // 相同的两个表，join 多次的情况，取第一个作为 join，后面的作为 where 条件
                Map<String, JoinPair> joinPairCache = new HashMap<>();
                for(JoinPair jp : joins) {
                    //左表加右表
                    String cacheKey = StringUtils.upperCase(jp.getLeft() + jp.getRight());
                    if(joinPairCache.get(cacheKey) == null) {
                        joinPairCache.put(cacheKey, jp);
                        joinList.add(jp);
                    } else {
                        // 已经存在关联的 join 转为 where 后的条件
                        WhereCause cause = new WhereCause();
                        cause.setTableName(jp.getLeft());
                        cause.setFieldName(jp.getLeftField());
                        cause.setToTableName(jp.getRight());
                        cause.setToFieldName(jp.getRightField());
                        cause.setOpera("=");
                        condSet.add(cause);
                    }
                }

                SQLJoin.Builder builder = null;
                if(joinList.size() == 1) {
                    // 2 个表的关联
                    SQLJoin.JoinTable left = tableMapping.get(joinList.get(0).getLeft());
                    SQLJoin.JoinTable right = tableMapping.get(joinList.get(0).getRight());

                    builder = new SQLJoin.Builder(left, joinList.get(0).getLeftField(),
                            right, joinList.get(0).getRightField(),
                            SQLJoin.JoinType.joinType(joinList.get(0).getJoinType()));
                } else {
                    // 3 个或者3 个以上的表关联
                    SQLJoin.JoinTable left = tableMapping.get(joinList.get(0).getLeft());
                    SQLJoin.JoinTable right = tableMapping.get(joinList.get(0).getRight());

                    builder = new SQLJoin.Builder(left, joinList.get(0).getLeftField(),
                            right, joinList.get(0).getRightField(),
                            SQLJoin.JoinType.joinType(joinList.get(0).getJoinType()));

                    for(int i = 1; i < joinList.size(); i++) {
                        JoinPair joinPair = joinList.get(i);
                        SQLJoin.JoinTable leftTable = tableMapping.get(joinPair.getLeft());
                        SQLJoin.JoinTable rightTable = tableMapping.get(joinPair.getRight());

                        // 下面使用要重新 new 一个，防止重新设置别名是影响原来的别名
                        if(builder.isCachedTable(leftTable.getTableName())) {
                            //A JOIN B JOIN C， 正好是符合的
                            builder.join(leftTable,
                                    joinPair.getLeftField(),
                                    rightTable,
                                    joinPair.getRightField(),
                                    SQLJoin.JoinType.joinType(joinPair.getJoinType()));
                        } else {
                            // A JOIN C
                            // B JOIN C 模式，生成 JOIN 应该是 A JOIN C JOIN B 的方式
                            SQLJoin.JoinType joinType = SQLJoin.JoinType.joinType(joinPair.getJoinType());
                            SQLJoin.JoinType realJoinType = joinType;
                            switch (joinType) {
                                case LEFT_JOIN:
                                    realJoinType = SQLJoin.JoinType.RIGHT_JOIN;
                                    break;
                                case RIGHT_JOIN:
                                    realJoinType = SQLJoin.JoinType.LEFT_JOIN;
                                    break;
                                default:
                            }

                            // 调换一下顺序
                            builder.join(
                                    rightTable,
                                    joinPair.getRightField(),
                                    leftTable,
                                    joinPair.getLeftField(),
                                    realJoinType);
                        }
                    }
                }

                // 把where 条件加入进来
                builder.where(condSet);
                SQLJoin sql = builder.build();
                // 前端不显示 SQL，如果加了回车在执行时会乱码报错。因此这里不能加回车进行格式化
                String resultSql = sql.show(true);
                log.info(resultSql);
                return resultSql;
            }
        };
    }
}