Mybatis标签<foreach>详解

接到一个批量更新表数据的需求，深入学习了<foreach>标签，下面做一个总结。

目录

一、的六个属性

二、的应用环境

1、批量插入表数据

2、批量更新表数据

(1)单条件更新

(2)多条件更新

3、批量查询表数据

(1)list单条件查询

(2)list多条件查询

(3)map多条件查询

---

 

一、<foreach>的六个属性

<foreach>属性介绍

属性	介绍
collection	有三种格式：list，array，map。按照传递参数的类型填写对应格式。
item	标签内对象集的参数名
separator	每个子循环结束后的分隔符，常用","
open	开始符，常用"("
close	结束符，常用")"
index	在list和array中，index指元素序号；在map中，index指元素key。从0开始自增（相当于数组下标）

*open和close指在foreach标签头和尾添加指定内容,常见于IN和VALUES函数

二、<foreach>的应用环境

1、批量插入表数据

INSERT INTO table (a, b, c, d) VALUES
<foreach collection = "list" item = "item" separatior = ",">
    (#{item.a}, #{item.b}, #{item.c}, #{item.d})
</foreach>

/*执行Mybatis后的SQL语句示例
 *<foreach>在每个数据集间添加了','
 */
INSERT INTO table (a, b, c, d) VALUES
 ('a',123,true,'112456789'),
 ('b',124,true,'112455789'),
 ('c',125,false,'112466789'),
 ('d',126,true,'112451789')

2、批量更新表数据

(1)单条件更新

UPDATE table SET b = '10'
WHERE a IN
<foreach collection = "list" item = "item" open = "(" close = ")" separator = ",">
    #{item.a}
</foreach>

/*执行Mybatis后的SQL语句示例
 *单条件用到了IN函数
 *<foreach>在标签头尾添加了'(' ')'，并在每个数据间添加了','
 */
UPDATE table SET b = '10'
WHERE a IN ('ZBD12131','ZBD12132','ZBD12133')

 

(2)多条件更新

UPDATE table
SET a =
CASE
<foreach collection="list" item="item">
    WHEN b=#{item.b} AND c = #{item.c} THEN '10'
</foreach>
ELSE a END;

/*执行Mybatis后的SQL语句示例
 *多条件批量更新用到了SQL语句中的CASE WHEN函数
 */
UPDATE table
SET d =
CASE
WHEN b=123 AND c = true THEN '10'
WHEN b=124 AND c = true THEN '10'
WHEN b=125 AND c = false THEN '10'
WHEN b=126 AND c = true THEN '10'
ELSE d END;

3、批量查询表数据

(1)list单条件查询

SELECT * FROM table
WHERE a IN
<foreach collection = "list" item = "item" open = '(' close = ')' separator = ','>
    #{item.a}
</foreach>

//执行Mybatis后的SQL语句示例
SELECT * FROM table
WHERE a IN ('ZBD123','ZBD124','ZBD125','ZBD126')

(2)list多条件查询

SELECT * FROM table
WHERE a = #{a}
AND
<foreach collection = "list" item = "item" open = '(' close = ')' separator = 'OR'>
    b = #{item.b} 
    AND c = #{item.c}
    AND d = #{item.d}
</foreach>

/*执行Mybatis后的SQL语句示例
 *多条件批量查询用到了SQL语句OR函数，视实际应用环境修改separator参数
 */
SELECT * FROM table
WHERE a = '123'
AND (
    b=123 AND c = true AND d = 'ZBD123' OR
    b=124 AND c = true AND d = 'ZBD124' OR
    b=125 AND c = false AND d = 'ZBD125' OR
    b=126 AND c = true AND d = 'ZBD126'
)

(3)map多条件查询

昨天接到了一个新的查询匹配需求，前端传给我包含订单委托明细集合的订单委托实体类，我需要在匹配主委托条件的同时匹配委托明细的各项条件。之前一直没有涉及<foreach>collection的map参数，所以没有让前端传给我委托明细集，我根据主子关系left join。但这个查询匹配如果关联委托主子表的话，就会涉及到跨库left join，容易出现问题，今天开始翻看公司源码库，找有没有更保险的操作，然后就第一次接触到了map的相关用法，做个记录。

SELECT
tp.aggregate_no tender_no,
tp.company_id
FROM db_tender.t_tender_price tp
LEFT JOIN db_tender.t_pallet_item_aggregate pia ON tp.aggregate_no = pia.aggregate_no
LEFT JOIN db_tender.t_pallet_item pi ON pi.company_id = pia.company_id AND find_in_set(pi.pallet_item_no, pia.pallet_item_nos)
where tp.company_id = #{consignorCompanyId}
AND tp.start_date <= #{executionTime}
AND tp.end_date >= #{executionTime}
AND tp.business_type = #{businessModuleId}
AND tp.origin_district_id = #{originDistrictId}
AND tp.destination_district_id = #{destinationDistrictId}
AND
<foreach collection="tOrderWTItemModels" open="(" close=")" item="item" separator="or">
( pi.contract_no = #{item.pickNo} AND pi.product_name = #{item.productName} 
  AND IF (pi.wl_relationship = '10',
    pi.min_length = #{item.minLength} AND pi.max_length = #{item.maxLength} AND pi.min_width = #{item.minWidth} AND pi.max_width = #{item.maxWidth},
    (pi.min_length = #{item.minLength} AND pi.max_length = #{item.maxLength}) OR (pi.min_width = #{item.minWidth} AND pi.max_width = #{item.maxWidth}))
)
</foreach>
GROUP BY tp.aggregate_no, tp.company_id