mybatis insert foreach Loop Insertion Mode
- 2021-10-27 07:10:02
- OfStack
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.