TableSqlCache.java 11.3 KB
Newer Older
yanzg's avatar
yanzg committed
1
package com.yanzuoguang.dao.impl;
yanzg's avatar
yanzg committed
2

yanzg's avatar
yanzg committed
3
import com.yanzuoguang.dao.DaoConst;
gongzuquan's avatar
gongzuquan committed
4
import com.yanzuoguang.util.YzgError;
yanzg's avatar
yanzg committed
5 6 7 8 9 10 11 12 13 14 15 16
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语句信息
yanzg's avatar
yanzg committed
17
 *
yanzg's avatar
yanzg committed
18
 * @author 颜佐光
yanzg's avatar
yanzg committed
19 20 21 22 23 24 25 26 27
 */
public class TableSqlCache {

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

    /**
yanzg's avatar
yanzg committed
28
     * 表结构信息
yanzg's avatar
yanzg committed
29
     */
yanzg's avatar
yanzg committed
30
    private TableStruct table;
yanzg's avatar
yanzg committed
31 32

    /**
yanzg's avatar
yanzg committed
33
     * 缓存的SQL信息,按照名称进行缓存
yanzg's avatar
yanzg committed
34
     */
yanzg's avatar
yanzg committed
35
    private MemoryCache<SqlData> nameCache = new MemoryCache<SqlData>();
yanzg's avatar
yanzg committed
36

yanzg's avatar
yanzg committed
37
    /**
yanzg's avatar
yanzg committed
38
     * 根据Sql语句类型进行缓存,按照类型进行缓存
yanzg's avatar
yanzg committed
39
     */
yanzg's avatar
yanzg committed
40
    private MemoryCache<List<SqlData>> typeCache = new MemoryCache<>();
yanzg's avatar
yanzg committed
41

yanzg's avatar
yanzg committed
42 43 44 45
    /**
     * 构造函数
     */
    public TableSqlCache() {
yanzg's avatar
yanzg committed
46
        this.table = new TableStruct();
yanzg's avatar
yanzg committed
47 48
    }

yanzg's avatar
yanzg committed
49 50 51 52 53 54 55 56 57

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

yanzg's avatar
yanzg committed
61 62 63 64 65 66 67 68 69 70 71
    /**
     * 添加SQL语句组
     *
     * @param sqls
     */
    public void add(SqlData... sqls) {
        for (SqlData sql : sqls) {
            this.add(sql);
        }
    }

yanzg's avatar
yanzg committed
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
    /**
     * 添加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();
yanzg's avatar
yanzg committed
93 94
        to.setSql(sql);
        to.setName(to.getName() + TableSqlCache.PAGE_SIZE_TAG);
yanzg's avatar
yanzg committed
95 96 97
        return this.add(to);
    }

yanzg's avatar
yanzg committed
98 99 100 101 102 103 104 105 106 107 108 109 110 111
    /**
     * 添加可执行的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;
    }

yanzg's avatar
yanzg committed
112 113 114 115 116 117 118
    /**
     * 生成GroupSQL语句
     *
     * @param tableWhereField
     * @param addField
     */
    public void addGroup(TableFieldString tableWhereField, TableFieldString addField) {
yanzg's avatar
yanzg committed
119
        this.table.addGroupSql(this, tableWhereField, addField);
yanzg's avatar
yanzg committed
120 121
    }

yanzg's avatar
yanzg committed
122 123 124 125 126 127 128 129 130
    /**
     * 生成GroupSQL语句
     *
     * @param tableWhereField
     * @param addField
     */
    public void addGroup(TableFieldString tableWhereField, TableFieldString replaceField, TableFieldString addField) {
        this.table.addGroupSql(this, tableWhereField, replaceField, addField);
    }
yanzg's avatar
yanzg committed
131 132 133 134 135 136 137 138

    /**
     * 生成GroupSQL语句
     *
     * @param sqlName
     * @param whereFields
     */
    public void addSaveWith(String sqlName, TableFieldString whereFields) {
yanzg's avatar
yanzg committed
139
        this.table.addSaveWithSql(this, sqlName, whereFields);
yanzg's avatar
yanzg committed
140 141 142 143 144 145 146 147 148 149 150 151 152
    }

