mybatis的＜foreach＞标签使用

记录：419

场景：使用MyBatis的<foreach></foreach>标签的循环遍历List类型的入参。使用collection属性指定List，item指定List中存放的对象，separator指定分割符号，open指定开始字符，close指定结束字符。

版本：JDK 1.8,Spring Boot 2.6.3,mybatis-3.5.9。

1.基础知识

1.1MyBatis的标签

(1)查看MyBatis支持的标签

地址：http://mybatis.org/dtd/mybatis-3-mapper.dtd

(2)查看标签使用

以<mapper></mapper>标签元素为例，在mybatis-3-mapper.dtd中如下：

<!ELEMENT mapper (cache-ref | cache | resultMap* | parameterMap* | sql* | insert* | update* | delete* | select* )+>
<!ATTLIST mapper
namespace CDATA #IMPLIED
>

<!ELEMENT mapper(...)+>，表示这是一个标签元素mapper.

(..| insert*| update* | delete* | select*),表示在mapper元素中可以嵌套使用的元素清单。

<!ATTLIST mapper>，表示这是一个元素标签的支持的属性。

1.2MyBatis的使用

(1)在application.yml配置文件配置mybatis映射的xml文件位置。

mybatis:
  mapper-locations: classpath*:mapper/**/*.xml

(2)创建一个Java接口。在接口中添加方法。

(3)创建一个Java接口映射的xml文件。在xml中使用<mapper></mapper>标签的namespace属性指定Java接口的全路径。Java接口和xml映射文件就完成了绑定关系。

(4)在<mapper></mapper>标签内，使用<insert><update><delete><select>等标签的id属性指定Java的方法名称。Java接口的方法和xml映射文件的<mapper></mapper>内部的标签就完成了绑定关系。

2.使用<foreach></foreach>标签元素

场景：<foreach></foreach>标签元素在<insert><update><delete><select>等标签元素内使用。

示例功能：示例使用<foreach></foreach>标签，在<insert><update><delete><select>标签内遍历输入的List类型入参。

2.1Java接口

@Repository
public interface Label03ForeachMapper {
  List<CityLabelPO> queryCity(List<Long> paraList);
  int insertCity(List<CityLabelPO> cityList);
  int insertCityUnionAll(List<CityLabelPO> cityList);
  int updateCity(List<CityLabelPO> cityList);
  int deleteCity(List<CityLabelPO> cityList);
}

