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

Example 46 with SqlToyResult

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

the class OracleDialect method findPageBySql.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.DialectSqlWrapper#findPageBySql(org.sagacity
	 * .sqltoy.SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.executor.QueryExecutor,
	 * org.sagacity.core.database.callback.RowCallbackHandler, java.lang.Long,
	 * java.lang.Integer, java.sql.Connection)
	 */
@Override
public QueryResult findPageBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Long pageNo, Integer pageSize, Connection conn) throws Exception {
    StringBuilder sql = new StringBuilder();
    boolean isNamed = sqlToyConfig.isNamedParam();
    int startIndex = 1;
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql());
        sql.append(" (");
        startIndex = 0;
    }
    sql.append("SELECT * FROM (SELECT ROWNUM page_row_id,SAG_Paginationtable.* FROM ( ");
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlToyConfig.getSql());
    sql.append(") SAG_Paginationtable ");
    /*
		 * 判断sql中是否存在排序，因为oracle排序查询的机制通过ROWNUM<=?每次查出的结果可能不一样 ， 请参见ROWNUM机制以及oracle
		 * SORT ORDER BY STOPKEY
		 */
    if (SqlToyConstants.oraclePageIgnoreOrder() || !SqlUtil.hasOrderBy(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlToyConfig.getSql(), 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()) {
        sql.append(") ").append(sqlToyConfig.getFastTailSql());
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), pageNo * pageSize, (pageNo - 1) * pageSize);
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), queryExecutor.getRowCallbackHandler(), conn, startIndex, queryExecutor.getFetchSize(), queryExecutor.getMaxRows());
}

Example 47 with SqlToyResult

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

the class MySqlDialect 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 {
    String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlToyConfig.getSql();
    /*
		 * select * from table order by rand() limit :randomCount 性能比较差,通过产生rand()
		 * row_number 再排序方式性能稍好 同时也可以保证通用性
		 */
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast())
        sql.append(sqlToyConfig.getFastPreSql()).append(" (");
    sql.append("select sag_random_table1.* from (");
    // sql中是否存在排序或union,存在order 或union 则在sql外包裹一层
    if (DialectUtils.hasOrderByOrUnion(innerSql)) {
        sql.append("select rand() as sag_row_number,sag_random_table.* from (");
        sql.append(innerSql);
        sql.append(") sag_random_table ");
    } else
        sql.append(innerSql.replaceFirst("(?i)select", "select rand() as sag_row_number,"));
    sql.append(" )  as sag_random_table1 ");
    sql.append(" order by sag_random_table1.sag_row_number limit ");
    sql.append(randomCount);
    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 48 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.DialectSqlWrapper#findPageBySql(org.sagacity
	 * .sqltoy.SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
	 * org.sagacity.sqltoy.executor.QueryExecutor,
	 * org.sagacity.core.database.callback.RowCallbackHandler, java.lang.Long,
	 * java.lang.Integer, java.sql.Connection)
	 */
@Override
public QueryResult findPageBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Long pageNo, Integer pageSize, Connection conn) throws Exception {
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql());
        sql.append(" (").append(sqlToyConfig.getFastSql());
    } else
        sql.append(sqlToyConfig.getSql());
    // 判断是否存在order by
    int lastFromIndex = StringUtil.matchLastIndex(sqlToyConfig.getSql(), FROM);
    // 没有order by 自动补充
    if (lastFromIndex > 0 && !StringUtil.matches(sqlToyConfig.getSql().substring(lastFromIndex), ORDER_BY))
        sql.append(" order by NEWID() ");
    sql.append(" offset ");
    sql.append(sqlToyConfig.isNamedParam() ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
    sql.append(" rows fetch next ");
    sql.append(sqlToyConfig.isNamedParam() ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    sql.append(" rows only");
    if (sqlToyConfig.isHasFast())
        sql.append(") ").append(sqlToyConfig.getFastTailSql());
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), (pageNo - 1) * pageSize, Long.valueOf(pageSize));
    return findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), queryExecutor.getRowCallbackHandler(), conn, queryExecutor.getFetchSize(), queryExecutor.getMaxRows());
}

Example 49 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.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 50 with SqlToyResult

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

the class SqliteDialect 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());
        sql.append(" (").append(sqlToyConfig.getFastSql());
    } else
        sql.append(sqlToyConfig.getSql());
    sql.append(" limit ");
    sql.append(new Double(topSize).intValue());
    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());
}