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

Example 1 with PageOptimize

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

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

the class PageOptimizeTest method main.

public static void main(String[] args) {
    SqlToyConfig sqlToyConfig = new SqlToyConfig("mysql");
    sqlToyConfig.setId("sqltoy_showcase");
    PageOptimize pageOptimize = new PageOptimize();
    // 60秒
    pageOptimize.aliveSeconds(60);
    // 200个
    pageOptimize.aliveMax(200);
    // 模仿60个用户
    for (int i = 0; i < 60; i++) {
        PageOptimizeThread thread = new PageOptimizeThread(sqlToyConfig, pageOptimize, i);
        thread.start();
    }
}

Example 3 with PageOptimize

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

the class SqlXMLConfigParse method parseSingleSql.

/**
 * @todo 解析单个sql element元素,update 2020-7-2 支持外部集成命名空间前缀适配
 * @param sqlElt
 * @param dialect
 * @return
 * @throws Exception
 */
public static SqlToyConfig parseSingleSql(Element sqlElt, String dialect) throws Exception {
    String realDialect = dialect;
    String nodeName = sqlElt.getNodeName().toLowerCase();
    // 剔除前缀
    int prefixIndex = nodeName.indexOf(":");
    if (prefixIndex > 0) {
        nodeName = nodeName.substring(prefixIndex + 1);
    }
    // 目前只支持传统sql、elastic、mongo三种类型的语法
    if (!nodeName.equals("sql") && !nodeName.equals("eql") && !nodeName.equals("mql")) {
        return null;
    }
    String id = sqlElt.getAttribute("id");
    if (id == null) {
        throw new RuntimeException("请检查sql配置,没有给定sql对应的 id值!");
    }
    // 获取元素的namespace前缀
    String localName = getElementPrefixName(sqlElt);
    String local = StringUtil.isBlank(localName) ? "" : localName.concat(":");
    // 判断是否xml为精简模式即只有<sql id=""><![CDATA[]]></sql>模式
    NodeList nodeList = sqlElt.getElementsByTagName(local.concat("value"));
    String sqlContent = null;
    if (nodeList.getLength() > 0) {
        sqlContent = StringUtil.trim(nodeList.item(0).getTextContent());
    } else {
        sqlContent = StringUtil.trim(sqlElt.getTextContent());
    }
    if (StringUtil.isBlank(sqlContent)) {
        throw new RuntimeException("请检查sql-id='" + id + "' 的配置,没有正确填写sql内容!");
    }
    nodeList = sqlElt.getElementsByTagName(local.concat("count-sql"));
    String countSql = null;
    if (nodeList.getLength() > 0) {
        countSql = StringUtil.trim(nodeList.item(0).getTextContent());
    }
    // 替换全角空格
    sqlContent = sqlContent.replaceAll("\u3000", " ");
    if (countSql != null) {
        countSql = countSql.replaceAll("\u3000", " ");
    }
    SqlType type = sqlElt.hasAttribute("type") ? SqlType.getSqlType(sqlElt.getAttribute("type")) : SqlType.search;
    // 是否nosql模式
    boolean isNoSql = false;
    if (nodeName.equals("mql") || nodeName.equals("eql")) {
        if (nodeName.equals("mql")) {
            realDialect = DataSourceUtils.Dialect.MONGO;
        } else if (nodeName.equals("eql")) {
            realDialect = DataSourceUtils.Dialect.ES;
        }
        isNoSql = true;
    }
    SqlToyConfig sqlToyConfig = SqlConfigParseUtils.parseSqlToyConfig(sqlContent, realDialect, type);
    sqlToyConfig.setId(id);
    sqlToyConfig.setSqlType(type);
    // 为sql提供特定数据库的扩展
    if (sqlElt.hasAttribute("dataSource")) {
        sqlToyConfig.setDataSource(sqlElt.getAttribute("dataSource"));
    } else if (sqlElt.hasAttribute("datasource")) {
        sqlToyConfig.setDataSource(sqlElt.getAttribute("datasource"));
    }
    if (countSql != null) {
        // 清理sql中的一些注释、以及特殊的符号
        countSql = SqlUtil.clearMistyChars(SqlUtil.clearMark(countSql), " ").concat(" ");
        countSql = FunctionUtils.getDialectSql(countSql, dialect);
        countSql = ReservedWordsUtil.convertSql(countSql, DataSourceUtils.getDBType(dialect));
        sqlToyConfig.setCountSql(countSql);
    }
    // select 1 from,减少不必要的执行运算，提升效率)
    if (sqlElt.hasAttribute("union-all-count")) {
        sqlToyConfig.setUnionAllCount(Boolean.parseBoolean(sqlElt.getAttribute("union-all-count")));
    }
    // 解析sql对应dataSource的sharding配置
    parseShardingDataSource(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("sharding-datasource")));
    // 解析sql对应的table的sharding配置
    parseShardingTables(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("sharding-table")));
    // 解析格式化
    parseFormat(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("date-format")), sqlElt.getElementsByTagName(local.concat("number-format")));
    // 参数值为空白是否当中null处理,默认为-1
    int blankToNull = -1;
    if (sqlElt.hasAttribute("blank-to-null")) {
        blankToNull = (Boolean.parseBoolean(sqlElt.getAttribute("blank-to-null"))) ? 1 : 0;
    }
    // 参数加工过滤器
    nodeList = sqlElt.getElementsByTagName(local.concat("filters"));
    // 解析参数过滤器
    if (nodeList.getLength() > 0) {
        parseFilters(sqlToyConfig, nodeList.item(0).getChildNodes(), blankToNull, local);
    } else {
        parseFilters(sqlToyConfig, null, blankToNull, local);
    }
    // 解析分页优化器
    // <page-optimize parallel="true" alive-max="100" alive-seconds="90"
    // parallel-maxwait-seconds="600"/>
    nodeList = sqlElt.getElementsByTagName(local.concat("page-optimize"));
    if (nodeList.getLength() > 0) {
        PageOptimize optimize = new PageOptimize();
        Element pageOptimize = (Element) nodeList.item(0);
        // 保留不同条件的count缓存记录量
        if (pageOptimize.hasAttribute("alive-max")) {
            optimize.aliveMax(Integer.parseInt(pageOptimize.getAttribute("alive-max")));
        }
        // 不同sql条件分页记录数量保存有效时长(默认90秒)
        if (pageOptimize.hasAttribute("alive-seconds")) {
            optimize.aliveSeconds(Integer.parseInt(pageOptimize.getAttribute("alive-seconds")));
        }
        // 是否支持并行查询
        if (pageOptimize.hasAttribute("parallel")) {
            optimize.parallel(Boolean.parseBoolean(pageOptimize.getAttribute("parallel")));
        }
        // 最大并行等待时长(秒)
        if (pageOptimize.hasAttribute("parallel-maxwait-seconds")) {
            optimize.parallelMaxWaitSeconds(Long.parseLong(pageOptimize.getAttribute("parallel-maxwait-seconds")));
        }
        sqlToyConfig.setPageOptimize(optimize);
    }
    // 解析翻译器
    parseTranslate(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("translate")));
    // 解析link
    parseLink(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("link")), local);
    // 解析对结果的运算
    parseCalculator(sqlToyConfig, sqlElt, local);
    // 解析解密字段
    parseSecureDecrypt(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("secure-decrypt")));
    // 解析安全脱敏配置
    parseSecureMask(sqlToyConfig, sqlElt.getElementsByTagName(local.concat("secure-mask")));
    // mongo/elastic查询语法
    if (isNoSql) {
        parseNoSql(sqlToyConfig, sqlElt, local);
    }
    return sqlToyConfig;
}