2.2Java接口映射的xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hub.example.mapper.Label03ForeachMapper">
  <!--  1.查询时,使用<foreach></foreach>标签-->
  <select id="queryCity" resultType="com.hub.example.domain.CityLabelPO">
      select CITY_ID AS "cityId",
      CITY_NAME AS "cityName",
      LAND_AREA AS "landArea",
      POPULATION AS "population",
      GROSS AS "gross",
      CITY_DESCRIBE AS "cityDescribe",
      DATA_YEAR AS "dataYear",
      UPDATE_TIME AS "updateTime"
      from t_city
      WHERE CITY_ID IN
      <foreach collection="list" item="cityId" open="(" separator="," close=")">
          #{cityId}
      </foreach>
  </select>
  <!--  2.1插入时,使用<foreach></foreach>标签,使用逗号分割-->
  <insert id="insertCity" parameterType="java.util.List">
      insert into t_city_01 (CITY_ID,
      CITY_NAME,
      LAND_AREA,
      POPULATION,
      GROSS,
      CITY_DESCRIBE,
      DATA_YEAR,
      UPDATE_TIME)
      values
      <foreach collection="list" item="cityPO" open="" separator="," close="">
          (#{cityPO.cityId},
          #{cityPO.cityName},
          #{cityPO.landArea},
          #{cityPO.population},
          #{cityPO.gross},
          #{cityPO.cityDescribe},
          #{cityPO.dataYear},
          #{cityPO.updateTime})
      </foreach>
  </insert>
  <!--  2.2插入时,使用<foreach></foreach>标签,使用union all分割-->
  <insert id="insertCityUnionAll" parameterType="java.util.List">
      insert into t_city_01 (CITY_ID,
      CITY_NAME,
      LAND_AREA,
      POPULATION,
      GROSS,
      CITY_DESCRIBE,
      DATA_YEAR,
      UPDATE_TIME)
      <foreach collection="list" item="cityPO" open="" separator="UNION ALL" close="">
          select
          #{cityPO.cityId},
          #{cityPO.cityName},
          #{cityPO.landArea},
          #{cityPO.population},
          #{cityPO.gross},
          #{cityPO.cityDescribe},
          #{cityPO.dataYear},
          #{cityPO.updateTime}
          from dual
      </foreach>
  </insert>
  <!--  3.更新时,使用<foreach></foreach>标签-->
  <update id="updateCity" parameterType="java.util.List">
      update
      t_city_01
      set CITY_DESCRIBE='杭州是一个发达城市'
      WHERE CITY_ID IN
      <foreach collection="list" item="cityPO" open="(" separator="," close=")">
          #{cityPO.cityId}
      </foreach>
  </update>
  <!--  4.删除时,使用<foreach></foreach>标签-->
  <delete id="deleteCity" parameterType="java.util.List">
      delete from t_city_01 where 1=1
      <if test="list.size()>0">
          AND CITY_ID IN
          <foreach collection="list" item="cityPO" open="(" separator="," close=")">
              #{cityPO.cityId}
          </foreach>
      </if>
  </delete>
</mapper>

3.测试

3.1测试代码

@Slf4j
@RestController
@RequestMapping("/hub/example/cityLabel")
public class CityLabelController {
  @Autowired
  private Label03ForeachMapper label03ForeachMapper;
  @GetMapping("/load03")
  public Object load03() {
    log.info("测试开始...");
    // 生成查询参数
    List<Long> paraList = Arrays.asList(1L, 2L, 3L);
    // 1.查询时,使用<foreach></foreach>标签
    List<CityLabelPO> list01 = label03ForeachMapper.queryCity(paraList);
    // 2.1插入时,使用<foreach></foreach>标签,使用逗号分割
    label03ForeachMapper.insertCity(list01);
    // 2.2插入时,使用<foreach></foreach>标签,使用union all分割
    paraList = Arrays.asList(4L, 5L, 6L);
    List<CityLabelPO> list02 = label03ForeachMapper.queryCity(paraList);
    label03ForeachMapper.insertCityUnionAll(list02);
    // 3.更新时,使用<foreach></foreach>标签
    label03ForeachMapper.updateCity(list01);
    // 4.删除时,使用<foreach></foreach>标签
    label03ForeachMapper.deleteCity(list01);
    log.info("测试结束...");
    return "执行成功";
  }
}

3.2测试请求

URL：http://127.0.0.1:18080/hub-example/hub/example/cityLabel/load03

3.3执行SQL

示例使用<foreach></foreach>标签后，遍历List类型入参，从具体对象中取值。

示例一，查询时,使用<foreach></foreach>标签。

SELECT
  CITY_ID AS "cityId",
  CITY_NAME AS "cityName",
  LAND_AREA AS "landArea",
  POPULATION AS "population",
  GROSS AS "gross",
  CITY_DESCRIBE AS "cityDescribe",
  DATA_YEAR AS "dataYear",
  UPDATE_TIME AS "updateTime"
FROM
  t_city
WHERE CITY_ID IN (?, ?, ?)

示例二,插入时,使用<foreach></foreach>标签,使用逗号分割。

INSERT INTO t_city_01 (
  CITY_ID,
  CITY_NAME,
  LAND_AREA,
  POPULATION,
  GROSS,
  CITY_DESCRIBE,
  DATA_YEAR,
  UPDATE_TIME
)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?, ?, ?, ?),
  (?, ?, ?, ?, ?, ?, ?, ?)

示例三,插入时,使用<foreach></foreach>标签,使用union all分割。

INSERT INTO t_city_01 (CITY_ID, CITY_NAME, LAND_AREA, 
 POPULATION, GROSS, CITY_DESCRIBE, DATA_YEAR, UPDATE_TIME) 
SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM DUAL 
UNION ALL 
SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM DUAL 
UNION ALL 
SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM DUAL

示例四,更新时,使用<foreach></foreach>标签。

UPDATE
  t_city_01
SET
  CITY_DESCRIBE = '杭州是一个发达城市'
WHERE CITY_ID IN (?, ?, ?)

示例五,删除时,使用<foreach></foreach>标签。

DELETE
FROM
  t_city_01
WHERE 1 = 1
  AND CITY_ID IN (?, ?, ?)

4.支撑

4.1实体对象

(1)封装结果对象CityLabelPO

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CityLabelPO {
  private Long cityId;
  private String cityName;
  private Double landArea;
  private Long population;
  private Double gross;
  private String cityDescribe;
  private String dataYear;
  private Date updateTime;
}

4.2建表语句

CREATE TABLE t_city (
  CITY_ID BIGINT(16) NOT NULL COMMENT '唯一标识',
  CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
  LAND_AREA DOUBLE DEFAULT NULL COMMENT '城市面积',
  POPULATION BIGINT(16) DEFAULT NULL COMMENT '城市人口',
  GROSS DOUBLE DEFAULT NULL COMMENT '生产总值',
  CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
  DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '数据年份',
  UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新时间'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

以上，感谢。

2023年4月23日