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

Example 56 with SqlToyResult

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

the class DialectUtils method update.

/**
 * @todo 单个对象修改，包含接连修改
 * @param sqlToyContext
 * @param entity
 * @param nullFunction
 * @param forceUpdateFields
 * @param cascade
 * @param generateSqlHandler
 * @param forceCascadeClasses
 * @param subTableForceUpdateProps
 * @param conn
 * @param tableName
 * @throws Exception
 */
public static Long update(SqlToyContext sqlToyContext, Serializable entity, String nullFunction, String[] forceUpdateFields, final boolean cascade, final GenerateSqlHandler generateSqlHandler, final Class[] forceCascadeClasses, final HashMap<Class, String[]> subTableForceUpdateProps, Connection conn, String tableName) throws Exception {
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
    Long updateCnt = update(sqlToyContext, entity, entityMeta, nullFunction, forceUpdateFields, conn, tableName);
    // 级联保存
    if (cascade && !entityMeta.getOneToManys().isEmpty()) {
        HashMap<Type, String> typeMap = new HashMap<Type, String>();
        // 即使子对象数据是null,也强制进行级联修改(null表示删除子表数据)
        if (forceCascadeClasses != null) {
            for (Type type : forceCascadeClasses) {
                typeMap.put(type, "");
            }
        }
        // 级联子表数据
        List subTableData;
        final Object[] IdValues = BeanUtil.reflectBeanToAry(entity, entityMeta.getIdArray(), null, null);
        String[] forceUpdateProps = null;
        EntityMeta subTableEntityMeta;
        // 对子表进行级联处理
        for (OneToManyModel oneToMany : entityMeta.getOneToManys()) {
            subTableEntityMeta = sqlToyContext.getEntityMeta(oneToMany.getMappedType());
            forceUpdateProps = (subTableForceUpdateProps == null) ? null : subTableForceUpdateProps.get(oneToMany.getMappedType());
            subTableData = (List) BeanUtil.invokeMethod(entity, "get".concat(StringUtil.firstToUpperCase(oneToMany.getProperty())), null);
            final String[] mappedFields = oneToMany.getMappedFields();
            /**
             * 针对子表存量数据,调用级联修改的语句，分delete 和update两种操作 1、删除存量数据;2、设置存量数据状态为停用
             */
            if (oneToMany.getCascadeUpdateSql() != null && ((subTableData != null && !subTableData.isEmpty()) || typeMap.containsKey(oneToMany.getMappedType()))) {
                // 根据quickvo配置文件针对cascade中update-cascade配置组织具体操作sql
                SqlToyResult sqlToyResult = SqlConfigParseUtils.processSql(oneToMany.getCascadeUpdateSql(), mappedFields, IdValues);
                executeSql(sqlToyResult.getSql(), sqlToyResult.getParamsValue(), null, conn, null);
            }
            // 子表数据不为空,采取saveOrUpdateAll操作
            if (subTableData != null && !subTableData.isEmpty()) {
                saveOrUpdateAll(sqlToyContext, subTableData, sqlToyContext.getBatchSize(), subTableEntityMeta, forceUpdateProps, generateSqlHandler, // 设置关联外键字段的属性值(来自主表的主键)
                new ReflectPropertyHandler() {

                    public void process() {
                        for (int i = 0; i < mappedFields.length; i++) {
                            this.setValue(mappedFields[i], IdValues[i]);
                        }
                    }
                }, conn, null);
            }
        }
    }
    return updateCnt;
}

Example 57 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
 * @return
 * @throws Exception
 */
public static QueryResult getRandomResult(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, Long totalCount, Long randomCount, Connection conn) throws Exception {
    String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql() : sqlToyConfig.getSql();
    StringBuilder sql = new StringBuilder();
    if (sqlToyConfig.isHasFast())
        sql.append(sqlToyConfig.getFastPreSql()).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())
        sql.append(") ").append(sqlToyConfig.getFastTailSql());
    SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor, sql.toString(), null, null);
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), queryExecutor.getRowCallbackHandler(), conn, 0, queryExecutor.getFetchSize(), queryExecutor.getMaxRows());
}

Example 58 with SqlToyResult

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

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

the class SybaseIQDialect method update.

/*
	 * (non-Javadoc)
	 * 
	 * @see org.sagacity.sqltoy.dialect.Dialect#update(org.sagacity.sqltoy.
	 * SqlToyContext , java.io.Serializable, java.lang.String[],
	 * java.sql.Connection)
	 */
