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

Example 1 with SqlWithAnalysis

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

the class SybaseIQDialect method findTopBySql.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.Dialect#findTopBySql(org.sagacity.sqltoy.
	 * SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.executor.QueryExecutor, double, java.sql.Connection)
	 */
@Override
public QueryResult findTopBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, double topSize, Connection conn) throws Exception {
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast())
        sql.append(sqlToyConfig.getFastPreSql()).append(" (");
    String minSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlToyConfig.getSql();
    String partSql = " select top " + new Double(topSize).intValue() + " ";
    if (sqlToyConfig.isHasWith()) {
        SqlWithAnalysis sqlWith = new SqlWithAnalysis(minSql);
        sql.append(sqlWith.getWithSql());
        minSql = sqlWith.getRejectWithSql();
    }
    if (DialectUtils.hasUnion(minSql, false)) {
        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())
        sql.append(") ").append(sqlToyConfig.getFastTailSql());
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), null, null);
    return findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), queryExecutor.getRowCallbackHandler(), conn, queryExecutor.getFetchSize(), queryExecutor.getMaxRows());
}

Example 2 with SqlWithAnalysis

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

the class DialectUtils method getCountBySql.

/**
 * @todo 通用的查询记录总数
 * @param sqlToyContext
 * @param sql
 * @param paramsValue
 * @param isLastSql
 * @param conn
 * @return
 * @throws Exception
 */
public static Long getCountBySql(final SqlToyContext sqlToyContext, final String sql, final Object[] paramsValue, final boolean isLastSql, final Connection conn) throws Exception {
    String lastCountSql;
    int paramCnt = 0;
    int withParamCnt = 0;
    // 通过配置直接给定的最优化count 语句
    if (isLastSql) {
        lastCountSql = sql;
    } else {
        // with as分析器
        SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
        String query_tmp = sqlWith.getRejectWithSql();
        int lastBracketIndex = query_tmp.lastIndexOf(")");
        // 剔除order提高运行效率
        int orderByIndex = StringUtil.matchLastIndex(query_tmp, ORDER_BY_PATTERN);
        // 剔除order by 语句
        if (orderByIndex > lastBracketIndex)
            query_tmp = query_tmp.substring(0, orderByIndex + 1);
        int groupIndex = StringUtil.matchLastIndex(query_tmp, GROUP_BY_PATTERN);
        // 判断group by 是否是内层，如select * from (select * from table group by)
        // 外层group by 必须要进行包裹(update by chenrenfei 2016-4-21)
        boolean isInnerGroup = false;
        if (groupIndex != -1)
            isInnerGroup = clearDisturbSql(query_tmp.substring(groupIndex + 1)).lastIndexOf(")") != -1;
        final StringBuilder countQueryStr = new StringBuilder();
        // 是否包含union,update 2012-11-21
        boolean hasUnion = StringUtil.matches(query_tmp, UNION_PATTERN);
        // 性能最优
        if (!StringUtil.matches(query_tmp.trim(), DISTINCT_PATTERN) && !hasUnion && (groupIndex == -1 || (groupIndex < lastBracketIndex && isInnerGroup))) {
            int sql_from_index = 0;
            // sql不以from开头，截取from 后的部分语句
            if (StringUtil.indexOfIgnoreCase(query_tmp, "from") != 0)
                sql_from_index = StringUtil.getSymMarkIndexIgnoreCase("select ", " from", query_tmp, 0);
            String selectFields = (sql_from_index < 1) ? "" : query_tmp.substring(0, sql_from_index).toLowerCase();
            // 存在统计函数 update by chenrenfei ,date: 2017-2-24
            if (StringUtil.matches(selectFields, STAT_PATTERN))
                countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
            else
                // 截取from后的部分
                countQueryStr.append("select count(1) ").append((sql_from_index != -1 ? query_tmp.substring(sql_from_index) : query_tmp));
        } else // 包含distinct 或包含union则直接将查询作为子表(普通做法)
        {
            countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
        }
        paramCnt = SqlUtil.getParamsCount(countQueryStr.toString(), false);
        withParamCnt = SqlUtil.getParamsCount(sqlWith.getWithSql(), false);
        countQueryStr.insert(0, sqlWith.getWithSql() + " ");
        lastCountSql = countQueryStr.toString();
    }
    final int paramCntFin = paramCnt;
    final int withParamCntFin = withParamCnt;
    Object[] realParamsTemp = null;
    if (paramsValue != null) {
        // 将from前的参数剔除
        realParamsTemp = isLastSql ? paramsValue : CollectionUtil.subtractArray(paramsValue, withParamCntFin, paramsValue.length - paramCntFin - withParamCntFin);
    }
    final Object[] realParams = realParamsTemp;
    // 打印sql
    SqlExecuteStat.showSql(lastCountSql, realParams);
    PreparedStatement pst = conn.prepareStatement(lastCountSql);
    ResultSet rs = null;
    return (Long) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException, IOException {
            long resultCount = 0;
            if (realParams != null) {
                SqlUtil.setParamsValue(conn, pst, realParams, null, 0);
            }
            rs = pst.executeQuery();
            if (rs.next()) {
                resultCount = rs.getLong(1);
            }
            this.setResult(resultCount);
        }
    });
}

