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

Example 31 with SqlToyConfig

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

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

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

the class SqlConfigParseUtils method parseSqlToyConfig.

/**
 * @todo 将动态的sql解析组合成一个SqlToyConfig模型，以便统一处理
 * @param querySql
 * @param dialect  当前的数据库类型,默认为null不指定
 * @param sqlType
 * @return
 */
public static SqlToyConfig parseSqlToyConfig(String querySql, String dialect, SqlType sqlType) {
    SqlToyConfig sqlToyConfig = new SqlToyConfig(dialect);
    // debug模式下面关闭sql打印
    sqlToyConfig.setShowSql(!StringUtil.matches(querySql, SqlToyConstants.NOT_PRINT_REGEX));
    // 是否忽视空记录
    sqlToyConfig.setIgnoreEmpty(StringUtil.matches(querySql, SqlToyConstants.IGNORE_EMPTY_REGEX));
    // 清理sql中的一些注释、以及特殊的符号
    String originalSql = SqlUtil.clearMistyChars(SqlUtil.clearMark(querySql), BLANK).concat(BLANK);
    // 对sql中的函数进行特定数据库方言转换
    originalSql = FunctionUtils.getDialectSql(originalSql, dialect);
    // 对关键词根据数据库类型进行转换,比如mysql的 ``变成mssql时变为[]
    originalSql = ReservedWordsUtil.convertSql(originalSql, DataSourceUtils.getDBType(dialect));
    // 判定是否有with查询模式
    sqlToyConfig.setHasWith(hasWith(originalSql));
    // 判定是否有union语句(先验证有union 然后再精确判断union 是否有效,在括号内的局部union 不起作用)
    if (StringUtil.matches(originalSql, SqlUtil.UNION_PATTERN)) {
        sqlToyConfig.setHasUnion(SqlUtil.hasUnion(originalSql, false));
    }
    // 只有在查询模式前提下才支持fastPage机制
    if (SqlType.search.equals(sqlType)) {
        // 判断是否有快速分页@fast 宏
        Matcher matcher = FAST_PATTERN.matcher(originalSql);
        if (matcher.find()) {
            int start = matcher.start();
            String preSql = originalSql.substring(0, start);
            String matchedFastSql = matcher.group();
            int endMarkIndex = StringUtil.getSymMarkIndex("(", ")", matchedFastSql, 0);
            // 得到分页宏处理器中的sql
            String fastSql = matchedFastSql.substring(matchedFastSql.indexOf("(") + 1, endMarkIndex);
            String tailSql = originalSql.substring(start + endMarkIndex + 1);
            // sql剔除掉快速分页宏,在分页查询时再根据presql和tailsql、fastsql自行组装，从而保障正常的非分页查询直接提取sql
            if (preSql.trim().endsWith("(") && tailSql.trim().startsWith(")")) {
                sqlToyConfig.setSql(preSql.concat(fastSql).concat(tailSql));
                sqlToyConfig.setIgnoreBracket(true);
            } else {
                sqlToyConfig.setSql(preSql.concat(" (").concat(fastSql).concat(") ").concat(tailSql));
            }
            sqlToyConfig.setFastSql(fastSql);
            sqlToyConfig.setFastPreSql(preSql);
            sqlToyConfig.setFastTailSql(tailSql);
            // 判断是否有快速分页
            sqlToyConfig.setHasFast(true);
        } else {
            sqlToyConfig.setSql(originalSql);
        }
    } else {
        sqlToyConfig.setSql(originalSql);
    }
    // 提取with fast查询语句
    processFastWith(sqlToyConfig, dialect);
    // 提取sql中的参数名称
    sqlToyConfig.setParamsName(getSqlParamsName(sqlToyConfig.getSql(dialect), true));
    return sqlToyConfig;
}

Example 34 with SqlToyConfig

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

the class SqlXMLConfigParse method parseSingleFile.

