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

Example 1 with StoreResult

use of org.sagacity.sqltoy.model.StoreResult 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
 * @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);
            }
            SqlUtil.setParamsValue(sqlToyContext.getTypeHandler(), conn, dbType, 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, 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 2 with StoreResult

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

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

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

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