TableSqlCache.java

package com.yanzuoguang.dao.impl;

import com.yanzuoguang.dao.DaoConst;
import com.yanzuoguang.util.cache.MemoryCache;
import com.yanzuoguang.util.helper.StringHelper;

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;

/**
 * 缓存的SQL语句信息
 *
 * @author 颜佐光
 */
public class TableSqlCache {

    /**
     * 分页标记
     */
    public static final String PAGE_SIZE_TAG = "_PageSize";

    /**
     * 表结构信息
     */
    private TableStruct table;

    /**
     * 缓存的SQL信息，按照名称进行缓存
     */
    private MemoryCache<SqlData> nameCache = new MemoryCache<SqlData>();

    /**
     * 根据Sql语句类型进行缓存，按照类型进行缓存
     */
    private MemoryCache<List<SqlData>> typeCache = new MemoryCache<>();

    /**
     * 构造函数
     */
    public TableSqlCache() {
        this.table = new TableStruct();
    }


    /**
     * 获取所有的Sql语句执行类型
     *
     * @param sqlType Sql语句执行类型
     * @return Sql语句列表
     */
    public List<SqlData> getSqlType(int sqlType) {
        String keyType = String.valueOf(sqlType);
        return typeCache.get(keyType);
    }

    /**
     * 添加SQL语句组
     *
     * @param sqls
     */
    public void add(SqlData... sqls) {
        for (SqlData sql : sqls) {
            this.add(sql);
        }
    }

    /**
     * 添加SQL
     *
     * @param sqlName SQL名称
     * @param sql     SQL语句
     * @param fields  字段
     */
    public SqlData add(String sqlName, String sql, String... fields) {
        SqlData model = new SqlData(sqlName, sql, fields);
        return this.add(model);
    }

    /**
     * 根据现有SQL语句生成分页查询
     *
     * @param from
     * @param sql
     * @return
     */
    public SqlData addPageSize(SqlData from, String sql) {
        SqlData to = from.copy();
        to.setSql(sql);
        to.setName(to.getName() + TableSqlCache.PAGE_SIZE_TAG);
        return this.add(to);
    }

    /**
     * 添加可执行的SQL语句
     *
     * @param sql SQL语句
     */
    public SqlData add(SqlData sql) {
        if (sql != null) {
            this.nameCache.put(sql.getName(), sql);
            String keyType = String.valueOf(sql.getSqlType());
            this.typeCache.get(keyType, new ArrayList<>()).add(sql);
        }
        return sql;
    }

    /**
     * 生成GroupSQL语句
     *
     * @param tableWhereField
     * @param addField
     */
    public void addGroup(TableFieldString tableWhereField, TableFieldString addField) {
        this.table.addGroupSql(this, tableWhereField, addField);
    }


    /**
     * 生成GroupSQL语句
     *
     * @param sqlName
     * @param whereFields
     */
    public void addSaveWith(String sqlName, TableFieldString whereFields) {
        this.table.addSaveWithSql(this, sqlName, whereFields);
    }

