foreach标签使用 Java ORM框架 mybatis的＜foreach＞标签使用

记录：419

场景：使用MyBatis的标签的循环遍历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)查看标签使用

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

namespace CDATA #IMPLIED

>

，表示这是一个标签元素mapper.

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

，表示这是一个元素标签的支持的属性。

1.2MyBatis的使用

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

mybatis:

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

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

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

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

2.使用标签元素

场景：标签元素在

等标签元素内使用。

示例功能：示例使用标签，在标签内遍历输入的List类型入参。

2.1Java接口

@Repository

public interface Label03ForeachMapper {

List queryCity(List paraList);

int insertCity(List cityList);

int insertCityUnionAll(List cityList);

int updateCity(List cityList);

int deleteCity(List cityList);

}

2.2Java接口映射的xml文件

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

#{cityId}

insert into t_city_01 (CITY_ID,

CITY_NAME,

LAND_AREA,

POPULATION,

GROSS,

CITY_DESCRIBE,

DATA_YEAR,

UPDATE_TIME)

values

(#{cityPO.cityId},

#{cityPO.cityName},

#{cityPO.landArea},

#{cityPO.population},

#{cityPO.gross},

#{cityPO.cityDescribe},

#{cityPO.dataYear},

#{cityPO.updateTime})

insert into t_city_01 (CITY_ID,

CITY_NAME,

LAND_AREA,

POPULATION,

GROSS,

CITY_DESCRIBE,

DATA_YEAR,

UPDATE_TIME)

select

#{cityPO.cityId},

#{cityPO.cityName},

#{cityPO.landArea},

#{cityPO.population},

#{cityPO.gross},

#{cityPO.cityDescribe},

#{cityPO.dataYear},

#{cityPO.updateTime}

from dual

update

t_city_01

set CITY_DESCRIBE='杭州是一个发达城市'

WHERE CITY_ID IN

#{cityPO.cityId}

delete from t_city_01 where 1=1

AND CITY_ID IN

#{cityPO.cityId}

3.测试

3.1测试代码

@Slf4j

@RestController

@RequestMapping("/hub/example/cityLabel")

public class CityLabelController {

@Autowired

private Label03ForeachMapper label03ForeachMapper;

@GetMapping("/load03")

public Object load03() {

log.info("测试开始...");

// 生成查询参数

List paraList = Arrays.asList(1L, 2L, 3L);

// 1.查询时,使用标签

List list01 = label03ForeachMapper.queryCity(paraList);

// 2.1插入时,使用标签,使用逗号分割

label03ForeachMapper.insertCity(list01);

// 2.2插入时,使用标签,使用union all分割

paraList = Arrays.asList(4L, 5L, 6L);

List list02 = label03ForeachMapper.queryCity(paraList);

label03ForeachMapper.insertCityUnionAll(list02);

// 3.更新时,使用标签

label03ForeachMapper.updateCity(list01);

// 4.删除时,使用标签

label03ForeachMapper.deleteCity(list01);

log.info("测试结束...");

return "执行成功";

}

}

3.2测试请求

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

3.3执行SQL

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

示例一，查询时,使用标签。

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 (?, ?, ?)

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

INSERT INTO t_city_01 (

CITY_ID,

CITY_NAME,

LAND_AREA,

POPULATION,

GROSS,

CITY_DESCRIBE,

DATA_YEAR,

UPDATE_TIME

)

VALUES

(?, ?, ?, ?, ?, ?, ?, ?),

(?, ?, ?, ?, ?, ?, ?, ?),

(?, ?, ?, ?, ?, ?, ?, ?)

示例三,插入时,使用标签,使用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

示例四,更新时,使用标签。

UPDATE

t_city_01

SET

CITY_DESCRIBE = '杭州是一个发达城市'

WHERE CITY_ID IN (?, ?, ?)

示例五,删除时,使用标签。

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日

相关链接

 您阅读本篇文章共花了：