Examples with SqlParamsModel org.sagacity.sqltoy.config.model.SqlParamsModel used on opensource projects

Example 1 with SqlParamsModel

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

the class DialectUtils method getUnifyParamsNamedConfig.

/**
 * @todo 统一将查询的sql参数由?形式变成:named形式(分页和查询随机记录时)
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param queryExecutor
 * @param dialect
 * @param wrapNamed     只在分页场景下需要将?模式传参统一成:name模式，便于跟后面分页startIndex和endIndex参数结合，从而利用sql预编译功能
 * @return
 * @throws Exception
 */
public static SqlToyConfig getUnifyParamsNamedConfig(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor, String dialect, boolean wrapNamed) throws Exception {
    QueryExecutorExtend extend = queryExecutor.getInnerModel();
    // 本身就是:named参数形式或sql中没有任何参数
    boolean isNamed = false;
    // 在QueryExecutorBuilder中已经对wrappedParamNames做了判断赋值
    if (!extend.wrappedParamNames) {
        // sql中是否存在? 形式参数
        boolean hasQuestArg = SqlConfigParseUtils.hasQuestMarkArgs(sqlToyConfig.getSql());
        isNamed = ((extend.paramsName != null && extend.paramsName.length > 0) || !hasQuestArg);
    }
    // 以queryExecutor自定义的分表策略覆盖sql xml中定义的
    List<ShardingStrategyConfig> tableShardings = sqlToyConfig.getTableShardings();
    if (!extend.tableShardings.isEmpty()) {
        tableShardings = extend.tableShardings;
    }
    // sql条件以:named形式、无分表、无扩展缓存翻译则不存在对SqlToyConfig 内容的修改，直接返回
    if ((isNamed || !wrapNamed) && tableShardings.isEmpty() && extend.translates.isEmpty()) {
        return sqlToyConfig;
    }
    // clone sqltoyConfig避免直接修改原始的sql配置对后续执行产生影响
    SqlToyConfig result = sqlToyConfig.clone();
    // 存在扩展的缓存翻译
    if (!extend.translates.isEmpty()) {
        result.getTranslateMap().putAll(extend.translates);
    }
    // ?传参且分页模式,原因是分页存在取count场景，在@fast()情况下无法断定paramValues的值跟?的参数对应关系
    if (!isNamed && wrapNamed) {
        SqlParamsModel sqlParams;
        // 存在fast查询
        if (result.isHasFast()) {
            // @fast 前部分
            String fastPreSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastPreSql(null));
            sqlParams = convertParamsToNamed(fastPreSql, 0);
            fastPreSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastPreSql(fastPreSql);
            int index = sqlParams.getParamCnt();
            // @fas() 中间部分
            String fastSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastSql(null));
            sqlParams = convertParamsToNamed(fastSql, index);
            fastSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastSql(fastSql);
            index = index + sqlParams.getParamCnt();
            // 尾部
            String tailSql = SqlConfigParseUtils.clearDblQuestMark(result.getFastTailSql(null));
            sqlParams = convertParamsToNamed(tailSql, index);
            tailSql = SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql());
            result.setFastTailSql(tailSql);
            // 完整sql
            result.setSql(fastPreSql.concat(" (").concat(fastSql).concat(") ").concat(tailSql));
            // 构造对应?参数个数的:named模式参数名数组
            String[] paramsName = new String[index];
            for (int i = 0; i < index; i++) {
                paramsName[i] = SqlToyConstants.DEFAULT_PARAM_NAME + (i + 1);
            }
            result.setParamsName(paramsName);
        } else {
            sqlParams = convertParamsToNamed(SqlConfigParseUtils.clearDblQuestMark(result.getSql(null)), 0);
            result.setSql(SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql()));
            result.setParamsName(sqlParams.getParamsName());
        }
        // 自定义分页的count sql，一般无需定义
        sqlParams = convertParamsToNamed(SqlConfigParseUtils.clearDblQuestMark(result.getCountSql(null)), 0);
        result.setCountSql(SqlConfigParseUtils.recoverDblQuestMark(sqlParams.getSql()));
        // 清空方言缓存
        result.clearDialectSql();
        SqlConfigParseUtils.processFastWith(result, dialect);
    }
    // sharding table 替换sql中的表名称
    ShardingUtils.replaceShardingSqlToyConfig(sqlToyContext, result, tableShardings, dialect, extend.getTableShardingParamsName(sqlToyConfig), extend.getTableShardingParamsValue(sqlToyConfig));
    return result;
}

Example 2 with SqlParamsModel

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

the class SqlConfigParseUtils method processNamedParamsQuery.

/**
 * @todo 处理named 条件参数，将所有:paramName 替换成? 并重构参数值数组
 * @param queryStr
 * @return
 */