    /**
     * 生成Group执行的SQL语句
     *
     * @param sqlName
     * @param tableWhereField
     * @param addField
     * @param sql
     * @param
     */
    public List<SqlData> addGroupList(String sqlName, TableFieldString tableWhereField, TableFieldString addField,
                                      String sql, String... field) {
        Map<String, Boolean> sqlFieldHas = new HashMap<>(DaoConst.COLLECTION_INIT_SIZE);
        Map<String, Boolean> sqlFieldWhere = new HashMap<>(DaoConst.COLLECTION_INIT_SIZE);
        Map<String, Boolean> sqlFieldAdd = new HashMap<>(DaoConst.COLLECTION_INIT_SIZE);

        SqlData sqlInit = new SqlData(sqlName + "_GroupInit", "", field);
        SqlData sqlAdd = new SqlData(sqlName + "_GroupAdd", "", field);

        // 1. 获取SQL语句中已包含的字段
        String sqlField = sql.substring(0, sql.replace("\n", " ")
                .replace("\t", " ")
                .toLowerCase().indexOf(" from ")) + ",";
        // 去掉字段中的函数
        sqlField = sqlField.replaceAll("\\(.+?\\)", "");
        String regex = "[\\s,]*(\\S+?)\\s*?,";
        Pattern p = Pattern.compile(regex);
        Matcher m = p.matcher(sqlField);
        // 寻找到的代码片段 不包含分括号
        while (m.find()) {
            String name = m.group(1);
            if (name.indexOf(".") >= -1) {
                name = name.substring(name.indexOf(".") + 1);
            }
            sqlFieldHas.put(name.toLowerCase(), true);
        }

        // 2. 判断所有纬度字段是否已经存在
        for (String item : tableWhereField.getFields()) {
            String name = item.toLowerCase();
            if (!sqlFieldHas.containsKey(name)) {
                throw new RuntimeException("SQL语句" + item + "不存在纬度" + item);
            }
            sqlFieldWhere.put(name, true);
        }
        for (String item : addField.getFields()) {
            String name = item.toLowerCase();
            sqlFieldAdd.put(name, true);
        }

        // 3. 生成SQL语句
        String selectModel = "SELECT {FromField} FROM ( {SQL} )t";
        String initModel = "INSERT INTO {TABLE}( {CreateField} ) " +
                "SELECT {FromInitField} FROM( {SelectSQL} )a " +
                "LEFT JOIN {TABLE} AS b ON a.{Key} = b.{Key} WHERE b.{Key} IS NULL";
        String addModel = "UPDATE {TABLE} AS a INNER JOIN ( {SelectSQL} ) AS b ON a.{Key} = b.{Key} SET {addConst} ";

        // 定义需要处理的SQL对象
        Map<String, StringBuilder> map = new HashMap<>(DaoConst.COLLECTION_INIT_SIZE);
        addString(map, "{SQL}", sql);
        addString(map, "{TABLE}", this.table.getName());

        if (this.table.getKeyType() == String.class) {
            addString(map, "{Key}", this.table.getKeyName());
        } else if (!StringHelper.isEmpty(this.table.getMD5KeyName())) {
            addString(map, "{Key}", this.table.getMD5KeyName());
        } else {
            throw new RuntimeException("表中未包含MD5字段");
        }

        String lowerKey = this.table.getKeyName().toLowerCase();
        String lowerMD5Key = this.table.getMD5KeyName().toLowerCase();

        List<TableFieldVo> allField = new ArrayList<TableFieldVo>();
        allField.add(this.table.getKey());
        allField.addAll(this.table.getFields());
        for (TableFieldVo fieldVo : allField) {
            String fieldName = fieldVo.name;
            String fieldLName = fieldVo.lName;
            if (fieldLName.equals(lowerKey) && this.table.getKeyType() != String.class) {
                continue;
            }

            // 处理逗号
            addFlag(map, "{CreateField}", ",");
            addFlag(map, "{FromInitField}", ",");

            addString(map, "{CreateField}", fieldName);

            if (fieldLName.equals(lowerKey) || fieldLName.equals(lowerMD5Key)) {
                addFlag(map, "{FromField}", ",");
                // 处理需要MD5的字段
                addString(map, "{FromField}", "MD5(CONCAT(");
                int i = 0;
                for (String whereField : tableWhereField.getFields()) {
                    if (i > 0) {
                        addString(map, "{FromField}", ",':',");
                    }
                    addString(map, "{FromField}", whereField);
                    i++;
                }
                addString(map, "{FromField}", ")) AS ");
                addString(map, "{FromField}", fieldName);
                addString(map, "{FromInitField}", "a." + fieldName);
            } else {

                // 处理常规字段
                if (sqlFieldAdd.containsKey(fieldLName) && sqlFieldHas.containsKey(fieldLName)) {
                    addFlag(map, "{addConst}", ",");
                    addString(map, "{addConst}", "a." + fieldName + "=a." + fieldName + "+b." + fieldName);
                }

                if (sqlFieldHas.containsKey(fieldLName)) {
                    addFlag(map, "{FromField}", ",");
                    addString(map, "{FromField}", fieldName);
                }

                if (sqlFieldWhere.containsKey(fieldLName)) {
                    addString(map, "{FromInitField}", "a." + fieldName);
                } else if (sqlFieldAdd.containsKey(fieldLName)) {
                    addString(map, "{FromInitField}", "0 AS " + fieldName);
                } else {
                    addString(map, "{FromInitField}", "a." + fieldName);
                    System.err.println("纬度" + this.table.getName() + "->" + sqlName + "->" + fieldName + "存在，但是没有统计");
                }
            }
        }

        // 最终SQL语句处理
        selectModel = replaceString(map, selectModel);
        addString(map, "{SelectSQL}", selectModel);
        sqlInit.setSql(replaceString(map, initModel));
        sqlAdd.setSql(replaceString(map, addModel));

        this.add(sqlInit);
        this.add(sqlAdd);

        List<SqlData> ret = new ArrayList<>();
        ret.add(sqlInit);
        ret.add(sqlAdd);
        return ret;
    }

    /**
     * 生成该字段值是否存在的SQL语句
     *
     * @param sqlName        SQL语句名称
     * @param allowNullField 允许为空字段
     */
    public void addExist(String sqlName, String... allowNullField) {
        this.addExist(sqlName, null, new TableFieldString(allowNullField));
    }

    /**
     * 生成该字段值是否存在的SQL语句
     *
     * @param sqlName        SQL语句名称
     * @param mustField      必须输入字段
     * @param allowNullField 允许为空字段
     */
    public void addExist(String sqlName, TableFieldString mustField, String... allowNullField) {
        this.table.addExist(this, sqlName, mustField, new TableFieldString(allowNullField));
    }

    /**
     * 生成该字段值是否存在的SQL语句
     *
     * @param sqlName        SQL语句名称
     * @param mustField      必须输入字段
     * @param allowNullField 允许为空字段
     */
    public void addExist(String sqlName, TableFieldString mustField, TableFieldString allowNullField) {
        this.table.addExist(this, sqlName, mustField, allowNullField);
    }

    private void addString(Map<String, StringBuilder> map, String name, String value) {
        if (!map.containsKey(name)) {
            map.put(name, new StringBuilder());
        }
        map.get(name).append(value);
    }

    private void addFlag(Map<String, StringBuilder> map, String name, String flag) {
        if (!map.containsKey(name)) {
            addString(map, name, "");
        } else if (map.get(name).length() > 0) {
            addString(map, name, flag);
        }
    }

    private String replaceString(Map<String, StringBuilder> map, String sql) {
        for (Map.Entry<String, StringBuilder> key : map.entrySet()) {
            sql = sql.replace(key.getKey(), key.getValue().toString());
        }
        return sql;
    }

    public TableStruct getTable() {
        return table;
    }

    public void setTable(TableStruct table) {
        this.table = table;
    }

    public MemoryCache<SqlData> getNameCache() {
        return nameCache;
    }

    public void setNameCache(MemoryCache<SqlData> nameCache) {
        this.nameCache = nameCache;
    }
}