背景说明

多数数据库如 Oracle、MySQL 等在执行 SQL 语句时都会有 1000 个参数的限制，超过 1000 个参数将无法执行，开发过程中经常会使用 where id in (:ids) 这样的语句来批量查询数据，如果 ids 集合超过 1000 个程序将会报错，本文提供了此问题的解决方案。

解决方案

此问题的解决方式有多种，如分批查询、union、临时表等，这里介绍使用临时表和分批查询两种方式。

1、分批查询

将 ids 按 1000 个进行分隔成多组，然后每组分别查询后将结果集合并成一个 List。

//将 ids 按 1000 个拆分后查询再合并结果集
ArrayList<IEntityData> list = new ArrayList<>();
List<List<String>> idsGroup = ListUtil.split(ids, 1000); //使用 hutool 工具类进行拆分
for (List<String> item : idsGroup) {
    list.addAll(getIEntityData(item));
}

/**
 * 根据 ids 查询数据
 * @param ids ID集合
 * @return IEntityData
 */
private ArrayList<IEntityData> getIEntityData(List<String> ids){
    //查询逻辑
}

2、临时表

先将要操作的 ids 插入临时表中，插入时指定一个统一的 TempID 用于后续获取 ids，然后关联这个临时表进行查询。
假设已经建好了临时表 SpmTempID，使用方法如下。

1）插入 ids

项目组提供了用于插入 ids 到临时表的工具类方法，大家可以直接调用。

String tempId = TempIdUtil.insertTempIds(ids);

方法内部实现示例（仅供参考）：

//region ========== 将要处理的 ids 插入临时表 ==========

//要处理的数据ids
List<String> ids = resultIds.stream().map(x -> x.get("ID").toString()).collect(Collectors.toList());
String insertTempTableSql = "insert into SPMTempID(ID, TempID, DataID, CreatedOn)\n" +
    "values (NewID(), :tempID, :id, :currentDateTime)";
//标识本次插入临时表数据的TempID
String tempID = UUID.randomUUID().toString();
//系统当前时间
OffsetDateTime currentDateTime = CAFContext.current.getCurrentDateTime();
//定义SQL参数
SqlParameterSource[] insertTempTableParam = SqlParameterSourceUtils.createBatch(ids.stream().map(x -> {
    Map<String, Object> param = new HashMap<>();
    param.put("tempID", tempID);
    param.put("id", x);
    param.put("currentDateTime", currentDateTime);
    return param;
}).toArray());

//通过批量方式执行SQL
NamedParameterJdbcTemplate namedJdbcTemplate = SpringBeanUtils.getBean(NamedParameterJdbcTemplate.class);
namedJdbcTemplate.batchUpdate(insertTempTableSql, insertTempTableParam);

//endregion

2）使用临时表

a. 使用临时表（inner join）

使用时直接 inner join 关联，需要注意：

• 临时表位置：放在要查询主要业务单据的后面，如下示例是查询采购订单，采购订单有主表、明细表，那么临时表放在明细表后面。
• 临时表别名：temp
• 临时表关联条件：on temp.tempid=:tempid and temp.dataid=a.id

select 
    a.ID,
  a.code,
  a.name,
  b.xx,
  c.xx
from ScmPO a
    inner join ScmPOItem b on b.parentid=a.id
  inner join SpmTempID temp on temp.tempid=:tempid and temp.dataid=a.id
  left join bfmaterials c on c.id=b.materialid
  left join xxx d on d.id=b.xxid
where 1=1
    and a.status='1' and a.istop='0'
order by a.billcode desc

b. 使用临时表（in）

有时执行 update 语句不能使用 inner join 的方式，此时可以使用 in 的方式，在执行效率上 in 不如 inner join 快。

update ScmPo set status='1' where id in (select dataid from SpmTempID where tempid=:tempid)

c. 使用临时表（merge into）

也可使用 merge into 语法关联临时表来更新其它表。

merge into ScmPO t
using (select dataid from SpmTempID where tempid=:tempid) s
    on (s.dataid=t.id)
when matched then
    update set t.status='1'

3）清理 ids

可能使用定时任务每天晚上清理 CreatedOn < 当天日期-1天 的数据，这里不再赘述。