@Override
public Long update(SqlToyContext sqlToyContext, Serializable entity, String[] forceUpdateFields, final boolean cascade, final Class[] emptyCascadeClasses, final HashMap<Class, String[]> subTableForceUpdateProps, Connection conn, final String tableName) throws Exception {
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
    Long updateCount = DialectUtils.update(sqlToyContext, entity, entityMeta, NVL_FUNCTION, forceUpdateFields, conn, tableName);
    // 级联保存
    if (cascade && null != entityMeta.getOneToManys() && !entityMeta.getOneToManys().isEmpty()) {
        HashMap<Type, String> typeMap = new HashMap<Type, String>();
        if (emptyCascadeClasses != null)
            for (Type type : emptyCascadeClasses) {
                typeMap.put(type, "");
            }
        // 级联子表数据
        List subTableData;
        final Object[] IdValues = BeanUtil.reflectBeanToAry(entity, entityMeta.getIdArray(), null, null);
        String[] forceUpdateProps = null;
        for (OneToManyModel oneToMany : entityMeta.getOneToManys()) {
            forceUpdateProps = (subTableForceUpdateProps == null) ? null : subTableForceUpdateProps.get(oneToMany.getMappedType());
            subTableData = (List) BeanUtil.invokeMethod(entity, "get".concat(StringUtil.firstToUpperCase(oneToMany.getProperty())), null);
            final String[] mappedFields = oneToMany.getMappedFields();
            /**
             * 针对子表存量数据,调用级联修改的语句，分delete 和update两种操作 1、删除存量数据;2、设置存量数据状态为停用
             */
            if (oneToMany.getCascadeUpdateSql() != null && ((subTableData != null && !subTableData.isEmpty()) || typeMap.containsKey(oneToMany.getMappedType()))) {
                SqlToyResult sqlToyResult = SqlConfigParseUtils.processSql(oneToMany.getCascadeUpdateSql(), mappedFields, IdValues);
                DialectUtils.executeSql(sqlToyResult.getSql(), sqlToyResult.getParamsValue(), null, conn, null);
            }
            // 子表数据不为空,采取saveOrUpdateAll操作
            if (subTableData != null && !subTableData.isEmpty()) {
                saveOrUpdateAll(sqlToyContext, subTableData, sqlToyContext.getBatchSize(), // 设置关联外键字段的属性值(来自主表的主键)
                new ReflectPropertyHandler() {

                    public void process() {
                        for (int i = 0; i < mappedFields.length; i++) {
                            this.setValue(mappedFields[i], IdValues[i]);
                        }
                    }
                }, forceUpdateProps, conn, null, null);
            }
        }
    }
    return updateCount;
}

Example 60 with SqlToyResult

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

the class DialectUtils method loadAll.

/**
 * @todo 提供统一的loadAll处理机制
 * @param sqlToyContext
 * @param sql
 * @param entities
 * @param cascadeTypes
 * @param conn
 * @return
 * @throws Exception
 */
public static List<?> loadAll(final SqlToyContext sqlToyContext, String sql, List<?> entities, List<Class> cascadeTypes, Connection conn) throws Exception {
    if (entities == null || entities.isEmpty())
        return entities;
    Class entityClass = entities.get(0).getClass();
    EntityMeta entityMeta = sqlToyContext.getEntityMeta(entityClass);
    // 没有主键不能进行load相关的查询
    if (entityMeta.getIdArray() == null) {
        throw new Exception("表:" + entityMeta.getSchemaTable() + " 没有主键,不符合load或loadAll规则,请检查表设计是否合理!");
    }
    // 主键值
    List pkValues = BeanUtil.reflectBeansToList(entities, entityMeta.getIdArray());
    int idSize = entityMeta.getIdArray().length;
    List[] idValues = new List[idSize];
    // 构造内部的list
    for (int i = 0; i < idSize; i++) {
        idValues[i] = new ArrayList();
    }
    List rowList;
    // 检查主键值,主键值必须不为null
    Object value;
    for (int i = 0, n = pkValues.size(); i < n; i++) {
        rowList = (List) pkValues.get(i);
        for (int j = 0; j < idSize; j++) {
            value = rowList.get(j);
            if (null == value)
                throw new Exception("loadAll method must assign value for pk,row:" + i + " pk field:" + entityMeta.getIdArray()[j]);
            if (!idValues[j].contains(value)) {
                idValues[j].add(value);
            }
        }
    }
    SqlToyResult sqlToyResult = SqlConfigParseUtils.processSql(sql, entityMeta.getIdArray(), idValues);
    SqlExecuteStat.showSql(sqlToyResult.getSql(), sqlToyResult.getParamsValue());
    List<?> entitySet = SqlUtil.findByJdbcQuery(sqlToyResult.getSql(), sqlToyResult.getParamsValue(), entityClass, null, conn);
    // 存在主表对应子表
    if (null != cascadeTypes && !cascadeTypes.isEmpty() && !entityMeta.getOneToManys().isEmpty()) {
        StringBuilder subTableSql = new StringBuilder();
        List items;
        SqlToyResult subToyResult;
        for (OneToManyModel oneToMany : entityMeta.getOneToManys()) {
            if (cascadeTypes.contains(oneToMany.getMappedType())) {
                // 清空buffer
                subTableSql.delete(0, subTableSql.length());
                subTableSql.append("select * from ").append(oneToMany.getMappedTable()).append(" where ");
                for (int i = 0; i < idSize; i++) {
                    if (i > 0)
                        subTableSql.append(" and ");
                    subTableSql.append(oneToMany.getMappedColumns()[i]);
                    subTableSql.append(" in (:" + entityMeta.getIdArray()[i] + ") ");
                }
                subToyResult = SqlConfigParseUtils.processSql(subTableSql.toString(), entityMeta.getIdArray(), idValues);
                SqlExecuteStat.showSql(subToyResult.getSql(), subToyResult.getParamsValue());
                items = SqlUtil.findByJdbcQuery(subToyResult.getSql(), subToyResult.getParamsValue(), oneToMany.getMappedType(), null, conn);
                // 调用vo中mapping方法,将子表对象规整到主表对象的oneToMany集合中
                BeanUtil.invokeMethod(entities.get(0), "mapping" + StringUtil.firstToUpperCase(oneToMany.getProperty()), new Object[] { entitySet, items });
            }
        }
    }
    return entitySet;
}