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

Example 61 with SqlToyResult

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

the class SqlExecuteStat method loggerSql.

private static void loggerSql() {
    try {
        SqlExecuteTrace sqlTrace = threadLocal.get();
        if (sqlTrace == null)
            return;
        long overTime = sqlTrace.getExecuteTime() - printSqlTimeoutMillis;
        if (overTime >= 0 && sqlTrace.getStart() != null)
            logger.warn("类型:{}的sql执行超出:{}毫秒的阀值, 共执行:{} 毫秒,请优化!", sqlTrace.getType(), printSqlTimeoutMillis, overTime + printSqlTimeoutMillis);
        else if (!sqlTrace.isError())
            return;
        List<SqlToyResult> sqlToyResults = sqlTrace.getSqlToyResults();
        for (SqlToyResult sqlResult : sqlToyResults) printSql(sqlResult.getSql(), sqlResult.getParamsValue(), true);
    } catch (Exception e) {
    }
}

Example 62 with SqlToyResult

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

the class DialectFactory method executeSql.

/**
 * @todo 执行sql修改性质的操作语句
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param paramsTypes
 * @param autoCommit
 * @param dataSource
 * @return
 */
public Long executeSql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final QueryExecutor queryExecutor, final Integer[] paramsTypes, final Boolean autoCommit, final DataSource dataSource) {
    try {
        SqlExecuteStat.start(sqlToyConfig.getId(), "executeSql", sqlToyConfig.isShowSql());
        // 将修改语句当做特殊的查询，其处理过程在交jdbc执行前完全一致
        final QueryExecutorExtend extend = queryExecutor.getInnerModel();
        // 组织参数和参数校验，但忽视数据权限数据的传参和校验
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        Long updateTotalCnt = (Long) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 进行sharding table替换
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                // 做sql签名
                String executeSql = SqlUtilsExt.signSql(queryParam.getSql(), dbType, realSqlToyConfig);
                this.setResult(SqlUtil.executeSql(sqlToyContext.getTypeHandler(), executeSql, queryParam.getParamsValue(), paramsTypes, conn, dbType, autoCommit, false));
            }
        });
        SqlExecuteStat.debug("执行结果", "受影响记录数量:{} 条!", updateTotalCnt);
        return updateTotalCnt;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 63 with SqlToyResult

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

the class DialectUtils method load.

/**
 * @todo 加载获取单笔数据库记录
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param entityMeta
 * @param entity
 * @param cascadeTypes
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
public static Serializable load(final SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, String sql, EntityMeta entityMeta, Serializable entity, List<Class> cascadeTypes, Connection conn, final Integer dbType) throws Exception {
    Object[] pkValues = BeanUtil.reflectBeanToAry(entity, entityMeta.getIdArray());
    // 检查主键值是否合法
    for (int i = 0; i < pkValues.length; i++) {
        if (StringUtil.isBlank(pkValues[i])) {
            throw new IllegalArgumentException(entityMeta.getSchemaTable(null, dbType) + " load method must assign value for pk,null pk field is:" + entityMeta.getIdArray()[i]);
        }
    }
    SqlToyResult sqlToyResult = SqlConfigParseUtils.processSql(sql, entityMeta.getIdArray(), pkValues, null);
    // 加密字段解密
    DecryptHandler decryptHandler = null;
    if (entityMeta.getSecureColumns() != null) {
        decryptHandler = new DecryptHandler(sqlToyContext.getFieldsSecureProvider(), entityMeta.getSecureColumns());
    }
    QueryResult queryResult = findBySql(sqlToyContext, sqlToyConfig, sqlToyResult.getSql(), sqlToyResult.getParamsValue(), null, decryptHandler, conn, dbType, 0, -1, -1);
    List rows = queryResult.getRows();
    Serializable result = null;
    Class entityClass;
    if (rows != null && rows.size() > 0) {
        entityClass = BeanUtil.getEntityClass(entity.getClass());
        rows = BeanUtil.reflectListToBean(sqlToyContext.getTypeHandler(), rows, ResultUtils.humpFieldNames(queryResult.getLabelNames(), entityMeta.getColumnFieldMap()), entityClass);
        result = (Serializable) rows.get(0);
        // 处理类中的@Translate注解，进行缓存翻译
        ResultUtils.wrapResultTranslate(sqlToyContext, result, entityClass);
    }
    if (result == null) {
        return null;
    }
    // 存在主表对应子表
    if (null != cascadeTypes && !cascadeTypes.isEmpty() && !entityMeta.getCascadeModels().isEmpty()) {
        List pkRefDetails;
        EntityMeta mappedMeta;
        Object[] mainFieldValues;
        String loadSubTableSql;
        for (TableCascadeModel cascadeModel : entityMeta.getCascadeModels()) {
            // 判定是否要加载
            if (cascadeTypes.contains(cascadeModel.getMappedType())) {
                mainFieldValues = BeanUtil.reflectBeanToAry(result, cascadeModel.getFields());
                loadSubTableSql = ReservedWordsUtil.convertSql(cascadeModel.getLoadSubTableSql(), dbType);
                sqlToyResult = SqlConfigParseUtils.processSql(loadSubTableSql, cascadeModel.getMappedFields(), mainFieldValues, null);
                SqlExecuteStat.showSql("级联子表加载查询", sqlToyResult.getSql(), sqlToyResult.getParamsValue());
                mappedMeta = sqlToyContext.getEntityMeta(cascadeModel.getMappedType());
                // 子表加密字段解密
                DecryptHandler subDecryptHandler = null;
                if (mappedMeta.getSecureColumns() != null) {
                    subDecryptHandler = new DecryptHandler(sqlToyContext.getFieldsSecureProvider(), mappedMeta.getSecureColumns());
                }
                pkRefDetails = SqlUtil.findByJdbcQuery(sqlToyContext.getTypeHandler(), sqlToyResult.getSql(), sqlToyResult.getParamsValue(), cascadeModel.getMappedType(), null, subDecryptHandler, conn, dbType, false, mappedMeta.getColumnFieldMap(), SqlToyConstants.FETCH_SIZE, -1);
                // 处理子类中@Translate注解，进行缓存翻译
                ResultUtils.wrapResultTranslate(sqlToyContext, pkRefDetails, cascadeModel.getMappedType());
                if (null != pkRefDetails && !pkRefDetails.isEmpty()) {
                    // oneToMany
                    if (cascadeModel.getCascadeType() == 1) {
                        BeanUtil.setProperty(result, cascadeModel.getProperty(), pkRefDetails);
                    } else {
                        BeanUtil.setProperty(result, cascadeModel.getProperty(), pkRefDetails.get(0));
                    }
                }
            }
        }
    }
    return result;
}

Example 64 with SqlToyResult

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

the class DialectFactory method executeStore.

/**
 * @todo 存储过程调用
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param inParamsValue
 * @param outParamsType
 * @param resultType
 * @param dataSource
 * @return
 */