public static SqlParamsModel processNamedParamsQuery(String queryStr) {
    // 提取sql语句中的命名参数
    SqlParamsModel sqlParam = new SqlParamsModel();
    sqlParam.setSql(queryStr);
    Matcher m = SqlToyConstants.SQL_NAMED_PATTERN.matcher(queryStr);
    // 用来替换:paramName
    List<String> paramsName = new ArrayList<String>();
    StringBuilder lastSql = new StringBuilder();
    int start = 0;
    String group;
    while (m.find()) {
        group = m.group();
        // 剔除\\W\\: 两位字符
        paramsName.add(group.substring(2).trim());
        lastSql.append(BLANK).append(queryStr.substring(start, m.start())).append(group.charAt(0)).append(ARG_NAME_BLANK);
        start = m.end();
    }
    // 没有别名参数
    if (start == 0) {
        return sqlParam;
    }
    // 添加尾部sql
    lastSql.append(queryStr.substring(start));
    sqlParam.setSql(lastSql.toString());
    sqlParam.setParamsName(paramsName.toArray(new String[paramsName.size()]));
    return sqlParam;
}

Example 3 with SqlParamsModel

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

the class SqlConfigParseUtils method processSql.

/**
 * @todo 判断条件为null,过滤sql的组合查询条件example: queryStr= select t1.* from xx_table t1
 *       where #[t1.status=?] #[and t1.auditTime=?]
 * @param queryStr
 * @param paramsNamed
 * @param paramsArg
 * @param dialect
 * @return
 */
public static SqlToyResult processSql(String queryStr, String[] paramsNamed, Object[] paramsArg, String dialect) {
    Object[] paramsValue = paramsArg;
    if (paramsNamed != null && paramsNamed.length > 0) {
        // 构造全是null的条件值，将全部条件去除
        if (null == paramsArg || paramsArg.length == 0) {
            paramsValue = new Object[paramsNamed.length];
        }
    } else // 无参数别名也无条件值
    if (null == paramsArg || paramsArg.length == 0) {
        return new SqlToyResult(queryStr, paramsArg);
    }
    SqlToyResult sqlToyResult = new SqlToyResult();
    // 是否:paramName 形式的参数模式
    boolean isNamedArgs = StringUtil.matches(queryStr, SqlToyConstants.SQL_NAMED_PATTERN);
    SqlParamsModel sqlParam;
    // 将sql中的问号临时先替换成特殊字符
    String questionMark = "#sqltoy_qsmark_placeholder#";
    if (isNamedArgs) {
        String sql = queryStr.replaceAll(ARG_REGEX, questionMark);
        // update 2020-09-23 处理sql中的循环(提前处理循环，避免循环中存在其它条件参数)
        sql = processLoop(sql, paramsNamed, paramsValue);
        sqlParam = processNamedParamsQuery(sql);
    } else {
        // 将sql中的??符号替换成特殊字符,?? 符号在json场景下有特殊含义
        String sql = queryStr.replaceAll(ARG_DBL_REGEX, DBL_QUESTMARK);
        sqlParam = processNamedParamsQuery(sql);
    }
    sqlToyResult.setSql(sqlParam.getSql());
    // 参数和参数值进行匹配
    sqlToyResult.setParamsValue(matchNamedParam(sqlParam.getParamsName(), paramsNamed, paramsValue));
    // 剔除查询条件为null的sql语句和对应的参数
    processNullConditions(sqlToyResult);
    // 替换@blank(?)为空白,增强sql组织能力
    processBlank(sqlToyResult);
    // 检查 like 对应参数部分，如果参数中不存在%符合则自动两边增加%
    processLike(sqlToyResult);
    // in 处理策略2012-7-10 进行了修改，提供参数preparedStatement.setObject()机制，并同时兼容
    // 用具体数据替换 in (?)中问号的处理机制
    processIn(sqlToyResult);
    // 参数为null的处理策略(用null直接代替变量)
    replaceNull(sqlToyResult, 0);
    // update 2021-4-29 放在最后，避免参数值中存在?号
    // 替换@value(?) 为参数对应的数值
    processValue(sqlToyResult, dialect);
    // 将特殊字符替换回问号
    if (isNamedArgs) {
        sqlToyResult.setSql(sqlToyResult.getSql().replaceAll(questionMark, ARG_NAME));
    } else {
        // 将代表json中的?? 符号换回
        sqlToyResult.setSql(sqlToyResult.getSql().replaceAll(DBL_QUESTMARK, ARG_DBL_NAME));
    }
    return sqlToyResult;
}

Example 4 with SqlParamsModel

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

the class DialectFactory method batchUpdate.

/**
 * @todo 批量执行sql修改或删除操作
 * @param sqlToyContext
 * @param sqlOrNamedSql
 * @param dataSet
 * @param batchSize
 * @param reflectPropertyHandler
 * @param insertCallhandler
 * @param autoCommit
 * @param dataSource
 * @return
 * @throws Exception
 */
