网站导航
> 文章列表 > MyBatis 动态SQL之＜foreach＞标签

MyBatis 动态SQL之＜foreach＞标签

网友: 文章列表 2024-03-21 18:17:18

MyBatis 动态SQL之＜foreach＞标签

简介

foreach 标签用于循环语句，它很好的支持了数据和 List、set 接口的集合，并对此提供遍历的功能。
对于一些 SQL 语句中含有 in 条件，需要迭代条件集合来生成的情况，可以使用 foreach 来实现 SQL 条件的迭代。

select * 
from TABLE
where (id=1 or id=3 or id=4)

要查询一个表中id为1，3，4的数据，可以把需要查的这些id的数据存放到一个集合中，通过遍历这个集合来查询到这些数据，这种时候我们就用到foreach了。

foreach 标签有六个属性：item，index，collection，open， close，separator

属性 作用
item 表示集合中每一个元素进行迭代时的别名
index 指定一个名字，表示在迭代过程中每次迭代到的位置
open 表示该语句以什么开始（既然是 in 条件语句，所以必然以’('开始)
close 表示该语句以什么结束（既然是 in 条件语句，所以必然以’)'结束。
separator 表示在每次进行迭代之间以什么符号作为分隔符（既然是 in 条件语句，所以必然以‘,’作为分隔符）。
collection 该属性是必选的，但在不同情况下该属性的值是不一样的，主要有以下 3 种情况:
(1)如果传入的是单参数且参数类型是一个 List，collection 属性值为 list
(2)如果传入的是单参数且参数类型是一个 array 数组，collection 的属性值为 array
(3)如果传入的参数是多个，需要把它们封装成一个 Map，当然单参数也可以封装成 Map。Map 的 key 是参数名，collection 属性值是传入的 List 或 array 对象在自己封装的 Map 中的 key。

语法格式

trim 一般用于去除 SQL 语句中多余的 AND 关键字、逗号，或者给 SQL 语句前拼接 where、set 等后缀，可用于选择性插入、更新、删除或者条件查询等操作。trim 语法格式如下。

<foreach item="item" index="index" collection="list|array|map key" open="(" separator="," close=")">参数值
</foreach>

网络案例

+----+----------------+----------------------------+-----+---------+---------------------+
| id | name           | url                        | age | country | createtime          |
+----+----------------+----------------------------+-----+---------+---------------------+
|  1 | 编程帮         | https://www.biancheng.net/ |  10 | CN      | 2021-02-23 10:20:40 |
|  2 | C语言中文网    | http://c.biancheng.net/    |  12 | CN      | 2021-03-08 11:23:27 |
|  3 | 百度           | https://www.baidu.com/     |  18 | CN      | 2021-03-08 11:23:53 |
|  4 | 淘宝           | https://www.taobao.com/    |  17 | CN      | 2021-03-10 10:33:54 |
|  5 | Google         | https://www.google.com/    |  23 | US      | 2021-03-10 10:34:34 |
|  6 | GitHub         | https://github.com/        |  13 | US      | 2021-03-10 10:34:34 |
|  7 | Stack Overflow | https://stackoverflow.com/ |  16 | US      | 2021-03-10 10:34:34 |
|  8 | Yandex         | http://www.yandex.ru/      |  11 | RU      | 2021-03-10 10:34:34 |
+----+----------------+----------------------------+-----+---------+---------------------+

<select id="selectWebsite"parameterType="net.biancheng.po.Website"resultType="net.biancheng.po.Website">SELECT id,name,url,age,countryFROM website WHERE age in<foreach item="age" index="index" collection="list" open="("separator="," close=")">#{age}</foreach>
</select>

基础环境

1.数据库准备

# 创建一个名称为t_customer的表
CREATE TABLE t_customer (id int(32) PRIMARY KEY AUTO_INCREMENT,username varchar(50),jobs varchar(50),phone varchar(16)
);
# 插入3条数据
INSERT INTO t_customer VALUES ('1', 'joy', 'teacher', '13733333333');
INSERT INTO t_customer VALUES ('2', 'jack', 'teacher', '13522222222');
INSERT INTO t_customer VALUES ('3', 'tom', 'worker', '15111111111');

2.新建项目或Module

MyBatis 动态SQL之＜foreach＞标签

3 pom.xml中添加

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><parent><artifactId>mybatis</artifactId><groupId>com.example</groupId><version>1.0-SNAPSHOT</version></parent><modelVersion>4.0.0</modelVersion><groupId>com.biem</groupId><artifactId>dynamaicSql</artifactId><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><dependencies><!--1.引入mybatis包--><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.6</version></dependency><!--2.单元测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><!--3.mysql驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.18</version><scope>runtime</scope></dependency><!--4.log4j日志--><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.16</version></dependency></dependencies>
</project>

4.创建package和文件夹

src/main/java/下创建package
com.biem.pojo
com.biem.mapper
com.biem.util
src/main/resources/下创建文件夹
com/biem/mapper
src/test/java下创建package
com.biem.test

5 框架配置文件

5.1 mybatis核心配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--引入properties文件--><properties resource="jdbc.properties"></properties><!--将下划线映射为驼峰--><settings><setting name="mapUnderscoreToCamelCase" value="true"/></settings><!--设置类型别名--><typeAliases><!--以包为单位，将包下所有的类型设置设置默认的类型别名，即类名且不区分大小写--><package name="com.biem.pojo"/></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!-- 引入映射文件 --><mappers><!--以包为单位引入映射文件要求：1. mapper接口所在的包要和映射文件所在的包一致2. mapper接口要和映射文件的名字一致--><package name="com.biem.mapper"/></mappers></configuration>

