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

Example 6 with SqlWithAnalysis

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

Example 7 with SqlWithAnalysis

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

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

the class SqlConfigParseUtils method processFastWith.

/**
 * @todo 提取fastWith
 * @param sqlToyConfig
 */
public static void processFastWith(SqlToyConfig sqlToyConfig) {
    // 提取with as 和fast部分的sql，用于分页或取随机记录查询记录数量提供最简sql
    if (sqlToyConfig.isHasFast() && sqlToyConfig.isHasWith()) {
        SqlWithAnalysis sqlWith = new SqlWithAnalysis(sqlToyConfig.getSql());
        // 存在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(), "\\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 ");
                        if (i > 0)
                            buffer.append(",");
                        buffer.append(aliasTableAs[0]).append(" as (").append(aliasTableAs[1]).append(") ");
                    }
                    sqlToyConfig.setFastWithSql(buffer.toString());
                }
            }
        }
    }
}

Example 9 with SqlWithAnalysis

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

the class SybaseIQDialect method getRandomResult.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.DialectSqlWrapper#getRandomResult(org.
	 * sagacity .sqltoy.SqlToyContext,
	 * org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.executor.QueryExecutor, java.lang.Long, java.lang.Long,
	 * java.sql.Connection)
	 */
@Override
public QueryResult getRandomResult(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Long totalCount, Long randomCount, Connection conn) throws Exception {
    SqlWithAnalysis sqlWith = new SqlWithAnalysis(sqlToyConfig.getSql());
    QueryResult queryResult = null;
    boolean isNamed = sqlToyConfig.isNamedParam();
    String tmpTable = "#SAG_TMP_" + System.nanoTime();
    // 组合需要被插入的sql
    String pageSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlWith.getRejectWithSql();
    StringBuilder insertTempSql = new StringBuilder(pageSql.length() + 100);
    // 判断sql中是否有distinct或top
    if (DialectUtils.isComplexPageQuery(pageSql)) {
        insertTempSql.append("select ");
        insertTempSql.append(" sag_tmp_table.* ");
        insertTempSql.append(" into  ");
        insertTempSql.append(tmpTable);
        insertTempSql.append(" from ( ");
        insertTempSql.append(pageSql);
        insertTempSql.append(") sag_tmp_table");
    } else {
        String lowerSql = pageSql.toLowerCase();
        int fastPageFromIndex = StringUtil.getSymMarkIndex("select ", " from", lowerSql, 0);
        String columns = pageSql.substring(lowerSql.indexOf("select ") + 6, fastPageFromIndex);
        insertTempSql.append("select ");
        insertTempSql.append(columns);
        insertTempSql.append(" into ");
        insertTempSql.append(tmpTable).append(" ");
        insertTempSql.append(pageSql.substring(fastPageFromIndex));
    }
    if (sqlToyConfig.isHasWith()) {
        insertTempSql.insert(0, " ");
        insertTempSql.insert(0, sqlToyConfig.isHasFast() ? sqlToyConfig.getFastWithSql() : sqlWith.getWithSql());
    }
    boolean hasCreateTmp = false;
    try {
        // 通过参数处理最终的sql和参数值
        SqlToyResult queryParam = SqlConfigParseUtils.processSql(insertTempSql.toString(), queryExecutor.getParamsName(sqlToyConfig), queryExecutor.getParamsValue(sqlToyConfig));
        // 执行sql将记录插入临时表
        DialectUtils.executeSql(queryParam.getSql(), queryParam.getParamsValue(), null, conn, true);
        hasCreateTmp = true;
        StringBuilder sql = new StringBuilder();
        sql.append("select ");
        sql.append(" rowid(").append(tmpTable).append(") as page_row_id,");
        sql.append(" * from ");
        sql.append(tmpTable).append(" where page_row_id in (");
        sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
        sql.append(")");
        if (sqlToyConfig.isHasFast()) {
            sql.insert(0, sqlToyConfig.getFastPreSql() + " ( ");
            sql.append(" ) ");
            sql.append(sqlToyConfig.getFastTailSql());
            if (sqlToyConfig.getFastWithIndex() != -1) {
                sqlWith = new SqlWithAnalysis(sql.toString());
                sql.delete(0, sql.length() - 1);
                String[] aliasTableAs;
                int index = 0;
                for (int i = sqlToyConfig.getFastWithIndex() + 1; i < sqlWith.getWithSqlSet().size(); i++) {
                    aliasTableAs = sqlWith.getWithSqlSet().get(i);
                    if (index == 0)
                        sql.append("with ");
                    else
                        sql.append(",");
                    sql.append(aliasTableAs[0]);
                    sql.append(" as (");
                    sql.append(aliasTableAs[1]);
                    sql.append(")");
                    index++;
                }
                sql.append(" ").append(sqlWith.getRejectWithSql());
            }
        }
        queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), CommonUtils.randomArray(totalCount.intValue(), randomCount.intValue()), null);
        queryResult = findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), queryExecutor.getRowCallbackHandler(), conn, queryExecutor.getFetchSize(), queryExecutor.getMaxRows());
    } catch (Exception e) {
        throw e;
    } finally {
        // 删除临时表
        if (hasCreateTmp)
            DialectUtils.executeSql("drop table ".concat(tmpTable), null, null, conn, true);
    }
    return queryResult;
}

Example 10 with SqlWithAnalysis

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

the class SqlWithAnalysisTest method main.

public static void main(String[] args) {
    String sql = "with      t1(a,b) as not  materialized (select * from table),t2 as materialized(select name from ta)";
    SqlWithAnalysis sqlWith = new SqlWithAnalysis(sql);
    for (String[] result : sqlWith.getWithSqlSet()) {
        for (String s : result) {
            System.err.println("[" + s + "]");
        }
    }
}