/**
 * @todo <b>解析单个sql对应的xml文件</b>
 * @param xmlFile
 * @param filesLastModifyMap
 * @param cache
 * @param encoding
 * @param dialect
 * @param isReload
 * @param index
 * @return
 * @throws Exception
 */
public static List<String> parseSingleFile(Object xmlFile, ConcurrentHashMap<String, Long> filesLastModifyMap, ConcurrentHashMap<String, SqlToyConfig> cache, String encoding, String dialect, boolean isReload, int index) throws Exception {
    InputStream fileIS = null;
    List<String> repeatSql = new ArrayList<String>();
    try {
        boolean isDebug = logger.isDebugEnabled();
        String sqlFile;
        if (xmlFile instanceof File) {
            File file = (File) xmlFile;
            sqlFile = file.getName();
            filesLastModifyMap.put(sqlFile, Long.valueOf(file.lastModified()));
            fileIS = new FileInputStream(file);
        } else {
            sqlFile = (String) xmlFile;
            fileIS = getResourceAsStream(sqlFile);
        }
        String logStr = "正在解析".concat((index != -1) ? "第:[" + index + "]个" : "").concat("sql文件:").concat(sqlFile);
        if (isDebug) {
            logger.debug(logStr);
        } else {
            out.println(logStr);
        }
        if (fileIS != null) {
            domFactory.setFeature(SqlToyConstants.XML_FETURE, false);
            DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
            Document doc = domBuilder.parse(fileIS);
            NodeList sqlElts = doc.getDocumentElement().getChildNodes();
            if (sqlElts == null || sqlElts.getLength() == 0) {
                return repeatSql;
            }
            // 解析单个sql
            SqlToyConfig sqlToyConfig;
            Element sqlElt;
            Node obj;
            for (int i = 0; i < sqlElts.getLength(); i++) {
                obj = sqlElts.item(i);
                if (obj.getNodeType() == Node.ELEMENT_NODE) {
                    sqlElt = (Element) obj;
                    sqlToyConfig = parseSingleSql(sqlElt, dialect);
                    if (sqlToyConfig != null) {
                        // 去除sql中的注释语句并放入缓存
                        if (cache.containsKey(sqlToyConfig.getId())) {
                            repeatSql.add(StringUtil.fillArgs("sql文件:{} 中发现重复的SQL语句id={} 已经被覆盖!", sqlFile, sqlToyConfig.getId()));
                            // 移除分页优化缓存
                            if (isReload) {
                                PageOptimizeUtils.remove(sqlToyConfig.getId());
                            }
                        }
                        cache.put(sqlToyConfig.getId(), sqlToyConfig);
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        logger.error("解析xml中对应的sql失败,对应文件={},正确的配置为<sql|mql|eql id=\"\"><![CDATA[]]></sql|mql|eql>或<sql|mql|eql id=\"\"><desc></desc><value><![CDATA[]]></value></sql|mql|eql>", xmlFile, e);
        throw e;
    } finally {
        if (fileIS != null) {
            fileIS.close();
        }
    }
    return repeatSql;
}

Example 35 with SqlToyConfig

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

the class DefaultDialect method load.

@Override
public Serializable load(SqlToyContext sqlToyContext, Serializable entity, List<Class> cascadeTypes, LockMode lockMode, Connection conn, Integer dbType, String dialect, String tableName) throws Exception {
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
    // 获取loadsql(loadsql 可以通过@loadSql进行改变，所以需要sqltoyContext重新获取)
    SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(entityMeta.getLoadSql(tableName), SqlType.search, dialect);
    String loadSql = sqlToyConfig.getSql(dialect);
    loadSql = loadSql.concat(getLockSql(loadSql, dbType, lockMode));
    return (Serializable) DialectUtils.load(sqlToyContext, sqlToyConfig, loadSql, entityMeta, entity, cascadeTypes, conn, dbType);
}