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

Example 1 with QueryExecutorExtend

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

the class ResultUtils method getPivotCategory.

/**
 * @todo 提取数据旋转对应的sql查询结果
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param conn
 * @param dbType
 * @param dialect
 * @return
 * @throws Exception
 */
public static List getPivotCategory(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Connection conn, final Integer dbType, String dialect) throws Exception {
    List resultProcessors = new ArrayList();
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (!sqlToyConfig.getResultProcessor().isEmpty()) {
        resultProcessors.addAll(sqlToyConfig.getResultProcessor());
    }
    // QueryExecutor中扩展的计算
    if (extend != null && !extend.calculators.isEmpty()) {
        resultProcessors.addAll(extend.calculators);
    }
    Object processor;
    for (int i = 0; i < resultProcessors.size(); i++) {
        processor = resultProcessors.get(i);
        // 数据旋转只能存在一个
        if (processor instanceof PivotModel) {
            PivotModel pivotModel = (PivotModel) processor;
            if (pivotModel.getCategorySql() != null) {
                SqlToyConfig pivotSqlConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyContext.getSqlToyConfig(pivotModel.getCategorySql(), SqlType.search, ""), queryExecutor, dialect, false);
                SqlToyResult pivotSqlToyResult = SqlConfigParseUtils.processSql(pivotSqlConfig.getSql(dialect), extend.getParamsName(pivotSqlConfig), extend.getParamsValue(sqlToyContext, pivotSqlConfig), dialect);
                List pivotCategory = SqlUtil.findByJdbcQuery(sqlToyContext.getTypeHandler(), pivotSqlToyResult.getSql(), pivotSqlToyResult.getParamsValue(), null, null, null, conn, dbType, sqlToyConfig.isIgnoreEmpty(), null, SqlToyConstants.FETCH_SIZE, -1);
                // 行转列返回
                return CollectionUtil.convertColToRow(pivotCategory, null);
            }
        }
    }
    return null;
}

Example 2 with QueryExecutorExtend

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

the class DialectUtils method getUnifyParamsNamedConfig.

/**
 * @todo 统一将查询的sql参数由?形式变成:named形式(分页和查询随机记录时)
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param dialect
 * @param wrapNamed     只在分页场景下需要将?模式传参统一成:name模式，便于跟后面分页startIndex和endIndex参数结合，从而利用sql预编译功能
 * @return
 * @throws Exception
 */
public static SqlToyConfig getUnifyParamsNamedConfig(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, String dialect, boolean wrapNamed) throws Exception {
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 本身就是:named参数形式或sql中没有任何参数
    boolean isNamed = false;
    // 在QueryExecutorBuilder中已经对wrappedParamNames做了判断赋值
    if (!extend.wrappedParamNames) {
        // sql中是否存在? 形式参数
        boolean hasQuestArg = SqlConfigParseUtils.hasQuestMarkArgs(sqlToyConfig.getSql());
        isNamed = ((extend.paramsName != null && extend.paramsName.length > 0) || !hasQuestArg);
    }
    // 以queryExecutor自定义的分表策略覆盖sql xml中定义的
    List<ShardingStrategyConfig> tableShardings = sqlToyConfig.getTableShardings();
    if (!extend.tableShardings.isEmpty()) {
        tableShardings = extend.tableShardings;
    }
    // sql条件以:named形式、无分表、无扩展缓存翻译则不存在对SqlToyConfig 内容的修改，直接返回
    if ((isNamed || !wrapNamed) && tableShardings.isEmpty() && extend.translates.isEmpty()) {
        return sqlToyConfig;
    }
    // clone sqltoyConfig避免直接修改原始的sql配置对后续执行产生影响
    SqlToyConfig result = sqlToyConfig.clone();
    // 存在扩展的缓存翻译
    if (!extend.translates.isEmpty()) {
        result.getTranslateMap().putAll(extend.translates);
    }
    // ?传参且分页模式,原因是分页存在取count场景，在@fast()情况下无法断定paramValues的值跟?的参数对应关系
    if (!isNamed && wrapNamed) {
        SqlParamsModel sqlParams;
        // 存在fast查询
        if (result.isHasFast()) {
            // @fast 前部分
            String fastPreSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastPreSql(null));
            sqlParams = convertParamsToNamed(fastPreSql, 0);
            fastPreSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastPreSql(fastPreSql);
            int index = sqlParams.getParamCnt();
            // @fas() 中间部分
            String fastSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastSql(null));
            sqlParams = convertParamsToNamed(fastSql, index);
            fastSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastSql(fastSql);
            index = index + sqlParams.getParamCnt();
            // 尾部
            String tailSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastTailSql(null));
            sqlParams = convertParamsToNamed(tailSql, index);
            tailSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastTailSql(tailSql);
            // 完整sql
            result.setSql(fastPreSql.concat(" (").concat(fastSql).concat(") ").concat(tailSql));
            // 构造对应?参数个数的:named模式参数名数组
            String[] paramsName = new String[index];
            for (int i = 0; i < index; i++) {
                paramsName[i] = SqlToyConstants.DEFAULT_PARAM_NAME + (i + 1);
            }
            result.setParamsName(paramsName);
        } else {
            sqlParams = convertParamsToNamed(SqlConfigParseUtils.clearDblQuestMark(result.getSql(null)), 0);
            result.setSql(SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql()));
            result.setParamsName(sqlParams.getParamsName());
        }
        // 自定义分页的count sql，一般无需定义
        sqlParams = convertParamsToNamed(SqlConfigParseUtils.clearDblQuestMark(result.getCountSql(null)), 0);
        result.setCountSql(SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql()));
        // 清空方言缓存
        result.clearDialectSql();
        SqlConfigParseUtils.processFastWith(result, dialect);
    }
    // sharding table 替换sql中的表名称
    ShardingUtils.replaceShardingSqlToyConfig(sqlToyContext, result, tableShardings, dialect, extend.getTableShardingParamsName(sqlToyConfig), extend.getTableShardingParamsValue(sqlToyConfig));
    return result;
}