public StoreResult executeStore(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final Object[] inParamsValue, final Integer[] outParamsType, final Class resultType, final DataSource dataSource) {
    try {
        Long startTime = System.currentTimeMillis();
        SqlExecuteStat.start(sqlToyConfig.getId(), "executeStore", sqlToyConfig.isShowSql());
        StoreResult result = (StoreResult) DataSourceUtils.processDataSource(sqlToyContext, dataSource, new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                String dialectSql = sqlToyConfig.getSql(dialect);
                int inCount = (inParamsValue == null) ? 0 : inParamsValue.length;
                int outCount = (outParamsType == null) ? 0 : outParamsType.length;
                // sql中问号数量
                int paramCnt = StringUtil.matchCnt(dialectSql, ARG_PATTERN);
                // 处理参数注入
                if (paramCnt != inCount + outCount) {
                    throw new IllegalArgumentException("存储过程语句中的输入和输出参数跟实际调用传递的数量不等!");
                }
                SqlToyResult sqlToyResult = new SqlToyResult(dialectSql, inParamsValue);
                // 判断是否是{?=call xxStore()} 模式(oracle 不支持此模式)
                boolean isFirstResult = StringUtil.matches(dialectSql, STORE_PATTERN);
                // 将call xxxStore(?,?) 后的条件参数判断是否为null，如果是null则改为call xxxStore(null,?,null)
                // 避免设置类型错误
                SqlConfigParseUtils.replaceNull(sqlToyResult, isFirstResult ? 1 : 0);
                // 针对不同数据库执行存储过程调用
                SqlExecuteStat.showSql("存储过程执行", sqlToyResult.getSql(), sqlToyResult.getParamsValue());
                StoreResult queryResult = getDialectSqlWrapper(dbType).executeStore(sqlToyContext, sqlToyConfig, sqlToyResult.getSql(), sqlToyResult.getParamsValue(), outParamsType, conn, dbType, dialect, -1);
                // 进行数据必要的数据处理(一般存储过程不会结合旋转sql进行数据旋转操作)
                // {此区域代码正常情况下不会使用
                QueryExecutor queryExecutor = new QueryExecutor(null, sqlToyConfig.getParamsName(), inParamsValue);
                List pivotCategorySet = ResultUtils.getPivotCategory(sqlToyContext, sqlToyConfig, queryExecutor, conn, dbType, dialect);
                boolean changedCols = ResultUtils.calculate(sqlToyContext.getDesensitizeProvider(), sqlToyConfig, queryResult, pivotCategorySet, null);
                // 映射成对象
                if (resultType != null) {
                    queryResult.setRows(ResultUtils.wrapQueryResult(sqlToyContext, queryResult.getRows(), queryResult.getLabelNames(), resultType, changedCols, true, false, null, null));
                }
                SqlExecuteStat.debug("执行结果", "存储过程影响记录:{} 条!", queryResult.getRecordCount());
                this.setResult(queryResult);
            }
        });
        result.setExecuteTime(System.currentTimeMillis() - startTime);
        return result;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 65 with SqlToyResult

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

the class SqlServerDialectUtils 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 {
    // sqlserver 不支持内部order by
    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)).append(" (");
    }
    // 存在order 或union 则在sql外包裹一层
    if (hasOrderOrUnion) {
        sql.append("select top " + randomCount);
        sql.append(" sag_random_table.* from (");
        sql.append(innerSql);
    } else {
        sql.append(innerSql.replaceFirst("(?i)select ", "select top " + randomCount + " "));
    }
    if (hasOrderOrUnion) {
        sql.append(") sag_random_table ");
    }
    sql.append(" order by NEWID() ");
    if (sqlToyConfig.isHasFast()) {
        sql.append(") ").append(sqlToyConfig.getFastTailSql(dialect));
    }
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    SqlToyResult queryParam = SqlConfigParseUtils.processSql(sql.toString(), extend.getParamsName(sqlToyConfig), extend.getParamsValue(sqlToyContext, sqlToyConfig), dialect);
    return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}