MyBatis 动态SQL之＜foreach＞标签

我不是女神ヾ 2024-03-24 15:25 109阅读 0赞

## 简介 ##

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

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

## 语法格式 ##

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

## 网络案例 ##

+----+----------------+----------------------------+-----+---------+---------------------+
    | 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,country
        FROM 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 ###

![在这里插入图片描述][002bc578219545d3a5ce3e5e9536c694.png]

### 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>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.6</version>
            </dependency>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
                <scope>runtime</scope>
            </dependency>
    
            
            <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 configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        
        <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>
            
            <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 mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.biem.mapper.CustomerMapper">
        
    
        
    
    </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;
        //利用静态块在初始化类时实例化sqlSessionFactory
        static {
        
            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();
            }
        }
    }

项目结构如下  
![在这里插入图片描述][2edbd7c1e35d4110a1328337f980fb99.png]

## foreach元素迭代数组 ##

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

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

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

<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 ###

@Test
        public 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);
            // 关闭SqlSession
            session.close();
        }

## foreach元素迭代List ##

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

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

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

<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 ###

@Test
        public 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);
            // 关闭SqlSession
            session.close();
        }

## foreach元素迭代Map ##

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

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

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

<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 ###

@Test
        public 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);
            // 关闭SqlSession
            session.close();
        }

[002bc578219545d3a5ce3e5e9536c694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c7742722137246d2bee6960bd5dfac09.png
[2edbd7c1e35d4110a1328337f980fb99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/bed7949717d64162beb3a82b31939043.png