Example 3 with SqlWithAnalysis

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

the class DialectUtils method getCountBySql.

/**
 * @todo 通用的查询记录总数(包含剔除order by和智能判断是直接select count from ()
 *       还是直接剔除from之前的语句补充select count)
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param isLastSql
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
public static Long getCountBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final boolean isLastSql, final Connection conn, final Integer dbType) throws Exception {
    String lastCountSql;
    int paramCnt = 0;
    int withParamCnt = 0;
    // 通过配置直接给定的最优化count 语句
    if (isLastSql) {
        lastCountSql = sql;
    } else {
        String countPart = " count(1) ";
        // es count(1) 不起作用
        if (dbType.equals(DBType.ES)) {
            countPart = " count(*) ";
        }
        String query_tmp = sql;
        String withSql = "";
        // with as分析器(避免每次做with 检测,提升效率)
        if (sqlToyConfig != null && sqlToyConfig.isHasWith()) {
            SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
            query_tmp = sqlWith.getRejectWithSql();
            withSql = sqlWith.getWithSql();
        }
        int lastBracketIndex = query_tmp.lastIndexOf(")");
        int sql_from_index = 0;
        // sql不以from开头，截取from 后的部分语句
        if (StringUtil.indexOfIgnoreCase(query_tmp, "from") != 0) {
            sql_from_index = StringUtil.getSymMarkMatchIndex(SELECT_REGEX, FROM_REGEX, query_tmp.toLowerCase(), 0);
        }
        // 剔除order提高运行效率
        int orderByIndex = StringUtil.matchLastIndex(query_tmp, ORDER_BY_PATTERN);
        // order by 在from 之后
        if (orderByIndex > sql_from_index) {
            // 剔除order by 语句
            if (orderByIndex > lastBracketIndex) {
                query_tmp = query_tmp.substring(0, orderByIndex + 1);
            } else {
                // 剔除掉order by 后面语句对称的() 内容
                String orderJudgeSql = clearDisturbSql(query_tmp.substring(orderByIndex + 1));
                // 在order by 不在子查询内,说明可以整体切除掉order by
                if (orderJudgeSql.indexOf(")") == -1) {
                    query_tmp = query_tmp.substring(0, orderByIndex + 1);
                }
            }
        }
        int groupIndex = StringUtil.matchLastIndex(query_tmp, GROUP_BY_PATTERN);
        // 判断group by 是否是内层，如select * from (select * from table group by)
        // 外层group by 必须要进行包裹(update by chenrenfei 2016-4-21)
        boolean isInnerGroup = false;
        if (groupIndex != -1) {
            isInnerGroup = clearDisturbSql(query_tmp.substring(groupIndex + 1)).lastIndexOf(")") != -1;
        }
        final StringBuilder countQueryStr = new StringBuilder();
        // 是否包含union,update 2012-11-21
        boolean hasUnion = StringUtil.matches(query_tmp, UNION_PATTERN);
        // 性能最优
        if (!StringUtil.matches(query_tmp.trim(), DISTINCT_PATTERN) && !hasUnion && (groupIndex == -1 || (groupIndex < lastBracketIndex && isInnerGroup))) {
            int selectIndex = StringUtil.matchIndex(query_tmp.toLowerCase(), SELECT_REGEX);
            // 截取出select 和from之间的语句
            String selectFields = (sql_from_index < 1) ? "" : query_tmp.substring(selectIndex + 6, sql_from_index).toLowerCase();
            // 剔除嵌套的子查询语句中select 和 from 之间的内容,便于判断统计函数的作用位置
            selectFields = clearSymSelectFromSql(selectFields);
            // 存在统计函数 update by chenrenfei ,date: 2017-2-24
            if (StringUtil.matches(selectFields, STAT_PATTERN)) {
                countQueryStr.append("select ").append(countPart).append(" from (").append(query_tmp).append(") sag_count_tmpTable ");
            } else {
                // 截取from后的部分
                countQueryStr.append("select ").append(countPart).append((sql_from_index != -1 ? query_tmp.substring(sql_from_index) : query_tmp));
            }
        } else // 包含distinct 或包含union则直接将查询作为子表(普通做法)
        {
            countQueryStr.append("select ").append(countPart).append(" from (").append(query_tmp).append(") sag_count_tmpTable ");
        }
        paramCnt = getParamsCount(countQueryStr.toString());
        withParamCnt = getParamsCount(withSql);
        countQueryStr.insert(0, withSql + " ");
        lastCountSql = countQueryStr.toString();
    }
    final int paramCntFin = paramCnt;
    final int withParamCntFin = withParamCnt;
    Object[] realParamsTemp = null;
    if (paramsValue != null) {
        // 将from前的参数剔除
        realParamsTemp = isLastSql ? paramsValue : CollectionUtil.subtractArray(paramsValue, withParamCntFin, paramsValue.length - paramCntFin - withParamCntFin);
    }
    final Object[] realParams = realParamsTemp;
    // 做sql签名
    lastCountSql = SqlUtilsExt.signSql(lastCountSql, dbType, sqlToyConfig);
    // 打印sql
    SqlExecuteStat.showSql("执行count查询", lastCountSql, realParams);
    PreparedStatement pst = conn.prepareStatement(lastCountSql);
    ResultSet rs = null;
    return (Long) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws SQLException, IOException {
            long resultCount = 0;
            if (realParams != null) {
                SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, pst, realParams, null, 0);
            }
            rs = pst.executeQuery();
            if (rs.next()) {
                resultCount = rs.getLong(1);
            }
            this.setResult(resultCount);
        }
    });
}

Example 4 with SqlWithAnalysis

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

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

the class SqlConfigParseUtils method processFastWith.

/**
 * @todo 提取fastWith(@fast 涉及到的cte 查询,这里是很别致的地方，假如sql中存在with as t1 (),t2 (),t3 ()
 *       select * from @fast(t1,t2) 做count查询时将执行: with as t1(),t2 () select
 *       count(1) from xxx,而不会额外的多执行t3)
 * @param sqlToyConfig
 * @param dialect
 */