public Long batchUpdate(final SqlToyContext sqlToyContext, final String sqlOrNamedSql, final List dataSet, final int batchSize, final ReflectPropertyHandler reflectPropertyHandler, final InsertRowCallbackHandler insertCallhandler, final Boolean autoCommit, final DataSource dataSource) throws Exception {
    try {
        final SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(sqlOrNamedSql, SqlType.update);
        SqlExecuteStat.start(sqlToyConfig.getId(), "batchUpdate", sqlToyConfig.isShowSql());
        return (Long) DataSourceUtils.processDataSource(sqlToyContext, dataSource, new DataSourceCallbackHandler() {

            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                String realSql = sqlToyContext.convertFunctions(sqlToyConfig.getSql(), dialect);
                Integer[] fieldTypes = null;
                List values = dataSet;
                // sql中存在:named参数模式，通过sql提取参数名称
                if (sqlToyConfig.getParamsName() != null) {
                    // 替换sql中:name为?并提取参数名称归集成数组
                    SqlParamsModel sqlParamsModel = SqlConfigParseUtils.processNamedParamsQuery(realSql);
                    values = BeanUtil.reflectBeansToList(dataSet, sqlParamsModel.getParamsName(), reflectPropertyHandler, false, 0);
                    fieldTypes = BeanUtil.matchMethodsType(dataSet.get(0).getClass(), sqlParamsModel.getParamsName());
                    realSql = sqlParamsModel.getSql();
                }
                SqlExecuteStat.showSql(realSql, null);
                this.setResult(SqlUtil.batchUpdateByJdbc(realSql, values, batchSize, insertCallhandler, fieldTypes, autoCommit, conn));
            }
        });
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw e;
    } finally {
        SqlExecuteStat.destroy();
    }
}

Example 5 with SqlParamsModel

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

the class DialectFactory method batchUpdate.

/**
 * @todo 批量执行sql修改或删除操作
 * @param sqlToyContext
 * @param sqlToyConfig
 * @param dataSet             支持List<List>、List<Object[]>(sql中?传参)
 *                            ;List<VO>、List<Map> 形式(sql中:paramName传参)
 * @param batchSize
 * @param reflectPropsHandler
 * @param insertCallhandler   使用反调方式自己对rs进行处理
 * @param autoCommit
 * @param dataSource
 * @return
 */
public Long batchUpdate(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final List dataSet, final int batchSize, final ReflectPropsHandler reflectPropsHandler, final InsertRowCallbackHandler insertCallhandler, final Boolean autoCommit, final DataSource dataSource) {
    // 首先合法性校验
    if (dataSet == null || dataSet.isEmpty()) {
        logger.warn("batchUpdate dataSet is null or empty,please check!");
        return 0L;
    }
    try {
        // 启动执行日志(会在threadlocal中创建一个当前执行信息,并建立一个唯一跟踪id)
        SqlExecuteStat.start(sqlToyConfig.getId(), "batchUpdate:[" + dataSet.size() + "]条记录!", sqlToyConfig.isShowSql());
        Long updateTotalCnt = (Long) DataSourceUtils.processDataSource(sqlToyContext, dataSource, new DataSourceCallbackHandler() {

            @Override
            public void doConnection(Connection conn, Integer dbType, String dialect) throws Exception {
                String realSql = sqlToyConfig.getSql(dialect);
                Integer[] fieldTypes = null;
                List values = dataSet;
                // sql中存在:named参数模式，通过sql提取参数名称
                if (sqlToyConfig.getParamsName() != null) {
                    // 替换sql中:name为?并提取参数名称归集成数组
                    SqlParamsModel sqlParamsModel = SqlConfigParseUtils.processNamedParamsQuery(realSql);
                    realSql = sqlParamsModel.getSql();
                    // update 2021-10-28 增加统一授权传参、根据insert、update 判断自动补充创建人、创建时间、修改人、修改时间等属性值的填充
                    ReflectPropsHandler realPropsHandler = DialectUtils.wrapReflectWithUnifyFields(realSql, reflectPropsHandler, sqlToyContext.getUnifyFieldsHandler());
                    values = BeanUtil.reflectBeansToList(dataSet, sqlParamsModel.getParamsName(), realPropsHandler);
                    fieldTypes = BeanUtil.matchMethodsType(dataSet.get(0).getClass(), sqlParamsModel.getParamsName());
                }
                // 做sql签名
                realSql = SqlUtilsExt.signSql(realSql, dbType, sqlToyConfig);
                SqlExecuteStat.showSql("批量sql执行", realSql, null);
                this.setResult(SqlUtil.batchUpdateByJdbc(sqlToyContext.getTypeHandler(), realSql, values, batchSize, insertCallhandler, fieldTypes, autoCommit, conn, dbType));
            }
        });
        // 输出执行结果更新记录量日志
        SqlExecuteStat.debug("执行结果", "批量更新记录数量:{} 条!", updateTotalCnt);
        return updateTotalCnt;
    } catch (Exception e) {
        SqlExecuteStat.error(e);
        throw new DataAccessException(e);
    } finally {
        // 输出执行效率和超时、错误日志
        SqlExecuteStat.destroy();
    }
}