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

Example 21 with QueryResult

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

the class DialectFactory method updateFetch.

/**
 * @todo 查询锁定记录,并进行修改
 * @param sqlToyContext
 * @param queryExecutor
 * @param updateRowHandler
 * @param dataSource
 * @return
 * @throws Exception
 */
public QueryResult updateFetch(final SqlToyContext sqlToyContext, final QueryExecutor queryExecutor, final UpdateRowHandler updateRowHandler, final DataSource dataSource) throws Exception {
    final SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(queryExecutor.getSql(), SqlType.search);
    try {
        SqlExecuteStat.start(sqlToyConfig.getId(), "updateFetch", 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).updateFetch(sqlToyContext, realSqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), 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 22 with QueryResult

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

the class OracleDialectUtils method executeStore.

/**
 * @todo <b>oracle 存储过程调用，inParam需放在outParam前面(oracle存储过程返回结果必须用out
 *       参数返回，返回结果集则out 参数类型必须是OracleTypes.CURSOR,相对其他数据库比较特殊 )</b>
 * @param sqlToyConfig
 * @param sqlToyContext
 * @param storeSql
 * @param inParamValues
 * @param outParamTypes
 * @param conn
 * @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) throws Exception {
    CallableStatement callStat = null;
    ResultSet rs = null;
    return (StoreResult) SqlUtil.callableStatementProcess(null, callStat, rs, new CallableStatementResultHandler() {

        public void execute(Object obj, CallableStatement callStat, ResultSet rs) throws Exception {
            callStat = conn.prepareCall(storeSql);
            SqlUtil.setParamsValue(conn, callStat, inParamValues, null, 0);
            int cursorIndex = -1;
            int cursorCnt = 0;
            int inCount = (inParamValues == null) ? 0 : inParamValues.length;
            int outCount = (outParamTypes == null) ? 0 : outParamTypes.length;
            // 注册输出参数
            if (outCount != 0) {
                for (int i = 0; i < outCount; i++) {
                    callStat.registerOutParameter(i + inCount + 1, outParamTypes[i]);
                    if (OracleTypes.CURSOR == outParamTypes[i].intValue()) {
                        cursorCnt++;
                        cursorIndex = i;
                    }
                }
            }
            callStat.execute();
            StoreResult storeResult = new StoreResult();
            // 只返回最后一个CURSOR 类型的数据集
            if (cursorIndex != -1) {
                rs = (ResultSet) callStat.getObject(inCount + cursorIndex + 1);
                QueryResult tempResult = ResultUtils.processResultSet(sqlToyContext, sqlToyConfig, conn, rs, null, null, 0);
                storeResult.setLabelNames(tempResult.getLabelNames());
                storeResult.setLabelTypes(tempResult.getLabelTypes());
                storeResult.setRows(tempResult.getRows());
            }
            // 有返回参数(CURSOR 的类型不包含在内)
            if (outCount != 0) {
                Object[] outParams = new Object[outCount - cursorCnt];
                int index = 0;
                for (int i = 0; i < outCount; i++) {
                    if (OracleTypes.CURSOR != outParamTypes[i].intValue()) {
                        // 存储过程自动分页第一个返回参数是总记录数
                        outParams[index] = callStat.getObject(i + inCount + 1);
                        index++;
                    }
                }
                storeResult.setOutResult(outParams);
            }
            this.setResult(storeResult);
        }
    });
}

Example 23 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
 * @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) throws Exception {
    CallableStatement callStat = null;
    ResultSet rs = null;
    return (StoreResult) SqlUtil.callableStatementProcess(null, callStat, rs, new CallableStatementResultHandler() {

        public void execute(Object obj, CallableStatement callStat, ResultSet rs) throws Exception {
            callStat = conn.prepareCall(storeSql);
            boolean isFirstResult = StringUtil.matches(storeSql, STORE_PATTERN);
            int addIndex = isFirstResult ? 1 : 0;
            SqlUtil.setParamsValue(conn, 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, 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);
        }
    });
}

Example 24 with QueryResult

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

the class DialectUtils method findBySql.

/**
 * @todo 实现普通的sql语句查询
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param sql
 * @param paramsValue
 * @param rowCallbackHandler
 * @param conn
 * @param startIndex
 * @param fetchSize
 * @param maxRows
 * @return
 * @throws Exception
 */
public static QueryResult findBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql, final Object[] paramsValue, final RowCallbackHandler rowCallbackHandler, final Connection conn, final int startIndex, final int fetchSize, final int maxRows) throws Exception {
    // 打印sql
    SqlExecuteStat.showSql(sql, paramsValue);
    PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    if (fetchSize > 0)
        pst.setFetchSize(fetchSize);
    if (maxRows > 0)
        pst.setMaxRows(maxRows);
    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, rowCallbackHandler, null, startIndex));
        }
    });
}

Example 25 with QueryResult

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