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

Example 26 with SqlToyResult

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

the class DB2DialectUtils 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);
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // sql中是否存在排序或union
    boolean hasOrderOrUnion = DialectUtils.hasOrderByOrUnion(innerSql);
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select sag_random_table.* from (");
    }
    sql.append(innerSql);
    if (hasOrderOrUnion) {
        sql.append(") sag_random_table ");
    }
    sql.append(" order by rand() fetch first ");
    sql.append(randomCount);
    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(), 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 27 with SqlToyResult

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

the class DefaultDialectUtils method deleteAll.

/**
 * @todo 批量删除对象
 * @param sqlToyContext
 * @param entities
 * @param batchSize
 * @param conn
 * @param dbType
 * @param autoCommit
 * @param tableName
 * @return
 * @throws Exception
 */
public static Long deleteAll(SqlToyContext sqlToyContext, List<?> entities, final int batchSize, Connection conn, final Integer dbType, final Boolean autoCommit, final String tableName) throws Exception {
    if (null == entities || entities.isEmpty()) {
        return 0L;
    }
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entities.get(0).getClass());
    String realTable = entityMeta.getSchemaTable(tableName, dbType);
    if (null == entityMeta.getIdArray() || entityMeta.getIdArray().length == 0) {
        throw new IllegalArgumentException("delete/deleteAll 操作,表:" + realTable + "没有主键,请检查表设计!");
    }
    int idSize = entityMeta.getIdArray().length;
    // 构造delete 语句
    StringBuilder deleteSql = new StringBuilder();
    // clickhouse 删除语法特殊
    if (dbType == DBType.CLICKHOUSE) {
        deleteSql.append("alter table ");
        deleteSql.append(realTable);
        deleteSql.append(" delete where ");
    } else {
        deleteSql.append("delete from ");
        deleteSql.append(realTable);
        deleteSql.append("  where ");
    }
    String field;
    SqlToyResult sqlToyResult = null;
    String colName;
    // 单主键
    if (idSize == 1) {
        Object[] idValues = BeanUtil.sliceToArray(entities, entityMeta.getIdArray()[0]);
        if (idValues == null || idValues.length == 0) {
            throw new IllegalArgumentException(tableName + " deleteAll method must assign value for pk field:" + entityMeta.getIdArray()[0]);
        }
        field = entityMeta.getIdArray()[0];
        colName = ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType);
        deleteSql.append(colName);
        deleteSql.append(" in (?) ");
        sqlToyResult = SqlConfigParseUtils.processSql(deleteSql.toString(), null, new Object[] { idValues }, null);
    } else {
        List<Object[]> idValues = BeanUtil.reflectBeansToInnerAry(entities, entityMeta.getIdArray(), null, null);
        int dataSize = idValues.size();
        Object[] rowData;
        Object cellValue;
        // 将条件构造成一个数组
        Object[] realValues = new Object[idValues.size() * idSize];
        int index = 0;
        for (int i = 0; i < dataSize; i++) {
            rowData = idValues.get(i);
            for (int j = 0; j < idSize; j++) {
                cellValue = rowData[j];
                // 验证主键值是否合法
                if (StringUtil.isBlank(cellValue)) {
                    throw new IllegalArgumentException(tableName + " deleteAll method must assign value for pk,row:" + i + " pk field:" + entityMeta.getIdArray()[j]);
                }
                realValues[index] = cellValue;
                index++;
            }
        }
        // 复合主键构造 (field1=? and field2=?)
        String condition = " (";
        for (int i = 0, n = idSize; i < n; i++) {
            field = entityMeta.getIdArray()[i];
            colName = ReservedWordsUtil.convertWord(entityMeta.getColumnName(field), dbType);
            if (i > 0) {
                condition = condition.concat(" and ");
            }
            condition = condition.concat(colName).concat("=?");
        }
        condition = condition.concat(")");
        // 构造 (field1=? and field2=?) or (field1=? and field2=?)
        for (int i = 0; i < dataSize; i++) {
            if (i > 0) {
                deleteSql.append(" or ");
            }
            deleteSql.append(condition);
        }
        sqlToyResult = SqlConfigParseUtils.processSql(deleteSql.toString(), null, realValues, null);
    }
    return SqlUtil.executeSql(sqlToyContext.getTypeHandler(), sqlToyResult.getSql(), sqlToyResult.getParamsValue(), null, conn, dbType, autoCommit, false);
}

Example 28 with SqlToyResult

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

the class DefaultDialectUtils method findTopBySql.

/**
 * @todo 实现top记录查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param topSize
 * @param conn
 * @param dbType
 * @param dialect
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static 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(sqlToyConfig.getFastSql(dialect));
    } else {
        sql.append(sqlToyConfig.getSql(dialect));
    }
    sql.append(" limit ");
    sql.append(topSize);
    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 29 with SqlToyResult

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

the class DefaultDialectUtils 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();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
        sql.append(sqlToyConfig.getFastSql(dialect));
    } else {
        sql.append(sqlToyConfig.getSql(dialect));
    }
    sql.append(" limit ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
    sql.append(" offset ");
    sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
    if (sqlToyConfig.isHasFast()) {
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(") ");
        }
        sql.append(sqlToyConfig.getFastTailSql(dialect));
    }
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), Long.valueOf(pageSize), (pageNo - 1) * pageSize, dialect);
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}

Example 30 with SqlToyResult

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

the class PostgreSqlDialectUtils 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);
    // sql中是否存在排序或union
    boolean hasOrderOrUnion = DialectUtils.hasOrderByOrUnion(innerSql);
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast()) {
        sql.append(sqlToyConfig.getFastPreSql(dialect));
        if (!sqlToyConfig.isIgnoreBracket()) {
            sql.append(" (");
        }
    }
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select sag_random_table.* from (");
    }
    sql.append(innerSql);
    if (hasOrderOrUnion) {
        sql.append(") sag_random_table ");
    }
    sql.append(" order by random() 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);
}