MyBatis更新邮编时传递addressId集合报错，求正确语法

Hey there! Let's work through how to correctly pass an addressId collection in MyBatis to update zip codes, and troubleshoot that MyBatisSystemException you're facing.

Step 1: Define the Mapper Interface with Proper Parameter Annotation #

First, make sure your mapper interface method uses @Param to explicitly name the collection parameter—this helps MyBatis identify it correctly, especially when multiple parameters are involved.

public interface AddressMapper {
    // Use @Param to give the collection a clear, identifiable name
    void updateZipCodeByAddressIds(@Param("addressIds") List<Long> addressIds, @Param("newZipCode") String newZipCode);
}

Step 2: Write the Update Statement with <foreach> in XML #

In your MyBatis XML mapper file, use the <foreach> tag to iterate over the addressIds collection and build a valid IN clause. This is the critical piece that's often misconfigured.

<update id="updateZipCodeByAddressIds">
    UPDATE address
    SET zip_code = #{newZipCode}
    WHERE address_id IN
    <!-- Iterate over the addressIds collection to build the IN clause -->
    <foreach collection="addressIds" 
             item="id" 
             open="(" 
             separator="," 
             close=")">
        #{id}
    </foreach>
</update>

Key <foreach> Attributes Explained: #

• collection: Must exactly match the name specified in @Param (here it's addressIds). If you were using an array instead of a List, you'd use array as the collection name, but @Param makes this explicit and avoids confusion.
• item: A short alias for each element in the collection (we use id here for simplicity).
• open/close: Wraps the collection elements in parentheses to form a valid SQL IN clause.
• separator: Adds commas between elements to separate them in the IN list.

Step 3: Fix Common Causes of MyBatisSystemException #

Here are the most likely issues triggering your exception:

• Missing @Param annotation: Without this, MyBatis can't map the collection parameter correctly, especially when multiple parameters are present.
• Incorrect collection attribute in <foreach>: Double-check that it matches the @Param name. Generic names like list work for single List parameters, but they're error-prone if you add more parameters later.
• Empty collection: If addressIds is empty, the IN clause becomes IN (), which is invalid SQL. Add a guard clause to handle this edge case:

  <update id="updateZipCodeByAddressIds">
      <if test="addressIds != null and addressIds.size() > 0">
          UPDATE address
          SET zip_code = #{newZipCode}
          WHERE address_id IN
          <foreach collection="addressIds" item="id" open="(" separator="," close=")">
              #{id}
          </foreach>
      </if>
  </update>
  

• Type mismatch: Ensure the data type of elements in addressIds matches the address_id column type in your database (e.g., if the column is Integer, don't pass a List<String>).

Alternative: Annotation-Based SQL #

If you prefer using annotations instead of XML, you can write the update statement directly in the mapper interface using <script> tags to enable MyBatis dynamic SQL:

@Update("<script>" +
        "UPDATE address SET zip_code = #{newZipCode} WHERE address_id IN " +
        "<foreach collection='addressIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +
        "</script>")
void updateZipCodeByAddressIds(@Param("addressIds") List<Long> addressIds, @Param("newZipCode") String newZipCode);

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