    /**
     * 生成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) {
yanzg's avatar
yanzg committed
153 154 155
        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);
yanzg's avatar
yanzg committed
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178

        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. 判断所有纬度字段是否已经存在
yanzg's avatar
yanzg committed
179
        for (String item : tableWhereField.getFields()) {
yanzg's avatar
yanzg committed
180 181
            String name = item.toLowerCase();
            if (!sqlFieldHas.containsKey(name)) {
yanzg's avatar
yanzg committed
182
                throw YzgError.getRuntimeException("032", sqlName, item);
yanzg's avatar
yanzg committed
183 184 185
            }
            sqlFieldWhere.put(name, true);
        }
yanzg's avatar
yanzg committed
186
        for (String item : addField.getFields()) {
yanzg's avatar
yanzg committed
187 188 189 190 191 192
            String name = item.toLowerCase();
            sqlFieldAdd.put(name, true);
        }

        // 3. 生成SQL语句
        String selectModel = "SELECT {FromField} FROM ( {SQL} )t";
yanzg's avatar
yanzg committed
193
        String initModel = "INSERT INTO {TABLE}( {CreateField} ) " +
yanzg's avatar
yanzg committed
194
                "SELECT {FromInitField} FROM( {SelectSQL} )a " +
yanzg's avatar
yanzg committed
195 196
                "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} ";
yanzg's avatar
yanzg committed
197 198

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

yanzg's avatar
yanzg committed
203 204 205 206
        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());
yanzg's avatar
yanzg committed
207
        } else {
gongzuquan's avatar
gongzuquan committed
208
            throw YzgError.getRuntimeException("033");
yanzg's avatar
yanzg committed
209 210
        }

yanzg's avatar
yanzg committed
211 212
        String lowerKey = this.table.getKeyName().toLowerCase();
        String lowerMD5Key = this.table.getMD5KeyName().toLowerCase();
yanzg's avatar
yanzg committed
213 214

        List<TableFieldVo> allField = new ArrayList<TableFieldVo>();
yanzg's avatar
yanzg committed
215 216
        allField.add(this.table.getKey());
        allField.addAll(this.table.getFields());
yanzg's avatar
yanzg committed
217 218 219
        for (TableFieldVo fieldVo : allField) {
            String fieldName = fieldVo.name;
            String fieldLName = fieldVo.lName;
yanzg's avatar
yanzg committed
220
            if (fieldLName.equals(lowerKey) && this.table.getKeyType() != String.class) {
yanzg's avatar
yanzg committed
221 222 223 224 225 226 227 228 229 230 231 232 233 234
                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;
yanzg's avatar
yanzg committed
235
                for (String whereField : tableWhereField.getFields()) {
yanzg's avatar
yanzg committed
236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
                    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);
yanzg's avatar
yanzg committed
264
                    System.err.println("纬度" + this.table.getName() + "->" + sqlName + "->" + fieldName + "存在,但是没有统计");
yanzg's avatar
yanzg committed
265 266 267 268 269 270 271
                }
            }
        }

        // 最终SQL语句处理
        selectModel = replaceString(map, selectModel);
        addString(map, "{SelectSQL}", selectModel);
yanzg's avatar
yanzg committed
272 273
        sqlInit.setSql(replaceString(map, initModel));
        sqlAdd.setSql(replaceString(map, addModel));
yanzg's avatar
yanzg committed
274 275 276 277

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

yanzg's avatar
yanzg committed
278
        List<SqlData> ret = new ArrayList<>();
yanzg's avatar
yanzg committed
279 280 281 282 283 284 285 286
        ret.add(sqlInit);
        ret.add(sqlAdd);
        return ret;
    }

    /**
     * 生成该字段值是否存在的SQL语句
     *
yanzg's avatar
yanzg committed
287 288
     * @param sqlName        SQL语句名称
     * @param allowNullField 允许为空字段
yanzg's avatar
yanzg committed
289
     */
yanzg's avatar
yanzg committed
290 291
    public void addExist(String sqlName, String... allowNullField) {
        this.addExist(sqlName, null, new TableFieldString(allowNullField));
yanzg's avatar
yanzg committed
292 293
    }

yanzg's avatar
yanzg committed
294 295 296 297 298 299 300 301 302 303 304
    /**
     * 生成该字段值是否存在的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));
    }

yanzg's avatar
yanzg committed
305 306 307 308 309 310 311 312 313
    /**
     * 生成该字段值是否存在的SQL语句
     *
     * @param sqlName        SQL语句名称
     * @param mustField      必须输入字段
     * @param allowNullField 允许为空字段
     */
    public void addExist(String sqlName, TableFieldString mustField, TableFieldString allowNullField) {
        this.table.addExist(this, sqlName, mustField, allowNullField);
yanzg's avatar
yanzg committed
314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331
    }

    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) {
yanzg's avatar
yanzg committed
332 333
        for (Map.Entry<String, StringBuilder> key : map.entrySet()) {
            sql = sql.replace(key.getKey(), key.getValue().toString());
yanzg's avatar
yanzg committed
334 335 336
        }
        return sql;
    }
yanzg's avatar
yanzg committed
337 338 339 340 341 342 343 344 345

    public TableStruct getTable() {
        return table;
    }

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

yanzg's avatar
yanzg committed
346 347
    public MemoryCache<SqlData> getNameCache() {
        return nameCache;
yanzg's avatar
yanzg committed
348 349
    }

yanzg's avatar
yanzg committed
350 351
    public void setNameCache(MemoryCache<SqlData> nameCache) {
        this.nameCache = nameCache;
yanzg's avatar
yanzg committed
352
    }
yanzg's avatar
yanzg committed
353
}