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

Example 26 with QueryExecutorExtend

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

the class DialectFactory method findSkipTotalCountPage.

/**
 * @TODO 跳过查询总记录的分页查询,提供给特殊的场景，尤其是移动端滚屏模式
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param pageNo
 * @param pageSize
 * @param dataSource
 * @return
 */
public QueryResult findSkipTotalCountPage(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final long pageNo, final Integer pageSize, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate sql is null!");
    }
    // 页数必须要大于等于1，pageSize必须要大于1
    if (pageNo < 1 || pageSize < 1) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate  pageSize:" + pageSize + "<1 or pageNo:" + pageNo + " < 1!");
    }
    int limitSize = sqlToyContext.getPageFetchSizeLimit();
    // 分页查询不允许单页数据超过上限，避免大规模数据提取
    if (pageSize >= limitSize) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate args is Illegal,pageSize={" + pageSize + "}>= limit:{" + limitSize + "}!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findSkipTotalCountPage", 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);
                QueryResult queryResult = getDialectSqlWrapper(dbType).findPageBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), pageNo, pageSize, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                queryResult.setPageNo(pageNo);
                queryResult.setPageSize(pageSize);
                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));
                    }
                }
                queryResult.setSkipQueryCount(true);
                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 27 with QueryExecutorExtend

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

the class DialectUtils method wrapPageSqlParams.

/**
 * @todo 处理分页sql的参数
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param pageSql
 * @param startIndex
 * @param endIndex
 * @return
 * @throws Exception
 */
public static SqlToyResult wrapPageSqlParams(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, String pageSql, Object startIndex, Object endIndex, String dialect) throws Exception {
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    String[] paramsNamed = extend.getParamsName(sqlToyConfig);
    Object[] paramsValue = extend.getParamsValue(sqlToyContext, sqlToyConfig);
    if (startIndex == null && endIndex == null) {
        return SqlConfigParseUtils.processSql(pageSql, paramsNamed, paramsValue, dialect);
    }
    String[] realParamNamed = null;
    Object[] realParamValue = null;
    int paramLength;
    // 针对sqlserver2008以及2005版本分页只需扩展一个参数
    int extendSize = (endIndex == null) ? 1 : 2;
    if (sqlToyConfig.isNamedParam()) {
        paramLength = paramsNamed.length;
        // 防止传null
        if (paramsValue == null) {
            paramsValue = new Object[paramLength];
        }
        realParamValue = new Object[paramLength + extendSize];
        // 设置开始记录和截止记录参数名称和对应的值
        realParamNamed = new String[paramLength + extendSize];
        if (paramLength > 0) {
            System.arraycopy(paramsNamed, 0, realParamNamed, 0, paramLength);
            System.arraycopy(paramsValue, 0, realParamValue, 0, paramLength);
        }
        realParamNamed[paramLength] = SqlToyConstants.PAGE_FIRST_PARAM_NAME;
        realParamValue[paramLength] = startIndex;
        if (extendSize == 2) {
            realParamNamed[paramLength + 1] = SqlToyConstants.PAGE_LAST_PARAM_NAME;
            realParamValue[paramLength + 1] = endIndex;
        }
    } else {
        int totalParamCnt = getParamsCount(sqlToyConfig.getSql(null));
        // sql中没有?条件参数
        if (totalParamCnt == 0) {
            realParamValue = new Object[extendSize];
            realParamValue[0] = startIndex;
            if (extendSize == 2) {
                realParamValue[1] = endIndex;
            }
        } else {
            if (paramsValue == null && totalParamCnt > 0) {
                paramsValue = new Object[totalParamCnt];
            }
            paramLength = (paramsValue == null) ? 0 : paramsValue.length;
            realParamValue = new Object[paramLength + extendSize];
            if (sqlToyConfig.isHasFast()) {
                int tailSqlParamCnt = getParamsCount(sqlToyConfig.getFastTailSql(null));
                // @fast() tail 前面部分参数数量
                int tailPreParamCnt = totalParamCnt - tailSqlParamCnt;
                System.arraycopy(paramsValue, 0, realParamValue, 0, tailPreParamCnt);
                realParamValue[tailPreParamCnt] = startIndex;
                if (extendSize == 2) {
                    realParamValue[tailPreParamCnt + 1] = endIndex;
                }
                if (tailSqlParamCnt > 0) {
                    System.arraycopy(paramsValue, tailPreParamCnt, realParamValue, tailPreParamCnt + extendSize, tailSqlParamCnt);
                }
            } else {
                if (paramLength > 0) {
                    System.arraycopy(paramsValue, 0, realParamValue, 0, paramLength);
                }
                realParamValue[paramLength] = startIndex;
                if (extendSize == 2) {
                    realParamValue[paramLength + 1] = endIndex;
                }
            }
        }
    }
    // 通过参数处理最终的sql和参数值
    return SqlConfigParseUtils.processSql(pageSql, realParamNamed, realParamValue, dialect);
}

Example 28 with QueryExecutorExtend

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

the class SqlServerDialectUtils 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 {
    // sqlserver 不支持内部order by
    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)).append(" (");
    }
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select top " + randomCount);
        sql.append(" sag_random_table.* from (");
        sql.append(innerSql);
    } else {
        sql.append(innerSql.replaceFirst("(?i)select ", "select top " + randomCount + " "));
    }
    if (hasOrderOrUnion) {
        sql.append(") sag_random_table ");
    }
    sql.append(" order by NEWID() ");
    if (sqlToyConfig.isHasFast()) {
        sql.append(") ").append(sqlToyConfig.getFastTailSql(dialect));
    }
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    SqlToyResult queryParam = SqlConfigParseUtils.processSql(sql.toString(), extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, sqlToyConfig), dialect);
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 29 with QueryExecutorExtend

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

the class DefaultDialectUtils 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);
    // select * from table order by rand() limit :randomCount 性能比较差,通过产生rand()
    // row_number 再排序方式性能稍好 同时也可以保证通用性
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    sql.append("select sag_random_table1.* from (");
    // sql中是否存在排序或union,存在order 或union 则在sql外包裹一层
    if (DialectUtils.hasOrderByOrUnion(innerSql)) {
        sql.append("select rand() as sag_row_number,sag_random_table.* from (");
        sql.append(innerSql);
        sql.append(") sag_random_table ");
    } else {
        sql.append(innerSql.replaceFirst("(?i)select", "select rand() as sag_row_number,"));
    }
    sql.append(" )  as sag_random_table1 ");
    sql.append(" order by sag_random_table1.sag_row_number limit ");
    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 30 with QueryExecutorExtend

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

the class Oracle11gDialect 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(" (");
        }
    }
    sql.append("SELECT SAG_Paginationtable.* FROM ( ");
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect));
    sql.append(") SAG_Paginationtable where ROWNUM <=");
    sql.append(Double.valueOf(topSize).intValue());
    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);
}