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

Example 36 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult 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 37 with SqlToyResult

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

the class SqlConfigParseUtils method processSql.

/**
 * @todo 判断条件为null,过滤sql的组合查询条件example: queryStr= select t1.* from xx_table t1
 *       where #[t1.status=?] #[and t1.auditTime=?]
 * @param queryStr
 * @param paramsNamed
 * @param paramsArg
 * @param dialect
 * @return
 */
public static SqlToyResult processSql(String queryStr, String[] paramsNamed, Object[] paramsArg, String dialect) {
    Object[] paramsValue = paramsArg;
    if (paramsNamed != null && paramsNamed.length > 0) {
        // 构造全是null的条件值，将全部条件去除
        if (null == paramsArg || paramsArg.length == 0) {
            paramsValue = new Object[paramsNamed.length];
        }
    } else // 无参数别名也无条件值
    if (null == paramsArg || paramsArg.length == 0) {
        return new SqlToyResult(queryStr, paramsArg);
    }
    SqlToyResult sqlToyResult = new SqlToyResult();
    // 是否:paramName 形式的参数模式
    boolean isNamedArgs = StringUtil.matches(queryStr, SqlToyConstants.SQL_NAMED_PATTERN);
    SqlParamsModel sqlParam;
    // 将sql中的问号临时先替换成特殊字符
    String questionMark = "#sqltoy_qsmark_placeholder#";
    if (isNamedArgs) {
        String sql = queryStr.replaceAll(ARG_REGEX, questionMark);
        // update 2020-09-23 处理sql中的循环(提前处理循环，避免循环中存在其它条件参数)
        sql = processLoop(sql, paramsNamed, paramsValue);
        sqlParam = processNamedParamsQuery(sql);
    } else {
        // 将sql中的??符号替换成特殊字符,?? 符号在json场景下有特殊含义
        String sql = queryStr.replaceAll(ARG_DBL_REGEX, DBL_QUESTMARK);
        sqlParam = processNamedParamsQuery(sql);
    }
    sqlToyResult.setSql(sqlParam.getSql());
    // 参数和参数值进行匹配
    sqlToyResult.setParamsValue(matchNamedParam(sqlParam.getParamsName(), paramsNamed, paramsValue));
    // 剔除查询条件为null的sql语句和对应的参数
    processNullConditions(sqlToyResult);
    // 替换@blank(?)为空白,增强sql组织能力
    processBlank(sqlToyResult);
    // 检查 like 对应参数部分，如果参数中不存在%符合则自动两边增加%
    processLike(sqlToyResult);
    // in 处理策略2012-7-10 进行了修改，提供参数preparedStatement.setObject()机制，并同时兼容
    // 用具体数据替换 in (?)中问号的处理机制
    processIn(sqlToyResult);
    // 参数为null的处理策略(用null直接代替变量)
    replaceNull(sqlToyResult, 0);
    // update 2021-4-29 放在最后，避免参数值中存在?号
    // 替换@value(?) 为参数对应的数值
    processValue(sqlToyResult, dialect);
    // 将特殊字符替换回问号
    if (isNamedArgs) {
        sqlToyResult.setSql(sqlToyResult.getSql().replaceAll(questionMark, ARG_NAME));
    } else {
        // 将代表json中的?? 符号换回
        sqlToyResult.setSql(sqlToyResult.getSql().replaceAll(DBL_QUESTMARK, ARG_DBL_NAME));
    }
    return sqlToyResult;
}

Example 38 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult 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);
}

Example 39 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult 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 40 with SqlToyResult

use of org.sagacity.sqltoy.config.model.SqlToyResult 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);
}