Example 3 with QueryExecutorExtend

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

the class PageOptimizeUtils method generateOptimizeKey.

/**
 * @todo 根据查询条件组成key
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param pageOptimize
 * @return
 * @throws Exception
 */
public static String generateOptimizeKey(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final QueryExecutor queryExecutor, PageOptimize pageOptimize) throws Exception {
    // 没有开放分页优化或sql id为null都不执行优化操作
    if (pageOptimize == null || pageOptimize.getAliveMax() <= 1 || pageOptimize.getAliveSeconds() < 1) {
        return null;
    }
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    String[] paramNames = extend.getParamsName(sqlToyConfig);
    Object[] paramValues = extend.getParamsValue(sqlToyContext, sqlToyConfig);
    // sql中所有参数都为null,返回sqlId作为key
    if (paramValues == null || paramValues.length == 0) {
        return sqlToyConfig.getIdOrSql();
    }
    StringBuilder cacheKey = new StringBuilder();
    boolean isParamsNamed = true;
    if (null == paramNames || paramNames.length == 0) {
        isParamsNamed = false;
    }
    int i = 0;
    // 循环查询条件的值构造key
    for (Object value : paramValues) {
        if (i > 0) {
            cacheKey.append(",");
        }
        if (isParamsNamed) {
            cacheKey.append(paramNames[i]).append("=");
        } else {
            cacheKey.append("p_").append(i).append("=");
        }
        if (value == null) {
            cacheKey.append("null");
        } else if ((value instanceof Object[]) || value.getClass().isArray() || (value instanceof List)) {
            Object[] arrayValue = (value instanceof List) ? ((List) value).toArray() : CollectionUtil.convertArray(value);
            cacheKey.append("[");
            for (Object obj : arrayValue) {
                cacheKey.append((obj == null) ? "null" : obj.toString()).append(",");
            }
            cacheKey.append("]");
        } else {
            cacheKey.append(value.toString());
        }
        i++;
    }
    return cacheKey.toString();
}

Example 4 with QueryExecutorExtend

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

the class DB2DialectUtils method getRandomResult.

/**
 * @todo 提供随机记录查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param totalCount
 * @param randomCount
 * @param conn
 * @param dbType
 * @param dialect
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult getRandomResult(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long totalCount, Long randomCount, Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows) throws Exception {
    String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // sql中是否存在排序或union
    boolean hasOrderOrUnion = DialectUtils.hasOrderByOrUnion(innerSql);
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select sag_random_table.* from (");
    }
    sql.append(innerSql);
    if (hasOrderOrUnion) {
        sql.append(") sag_random_table ");
    }
    sql.append(" order by rand() fetch first ");
    sql.append(randomCount);
    sql.append(" rows only ");
    if (sqlToyConfig.isHasFast()) {
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(") ");
        }
        sql.append(sqlToyConfig.getFastTailSql(dialect));
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), null, null, dialect);
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 5 with QueryExecutorExtend

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

the class DefaultDialectUtils method findTopBySql.

/**
 * @todo 实现top记录查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param topSize
 * @param conn
 * @param dbType
 * @param dialect
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult findTopBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Integer topSize, Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows) throws Exception {
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
        sql.append(sqlToyConfig.getFastSql(dialect));
    } else {
        sql.append(sqlToyConfig.getSql(dialect));
    }
    sql.append(" limit ");
    sql.append(topSize);
    if (sqlToyConfig.isHasFast()) {
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(") ");
        }
        sql.append(sqlToyConfig.getFastTailSql(dialect));
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), null, null, dialect);
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}