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

Example 16 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 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 17 with QueryExecutorExtend

use of org.sagacity.sqltoy.model.inner.QueryExecutorExtend 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();
    }
}

Example 18 with QueryExecutorExtend

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

the class DialectFactory method findTop.

/**
 * @todo 取符合条件的前多少条记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param topSize
 * @param dataSource
 * @return
 */
public QueryResult findTop(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final double topSize, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 合法校验
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("findTop operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, (topSize < 1) ? true : false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findTop", 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, (topSize < 1) ? true : false);
                Integer realTopSize;
                // 小于1表示按比例提取
                if (topSize < 1) {
                    Long totalCount = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    realTopSize = Double.valueOf(topSize * totalCount.longValue()).intValue();
                    SqlExecuteStat.debug("过程提示", "按比例提取,总记录数:{}条,按比例top记录要取:{} 条!", totalCount, realTopSize);
                } else {
                    realTopSize = Double.valueOf(topSize).intValue();
                }
                if (realTopSize == 0) {
                    this.setResult(new QueryResult());
                    SqlExecuteStat.debug("查询结果", "实际取得top记录数:0 条!");
                    return;
                }
                // 调用数据库方言查询结果
                QueryResult queryResult = getDialectSqlWrapper(dbType).findTopBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), realTopSize, 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("查询结果", "实际取得top记录数: {}条!", 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 19 with QueryExecutorExtend

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

the class DialectFactory method findPage.

/**
 * @todo 分页查询, pageNo为负一表示取全部记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param pageNo
 * @param pageSize
 * @param dataSource
 * @return
 */
public QueryResult findPage(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("findPage operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, true);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findPage", 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, true);
                QueryResult queryResult = null;
                PageOptimize pageOptimize = extend.pageOptimize;
                if (pageOptimize == null) {
                    pageOptimize = realSqlToyConfig.getPageOptimize();
                }
                Long recordCnt = null;
                // 通过查询条件构造唯一的key
                String pageQueryKey = PageOptimizeUtils.generateOptimizeKey(sqlToyContext, sqlToyConfig, queryExecutor, pageOptimize);
                // 需要进行分页查询优化
                if (null != pageQueryKey) {
                    // 从缓存中提取总记录数
                    recordCnt = PageOptimizeUtils.getPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey);
                    if (recordCnt != null) {
                        SqlExecuteStat.debug("过程提示", "分页优化条件命中,从缓存中获得总记录数:{}!!", recordCnt);
                    }
                }
                // 并行且缓存中无总记录数量，执行并行处理
                if (pageOptimize != null && pageOptimize.isParallel() && pageNo != -1 && recordCnt == null) {
                    queryResult = parallelPage(sqlToyContext, queryExecutor, realSqlToyConfig, extend, pageNo, pageSize, pageOptimize, conn, dbType, dialect);
                    recordCnt = queryResult.getRecordCount();
                    // 将并行后得到的总记录数登记到缓存
                    if (null != pageQueryKey) {
                        PageOptimizeUtils.registPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey, recordCnt);
                    }
                } else {
                    // 非并行且分页缓存未命中，执行count查询
                    if (recordCnt == null) {
                        recordCnt = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    }
                    // 将总记录数登记到缓存
                    if (null != pageQueryKey) {
                        PageOptimizeUtils.registPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey, recordCnt);
                    }
                    // pageNo=-1时的提取数据量限制
                    int limitSize = sqlToyContext.getPageFetchSizeLimit();
                    // pageNo=-1时,总记录数超出限制则返回空集合
                    boolean illegal = (pageNo == -1 && (limitSize != -1 && recordCnt > limitSize));
                    if (recordCnt == 0 || illegal) {
                        queryResult = new QueryResult();
                        if (recordCnt == 0 && sqlToyContext.isPageOverToFirst()) {
                            queryResult.setPageNo(1L);
                        } else {
                            queryResult.setPageNo(pageNo);
                        }
                        queryResult.setPageSize(pageSize);
                        queryResult.setRecordCount(0L);
                        if (illegal) {
                            logger.warn("非法分页查询,提取记录总数为:{}>{}上限(可设置sqlToyContext中的pageFetchSizeLimit进行调整),sql={}", recordCnt, limitSize, sqlToyConfig.getIdOrSql());
                        } else {
                            SqlExecuteStat.debug("过程提示", "提取count数为:0,sql={}", sqlToyConfig.getIdOrSql());
                        }
                    } else {
                        // 合法的全记录提取,设置页号为1按记录数
                        if (pageNo == -1) {
                            // 通过参数处理最终的sql和参数值
                            SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(realSqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                            queryResult = getDialectSqlWrapper(dbType).findBySql(sqlToyContext, realSqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, wrapDecryptHandler(sqlToyContext, extend.resultType), conn, null, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                            long totalRecord = (queryResult.getRows() == null) ? 0 : queryResult.getRows().size();
                            queryResult.setPageNo(1L);
                            queryResult.setPageSize(Long.valueOf(totalRecord).intValue());
                            queryResult.setRecordCount(totalRecord);
                        } else {
                            // 实际开始页(页数据超出总记录,则从第一页重新开始,相反如继续按指定的页查询则记录为空,且实际页号也不存在)
                            boolean isOverPage = (pageNo * pageSize >= (recordCnt + pageSize));
                            // 允许页号超出总页数，结果返回空集合
                            if (isOverPage && !sqlToyContext.isPageOverToFirst()) {
                                queryResult = new QueryResult();
                                queryResult.setPageNo(pageNo);
                            } else {
                                long realStartPage = isOverPage ? 1 : pageNo;
                                queryResult = getDialectSqlWrapper(dbType).findPageBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), realStartPage, pageSize, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                                queryResult.setPageNo(realStartPage);
                            }
                            queryResult.setPageSize(pageSize);
                            queryResult.setRecordCount(recordCnt);
                        }
                    }
                }
                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) queryResult).getRecordCount(), ((QueryResult) queryResult).getRows().size());
                this.setResult(queryResult);
            }
        });
        result.setExecuteTime(System.currentTimeMillis() - startTime);
        return result;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 20 with QueryExecutorExtend

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

the class Oracle11gDialect 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,
	 * org.sagacity.sqltoy.callback.RowCallbackHandler, 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 {
    StringBuilder sql = new StringBuilder();
    boolean isNamed = sqlToyConfig.isNamedParam();
    int startIndex = 1;
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
        startIndex = 0;
    }
    sql.append("SELECT * FROM (SELECT ROWNUM page_row_id,SAG_Paginationtable.* FROM ( ");
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect));
    sql.append(") SAG_Paginationtable ");
    // SORT ORDER BY STOPKEY
    if (SqlToyConstants.oraclePageIgnoreOrder() || !SqlUtil.hasOrderBy(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect), true)) {
        sql.append(" where ROWNUM <=");
        sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
        sql.append(" ) WHERE page_row_id>");
        sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    } else {
        sql.append(" ) WHERE page_row_id<=");
        sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
        sql.append(" and page_row_id >");
        sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    }
    if (sqlToyConfig.isHasFast()) {
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(") ");
        }
        sql.append(sqlToyConfig.getFastTailSql(dialect));
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), pageNo * pageSize, (pageNo - 1) * pageSize, dialect);
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, startIndex, fetchSize, maxRows);
}