Examples with SqlToyResult org.sagacity.sqltoy.config.model.SqlToyResult used on opensource projects

Example 31 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult in project sagacity-sqltoy by chenrenfei.

the class OracleDialectUtils 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();
    // 是否有order by
    boolean hasOrderBy = SqlUtil.hasOrderBy(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect), true);
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // order by 外包裹一层,确保查询结果是按排序
    if (hasOrderBy) {
        sql.append("select SAG_Paginationtable.* from (");
    }
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect));
    if (hasOrderBy) {
        sql.append(") SAG_Paginationtable ");
    }
    sql.append(" fetch first ");
    sql.append(topSize);
    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 32 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult in project sagacity-sqltoy by chenrenfei.

the class OracleDialectUtils 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 {
    // 注：dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
    String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
    // sql中是否存在排序或union
    boolean hasOrderOrUnion = DialectUtils.hasOrderByOrUnion(innerSql);
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select * from (");
        sql.append(" select sag_random_table.* from ( ");
        sql.append(innerSql);
        sql.append(") sag_random_table ");
        sql.append(" order by dbms_random.random )");
    } else {
        sql.append("select sag_random_table.* from ( ");
        sql.append(innerSql);
        sql.append(" order by dbms_random.random) sag_random_table ");
    }
    sql.append(" where rownum<=");
    sql.append(randomCount);
    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 33 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method findByQuery.

/**
 * @todo 查询符合条件的数据集合
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param lockMode
 * @param dataSource
 * @return
 */
public QueryResult findByQuery(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final LockMode lockMode, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 合法校验
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("findByQuery operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findByQuery", sqlToyConfig.isShowSql());
        QueryResult result = (QueryResult) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 处理sql中的?为统一的:named形式，并进行sharding table替换
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                // 通过参数处理最终的sql和参数值
                SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(realSqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                QueryResult queryResult = getDialectSqlWrapper(dbType).findBySql(sqlToyContext, realSqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, wrapDecryptHandler(sqlToyContext, extend.resultType), conn, lockMode, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                if (queryResult.getRows() != null && !queryResult.getRows().isEmpty()) {
                    // 存在计算和旋转的数据不能映射到对象(数据类型不一致，如汇总平均以及数据旋转)
                    List pivotCategorySet = ResultUtils.getPivotCategory(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    // 对查询结果进行计算处理:字段脱敏、格式化、数据旋转、同步环比、分组汇总等
                    boolean changedCols = ResultUtils.calculate(sqlToyContext.getDesensitizeProvider(), realSqlToyConfig, queryResult, pivotCategorySet, extend);
                    // 将结果映射对象单独出来为了解耦，性能影响其实可以忽略，上万条也是1毫秒级
                    if (extend.resultType != null) {
                        queryResult.setRows(ResultUtils.wrapQueryResult(sqlToyContext, queryResult.getRows(), queryResult.getLabelNames(), (Class) extend.resultType, changedCols, extend.humpMapLabel, extend.hiberarchy, extend.hiberarchyClasses, extend.fieldsMap));
                    }
                }
                SqlExecuteStat.debug("查询结果", "共查询出记录数={}条!", queryResult.getRecordCount());
                this.setResult(queryResult);
            }
        });
        result.setExecuteTime(System.currentTimeMillis() - startTime);
        return result;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 34 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method getCountBySql.

/**
 * @todo 获取记录总数
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param conn
 * @param dbType
 * @param dialect
 * @return
 * @throws Exception
 */
private Long getCountBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final QueryExecutor queryExecutor, final Connection conn, Integer dbType, String dialect) throws Exception {
    String sql;
    boolean isLastSql = false;
    // 是否自定义了count sql语句(直接定义了则跳过各种优化处理)
    String tmp = sqlToyConfig.getCountSql(dialect);
    if (tmp != null) {
        sql = tmp;
        isLastSql = true;
    } else {
        // 是否是select * from @fast(select * from xxx where xxx) t1 left join xx 模式
        if (!sqlToyConfig.isHasFast()) {
            sql = sqlToyConfig.getSql(dialect);
        } else {
            String fastWithSql = sqlToyConfig.getFastWithSql(dialect);
            sql = (fastWithSql == null ? "" : fastWithSql).concat(" ").concat(sqlToyConfig.getFastSql(dialect));
        }
        String rejectWithSql = sql;
        String withSql = "";
        boolean hasUnion = false;
        // 存在可以简化的 union all 模式(sql xml 文件通过union-all-count 属性由开发者指定)
        if (sqlToyConfig.isHasUnion() && sqlToyConfig.isUnionAllCount()) {
            if (sqlToyConfig.isHasWith()) {
                SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
                rejectWithSql = sqlWith.getRejectWithSql();
                withSql = sqlWith.getWithSql();
            }
            hasUnion = SqlUtil.hasUnion(rejectWithSql, false);
        }
        // 判定union all并且可以进行union all简化处理(sql文件中进行配置)
        if (hasUnion && StringUtil.matches(rejectWithSql, SqlToyConstants.UNION_ALL_REGEX)) {
            isLastSql = true;
            String[] unionSqls = rejectWithSql.split(SqlToyConstants.UNION_ALL_REGEX);
            StringBuilder countSql = new StringBuilder();
            countSql.append(withSql);
            countSql.append(" select sum(row_count) from (");
            int sql_from_index;
            int unionSqlSize = unionSqls.length;
            String countPart = dbType.equals(DBType.ES) ? " count(*) " : " count(1) ";
            for (int i = 0; i < unionSqlSize; i++) {
                sql_from_index = StringUtil.getSymMarkMatchIndex("(?i)select\\s+", "(?i)\\s+from[\\(\\s+]", unionSqls[i], 0);
                countSql.append(" select ").append(countPart).append(" row_count ").append((sql_from_index != -1 ? unionSqls[i].substring(sql_from_index) : unionSqls[i]));
                if (i < unionSqlSize - 1) {
                    countSql.append(" union all ");
                }
            }
            countSql.append(" ) ");
            sql = countSql.toString();
        }
    }
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 通过参数处理最终的sql和参数值
    SqlToyResult queryParam = SqlConfigParseUtils.processSql(sql, extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, sqlToyConfig), dialect);
    return getDialectSqlWrapper(dbType).getCountBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), isLastSql, conn, dbType, dialect);
}

