Examples with TranslateExtend org.sagacity.sqltoy.model.inner.TranslateExtend used on opensource projects

Example 1 with TranslateExtend

use of org.sagacity.sqltoy.model.inner.TranslateExtend in project sagacity-sqltoy by chenrenfei.

the class ResultUtils method getResultSet.

private static List getResultSet(SqlToyConfig sqlToyConfig, SqlToyContext sqlToyContext, Connection conn, ResultSet rs, UpdateRowHandler updateRowHandler, DecryptHandler decryptHandler, int rowCnt, HashMap<String, Integer> labelIndexMap, String[] labelNames, int startColIndex) throws Exception {
    // 字段连接(多行数据拼接成一个数据,以一行显示)
    LinkModel linkModel = sqlToyConfig.getLinkModel();
    // update 2020-09-13 存在多列link(独立出去编写,避免对单列产生影响)
    if (linkModel != null && linkModel.getColumns().length > 1) {
        return getMoreLinkResultSet(sqlToyConfig, sqlToyContext, decryptHandler, conn, rs, rowCnt, labelIndexMap, labelNames, startColIndex);
    }
    List<List> items = new ArrayList();
    // 判断是否有缓存翻译器定义
    Boolean hasTranslate = (sqlToyConfig.getTranslateMap().isEmpty()) ? false : true;
    HashMap<String, Translate> translateMap = sqlToyConfig.getTranslateMap();
    HashMap<String, HashMap<String, Object[]>> translateCache = null;
    if (hasTranslate) {
        translateCache = sqlToyContext.getTranslateManager().getTranslates(translateMap);
        if (translateCache == null || translateCache.isEmpty()) {
            hasTranslate = false;
            logger.debug("通过缓存配置未获取到缓存数据,请正确配置TranslateManager!");
        }
    }
    // link 目前只支持单个字段运算
    int columnSize = labelNames.length;
    int index = 0;
    // 警告阀值
    int warnThresholds = SqlToyConstants.getWarnThresholds();
    boolean warnLimit = false;
    // 最大阀值
    long maxThresholds = SqlToyConstants.getMaxThresholds();
    boolean maxLimit = false;
    // 是否判断全部为null的行记录
    boolean ignoreAllEmpty = sqlToyConfig.isIgnoreEmpty();
    // 最大值要大于等于警告阀值
    if (maxThresholds > 1 && maxThresholds <= warnThresholds) {
        maxThresholds = warnThresholds;
    }
    List rowTemp;
    if (linkModel != null) {
        Object identity = null;
        String linkColumn = linkModel.getColumns()[0];
        String linkColumnLow = linkColumn.toLowerCase();
        if (!labelIndexMap.containsKey(linkColumnLow)) {
            throw new DataAccessException("做link操作时,查询结果字段中没有字段:" + linkColumn + ",请检查sql或link 配置的正确性!");
        }
        Set<String> linkSet = new HashSet<String>();
        int linkIndex = labelIndexMap.get(linkColumnLow);
        StringBuilder linkBuffer = new StringBuilder();
        boolean hasDecorate = (linkModel.getDecorateAppendChar() == null) ? false : true;
        boolean isLeft = true;
        if (hasDecorate) {
            isLeft = linkModel.getDecorateAlign().equals("left") ? true : false;
        }
        Object preIdentity = null;
        Object linkValue;
        String linkStr;
        boolean translateLink = hasTranslate ? translateMap.containsKey(linkColumnLow) : false;
        HashMap<String, Object[]> linkTranslateMap = null;
        int linkTranslateIndex = 1;
        TranslateExtend extend = null;
        if (translateLink) {
            extend = translateMap.get(linkColumnLow).getExtend();
            linkTranslateIndex = extend.index;
            linkTranslateMap = translateCache.get(extend.column);
        }
        Object[] cacheValues;
        // 判断link拼接是否重新开始
        boolean isLastProcess = false;
        boolean doLink = true;
        while (rs.next()) {
            isLastProcess = false;
            linkValue = rs.getObject(linkColumn);
            if (linkValue == null) {
                linkStr = "";
            } else {
                if (translateLink) {
                    cacheValues = linkTranslateMap.get(linkValue.toString());
                    if (cacheValues == null) {
                        linkStr = "[" + linkValue + "]未匹配";
                        logger.debug("translate cache:{},cacheType:{}, 对应的key:{} 没有设置相应的value!", extend.cache, extend.cacheType, linkValue);
                    } else {
                        linkStr = (cacheValues[linkTranslateIndex] == null) ? "" : cacheValues[linkTranslateIndex].toString();
                    }
                } else {
                    linkStr = linkValue.toString();
                }
            }
            identity = (linkModel.getIdColumns() == null) ? "default" : getLinkColumnsId(rs, linkModel.getIdColumns());
            // 不相等
            if (!identity.equals(preIdentity)) {
                if (index != 0) {
                    items.get(items.size() - 1).set(linkIndex, linkBuffer.toString());
                    linkBuffer.delete(0, linkBuffer.length());
                    linkSet.clear();
                }
                linkBuffer.append(linkStr);
                linkSet.add(linkStr);
                if (hasTranslate) {
                    rowTemp = processResultRowWithTranslate(translateMap, translateCache, labelNames, rs, columnSize, decryptHandler, ignoreAllEmpty);
                } else {
                    rowTemp = processResultRow(rs, labelNames, columnSize, decryptHandler, ignoreAllEmpty);
                }
                if (rowTemp != null) {
                    items.add(rowTemp);
                }
                preIdentity = identity;
            } else {
                isLastProcess = true;
                doLink = true;
                if (linkModel.isDistinct() && linkSet.contains(linkStr)) {
                    doLink = false;
                }
                linkSet.add(linkStr);
                if (doLink) {
                    if (linkBuffer.length() > 0) {
                        linkBuffer.append(linkModel.getSign());
                    }
                    linkBuffer.append(hasDecorate ? StringUtil.appendStr(linkStr, linkModel.getDecorateAppendChar(), linkModel.getDecorateSize(), isLeft) : linkStr);
                }
            }
            index++;
            // 存在超出25000条数据的查询
            if (index == warnThresholds) {
                warnLimit = true;
            }
            // 提取数据超过上限(-1表示不限制)
            if (index == maxThresholds) {
                maxLimit = true;
                break;
            }
        }
        // 对最后一条写入循环值
        if (isLastProcess) {
            items.get(items.size() - 1).set(linkIndex, linkBuffer.toString());
        }
    } else {
        // 修改操作不支持link操作
        boolean isUpdate = false;
        if (updateRowHandler != null) {
            isUpdate = true;
        }
        // 循环通过java reflection将rs中的值映射到VO中
        if (hasTranslate) {
            while (rs.next()) {
                // 先修改后再获取最终值
                if (isUpdate) {
                    updateRowHandler.updateRow(rs, index);
                    rs.updateRow();
                }
                rowTemp = processResultRowWithTranslate(translateMap, translateCache, labelNames, rs, columnSize, decryptHandler, ignoreAllEmpty);
                if (rowTemp != null) {
                    items.add(rowTemp);
                }
                index++;
                // 存在超出25000条数据的查询(具体数据规模可以通过参数进行定义)
                if (index == warnThresholds) {
                    warnLimit = true;
                }
                // 超出最大提取数据阀值,直接终止数据提取
                if (index == maxThresholds) {
                    maxLimit = true;
                    break;
                }
            }
        } else {
            while (rs.next()) {
                if (isUpdate) {
                    updateRowHandler.updateRow(rs, index);
                    rs.updateRow();
                }
                rowTemp = processResultRow(rs, labelNames, columnSize, decryptHandler, ignoreAllEmpty);
                if (rowTemp != null) {
                    items.add(rowTemp);
                }
                index++;
                // 存在超出警告规模级的数据查询
                if (index == warnThresholds) {
                    warnLimit = true;
                }
                // 提取数据超过上限(-1表示不限制)
                if (index == maxThresholds) {
                    maxLimit = true;
                    break;
                }
            }
        }
    }
    // 超出警告阀值
    if (warnLimit) {
        warnLog(sqlToyConfig, index);
    }
    // 超过最大提取数据阀值
    if (maxLimit) {
        logger.error("MaxLargeResult:执行sql提取数据超出最大阀值限制{},sqlId={},具体语句={}", index, sqlToyConfig.getId(), sqlToyConfig.getSql(null));
    }
    return items;
}

