虽然是很基础的东西，不过难免会忘记，所以写个笔记巩固一下，顺便分享。

实体类：

@Data
public class EventOrder {
​
private Long id;
​
private String eventName;
​
private Integer eventType;
​
private String orderNo;
​
}

表字段：

`id`, event_name, event_type, order_no

<insert id="batchInsertByValues" parameterType="com.zyuan.boot.entity.EventOrder">  
    insert into event\_order  
    (<include refid="BaseColumn"/>)  
    values  
    <foreach collection="eventOrders" item="item" separator=",">  
        (  
        #{item.id}, #{item.eventName}, #{item.eventType}, #{item.orderNo}  
        )  
    </foreach>  
</insert>

< foreach >标签中，collection表示传入的集合名，item为每个元素，separator为分隔符

xml对应的mapper文件中最好加上@Param注解，保证准确性：

void batchInsertByValues(@Param("eventOrders") List eventOrders);

测试：

@Autowired
private EventOrderMapper eventOrderMapper;
​
@Test
public void batchInsertByValues() {
List eventOrders = getList();
eventOrderMapper.batchInsertByValues(eventOrders);
}
​
private List getList() {
List eventOrders = new ArrayList<>();
for (Long i = 1L; i <= 10; i++) {
EventOrder addEntity = new EventOrder();
addEntity.setId(i);
addEntity.setEventName("事件" + i);
addEntity.setEventType(1);
addEntity.setOrderNo("2");
eventOrders.add(addEntity);
}
return eventOrders;
}

打印的sql语句：

INSERT INTO event_order ( `id`, event_name, event_type, order_no )
VALUES
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? ),
( ?, ?, ?, ? )

update event_order when `id` = #{item.id} then #{item.eventName} when `id` = #{item.id} then #{item.eventType} when `id` = #{item.id} then #{item.orderNo} where id in #{item.id}

解释：第一个< trim >用于拼接set前缀以及标签中所有< trim >连接的 “,” ，然后里面的< trim >，prefix存放的是：“被修改字段” + “=” + “case” ，suffix存放的是case的终止关键字end，foreach是为了遍历出所有id的情况并通过then来给值。

打印sql：

UPDATE event_order
SET
event_name =
CASE
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
END,
​
event_type =
CASE
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
END,

order_no =
CASE
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
WHEN `id` = ? THEN ?
END
WHERE
id IN ( ?, ?, ?, ? )

还有一种通过循环的方式来更新，但是并不推荐使用，参考：

update event_order set `event_name` = #{item.eventName}, `event_type` = #{item.eventType}, `order_no` = #{item.orderNo} where `id` = #{item.id}

因为集合有多少数据，就执行多少次update，并不高效

update event_order when `id` = #{key} then #{value.eventName} when `id` = #{key} then #{value.eventType} when `id` = #{key} then #{value.orderNo} where id in #{key}

解释：其实map的循环只需要利用entrySet即可，并且在< foreach >标签中，index其实代表的就是map中的key，而遍历entrySet,，他的每一个元素就是value，其他的写法就与上面的批量更新一致。

测试：

@Test
public void batchUpdateByMap() {
// key为id，value为对象
Map eventOrderMap = getUpdateMap();
eventOrderMapper.batchUpdateByMap(eventOrderMap);
}
​
private Map getUpdateMap() {
Map eventOrderMap = new HashMap<>();
for (Long i = 1L; i <= 10; i++) {
EventOrder addEntity = new EventOrder();
addEntity.setId(i);
addEntity.setEventName(i + "事件");
addEntity.setEventType(11);
addEntity.setOrderNo("20.21");
eventOrderMap.put(i,addEntity);
}
return eventOrderMap;
}

打印SQL：

UPDATE event_order
SET event_name =
CASE
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
END,
event_type =
CASE
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
END,
order_no =
CASE
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
WHEN `id` = ? THEN
?
END
WHERE
id IN ( ?, ?, ?, ? )