问题描述
- 日常开发中，我们难免后用到下面这样的逻辑。尤其是在做数据查询的时候，后端根据前端多选控件 传递的 的条件进行查询的时候，这样的查询方式几乎没有替代方案
select * from tbl_name where id in (?, ?, ?)
-
之前做项目的时候使用了 oracle 数据库，就出现了多选控件数据太多，选择的内容超过 1000，传递给后端查询语句的时候，超出 oracle in 语句的上限，导致 sql 异常错误，同时用户不同意前端控件控件选择 1000 的上限
-
其实这也很好解决，基本有以下几种解决方式：
1，将 in 语句的内容拆分，修改为 （in or in） 的结构
2，创建临时表，把临时表作为 in 的子查询，查询完成后在删除临时表
ps：不管采用哪种方式，对于性能都影响很大，本身 in 语句内容太多就不应该，有条件还是建议采用搜索引擎解决，本文只是提供一个简略解决方式
-
当这个问题报出来之后，公司居然要求评估所有采用 in 语句的风险，要求排除风险，这就麻烦了，因为所有条件查询基本都离不开上面的语句，不管是 1，2 那种方式，都需要挨个接口去改，简直想死
-
其次，如果在业务代码里修改，会让业务代码逻辑变得复杂，同时代码也不再优雅，作为强迫症不能容忍
解决方案
- 现在大部分项目应该都是使用的 mybatis 框架查询数据库，那我们就可以考虑通过拦截器的方式，对 in 语句实行自动转化 为 (in or in) 语句的结构，这样业务代码就不用修改了，以后就可以放心大胆的使用 in 语句了
- 相关代码如下:
- 以下代码的重点 是 IN 这个正则表达式 能准确的匹配到 想要的那一段内容，笔者目前测试下来基本都能匹配上，如果有其它情况可以通过修改该正则，完成自己的需求
package com.xctech.xhe.provider.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Intercepts({
@Signature( // 正常查询
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
),
@Signature( // 从缓存中查询
type = Executor.class,
method = "query",
args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class, CacheKey.class, BoundSql.class
}
)
})
@Component
@Slf4j
public class MbInInterceptor implements Interceptor {
private final Pattern EXEGESIS = Pattern.compile("-- .*\n");
private final Pattern PATTERN = Pattern.compile("\\s+|\t|\r|\n");
private final Pattern IN = Pattern.compile("[A-z0-9._]+ [IN|in]+ \\([, ?]+\\)");
/**
* 返回插件
*/
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* 设置参数
*/
@Override
public void setProperties(Properties properties) {}
/**
* 拦截执行器，解决 oracle 数据库 sql 语句 in 10000 上限问题
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
if (SqlCommandType.SELECT.equals(ms.getSqlCommandType())) {
Object parameterObject = args[1];
BoundSql boundSql = args.length == 6 ? (BoundSql) args[5] : ms.getBoundSql(parameterObject);
MappedStatement statement = (MappedStatement) args[0];
MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
Field field = boundSql.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, buildInSql(boundSql.getSql()));
args[0] = newStatement;
}
return invocation.proceed();
}
/**
* 初始化新的 MappedStatement，替换原本 args 中的第一个元素
*/
private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(
ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
/**
* 替换超过 1000 的 IN 语句为 ( IN OR IN ） 结构
* @param sql 原 sql 语句
*/
private String buildInSql(String sql) {
sql = EXEGESIS.matcher(sql).replaceAll(""); // 删掉注释
sql = PATTERN.matcher(sql).replaceAll(" "); // 格式化为单行
Matcher matcher = IN.matcher(sql);
while (matcher.find()) {
String inSql = matcher.group();
String[] strs = inSql.split("\\(");
String str0 = strs[0];
String str1 = strs[1].replaceAll("\\)", "").replaceAll(" ", "");
int count = str1.split(",").length;
if (count <= 1000) continue;
List<String> inStr = new ArrayList<>();
int start = 0, end = 0, offset = 1000;
while (end < count) {
end = start + offset;
end = Math.min(end, count);
String str = str1.substring(2 * start, 2 * end - 1);
inStr.add(str0 + "(" + str + ")");
start = end;
}
String newSql = String.join(" OR ", inStr);
newSql = "(" + newSql + ")";
sql = sql.replace(inSql, newSql);
}
return sql;
}
/**
* 声明自己的 SqlSource
*/
private static class BoundSqlSqlSource implements SqlSource {
private final BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
测试结果
ps：这里为了方便，临时把上限 调成了 10 个
-
原语句
SELECT id,broker_id,indicator_id,indicator_name,industry,source,fir_dir,sec_dir,thr_dir,other_dir,unit,frequency,description,dict_create_time,dict_update_time,deleted,uid_modify,gmt_modify FROM t_indicator_dict WHERE id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) AND deleted=0 AND t.Indicator_Id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) -
替换后的语句
SELECT id,broker_id,indicator_id,indicator_name,industry,source,fir_dir,sec_dir,thr_dir,other_dir,unit,frequency,description,dict_create_time,dict_update_time,deleted,uid_modify,gmt_modify FROM t_indicator_dict WHERE (id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?,?,?,?,?,?,?,?,?) OR id IN (?,?)) AND deleted=0 AND (t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?,?) OR t.Indicator_Id in (?,?,?,?,?,?,?,?,?))
1058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