Example 2 with TranslateExtend

use of org.sagacity.sqltoy.model.inner.TranslateExtend in project sagacity-sqltoy by chenrenfei.

the class MongoElasticUtils method translate.

/**
 * @todo 对结果集合进行缓存翻译
 * @param translateCache
 * @param translateMap
 * @param dataSet
 * @param dataMap
 * @param fields
 */
private static void translate(HashMap<String, HashMap<String, Object[]>> translateCache, HashMap<String, Translate> translateMap, List<List> dataSet, Map dataMap, String[] fields) {
    if (translateMap == null || translateMap.isEmpty()) {
        return;
    }
    if ((dataSet == null || dataSet.isEmpty()) && (dataMap == null || dataMap.isEmpty())) {
        return;
    }
    int[] cacheMapIndex = new int[translateMap.size()];
    int[] realIndex = new int[translateMap.size()];
    String[] lables = new String[translateMap.size()];
    String field;
    int index = 0;
    TranslateExtend extend;
    HashMap<String, Integer> map = new HashMap<String, Integer>();
    for (int i = 0; i < fields.length; i++) {
        map.put(fields[i].toLowerCase(), i);
    }
    for (int i = 0; i < fields.length; i++) {
        field = fields[i].toLowerCase();
        if (translateMap.containsKey(field)) {
            extend = translateMap.get(field).getExtend();
            cacheMapIndex[index] = i;
            // alias是对应有效列
            realIndex[index] = map.get(extend.alias);
            // 实际对应的列
            lables[index] = field;
            index++;
        }
    }
    Object value;
    HashMap<String, Object[]> keyValues;
    int cacheIndex;
    Object[] translateAry;
    if (dataSet != null) {
        int size = dataSet.size();
        int colIndex;
        for (int i = 0; i < cacheMapIndex.length; i++) {
            colIndex = cacheMapIndex[i];
            keyValues = translateCache.get(lables[i]);
            extend = translateMap.get(lables[i]).getExtend();
            cacheIndex = extend.index;
            for (int j = 0; j < size; j++) {
                value = dataSet.get(j).get(realIndex[i]);
                if (value != null) {
                    translateAry = keyValues.get(value.toString());
                    if (null != translateAry) {
                        dataSet.get(j).set(colIndex, keyValues.get(value.toString())[cacheIndex]);
                    }
                }
            }
        }
    } else {
        for (int i = 0; i < cacheMapIndex.length; i++) {
            keyValues = translateCache.get(lables[i]);
            extend = translateMap.get(lables[i]).getExtend();
            cacheIndex = extend.index;
            // 实际列
            value = dataMap.get(extend.alias);
            if (value != null) {
                translateAry = keyValues.get(value.toString());
                if (null != translateAry) {
                    dataMap.put(lables[i], keyValues.get(value.toString())[cacheIndex]);
                }
            }
        }
    }
}

