Examples with DataAccessException org.sagacity.sqltoy.exception.DataAccessException used on opensource projects

Example 16 with DataAccessException

use of org.sagacity.sqltoy.exception.DataAccessException in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method updateAll.

/**
 * @todo 批量修改对象
 * @param sqlToyContext
 * @param entities
 * @param batchSize
 * @param uniqueFields        唯一性索引字段
 * @param forceUpdateFields
 * @param reflectPropsHandler
 * @param dataSource
 * @param autoCommit
 * @return
 */
public Long updateAll(final SqlToyContext sqlToyContext, final List<?> entities, final int batchSize, final String[] uniqueFields, final String[] forceUpdateFields, final ReflectPropsHandler reflectPropsHandler, final DataSource dataSource, final Boolean autoCommit) {
    if (entities == null || entities.isEmpty()) {
        logger.warn("updateAll entities is null or empty,please check!");
        return 0L;
    }
    try {
        SqlExecuteStat.start(BeanUtil.getEntityClass(entities.get(0).getClass()).getName(), "updateAll:[" + entities.size() + "]条记录!", null);
        // 分库分表并行执行
        List<Long> result = ParallelUtils.execute(sqlToyContext, entities, false, dataSource, (context, batchModel) -> {
            ShardingModel shardingModel = batchModel.getShardingModel();
            Long updateCnt = (Long) DataSourceUtils.processDataSource(context, shardingModel.getDataSource(), new DataSourceCallbackHandler() {

                @Override
                public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                    this.setResult(getDialectSqlWrapper(dbType).updateAll(context, batchModel.getEntities(), batchSize, uniqueFields, forceUpdateFields, reflectPropsHandler, conn, dbType, dialect, autoCommit, shardingModel.getTableName()));
                }
            });
            List<Long> tmp = new ArrayList();
            tmp.add(updateCnt);
            return tmp;
        });
        long updateTotalCnt = 0;
        if (result != null) {
            for (Long cnt : result) {
                updateTotalCnt = updateTotalCnt + cnt.longValue();
            }
        }
        SqlExecuteStat.debug("执行结果", "批量更新影响记录量:{} 条!", updateTotalCnt);
        return Long.valueOf(updateTotalCnt);
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 17 with DataAccessException

use of org.sagacity.sqltoy.exception.DataAccessException in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method findTop.

/**
 * @todo 取符合条件的前多少条记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param topSize
 * @param dataSource
 * @return
 */
public QueryResult findTop(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final double topSize, final DataSource dataSource) {
    final QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 合法校验
    if (StringUtil.isBlank(extend.sql)) {
        throw new IllegalArgumentException("findTop operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, (topSize < 1) ? true : false);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findTop", 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, (topSize < 1) ? true : false);
                Integer realTopSize;
                // 小于1表示按比例提取
                if (topSize < 1) {
                    Long totalCount = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    realTopSize = Double.valueOf(topSize * totalCount.longValue()).intValue();
                    SqlExecuteStat.debug("过程提示", "按比例提取,总记录数:{}条,按比例top记录要取:{} 条!", totalCount, realTopSize);
                } else {
                    realTopSize = Double.valueOf(topSize).intValue();
                }
                if (realTopSize == 0) {
                    this.setResult(new QueryResult());
                    SqlExecuteStat.debug("查询结果", "实际取得top记录数:0 条!");
                    return;
                }
                // 调用数据库方言查询结果
                QueryResult queryResult = getDialectSqlWrapper(dbType).findTopBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), realTopSize, 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("查询结果", "实际取得top记录数: {}条!", 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 18 with DataAccessException

use of org.sagacity.sqltoy.exception.DataAccessException in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method findPage.

/**
 * @todo 分页查询, pageNo为负一表示取全部记录
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param pageNo
 * @param pageSize
 * @param dataSource
 * @return
 */
public QueryResult findPage(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("findPage operate sql is null!");
    }
    try {
        Long startTime = System.currentTimeMillis();
        // 规整查询参数名称和参数名称对应的值
        QueryExecutorBuilder.initQueryExecutor(sqlToyContext, extend, sqlToyConfig, true);
        SqlExecuteStat.start(sqlToyConfig.getId(), "findPage", 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, true);
                QueryResult queryResult = null;
                PageOptimize pageOptimize = extend.pageOptimize;
                if (pageOptimize == null) {
                    pageOptimize = realSqlToyConfig.getPageOptimize();
                }
                Long recordCnt = null;
                // 通过查询条件构造唯一的key
                String pageQueryKey = PageOptimizeUtils.generateOptimizeKey(sqlToyContext, sqlToyConfig, queryExecutor, pageOptimize);
                // 需要进行分页查询优化
                if (null != pageQueryKey) {
                    // 从缓存中提取总记录数
                    recordCnt = PageOptimizeUtils.getPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey);
                    if (recordCnt != null) {
                        SqlExecuteStat.debug("过程提示", "分页优化条件命中,从缓存中获得总记录数:{}!!", recordCnt);
                    }
                }
                // 并行且缓存中无总记录数量，执行并行处理
                if (pageOptimize != null && pageOptimize.isParallel() && pageNo != -1 && recordCnt == null) {
                    queryResult = parallelPage(sqlToyContext, queryExecutor, realSqlToyConfig, extend, pageNo, pageSize, pageOptimize, conn, dbType, dialect);
                    recordCnt = queryResult.getRecordCount();
                    // 将并行后得到的总记录数登记到缓存
                    if (null != pageQueryKey) {
                        PageOptimizeUtils.registPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey, recordCnt);
                    }
                } else {
                    // 非并行且分页缓存未命中，执行count查询
                    if (recordCnt == null) {
                        recordCnt = getCountBySql(sqlToyContext, realSqlToyConfig, queryExecutor, conn, dbType, dialect);
                    }
                    // 将总记录数登记到缓存
                    if (null != pageQueryKey) {
                        PageOptimizeUtils.registPageTotalCount(realSqlToyConfig, pageOptimize, pageQueryKey, recordCnt);
                    }
                    // pageNo=-1时的提取数据量限制
                    int limitSize = sqlToyContext.getPageFetchSizeLimit();
                    // pageNo=-1时,总记录数超出限制则返回空集合
                    boolean illegal = (pageNo == -1 && (limitSize != -1 && recordCnt > limitSize));
                    if (recordCnt == 0 || illegal) {
                        queryResult = new QueryResult();
                        if (recordCnt == 0 && sqlToyContext.isPageOverToFirst()) {
                            queryResult.setPageNo(1L);
                        } else {
                            queryResult.setPageNo(pageNo);
                        }
                        queryResult.setPageSize(pageSize);
                        queryResult.setRecordCount(0L);
                        if (illegal) {
                            logger.warn("非法分页查询,提取记录总数为:{}>{}上限(可设置sqlToyContext中的pageFetchSizeLimit进行调整),sql={}", recordCnt, limitSize, sqlToyConfig.getIdOrSql());
                        } else {
                            SqlExecuteStat.debug("过程提示", "提取count数为:0,sql={}", sqlToyConfig.getIdOrSql());
                        }
                    } else {
                        // 合法的全记录提取,设置页号为1按记录数
                        if (pageNo == -1) {
                            // 通过参数处理最终的sql和参数值
                            SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(dialect), extend.getParamsName(realSqlToyConfig), extend.getParamsValue(sqlToyContext, realSqlToyConfig), dialect);
                            queryResult = getDialectSqlWrapper(dbType).findBySql(sqlToyContext, realSqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend.rowCallbackHandler, wrapDecryptHandler(sqlToyContext, extend.resultType), conn, null, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                            long totalRecord = (queryResult.getRows() == null) ? 0 : queryResult.getRows().size();
                            queryResult.setPageNo(1L);
                            queryResult.setPageSize(Long.valueOf(totalRecord).intValue());
                            queryResult.setRecordCount(totalRecord);
                        } else {
                            // 实际开始页(页数据超出总记录,则从第一页重新开始,相反如继续按指定的页查询则记录为空,且实际页号也不存在)
                            boolean isOverPage = (pageNo * pageSize >= (recordCnt + pageSize));
                            // 允许页号超出总页数，结果返回空集合
                            if (isOverPage && !sqlToyContext.isPageOverToFirst()) {
                                queryResult = new QueryResult();
                                queryResult.setPageNo(pageNo);
                            } else {
                                long realStartPage = isOverPage ? 1 : pageNo;
                                queryResult = getDialectSqlWrapper(dbType).findPageBySql(sqlToyContext, realSqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), realStartPage, pageSize, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                                queryResult.setPageNo(realStartPage);
                            }
                            queryResult.setPageSize(pageSize);
                            queryResult.setRecordCount(recordCnt);
                        }
                    }
                }
                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) queryResult).getRecordCount(), ((QueryResult) queryResult).getRows().size());
                this.setResult(queryResult);
            }
        });
        result.setExecuteTime(System.currentTimeMillis() - startTime);
        return result;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 19 with DataAccessException

