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

Example 66 with SqlToyResult

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

the class DefaultDialectUtils method getRandomResult.

/**
 * @TODO 取随机记录
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param totalCount
 * @param randomCount
 * @param conn
 * @param dbType
 * @param dialect
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult getRandomResult(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long totalCount, Long randomCount, Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows) throws Exception {
    String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
    // select * from table order by rand() limit :randomCount 性能比较差,通过产生rand()
    // row_number 再排序方式性能稍好 同时也可以保证通用性
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.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()) {
        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 DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 67 with SqlToyResult

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

the class Oracle11gDialect 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(" (");
        }
    }
    sql.append("SELECT SAG_Paginationtable.* FROM ( ");
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect));
    sql.append(") SAG_Paginationtable where ROWNUM <=");
    sql.append(Double.valueOf(topSize).intValue());
    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 DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 68 with SqlToyResult

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

the class OracleDialectUtils method findPageBySql.

/**
 * @TODO 分页查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param pageNo
 * @param pageSize
 * @param conn
 * @param dbType
 * @param dialect
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult findPageBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long pageNo, Integer pageSize, Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows) throws Exception {
    StringBuilder sql = new StringBuilder();
    boolean isNamed = sqlToyConfig.isNamedParam();
    // 是否有order by,update 2017-5-22
    boolean hasOrderBy = SqlUtil.hasOrderBy(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect), true);
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // order by 外包裹一层,确保查询结果是按排序
    if (hasOrderBy) {
        sql.append(" select SAG_Paginationtable.* from (");
    }
    sql.append(sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect));
    if (hasOrderBy) {
        sql.append(") SAG_Paginationtable ");
    }
    sql.append(" offset ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
    sql.append(" rows fetch next ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    sql.append(" rows only ");
    if (sqlToyConfig.isHasFast()) {
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(") ");
        }
        sql.append(sqlToyConfig.getFastTailSql(dialect));
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), (pageNo - 1) * pageSize, Long.valueOf(pageSize), dialect);
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 69 with SqlToyResult

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

the class SqlConfigParseUtilsTest method testChinaParamName.

@Test
public void testChinaParamName() throws Exception {
    String sql = "select * from table where 1=1 #[and id=:单据_编号_id] and name like @value(:name) #[and status=:status]";
    SqlToyResult result = SqlConfigParseUtils.processSql(sql, new String[] { "单据_编号_id", "name", "status" }, new Object[] { "1", null, "1" });
    System.err.println(JSON.toJSONString(result));
}

Example 70 with SqlToyResult

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

the class SqlConfigParseUtilsTest method testSynSign.

@Test
public void testSynSign() throws Exception {
    String sql = "select * from table where #[id in [arraystringconcat(name)] and id=:id ]#[and name like :name] #[and status=:status]";
    SqlToyResult result = SqlConfigParseUtils.processSql(sql, new String[] { "id", "name", "status" }, new Object[] { "1", null, "1" });
    System.err.println(JSON.toJSONString(result));
}