Example 3 with TranslateExtend

use of org.sagacity.sqltoy.model.inner.TranslateExtend in project sagacity-sqltoy by chenrenfei.

the class QueryExecutor method translates.

/**
 * @TODO 对sql语句指定缓存翻译
 * @param translates
 * @return
 */
public QueryExecutor translates(Translate... translates) {
    if (translates != null && translates.length > 0) {
        TranslateExtend extend;
        for (Translate trans : translates) {
            extend = trans.getExtend();
            if (StringUtil.isBlank(extend.cache) || StringUtil.isBlank(extend.column)) {
                throw new IllegalArgumentException("给查询增加的缓存翻译时未定义具体的cacheName=[" + extend.cache + "] 或 对应的column=[" + extend.column + "]!");
            }
            innerModel.translates.put(extend.column, trans);
        }
    }
    return this;
}

Example 4 with TranslateExtend

use of org.sagacity.sqltoy.model.inner.TranslateExtend in project sagacity-sqltoy by chenrenfei.

the class SqlToyDaoSupport method findEntityBase.

/**
 * @TODO 提供findEntity的基础实现，供对外接口包装，额外开放了resultClass的自定义功能
 * @param entityClass
 * @param page        如分页查询则需指定，非分页则传null
 * @param entityQuery
 * @param resultClass 指定返回结果类型
 * @param isCount
 * @return
 */
