根据数据构造sql临时表、根据条件数据构建exists语法内容, 解决PreparedStatement can have at most 65,535 parameters
PreparedStatement can have at most 65,535 parameterspg/cmdb 语法mysql/pg/cmdb 语法Oracle 语法
·
前言
原因：
受限jdbc规范、数据库底层实现，参数数量最多为 65,535 个
解决方案：
分批处理：
将大量数据分成多个较小的批次进行处理——部分场景无法分批，例如语句需做分页
使用临时表：
将数据先插入到一个临时表中，然后通过 SQL 查询或存储过程处理这些数据。—— 考虑过会话级临时表，验证时发现数据插入很慢，影响整体查询效率
直接生成 SQL：
将需要使用的参数，直接拼接到sql语句中，即下方内容。
*
【fix】 PreparedStatement can have at most 65,535 parameters 的问题
*
语句由 t1.assets_id in (:assetsIdList) 变为 EXISTS (SELECT 1 FROM (VALUES (1), (2), (3), …, (10000)) AS ids(id) WHERE t.id = ids.id)
实现
sql展示
数据构造临时表sql语法
-- 将传入的数据，例如 'A350550343'、'A350550288'、'A3505502881‘ 转为sql中直接可用的临时表数据
---------- Oracle 语法 ----------
select assets_id from (
SELECT 'A350550343' as assets_id from dual
UNION
SELECT 'A350550288' as assets_id from dual
UNION
SELECT 'A3505502881' as assets_id from dual
) temp
---------- mysql/pg/cmdb 语法 ----------
select assets_id
from (
-- 需去重时 union,不去重时 UNION ALL
SELECT 'A350550343' AS assets_id
UNION
SELECT 'A350550288' AS assets_id
UNION
SELECT 'E350000858' AS assets_id
) temp
---------- pg/cmdb 语法 ----------
select assets_id
from (values ('A350550343'),('A350550288'),('A3505502881')) temp
java
DAO层使用
使用SqlBuildUtil.sqlBuildExistsSyntax，将参数转为临时表关联查询
if(CollectionUtils.isNotEmpty(assetsReqBean.getAssetsIdList())){
// oracle in 1000 超1000会报错，由于asssetsId in 在最后的查询语句且带有分页，没法使用拆分多次查询,先使用 or 支撑，
// 后续若有性能问题，考虑改为会话级临时表
if( SqlBuildUtil.isOracleOutInNum(dbType,assetsReqBean.getAssetsIdList())) {
SqlBuildUtil.splitIdConcatSql(assetsReqBean.getAssetsIdList(), splitAssetsIdList, sql ,"t1","assets_id","assetsIdList");
} else if( SqlBuildUtil.isOutInNumLimit(dbType,assetsReqBean.getAssetsIdList()) ) {
// PreparedStatement can have at most 65,535 parameters
outJdbcLimit = Boolean.TRUE;
sqlForOutJdbcLimit.append("(exists 语句过滤id数："+assetsReqBean.getAssetsIdList().size() +")");
SqlBuildUtil.sqlBuildExistsSyntax(dbType,"and",assetsReqBean.getAssetsIdList(),"temp",sql,"t1","assets_id");
} else {
sql.append(" and t1.assets_id in (:assetsIdList) ");
}
} else {
sql.append(" and 1 <> 1 ");
}
常量类
public class AssetsConst {
public static final int MAX_IN_NUM = 800;//oracle in包含的最大条件数
public static final int MAX_IN_NUM_JDBC = 65535;//jdbc in包含的最大条件数
public static final int FAST_IN_NUM_CMDB = 800;//磐维使用in 比exists 快的临界值
}
SqlBuildUtil
/**
* sql语句构建工具类
* @author yangqingwang
* @create 2024/12/10 10:19
*/
public class SqlBuildUtil {
private static final Logger log = LoggerFactory.getLogger(SqlBuildUtil.class);
private SqlBuildUtil(){}
/**
* 是否为Oracle 超出 in的最大数量限制
* @param dbType
* @param originIdList
* @return
*/
public static boolean isOracleOutInNum(DbTypes dbType,List<String> originIdList){
return Objects.nonNull(dbType) && CollectionUtils.isNotEmpty(originIdList)
&& dbType.equals(DbTypes.ORACLE) && (originIdList.size() > AssetsConst.MAX_IN_NUM);
}
/**
* 是否超出 in的最大数量限制
* @param dbType
* @param originIdList
* @return
*/
public static boolean isOutInNumLimit(DbTypes dbType,List<String> originIdList){
if (Objects.isNull(dbType) || CollectionUtils.isEmpty(originIdList)) {
return false;
} else if (dbType.equals(DbTypes.CHINAMOBILEDB)) {
return originIdList.size() > AssetsConst.FAST_IN_NUM_CMDB;
} else if (dbType.equals(DbTypes.MYSQL)) {
// 未验证mysql，先使用最大值
return originIdList.size() > AssetsConst.MAX_IN_NUM_JDBC;
} else {
return isOracleOutInNum(dbType,originIdList);
}
}
/**
* 是否超出 in的最大数量限制
* @param dbType
* @param originIdList
* @param limitNum 指定限制数量
* @return
*/
public static boolean isOutInNumLimit(DbTypes dbType,List<String> originIdList,int limitNum){
if (Objects.isNull(dbType) || CollectionUtils.isEmpty(originIdList)) {
return false;
} else if (dbType.equals(DbTypes.CHINAMOBILEDB) || dbType.equals(DbTypes.MYSQL)) {
return originIdList.size() > limitNum;
} else {
return isOracleOutInNum(dbType,originIdList);
}
}
/**
* 拆分id，拼接sql
* 对 Oracle in 超一千的问题处理
* <p>语句由 t1.assets_id in (:assetsIdList0) 变为 t1.assets_id in (:assetsIdList0) or t1.assets_id in (:assetsIdList1) or t1.assets_id in (:assetsIdList2)</p>
* @param originIdList 原始id列表
* @param splitIdList 存放拆分后的id列表
* @param sql 拼接的sql
* @param tableName 条件表
* @param fieldName 条件字段
* @param listPrefix 参数列表前缀
*/
public static void splitIdConcatSql(List<String> originIdList, List<List<String>> splitIdList, StringBuilder sql,
String tableName, String fieldName, String listPrefix) {
String realAssetsIdSQL = "";
int size = originIdList.size();
int offSet = 0;
List<String> assetsIdList = new ArrayList<>();
int splitListIndex = 0;
while (offSet < size){
List<String> idList = originIdList.subList(offSet, offSet + AssetsConst.MAX_IN_NUM > size ? size : offSet + AssetsConst.MAX_IN_NUM);
splitIdList.add(idList);
assetsIdList.add(tableName + "." + fieldName + " in (:" + listPrefix +splitListIndex + ")");
offSet += AssetsConst.MAX_IN_NUM;
splitListIndex++;
}
realAssetsIdSQL = " and (" + StringUtils.join(assetsIdList," or ") + ") ";
sql.append(realAssetsIdSQL);
}
/**
* 根据条件数据构建exists语法内容
* <p>用于【fix】 PreparedStatement can have at most 65,535 parameters 的问题 </p>
* <p>语句由 t1.assets_id in (:assetsIdList) 变为 EXISTS (SELECT 1 FROM (VALUES (1), (2), (3), ..., (10000)) AS ids(id) WHERE t.id = ids.id)</p>
* @param dbType 当前配置库类型，决定具体语法选择
* @param whereOpt 条件操作符 or | and
* @param originIdList 原始id列表
* @param tempTableName 临时表名
* @param sql 拼接的sql
* @param tableName 条件表
* @param fieldName 条件字段
*/
public static void sqlBuildExistsSyntax(DbTypes dbType, String whereOpt, List<String> originIdList, String tempTableName,
StringBuilder sql, String tableName, String fieldName) {
// PreparedStatement can have at most 65,535 parameters
StopWatch stopWatch = new StopWatch("sqlBuildExistsSyntax-");
AssetsUtil.startAndStopPrevious(stopWatch, "sqlBuildTempTableByData");
if (Objects.nonNull(dbType)) {
sql.append(" " + whereOpt);
sql.append(" exists (");
SqlBuildUtil.sqlBuildTempTableByData(dbType,originIdList,sql,tempTableName,fieldName);
sql.append(" where "+tempTableName+"."+fieldName+" = "+ tableName+"."+fieldName);
sql.append(" )");
} else {
throw new PubMetaInfoException("sqlBuildExistsSyntax，数据库类型不能为空", PubMetaInfoExceptionCode.BUS_HANDLE_ERROR_DESC);
}
log.info("\n" + AssetsUtil.stopWatchPrettyPrint(stopWatch));
}
/**
* 根据数据构造sql临时表
*
* <p> 将传入的数据，例如 'A350550343'、'A350550288'、'A3505502881‘ 转为sql中直接可用的临时表数据 </p>
* <p> ---------- Oracle 语法 ---------- </p>
* <p> select assets_id from ( </p>
* <p> SELECT 'A350550343' as assets_id from dual </p>
* <p> UNION </p>
* <p> SELECT 'A350550288' as assets_id from dual </p>
* <p> UNION </p>
* <p> SELECT 'A3505502881' as assets_id from dual </p>
* <p> ) temp </p>
* <p> ---------- mysql/pg/cmdb 语法 ---------- </p>
* <p> select assets_id </p>
* <p> from ( </p>
* <p> -- 需去重时 union,不去重时 UNION ALL </p>
* <p> SELECT 'A350550343' AS assets_id </p>
* <p> UNION </p>
* <p> SELECT 'A350550288' AS assets_id </p>
* <p> UNION </p>
* <p> SELECT 'E350000858' AS assets_id </p>
* <p> ) temp </p>
* <p> ---------- pg/cmdb 语法 ---------- </p>
* <p> select assets_id </p>
* <p> from (values ('A350550343'),('A350550288'),('A3505502881')) temp </p>
* @param dbType 当前配置库类型，决定具体语法选择
* @param originIdList 原始id列表
* @param sql 拼接的sql
* @param tempTableName 临时表名
* @param fieldName 条件表字段
*/
public static void sqlBuildTempTableByData(DbTypes dbType,List<String> originIdList, StringBuilder sql,
String tempTableName,String fieldName) {
if ( dbType.equals(DbTypes.POSTGRESQL) || dbType.equals(DbTypes.CHINAMOBILEDB) ) {
// 使用 pg/cmdb 语法
sql.append(" select " + fieldName+ " from (values ");
sql.append(originIdList.stream()
.map(id -> "('" + id + "')")
.collect(Collectors.joining(",")));
sql.append(") "+tempTableName+"("+fieldName+")");
} else if (dbType.equals(DbTypes.MYSQL) ) {
// 使用 mysql/pg/cmdb 语法
sql.append(" select " + fieldName);
sql.append(" from (");
sql.append(originIdList.stream()
.map(id -> " SELECT '" + id + "' AS " + fieldName)
.collect(Collectors.joining(" UNION ")));
sql.append(" ) " + tempTableName);
} else if (dbType.equals(DbTypes.ORACLE) ) {
// 使用 Oracle 语法
sql.append(" select " + fieldName);
sql.append(" from (");
sql.append(originIdList.stream()
.map(id -> " SELECT '" + id + "' AS " + fieldName + " from dual ")
.collect(Collectors.joining(" UNION ")));
sql.append(" ) " + tempTableName);
} else {
throw new PubMetaInfoException("sqlBuildTempTableByData不支持的数据库类型：" + dbType,PubMetaInfoExceptionCode.BUS_HANDLE_ERROR_DESC);
}
}
}
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
8
0- 0
已为社区贡献1条内容
所有评论(0)