mybatis insert foreach Loop Insertion Mode

mybatis insert foreach Loop Insertion

@Insert("<script>" +
            "insert into driver_account_appeal_photo (appeal_id,appeal_photo_path) values\n" +
            "<foreach collection=\"photoList\" item=\"item\" index=\"index\" separator=\",\">\n" +
            "\t(#{appealId},#{item})\n" +
            "</foreach>" +
            "</script>")
//@Insert("insert into driver_account_appeal_photo (appeal_id,appeal_photo_path) values(#{appealId},#{appealPhotoPath})")
void addAppealPhoto(AppealPhoto appealPhoto);

foreach statement inserts data in batches

This example technique: Spring+SpringMVC+MyBatis+Oracle

Problem description:

It is necessary to save a collection in the program to the database, and the type of the collection corresponds to an entity in the database. If it is troublesome to traverse the collection in the program and save it to the database table, it is possible to use MyBatis's foreach statement to insert data in batches.

Core code list:

Item (Entity Class):

public class Item {
    private String itemCode;// Project code 
    private String itemName;// Project name 
    private String itemValue;// Item value (multiple values are separated by commas) 
    private String itemCategory;// Project category 
 
    public String getItemCode() {
        return itemCode;
    }
 
    public void setItemCode(String itemCode) {
        this.itemCode = itemCode;
    }
 
    public String getItemName() {
        return itemName;
    }
 
    public void setItemName(String itemName) {
        this.itemName = itemName;
    }
 
    public String getItemValue() {
        return itemValue;
    }
 
    public void setItemValue(String itemValue) {
        this.itemValue = itemValue;
    }
 
    public String getItemCategory() {
        return itemCategory;
    }
 
    public void setItemCategory(String itemCategory) {
        this.itemCategory = itemCategory;
    }
}

Service Implementation Layer Approach:

    public Integer submitItem(List<Item> list ){
        return researchMapper.submitItem(list);
    }

Statement of mapper configuration file for MyBatis

In Oracle data, multiple pieces of data are connected by union all, and MySQL database is used:

 <insert id="submitItem"  parameterType="java.util.List">
        insert into ITEM (
        ITEM_CODE,
        ITEM_NAME,
        ITEM_VALUE,
        ITEM_CATAGORY
        )
        select  item.* from
        (
        <foreach collection="list" item="item" index="index" separator="UNION ALL" >
            select
            #{item.itemCode,jdbcType=VARCHAR},
            #{item.itemName,jdbcType=VARCHAR},
            #{item.itemValue,jdbcType=VARCHAR},
            #{item.itemCategory,jdbcType=VARCHAR}
            from dual
        </foreach>
        ) item
    </insert>

<!--MySql Writing style -->
<insert id="submitItem"  parameterType="java.util.List">
    insert into ITEM (
    ITEM_CODE,
    ITEM_NAME,
    ITEM_VALUE,
    ITEM_CATAGORY
    )
    values
    <foreach collection="list" item="item" index="index" separator="," >
      (
        #{item.itemCode,jdbcType=VARCHAR},
        #{item.itemName,jdbcType=VARCHAR},
        #{item.itemValue,jdbcType=VARCHAR},
        #{item.itemCategory,jdbcType=VARCHAR}
     )
    </foreach>
</insert>

foreach Element Resolution:

foreach Element is a loop statement that iterates through a collection, which supports iterating through collections of arrays, List, and Set interfaces.

foreach Element, collection is the parameter name passed in, which can be an array or a collection of List, Set, etc.

item Is the current element in the loop (the name of the configured item is optional, similar to iterator);

index Is the subscript of the position of the current element in the collection;

seperator Is the spacer of each element;

() are open and close elements, respectively, indicating what symbols are used to wrap these collection elements.

Note: Because the SQL of some databases has a limit on the length of SQL, it is necessary to estimate the length of collection object when using foreach element; foreach can also be used in constructing in conditions (you can try it yourself) in addition to the loop insertion used in this example.