private Object findEntityBase(Class entityClass, Page page, EntityQuery entityQuery, Class resultClass, boolean isCount) {
    EntityMeta entityMeta = getEntityMeta(entityClass);
    EntityQueryExtend innerModel = entityQuery.getInnerModel();
    String translateFields = "";
    // 将缓存翻译对应的查询补充到select column 上,形成select keyColumn as viewColumn 模式
    if (!innerModel.translates.isEmpty()) {
        Iterator<Translate> iter = innerModel.translates.values().iterator();
        String keyColumn;
        TranslateExtend extend;
        while (iter.hasNext()) {
            extend = iter.next().getExtend();
            // 将java模式的字段名称转化为数据库字段名称
            keyColumn = entityMeta.getColumnName(extend.keyColumn);
            if (keyColumn == null) {
                keyColumn = extend.keyColumn;
            }
            // 保留字处理
            keyColumn = ReservedWordsUtil.convertWord(keyColumn, null);
            translateFields = translateFields.concat(",").concat(keyColumn).concat(" as ").concat(extend.column);
        }
    }
    // 将notSelect构造成select，形成统一处理机制
    String[] selectFieldAry = null;
    Set<String> notSelect = innerModel.notSelectFields;
    if (notSelect != null) {
        List<String> selectFields = new ArrayList<String>();
        for (String field : entityMeta.getFieldsArray()) {
            if (!notSelect.contains(field.toLowerCase())) {
                selectFields.add(field);
            }
        }
        if (selectFields.size() > 0) {
            selectFieldAry = new String[selectFields.size()];
            selectFields.toArray(selectFieldAry);
        }
    } else {
        selectFieldAry = innerModel.fields;
    }
    // 指定的查询字段
    String fields = "";
    if (selectFieldAry != null && selectFieldAry.length > 0) {
        int index = 0;
        String colName;
        HashSet<String> cols = new HashSet<String>();
        boolean notAllPureField = false;
        for (String field : selectFieldAry) {
            // 去除重复字段
            if (!cols.contains(field)) {
                colName = entityMeta.getColumnName(field);
                // 非表字段对应pojo的属性名称
                if (colName == null) {
                    colName = field;
                    // 非字段名称
                    if (!entityMeta.getColumnFieldMap().containsKey(colName.toLowerCase())) {
                        notAllPureField = true;
                    } else {
                        // 保留字处理
                        colName = ReservedWordsUtil.convertWord(colName, null);
                    }
                } else {
                    // 保留字处理
                    colName = ReservedWordsUtil.convertWord(colName, null);
                }
                if (index > 0) {
                    fields = fields.concat(",");
                }
                fields = fields.concat(colName);
                index++;
                cols.add(field);
            }
        }
        // select 字段中可能存在max(field)或field as xxx等非字段形式
        if (notAllPureField) {
            fields = SqlUtil.convertFieldsToColumns(entityMeta, fields);
        }
    } else {
        fields = entityMeta.getAllColumnNames();
    }
    String sql = "select ".concat((innerModel.distinct) ? " distinct " : "").concat(fields).concat(translateFields).concat(" from ").concat(entityMeta.getSchemaTable(null, null));
    // where条件
    String where = "";
    // 动态组织where 后面的条件语句,此功能并不建议使用,where 一般需要指定明确条件
    if (StringUtil.isBlank(innerModel.where)) {
        if (innerModel.values != null && innerModel.values.length > 0) {
            where = SqlUtil.wrapWhere(entityMeta);
        }
    } else {
        where = SqlUtil.convertFieldsToColumns(entityMeta, innerModel.where);
    }
    if (StringUtil.isNotBlank(where)) {
        sql = sql.concat(" where ").concat(where);
    }
    // 分组和having
    if (StringUtil.isNotBlank(innerModel.groupBy)) {
        sql = sql.concat(" group by ").concat(SqlUtil.convertFieldsToColumns(entityMeta, innerModel.groupBy));
        if (StringUtil.isNotBlank(innerModel.having)) {
            sql = sql.concat(" having ").concat(SqlUtil.convertFieldsToColumns(entityMeta, innerModel.having));
        }
    }
    // 处理order by 排序
    if (!innerModel.orderBy.isEmpty()) {
        sql = sql.concat(" order by ");
        Iterator<Entry<String, String>> iter = innerModel.orderBy.entrySet().iterator();
        Entry<String, String> entry;
        String columnName;
        int index = 0;
        while (iter.hasNext()) {
            entry = iter.next();
            columnName = entityMeta.getColumnName(entry.getKey());
            if (columnName == null) {
                columnName = entry.getKey();
            }
            // 保留字处理
            columnName = ReservedWordsUtil.convertWord(columnName, null);
            if (index > 0) {
                sql = sql.concat(",");
            }
            // entry.getValue() is order way,like: desc or " "
            sql = sql.concat(columnName).concat(entry.getValue());
            index++;
        }
    }
    QueryExecutor queryExecutor;
    Class resultType = (resultClass == null) ? entityClass : resultClass;
    // :named 模式(named模式参数值必须存在)
    if (SqlConfigParseUtils.hasNamedParam(where) && StringUtil.isBlank(innerModel.names)) {
        queryExecutor = new QueryExecutor(sql, (innerModel.values == null || innerModel.values.length == 0) ? null : (Serializable) innerModel.values[0]).resultType(resultType).dataSource(getDataSource(innerModel.dataSource)).fetchSize(innerModel.fetchSize).maxRows(innerModel.maxRows);
    } else {
        queryExecutor = new QueryExecutor(sql).names(innerModel.names).values(innerModel.values).resultType(resultType).dataSource(getDataSource(innerModel.dataSource)).fetchSize(innerModel.fetchSize).maxRows(innerModel.maxRows);
    }
    // 设置是否空白转null
    queryExecutor.getInnerModel().blankToNull = innerModel.blankToNull;
    // 设置额外的缓存翻译
    if (!innerModel.translates.isEmpty()) {
        queryExecutor.getInnerModel().translates.putAll(innerModel.translates);
    }
    // 设置额外的参数条件过滤
    if (!innerModel.paramFilters.isEmpty()) {
        queryExecutor.getInnerModel().paramFilters.addAll(innerModel.paramFilters);
    }
    // 设置安全脱敏
    if (!innerModel.secureMask.isEmpty()) {
        queryExecutor.getInnerModel().secureMask.putAll(innerModel.secureMask);
    }
    // 设置分页优化
    queryExecutor.getInnerModel().pageOptimize = innerModel.pageOptimize;
    SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(queryExecutor, SqlType.search, getDialect(queryExecutor.getInnerModel().dataSource));
    // 加密字段，查询时解密
    if (entityMeta.getSecureColumns() != null) {
        sqlToyConfig.setDecryptColumns(entityMeta.getSecureColumns());
    }
    // 分库分表策略
    setEntitySharding(queryExecutor, entityMeta);
    if (innerModel.dbSharding != null) {
        queryExecutor.getInnerModel().dbSharding = innerModel.dbSharding;
    }
    if (innerModel.tableSharding != null) {
        ShardingStrategyConfig shardingConfig = innerModel.tableSharding;
        // 补充表名称
        shardingConfig.setTables(new String[] { entityMeta.getTableName() });
        List<ShardingStrategyConfig> tableShardings = new ArrayList<ShardingStrategyConfig>();
        tableShardings.add(shardingConfig);
        queryExecutor.getInnerModel().tableShardings = tableShardings;
    }
    DataSource realDataSource = getDataSource(queryExecutor.getInnerModel().dataSource, sqlToyConfig);
    // 取count数量
    if (isCount) {
        return dialectFactory.getCountBySql(sqlToyContext, queryExecutor, sqlToyConfig, realDataSource);
    }
    // 非分页
    if (page == null) {
        // 取top
        if (innerModel.pickType == 0) {
            return dialectFactory.findTop(sqlToyContext, queryExecutor, sqlToyConfig, innerModel.pickSize, realDataSource).getRows();
        } else // 取随机记录
        if (innerModel.pickType == 1) {
            return dialectFactory.getRandomResult(sqlToyContext, queryExecutor, sqlToyConfig, innerModel.pickSize, realDataSource).getRows();
        } else {
            return dialectFactory.findByQuery(sqlToyContext, queryExecutor, sqlToyConfig, innerModel.lockMode, realDataSource).getRows();
        }
    }
    // 跳过总记录数形式的分页
    if (page.getSkipQueryCount()) {
        return dialectFactory.findSkipTotalCountPage(sqlToyContext, queryExecutor, sqlToyConfig, page.getPageNo(), page.getPageSize(), realDataSource).getPageResult();
    }
    return dialectFactory.findPage(sqlToyContext, queryExecutor, sqlToyConfig, page.getPageNo(), page.getPageSize(), realDataSource).getPageResult();
}