Example 35 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method updateFetch.

/**
 * @todo 查询锁定记录,并进行修改
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param updateRowHandler
 * @param dataSource
 * @return
 */
public QueryResult updateFetch(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final UpdateRowHandler updateRowHandler, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    try {
        Long startTime = System.currentTimeMillis();
        SqlExecuteStat.start(sqlToyConfig.getId(), "updateFetch", sqlToyConfig.isShowSql());
        // 组织参数和参数校验，但忽视数据权限数据的传参和校验
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        QueryResult result = (QueryResult) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 处理sql中的?为统一的:named形式
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(realSqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                QueryResult queryResult = getDialectSqlWrapper(dbType).updateFetch(sqlToyContext, realSqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), updateRowHandler, conn, dbType, dialect, (extend.lockMode == null) ? LockMode.UPGRADE : extend.lockMode, getFetchSize(extend.fetchSize), extend.maxRows);
                if (extend.resultType != null) {
                    queryResult.setRows(ResultUtils.wrapQueryResult(sqlToyContext, queryResult.getRows(), queryResult.getLabelNames(), (Class) extend.resultType, false, extend.humpMapLabel, extend.hiberarchy, extend.hiberarchyClasses, extend.fieldsMap));
                }
                SqlExecuteStat.debug("执行结果", "修改并返回记录操作影响记录:{} 条!", queryResult.getRecordCount());
                this.setResult(queryResult);
            }
        });
        result.setExecuteTime(System.currentTimeMillis() - startTime);
        return result;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}