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

Example 11 with QueryResult

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

Example 12 with QueryResult

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

the class DialectFactory method updateFetchTop.

@Deprecated
public QueryResult updateFetchTop(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final Integer topSize, final UpdateRowHandler updateRowHandler, final DataSource dataSource) throws Exception {
    final SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(queryExecutor.getSql(), SqlType.search);
    try {
        SqlExecuteStat.start(sqlToyConfig.getId(), "updateFetchTop", sqlToyConfig.isShowSql());
        return (QueryResult) DataSourceUtils.processDataSource(sqlToyContext, ShardingUtils.getShardingDataSource(sqlToyContext, sqlToyConfig, queryExecutor, dataSource), new DataSourceCallbackHandler() {

            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                // 处理sql中的?为统一的:named形式
                SqlToyConfig realSqlToyConfig = DialectUtils.getUnifyParamsNamedConfig(sqlToyContext, sqlToyConfig, queryExecutor, dialect, false);
                SqlToyResult queryParam = SqlConfigParseUtils.processSql(realSqlToyConfig.getSql(), queryExecutor.getParamsName(realSqlToyConfig), queryExecutor.getParamsValue(realSqlToyConfig));
                QueryResult queryResult = getDialectSqlWrapper(dbType).updateFetchTop(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), topSize, updateRowHandler, conn);
                if (queryExecutor.getResultType() != null) {
                    queryResult.setRows(ResultUtils.wrapQueryResult(queryResult.getRows(), ResultUtils.humpFieldNames(queryExecutor, queryResult.getLabelNames()), (Class) queryExecutor.getResultType()));
                }
                this.setResult(queryResult);
            }
        });
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw e;
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 13 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
 * @return
 * @throws Exception
 */
public static Serializable load(final SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, String sql, EntityMeta entityMeta, Serializable entity, List<Class> cascadeTypes, Connection conn) throws Exception {
    Object[] pkValues = BeanUtil.reflectBeanToAry(entity, entityMeta.getIdArray(), null, null);
    for (int i = 0; i < pkValues.length; i++) if (null == pkValues[i])
        throw new Exception("load method must assign value for pk,null pk field is:" + entityMeta.getIdArray()[i]);
    SqlToyResult sqlToyResult = SqlConfigParseUtils.processSql(sql, entityMeta.getIdArray(), pkValues);
    // 显示sql
    SqlExecuteStat.showSql(sqlToyResult.getSql(), sqlToyResult.getParamsValue());
    QueryResult queryResult = findBySql(sqlToyContext, sqlToyConfig, sqlToyResult.getSql(), sqlToyResult.getParamsValue(), null, conn, 0, -1, -1);
    List rows = queryResult.getRows();
    Serializable result = null;
    if (rows != null && rows.size() > 0) {
        rows = BeanUtil.reflectListToBean(rows, ResultUtils.humpFieldNames(queryResult.getLabelNames()), entity.getClass());
        result = (Serializable) rows.get(0);
    }
    if (result == null)
        return null;
    // 存在主表对应子表
    if (null != cascadeTypes && !cascadeTypes.isEmpty() && !entityMeta.getOneToManys().isEmpty()) {
        List pkRefDetails;
        for (OneToManyModel oneToMany : entityMeta.getOneToManys()) {
            // 判定是否要加载
            if (cascadeTypes.contains(oneToMany.getMappedType())) {
                sqlToyResult = SqlConfigParseUtils.processSql(oneToMany.getLoadSubTableSql(), oneToMany.getMappedFields(), pkValues);
                if (sqlToyContext.isDebug())
                    out.println("auto load sub table dataSet sql:".concat(sqlToyResult.getSql()));
                pkRefDetails = SqlUtil.findByJdbcQuery(sqlToyResult.getSql(), sqlToyResult.getParamsValue(), oneToMany.getMappedType(), null, conn);
                if (null != pkRefDetails)
                    BeanUtils.setProperty(result, oneToMany.getProperty(), pkRefDetails);
            }
        }
    }
    return result;
}

Example 14 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 startIndex
 * @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 int startIndex) throws Exception {
    // 打印sql
    SqlExecuteStat.showSql(sql, paramsValue);
    PreparedStatement pst = null;
    if (updateRowHandler == null)
        pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    else
        pst = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = null;
    return (QueryResult) SqlUtil.preparedStatementProcess(null, pst, rs, new PreparedStatementResultHandler() {

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

Example 15 with QueryResult

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

the class ResultUtils method processResultSet.

/**
 * @todo 处理sql查询时的结果集,当没有反调或voClass反射处理时以数组方式返回resultSet的数据
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param conn
 * @param rs
 * @param rowCallbackHandler
 * @param updateRowHandler
 * @param startColIndex
 * @return
 * @throws Exception
 */
public static QueryResult processResultSet(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, Connection conn, ResultSet rs, RowCallbackHandler rowCallbackHandler, UpdateRowHandler updateRowHandler, int startColIndex) throws Exception {
    QueryResult result = new QueryResult();
    // 记录行记数器
    int index = 0;
    if (rowCallbackHandler != null) {
        while (rs.next()) {
            rowCallbackHandler.processRow(rs, index);
            index++;
        }
        result.setRows(rowCallbackHandler.getResult());
    } else {
        // 取得字段列数,在没有rowCallbackHandler時用数组返回
        int rowCnt = rs.getMetaData().getColumnCount();
        String[] labelNames = new String[rowCnt - startColIndex];
        String[] labelTypes = new String[rowCnt - startColIndex];
        HashMap<String, Integer> labelIndexMap = new HashMap<String, Integer>();
        for (int i = startColIndex; i < rowCnt; i++) {
            labelNames[index] = rs.getMetaData().getColumnLabel(i + 1);
            labelIndexMap.put(labelNames[index].toLowerCase(), index);
            labelTypes[index] = rs.getMetaData().getColumnTypeName(i + 1);
            index++;
        }
        result.setLabelNames(labelNames);
        result.setLabelTypes(labelTypes);
        // 返回结果为非VO class时才可以应用旋转和汇总合计功能
        result.setRows(getResultSet(sqlToyConfig, sqlToyContext, conn, rs, updateRowHandler, rowCnt, labelIndexMap, labelNames, startColIndex));
        // 字段脱敏
        if (sqlToyConfig.getSecureMasks() != null && result.getRows() != null) {
            secureMask(result, sqlToyConfig, labelIndexMap);
        }
    }
    // 填充记录数
    if (result.getRows() != null)
        result.setRecordCount(new Long(result.getRows().size()));
    return result;
}