Example 5 with TranslateExtend

use of org.sagacity.sqltoy.model.inner.TranslateExtend in project sagacity-sqltoy by chenrenfei.

the class ResultUtils method processResultRowWithTranslate.

/**
 * @todo 存在缓存翻译的结果处理
 * @param translateMap
 * @param translateCaches
 * @param labelNames
 * @param rs
 * @param size
 * @param decryptHandler
 * @param ignoreAllEmptySet
 * @return
 * @throws Exception
 */
private static List processResultRowWithTranslate(HashMap<String, Translate> translateMap, HashMap<String, HashMap<String, Object[]>> translateCaches, String[] labelNames, ResultSet rs, int size, DecryptHandler decryptHandler, boolean ignoreAllEmptySet) throws Exception {
    List rowData = new ArrayList();
    Object fieldValue;
    TranslateExtend extend;
    String label;
    String keyIndex;
    boolean allNull = true;
    for (int i = 0; i < size; i++) {
        label = labelNames[i];
        fieldValue = rs.getObject(label);
        label = label.toLowerCase();
        keyIndex = Integer.toString(i);
        if (null != fieldValue) {
            allNull = false;
            if (fieldValue instanceof java.sql.Clob) {
                fieldValue = SqlUtil.clobToString((java.sql.Clob) fieldValue);
            }
            // 解密
            if (decryptHandler != null) {
                fieldValue = decryptHandler.decrypt(label, fieldValue);
            }
            if (translateMap.containsKey(label) || translateMap.containsKey(keyIndex)) {
                extend = translateMap.get(label).getExtend();
                if (extend == null) {
                    extend = translateMap.get(keyIndex).getExtend();
                }
                fieldValue = translateKey(extend, translateCaches.get(extend.column), fieldValue);
            }
        }
        rowData.add(fieldValue);
    }
    if (allNull && ignoreAllEmptySet) {
        return null;
    }
    return rowData;
}