5.2 mybatis属性文件jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root

5.3 log4j.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"><log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"><appender name="STDOUT" class="org.apache.log4j.ConsoleAppender"><param name="Encoding" value="UTF-8"/><layout class="org.apache.log4j.PatternLayout"><param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \\n"/></layout></appender><logger name="java.sql"><level value="debug"/></logger><logger name="org.apache.ibatis"><level value="info"/></logger><root><level value="debug"/><appender-ref ref="STDOUT"/></root>
</log4j:configuration>

6 用户配置文件

6.1 实体类

package com.biem.pojo;import lombok.*;/* ClassName: Customer* Package: com.biem.pojo* Description: @Create 2023/4/5 22:17* @Version 1.0*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class Customer {private Integer id;private String username;private String jobs;private String phone;
}

需要在pom.xml中引入lombok，简化原来的实体类的代码

6.2 mybatis接口类

package com.biem.mapper;/* ClassName: CustomerMapper* Package: com.biem.mapper* Description: @Create 2023/4/5 22:19* @Version 1.0*/
public interface CustomerMapper {
}

6.3 mybatis用户配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.biem.mapper.CustomerMapper"><!-- namespace要和mapper接口的全类名保持一致，例如com.biem.mybatis.mapper.xxxMapper --><!-- sql语句要和接口的方法名保持一致 --></mapper>

6.4 mybatis工具类

package com.biem.util;import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;
import java.io.InputStream;/* ClassName: MybatisUtil* Package: com.biem.utils* Description: @Create 2023/4/5 22:23* @Version 1.0*/
public class MybatisUtil {//利用static(静态)属于类不属于对象,且全局唯一private static SqlSessionFactory sqlSessionFactory = null;//利用静态块在初始化类时实例化sqlSessionFactorystatic {InputStream is= null;try {is = Resources.getResourceAsStream("mybatis-config.xml");sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);} catch (IOException e) {e.printStackTrace();throw new ExceptionInInitializerError(e);}}/* openSession 创建一个新的SqlSession对象* @return SqlSession对象*/public static SqlSession openSession(boolean autoCommit){return sqlSessionFactory.openSession(autoCommit);}public static SqlSession openSession(){return sqlSessionFactory.openSession();}/* 释放一个有效的SqlSession对象* @param session 准备释放SqlSession对象*/public static void closeSession(SqlSession session){if(session != null){session.close();}}
}

项目结构如下
MyBatis 动态SQL之＜foreach＞标签

foreach元素迭代数组

1 com.biem.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByArray(int [] ids);

2 com/biem/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByArray(int [] arrays);--><select id="findCustomerByArray" parameterType="java.util.Arrays" resultType="customer">select * from t_customer where id in<foreach item = "id" index = "index" collection="array" open="(" separator="," close=")">#{id}</foreach></select>

3.功能测试 com.biem.test.TestMybatis.java

    @Testpublic void testFindCustomerByArray(){// 通过工具类获取SqlSession对象SqlSession session = MybatisUtil.openSession();int[] ids={1,3};CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByArray(ids);System.out.println("customers = " + customers);// 关闭SqlSessionsession.close();}

foreach元素迭代List

1 com.biem.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByList(List<Integer> ids);

2 com/biem/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByList(List<Integer> ids);--><select id="findCustomerByList" parameterType="java.util.List" resultType="customer">select * from t_customer where id in<foreach item = "id" index = "index" collection="list" open="(" separator="," close=")">#{id}</foreach></select>

3.功能测试 com.biem.test.TestMybatis.java

@Testpublic void testFindCustomerByList(){// 通过工具类获取SqlSession对象SqlSession session = MybatisUtil.openSession();List<Integer> ids = new ArrayList<>();ids.add(1);ids.add(2);CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByList(ids);System.out.println("customers = " + customers);// 关闭SqlSessionsession.close();}

foreach元素迭代Map

1 com.biem.mapper.CustomerMapper.class中添加

public List<Customer> findCustomerByMap(Map<String, Object> map);

2 com/biem/mapper/CustomerMapper.xml中添加

    <!--public List<Customer> findCustomerByMap(Map<String, Object> map);--><select id="findCustomerByMap" parameterType="java.util.Map" resultType="customer">select * from t_customer where jobs=#{jobs} and id in<foreach item = "ids" index = "index" collection="id" open="(" separator="," close=")">#{ids}</foreach></select>

解析：使用foreach标签迭代Map集合，由于传入的参数是Map类型，所以在SQL中需要根据key分别获得相应的value值，在以上语句中，#{jobs}获取Map集合中key为jobs的value的值，而collection="id"则获取的是Map集合中key="id"所对应的value的值。

3.功能测试 com.biem.test.TestMybatis.java

    @Testpublic void testFindCustomerByMap(){// 通过工具类获取SqlSession对象SqlSession session = MybatisUtil.openSession();List<Integer> ids = new ArrayList<>();ids.add(1);ids.add(2);ids.add(3);Map<String, Object> maps = new HashMap<>();maps.put("id", ids);maps.put("jobs","teacher");CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByMap(maps);System.out.println("customers = " + customers);// 关闭SqlSessionsession.close();}
网络标签：

相关问题