MyBatis | Dynamic SQL | foreach

DB table

test_table

id
1
2
3
4
5

Source code

sample_mapper.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="sample.mybatis">

  <select id="selectTest" resultType="sample.mybatis.TestTable">
    select *
      from test_table
     where id in
     <foreach collection="list" item="item"
              open="(" separator="," close=")">
       #{item}
     </foreach>
  </select>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;
import java.util.Arrays;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                session
                    .selectList("sample.mybatis.selectTest", Arrays.asList(1, 3, 5))
                    .forEach(System.out::println);
            }
        }
    }
}

Execution result

[DEBUG] s.m.selectTest  - ==>  Preparing: select * from test_table where id in ( ? , ? , ? ) 
[DEBUG] s.m.selectTest  - ==> Parameters: 1(Integer), 3(Integer), 5(Integer)
[DEBUG] s.m.selectTest  - <==      Total: 3
TestTable [id=1]
TestTable [id=3]
TestTable [id=5]

Explanation

Use the <foreach> tag to generate SQL while iterating over a collection.
Specify the collection to iterate over in the collection attribute.
- list is a reference name that can be used by default when an iterable object is passed as a parameter. See special parameter names.
In the item attribute, declare the temporary variable name that references each element in the iteration.
The open attribute specifies the string to add at the beginning of the iteration.
The separator attribute specifies the string inserted between each iteration result.
The close attribute specifies the string to add at the end of the iteration.
There is also an index attribute, which can declare a temporary variable name for referencing the repeated index value. It is mainly used to dynamically generate IN clauses.