Examples with QueryResult org.sagacity.sqltoy.model.QueryResult used on opensource projects

Example 26 with QueryResult

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

the class DialectFactory method getRandomResult.

/**
 * @todo 取随机记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param randomCount
 * @param dataSource
 * @return
 */
public QueryResult getRandomResult(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final Double randomCount, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (extend.sql == null) {
        throw new IllegalArgumentException("getRandomResult operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, (randomCount < 1) ? true : false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "getRandomResult", sqlToyConfig.isShowSql());
        QueryResult result = (QueryResult) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 处理sql中的?为统一的:named形式，并进行sharding table替换
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, (randomCount < 1) ? true : false);
                // 判断数据库是否支持取随机记录(只有informix和sybase不支持)
                Long totalCount = null;
                Long randomCnt;
                // 记录数量大于1表示取随机记录数量
                if (randomCount >= 1) {
                    randomCnt = randomCount.longValue();
                } else // 按比例提取
                {
                    // 提取总记录数
                    if (totalCount == null) {
                        totalCount = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    }
                    randomCnt = Double.valueOf(totalCount * randomCount.doubleValue()).longValue();
                    SqlExecuteStat.debug("过程提示", "按比例提取总记录数:{}条,需取随机记录:{}条!", totalCount, randomCnt);
                    // 如果总记录数不为零，randomCnt最小为1
                    if (totalCount >= 1 && randomCnt < 1) {
                        randomCnt = 1L;
                    }
                }
                QueryResult queryResult;
                // 总记录数为零
                if (totalCount != null && totalCount == 0) {
                    queryResult = new QueryResult();
                    queryResult.setRows(new ArrayList());
                    this.setResult(queryResult);
                    logger.warn("getRandom,total Records is zero,please check sql!sqlId={}", sqlToyConfig.getIdOrSql());
                    return;
                }
                queryResult = getDialectSqlWrapper(dbType).getRandomResult(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), totalCount, randomCnt, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                if (queryResult.getRows() != null && !queryResult.getRows().isEmpty()) {
                    // 存在计算和旋转的数据不能映射到对象(数据类型不一致，如汇总平均以及数据旋转)
                    List pivotCategorySet = ResultUtils.getPivotCategory(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    // 对查询结果进行计算处理:字段脱敏、格式化、数据旋转、同步环比、分组汇总等
                    boolean changedCols = ResultUtils.calculate(sqlToyContext.getDesensitizeProvider(), realSqlToyConfig, queryResult, pivotCategorySet, extend);
                    // 将结果映射对象单独出来为了解耦，性能影响其实可以忽略，上万条也是1毫秒级
                    if (extend.resultType != null) {
                        queryResult.setRows(ResultUtils.wrapQueryResult(sqlToyContext, queryResult.getRows(), queryResult.getLabelNames(), (Class) extend.resultType, changedCols, extend.humpMapLabel, extend.hiberarchy, extend.hiberarchyClasses, extend.fieldsMap));
                    }
                }
                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 27 with QueryResult

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

the class DialectFactory method findSkipTotalCountPage.

/**
 * @TODO 跳过查询总记录的分页查询,提供给特殊的场景，尤其是移动端滚屏模式
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param pageNo
 * @param pageSize
 * @param dataSource
 * @return
 */
public QueryResult findSkipTotalCountPage(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final long pageNo, final Integer pageSize, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate sql is null!");
    }
    // 页数必须要大于等于1，pageSize必须要大于1
    if (pageNo < 1 || pageSize < 1) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate  pageSize:" + pageSize + "<1 or pageNo:" + pageNo + " < 1!");
    }
    int limitSize = sqlToyContext.getPageFetchSizeLimit();
    // 分页查询不允许单页数据超过上限，避免大规模数据提取
    if (pageSize >= limitSize) {
        throw new IllegalArgumentException("findSkipTotalCountPage operate args is Illegal,pageSize={" + pageSize + "}>= limit:{" + limitSize + "}!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findSkipTotalCountPage", sqlToyConfig.isShowSql());
        QueryResult result = (QueryResult) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 处理sql中的?为统一的:named形式，并进行sharding table替换
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                QueryResult queryResult = getDialectSqlWrapper(dbType).findPageBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), pageNo, pageSize, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                queryResult.setPageNo(pageNo);
                queryResult.setPageSize(pageSize);
                if (queryResult.getRows() != null && !queryResult.getRows().isEmpty()) {
                    // 存在计算和旋转的数据不能映射到对象(数据类型不一致，如汇总平均以及数据旋转)
                    List pivotCategorySet = ResultUtils.getPivotCategory(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    // 对查询结果进行计算处理:字段脱敏、格式化、数据旋转、同步环比、分组汇总等
                    boolean changedCols = ResultUtils.calculate(sqlToyContext.getDesensitizeProvider(), realSqlToyConfig, queryResult, pivotCategorySet, extend);
                    // 将结果映射对象单独出来为了解耦，性能影响其实可以忽略，上万条也是1毫秒级
                    if (extend.resultType != null) {
                        queryResult.setRows(ResultUtils.wrapQueryResult(sqlToyContext, queryResult.getRows(), queryResult.getLabelNames(), (Class) extend.resultType, changedCols, extend.humpMapLabel, extend.hiberarchy, extend.hiberarchyClasses, extend.fieldsMap));
                    }
                }
                queryResult.setSkipQueryCount(true);
                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 28 with QueryResult

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

the class DialectUtils method updateFetchBySql.

/**
 * @todo 实现普通的sql语句查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param updateRowHandler
 * @param conn
 * @param dbType
 * @param startIndex
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult updateFetchBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final UpdateRowHandler updateRowHandler, final Connection conn, final Integer dbType, final int startIndex, final int fetchSize, final int maxRows) throws Exception {
    // 做sql签名
    String lastSql = SqlUtilsExt.signSql(sql, dbType, sqlToyConfig);
    // 打印sql
    SqlExecuteStat.showSql("执行updateFetch", lastSql, paramsValue);
    PreparedStatement pst = null;
    if (updateRowHandler == null) {
        pst = conn.prepareStatement(lastSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    } else {
        pst = conn.prepareStatement(lastSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    }
    if (fetchSize > 0) {
        pst.setFetchSize(fetchSize);
    }
    if (maxRows > 0) {
        pst.setMaxRows(maxRows);
    }
    ResultSet rs = null;
    return (QueryResult) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

        @Override
        public void execute(Object obj, PreparedStatement pst, ResultSet rs) throws Exception {
            SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, pst, paramsValue, null, 0);
            rs = pst.executeQuery();
            this.setResult(ResultUtils.processResultSet(sqlToyContext, sqlToyConfig, conn, rs, null, updateRowHandler, null, startIndex));
        }
    });
}

Example 29 with QueryResult

use of org.sagacity.sqltoy.model.QueryResult 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 30 with QueryResult

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

the class DialectUtils method executeStore.

/**
 * @todo <b>通用的存储过程调用，inParam需放在outParam前面</b>
 * @param sqlToyConfig
 * @param sqlToyContext
 * @param storeSql
 * @param inParamValues
 * @param outParamTypes
 * @param conn
 * @param dbType
 * @param fetchSize
 * @return
 * @throws Exception
 */
public static StoreResult executeStore(final SqlToyConfig sqlToyConfig, final SqlToyContext sqlToyContext, final String storeSql, final Object[] inParamValues, final Integer[] outParamTypes, final Connection conn, final Integer dbType, final int fetchSize) throws Exception {
    CallableStatement callStat = null;
    ResultSet rs = null;
    return (StoreResult) SqlUtil.callableStatementProcess(null, callStat, rs, new CallableStatementResultHandler() {

        @Override
        public void execute(Object obj, CallableStatement callStat, ResultSet rs) throws Exception {
            callStat = conn.prepareCall(storeSql);
            if (fetchSize > 0) {
                callStat.setFetchSize(fetchSize);
            }
            boolean isFirstResult = StringUtil.matches(storeSql, STORE_PATTERN);
            int addIndex = isFirstResult ? 1 : 0;
            SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, callStat, inParamValues, null, addIndex);
            int inCount = (inParamValues == null) ? 0 : inParamValues.length;
            int outCount = (outParamTypes == null) ? 0 : outParamTypes.length;
            // 注册输出参数
            if (outCount != 0) {
                if (isFirstResult) {
                    callStat.registerOutParameter(1, outParamTypes[0]);
                }
                for (int i = addIndex; i < outCount; i++) {
                    callStat.registerOutParameter(i + inCount + 1, outParamTypes[i]);
                }
            }
            callStat.execute();
            rs = callStat.getResultSet();
            // 执行查询 解决存储过程返回多个结果集问题，取最后一个结果集
            // while (callStat.getMoreResults()) {
            // rs = callStat.getResultSet();
            // }
            StoreResult storeResult = new StoreResult();
            if (rs != null) {
                QueryResult tempResult = ResultUtils.processResultSet(sqlToyContext, sqlToyConfig, conn, rs, null, null, null, 0);
                storeResult.setLabelNames(tempResult.getLabelNames());
                storeResult.setLabelTypes(tempResult.getLabelTypes());
                storeResult.setRows(tempResult.getRows());
            }
            // 有返回参数如:(?=call (? in,? out) )
            if (outCount != 0) {
                Object[] outParams = new Object[outCount];
                if (isFirstResult) {
                    outParams[0] = callStat.getObject(1);
                }
                for (int i = addIndex; i < outCount; i++) {
                    outParams[i] = callStat.getObject(i + inCount + 1);
                }
                storeResult.setOutResult(outParams);
            }
            this.setResult(storeResult);
        }
    });
}