use of org.sagacity.sqltoy.exception.DataAccessException in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method saveOrUpdate.

// mysql、postgresql、sqlite等类似的on duplicate key update
// 存在缺陷,所以改为先update后save;但oracle、mssql、db2等可以用merge实现一次交互完成新增和修改
/**
 * @todo 保存或修改单个对象(数据库单条记录)
 * @param sqlToyContext
 * @param entity
 * @param forceUpdateProps
 * @param dataSource
 * @return
 */
public Long saveOrUpdate(final SqlToyContext sqlToyContext, final Serializable entity, final String[] forceUpdateProps, final DataSource dataSource) {
    if (entity == null) {
        logger.warn("saveOrUpdate entity is null,please check!");
        return 0L;
    }
    // 主键值为空，直接调用save操作
    if (DialectUtils.isEmptyPK(sqlToyContext, entity)) {
        logger.debug("主键字段对应值存在null，因此saveOrUpdate转执行save操作!");
        save(sqlToyContext, entity, dataSource);
        return 1L;
    }
    try {
        final ShardingModel shardingModel = ShardingUtils.getSharding(sqlToyContext, entity, true, dataSource);
        SqlExecuteStat.start(entity.getClass().getName(), "saveOrUpdate", null);
        Long updateTotalCnt = (Long) DataSourceUtils.processDataSource(sqlToyContext, shardingModel.getDataSource(), new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                this.setResult(getDialectSqlWrapper(dbType).saveOrUpdate(sqlToyContext, entity, forceUpdateProps, conn, dbType, dialect, null, shardingModel.getTableName()));
            }
        });
        SqlExecuteStat.debug("执行结果", "实际影响记录数量:{} 条!", updateTotalCnt);
        return updateTotalCnt;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 20 with DataAccessException

