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

Example 21 with QueryExecutorExtend

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

the class SqlServerDialect method findPageBySql.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.Dialect#findPageBySql(org.sagacity
	 * .sqltoy.SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.model.QueryExecutor,java.lang.Long, java.lang.Integer,
	 * java.sql.Connection)
	 */
@Override
public QueryResult findPageBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long pageNo, Integer pageSize, Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows) throws Exception {
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    StringBuilder sql = new StringBuilder();
    boolean isNamed = sqlToyConfig.isNamedParam();
    String realSql = sqlToyConfig.getSql(dialect);
    String fastSql = "";
    // 存在@fast() 快速分页
    if (sqlToyConfig.isHasFast()) {
        fastSql = sqlToyConfig.getFastSql(dialect);
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
        sql.append(fastSql);
    } else {
        sql.append(realSql);
    }
    // update 2021-10-20 提前试算一下实际sql,便于判断最终sql中是否包含order by
    String judgeOrderSql = sqlToyConfig.isHasFast() ? fastSql : realSql;
    // 避免条件用?模式,导致实际参数位置不匹配,因此只针对:name模式进行处理
    if (isNamed) {
        SqlToyResult tmpResult = SqlConfigParseUtils.processSql(judgeOrderSql, extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, sqlToyConfig), dialect);
        judgeOrderSql = tmpResult.getSql();
    }
    // order by位置
    int orderByIndex = StringUtil.matchIndex(judgeOrderSql, ORDER_BY);
    // 存在order by，继续判断order by 是否在子查询内
    if (orderByIndex > 0) {
        // 剔除select 和from 之间内容，剔除sql中所有()之间的内容,即剔除所有子查询，再判断是否有order by
        orderByIndex = StringUtil.matchIndex(DialectUtils.clearDisturbSql(judgeOrderSql), ORDER_BY);
    }
    // 不存在order by或order by存在于子查询中
    if (orderByIndex < 0) {
        sql.append(" order by NEWID() ");
    }
    // 增加分页语句
    sql.append(" offset ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
    sql.append(" rows fetch next ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    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(), (pageNo - 1) * pageSize, Long.valueOf(pageSize), dialect);
    return findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, null, dbType, dialect, fetchSize, maxRows);
}

Example 22 with QueryExecutorExtend

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

the class SqlServerDialect method findTopBySql.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.Dialect#findTopBySql(org.sagacity.sqltoy.
	 * SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.model.QueryExecutor, double, java.sql.Connection)
	 */
@Override
public 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(" (");
        }
    }
    String minSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
    String partSql = " select top " + topSize + " ";
    if (sqlToyConfig.isHasWith()) {
        SqlWithAnalysis sqlWith = new SqlWithAnalysis(minSql);
        sql.append(sqlWith.getWithSql());
        minSql = sqlWith.getRejectWithSql();
    }
    boolean hasUnion = false;
    if (sqlToyConfig.isHasUnion()) {
        hasUnion = SqlUtil.hasUnion(minSql, false);
    }
    if (hasUnion) {
        sql.append(partSql);
        sql.append(" SAG_Paginationtable.* from (");
        sql.append(minSql);
        sql.append(") as SAG_Paginationtable ");
    } else {
        sql.append(minSql.replaceFirst("(?i)select ", partSql));
    }
    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 findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, null, dbType, dialect, fetchSize, maxRows);
}

Example 23 with QueryExecutorExtend

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

the class DialectFactory method getRandomResult.

/**
 * @todo 取随机记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param randomCount
 * @param dataSource
 * @return
 */
public QueryResult getRandomResult(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final Double randomCount, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (extend.sql == null) {
        throw new IllegalArgumentException("getRandomResult operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, (randomCount < 1) ? true : false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "getRandomResult", 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, (randomCount < 1) ? true : false);
                // 判断数据库是否支持取随机记录(只有informix和sybase不支持)
                Long totalCount = null;
                Long randomCnt;
                // 记录数量大于1表示取随机记录数量
                if (randomCount >= 1) {
                    randomCnt = randomCount.longValue();
                } else // 按比例提取
                {
                    // 提取总记录数
                    if (totalCount == null) {
                        totalCount = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    }
                    randomCnt = Double.valueOf(totalCount * randomCount.doubleValue()).longValue();
                    SqlExecuteStat.debug("过程提示", "按比例提取总记录数:{}条,需取随机记录:{}条!", totalCount, randomCnt);
                    // 如果总记录数不为零，randomCnt最小为1
                    if (totalCount >= 1 && randomCnt < 1) {
                        randomCnt = 1L;
                    }
                }
                QueryResult queryResult;
                // 总记录数为零
                if (totalCount != null && totalCount == 0) {
                    queryResult = new QueryResult();
                    queryResult.setRows(new ArrayList());
                    this.setResult(queryResult);
                    logger.warn("getRandom,total Records is zero,please check sql!sqlId={}", sqlToyConfig.getIdOrSql());
                    return;
                }
                queryResult = getDialectSqlWrapper(dbType).getRandomResult(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), totalCount, randomCnt, conn, 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 24 with QueryExecutorExtend

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

the class DialectFactory method executeSql.

/**
 * @todo 执行sql修改性质的操作语句
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param paramsTypes
 * @param autoCommit
 * @param dataSource
 * @return
 */
public Long executeSql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final QueryExecutor queryExecutor, final Integer[] paramsTypes, final Boolean autoCommit, final DataSource dataSource) {
    try {
        SqlExecuteStat.start(sqlToyConfig.getId(), "executeSql", sqlToyConfig.isShowSql());
        // 将修改语句当做特殊的查询，其处理过程在交jdbc执行前完全一致
        final QueryExecutorExtend extend = queryExecutor.getInnerModel();
        // 组织参数和参数校验，但忽视数据权限数据的传参和校验
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        Long updateTotalCnt = (Long) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 进行sharding table替换
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                // 做sql签名
                String executeSql = SqlUtilsExt.signSql(queryParam.getSql(), dbType, realSqlToyConfig);
                this.setResult(SqlUtil.executeSql(sqlToyContext.getTypeHandler(), executeSql, queryParam.getParamsValue(), paramsTypes, conn, dbType, autoCommit, false));
            }
        });
        SqlExecuteStat.debug("执行结果", "受影响记录数量:{} 条!", updateTotalCnt);
        return updateTotalCnt;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 25 with QueryExecutorExtend

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

the class DialectFactory method getCountBySql.

/**
 * @todo 查询符合条件的记录数量
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param dataSource
 * @return
 */
public Long getCountBySql(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("getCountBySql operate sql is null!");
    }
    try {
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "getCountBySql", sqlToyConfig.isShowSql());
        Long count = (Long) 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);
                this.setResult(getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect));
            }
        });
        SqlExecuteStat.debug("查询结果", "count查询结果={}!", count);
        return count;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}