public static void processFastWith(SqlToyConfig sqlToyConfig, String dialect) {
    // 不存在fast 和with 不做处理
    if (!sqlToyConfig.isHasFast() || !sqlToyConfig.isHasWith()) {
        return;
    }
    // 提取with as 和fast部分的sql，用于分页或取随机记录查询记录数量提供最简sql
    SqlWithAnalysis sqlWith = new SqlWithAnalysis(sqlToyConfig.getSql(dialect));
    // 存在with xxx as () 形式的查询
    if (null != sqlWith.getWithSqlSet()) {
        String[] aliasTableAs;
        int endIndex = -1;
        int withSqlSize = sqlWith.getWithSqlSet().size();
        // 判定fast查询引用到第几个位置的with
        for (int i = withSqlSize - 1; i >= 0; i--) {
            aliasTableAs = sqlWith.getWithSqlSet().get(i);
            if (StringUtil.matches(sqlToyConfig.getFastSql(dialect), "\\W".concat(aliasTableAs[0]).concat("\\W"))) {
                endIndex = i;
                sqlToyConfig.setFastWithIndex(endIndex);
                break;
            }
        }
        // 组装with xx as () +fastsql
        if (endIndex != -1) {
            if (endIndex == withSqlSize - 1) {
                sqlToyConfig.setFastWithSql(sqlWith.getWithSql());
            } else {
                StringBuilder buffer = new StringBuilder();
                for (int i = 0; i < endIndex + 1; i++) {
                    aliasTableAs = sqlWith.getWithSqlSet().get(i);
                    if (i == 0) {
                        buffer.append(" with ").append(aliasTableAs[3]);
                    }
                    if (i > 0) {
                        buffer.append(",").append(aliasTableAs[3]);
                    }
                    // aliasTableAs 结构{aliasName,as和括号之间的字符串,as内容,with 和aliasTable之间的参数}
                    buffer.append(aliasTableAs[0]).append(" as ").append(aliasTableAs[1]).append(" (").append(aliasTableAs[2]).append(") ");
                }
                sqlToyConfig.setFastWithSql(buffer.toString());
            }
        }
    }
}