Got it, this is a super common pain point when working with Oracle and large IN clauses in MyBatis. Oracle's hard limit of 1000 expressions in an IN clause is tricky, but there are a couple of solid solutions you can implement depending on your data size:

1. Split Your List into Chunks (Simple & Quick for Medium Data) #

The easiest approach is to break your large list into smaller chunks (max 1000 elements each) and generate multiple IN clauses joined by OR. Here's how to do it:

Step 1: Split the List in Java

First, add a helper method to split your original list into sublists of 1000 elements:

public static <T> List<List<T>> splitIntoChunks(List<T> list, int chunkSize) {
    List<List<T>> chunks = new ArrayList<>();
    for (int i = 0; i < list.size(); i += chunkSize) {
        int end = Math.min(i + chunkSize, list.size());
        chunks.add(list.subList(i, end));
    }
    return chunks;
}

Step 2: Update Your Mapper Interface

Modify your mapper to accept the chunked list instead of the original:

void updateAddresses(
    @Param("status") Integer status,
    @Param("idChunks") List<List<Long>> idChunks
);

Step 3: Write the MyBatis XML Mapping

Use nested foreach tags to generate the split IN clauses:

<update id="updateAddresses">
  UPDATE address_table
  SET status = #{status}
  WHERE
  <foreach collection="idChunks" item="chunk" separator=" OR ">
    id IN
    <foreach collection="chunk" item="id" open="(" close=")" separator=",">
      #{id}
    </foreach>
  </foreach>
</update>

This will generate SQL like WHERE id IN (1,2,...1000) OR id IN (1001,...2000) which stays within Oracle's limit.

2. Use a Temporary Table (Better for Large Datasets) #

If you're dealing with tens of thousands of records, splitting into OR clauses can hurt performance. A temporary table is more efficient here:

Step 1: Create a Session-Level Temporary Table

First, create a temporary table (you only need to do this once per session, or define it upfront in your schema):

<update id="createTempAddressIdsTable">
  CREATE GLOBAL TEMPORARY TABLE temp_address_ids (
    id NUMBER(19) PRIMARY KEY
  ) ON COMMIT DELETE ROWS;
</update>

The ON COMMIT DELETE ROWS ensures the table is cleared when your transaction commits.

Step 2: Batch Insert IDs into the Temporary Table

<insert id="batchInsertTempIds">
  INSERT INTO temp_address_ids (id)
  VALUES
  <foreach collection="idList" item="id" separator=",">
    (#{id})
  </foreach>
</insert>

Step 3: Update Using the Temporary Table

Join your target table with the temporary table to perform the update:

<update id="updateAddressesWithTempTable">
  UPDATE address_table a
  SET a.status = #{status}
  WHERE EXISTS (
    SELECT 1 FROM temp_address_ids t WHERE t.id = a.id
  )
</update>

Step 4: Execute in a Transaction

In your service layer, run these three steps in a single transaction to keep the temporary table data available for the update.

3. Use MyBatis-Plus (If You're Already Using It) #

If you're using MyBatis-Plus, it handles this edge case automatically! Just use the standard in method in your wrapper:

LambdaUpdateWrapper<Address> updateWrapper = Wrappers.lambdaUpdate();
updateWrapper.in(Address::getId, largeIdList)
             .set(Address::getStatus, status);
addressMapper.update(null, updateWrapper);

MP will split the list into chunks of 1000 and generate the OR-joined IN clauses for you behind the scenes.

内容的提问来源于stack exchange，提问作者user9193174