use of org.sagacity.sqltoy.exception.DataAccessException in project sagacity-sqltoy by chenrenfei.

the class DialectFactory method parallelPage.

/**
 * @update data:2021-01-25 分页支持并行查询
 * @TODO 并行分页查询，同时执行count和rows记录查询
 * @param sqlToyContext
 * @param queryExecutor
 * @param sqlToyConfig
 * @param extend
 * @param pageNo
 * @param pageSize
 * @param pageOptimize
 * @param conn
 * @param dbType
 * @param dialect
 * @return
 * @throws Exception
 */
private QueryResult parallelPage(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final SqlToyConfig sqlToyConfig, final QueryExecutorExtend extend, final long pageNo, final Integer pageSize, PageOptimize pageOptimize, Connection conn, Integer dbType, String dialect) throws Exception {
    final QueryResult queryResult = new QueryResult();
    queryResult.setPageNo(pageNo);
    queryResult.setPageSize(pageSize);
    ExecutorService pool = null;
    try {
        SqlExecuteStat.debug("过程提示", "分页查询开始并行查询count总记录数和单页记录数据!");
        final SqlExecuteTrace sqlTrace = SqlExecuteStat.get();
        pool = Executors.newFixedThreadPool(2);
        // 查询总记录数量
        pool.submit(new Runnable() {

            @Override
            public void run() {
                try {
                    // 规避新的线程日志无法采集
                    SqlExecuteStat.mergeTrace(sqlTrace);
                    Long startTime = System.currentTimeMillis();
                    queryResult.setRecordCount(getCountBySql(sqlToyContext, sqlToyConfig, queryExecutor, conn, dbType, dialect));
                    SqlExecuteStat.debug("查询count执行耗时", (System.currentTimeMillis() - startTime) + "毫秒!");
                    if (sqlTrace != null && SqlExecuteStat.get() != null) {
                        sqlTrace.addLogs(SqlExecuteStat.get().getExecuteLogs());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    queryResult.setSuccess(false);
                    queryResult.setMessage("查询总记录数异常:" + e.getMessage());
                } finally {
                    SqlExecuteStat.destroyNotLog();
                }
            }
        });
        // 获取记录
        pool.submit(new Runnable() {

            @Override
            public void run() {
                try {
                    SqlExecuteStat.mergeTrace(sqlTrace);
                    Long startTime = System.currentTimeMillis();
                    QueryResult result = getDialectSqlWrapper(dbType).findPageBySql(sqlToyContext, sqlToyConfig, queryExecutor, wrapDecryptHandler(sqlToyContext, extend.resultType), pageNo, pageSize, conn, dbType, dialect, getFetchSize(extend.fetchSize), extend.maxRows);
                    queryResult.setRows(result.getRows());
                    queryResult.setLabelNames(result.getLabelNames());
                    queryResult.setLabelTypes(result.getLabelTypes());
                    SqlExecuteStat.debug("查询分页记录耗时", (System.currentTimeMillis() - startTime) + "毫秒!");
                    if (sqlTrace != null && SqlExecuteStat.get() != null) {
                        sqlTrace.addLogs(SqlExecuteStat.get().getExecuteLogs());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    queryResult.setSuccess(false);
                    queryResult.setMessage("查询单页记录数据异常:" + e.getMessage());
                } finally {
                    SqlExecuteStat.destroyNotLog();
                }
            }
        });
        pool.shutdown();
        // 设置最大等待时长(秒)
        if (pageOptimize.getParallelMaxWaitSeconds() > 0) {
            pool.awaitTermination(pageOptimize.getParallelMaxWaitSeconds(), TimeUnit.SECONDS);
        } else {
            pool.awaitTermination(SqlToyConstants.PARALLEL_MAXWAIT_SECONDS, TimeUnit.SECONDS);
        }
        // 发生异常
        if (!queryResult.isSuccess()) {
            throw new DataAccessException("并行查询执行错误:" + queryResult.getMessage());
        }
        int rowSize = (queryResult.getRows() == null) ? 0 : queryResult.getRows().size();
        // 修正实际结果跟count的差异,比如:pageNo=3,rows=9,count=27,则需要将count调整为29
        long minCount = (queryResult.getPageNo() - 1) * queryResult.getPageSize() + rowSize;
        // 总记录数小于实际查询记录数量(rowSize <= queryResult.getPageSize() 防止单页数据关联扩大了记录量的场景)
        if (queryResult.getRecordCount() < minCount && minCount >= 0 && rowSize <= queryResult.getPageSize()) {
            queryResult.setRecordCount(minCount);
        }
        // 总记录数量大于实际记录数量
        if (rowSize < queryResult.getPageSize() && (queryResult.getRecordCount() > minCount) && minCount >= 0) {
            queryResult.setRecordCount(minCount);
        }
        if (queryResult.getRecordCount() == 0 && sqlToyContext.isPageOverToFirst()) {
            queryResult.setPageNo(1L);
        }
    } catch (Exception e) {
        e.printStackTrace();
        throw new DataAccessException("并行查询执行错误:" + e.getMessage(), e);
    } finally {
        if (pool != null) {
            pool.shutdownNow();
        }
    }
    return queryResult;
}