MyBatis -- foreach of dynamic sql

preface

The foreach tag of mybatis is often used to traverse collections and build in conditional statements or batch operation statements.

1, foreach build in condition

foreach implementation of in set or array is the simplest and most common case. The syntax is as follows:

< foreach collection="Collection type" open="Start character" close="Ending character"
          item="Members in the collection" separator="Separator between collection members">
              #{value of item}
</ foreach>

Label properties:
Collection: indicates whether the circular object is an array or a list collection. If the formal parameter of dao interface method is an array,
Collection = "array". If dao interface parameter is list, collection = "list".
open: character at the beginning of the loop.
close: the character at the end of the loop.
item: collection member, custom variable.
Separator: separator between collection members.
#{value of item}: get the value of the collection member.

1. The parameter is a simple type

Scenario: query all qualified users through an incoming user id set.

Interface:

List<Student> selectByIdList(List<Integer> idList);

mapper file:

<select id="selectByIdList" resultType="com.macay.entity.Student">
    select id, name, email, age from student
    where id in
    <foreach collection="list" item="ids" open="(" close=")" separator=",">
        #{ids}
    </foreach>
</select>

Test class:

@Test
public void selectByIdList() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Integer> list = new ArrayList<>();
    list.add(1);
    list.add(2);
    list.add(3);
    List<Student> students = mapper.selectByIdList(list);
    System.out.println(students);
    sqlSession.commit();
    sqlSession.close();
}

The results are as follows:

It must be noted here that if the input parameter is a collection type, the collection="list" in the mapper file is required. You can't take it for granted to write the interface input parameter collection = "idList", otherwise the following error will be reported:

In addition, if there is no empty judgment on the set in the program, there is still a problem with our previous writing method. When the set is null or empty, there will be syntax errors. In order to make the program more robust, we also need to add the empty judgment operation. The mapper code is as follows:

<select id="selectByIdList" resultType="com.macay.entity.Student">
    select id, name, email, age from student
    <if test="list !=null and list.size > 0">
        where id in
        <foreach collection="list" item="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
    </if>
</select>


As you can see, even if the collection is empty, there will be no syntax errors.

2. The parameter is a list < object type >

Interface:

List<Student> selectByIdList2(List<Student> students);

mapper file:

<select id="selectByIdList2" resultType="com.macay.entity.Student">
    select id, name, email, age from student
    <if test="list !=null and list.size > 0">
        where id in
        <foreach collection="list" item="stu" open="(" close=")" separator=",">
            #{stu.id}
        </foreach>
    </if>
</select>

Note: if the list is an object type, the method of "attribute. Attribute" is used when referencing, such as stu.id.

Test class:

@Test
public void selectByIdList2() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Student> list = new ArrayList<>();
    Student student = new Student();
    student.setId(1);
    Student student1 = new Student();
    student1.setId(2);
    list.add(student);
    list.add(student1);
    List<Student> students = mapper.selectByIdList2(list);
    System.out.println(students);
    sqlSession.commit();
    sqlSession.close();
}

The results are as follows:

3. Parameter is an array type

Interface:

List<Student> selectByIdArray(Integer[] idArray);

mapper file:

<select id="selectByIdArray" resultType="com.macay.entity.Student">
    select id, name, email, age from student
    where id in
    <foreach collection="array" item="ids" open="(" close=")" separator=",">
        #{ids}
    </foreach>
</select>

Add the processing of empty array:

<select id="selectByIdArray" resultType="com.macay.entity.Student">
    select id, name, email, age from student
    <if test="array !=null">
        where id in
        <foreach collection="array" item="ids" open="(" close=")" separator=",">
            #{ids}
        </foreach>
    </if>
</select>

Test class:

@Test
public void selectByIdArray() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Integer> list = new ArrayList<>();
    Integer[] ids = new Integer[3];
    list.add(1);
    list.add(2);
    list.add(3);
    Integer[] integers = list.toArray(ids);
    List<Student> students = mapper.selectByIdArray(integers);
    System.out.println(students);
    sqlSession.commit();
    sqlSession.close();
}

The results are as follows:

2, foreach is used for batch operations

1. foreach implements batch insertion

In mysql, the syntax of batch insert is as follows:

insert into table_name (c1, c2, c3 ...) values (v1a, v2a, v3a), (v1b, v2b, v3b),(v1c, v2c, v3c)....

From the part to be processed, you can see that the following value is a loop body, so you can realize loop insertion through foreach.

Interface:

int insertStudentBatch(List<Student> students);

mapper file:

<insert id="insertStudentBatch">
    insert into student (id, name, email, age)
    values
    <foreach collection="list" separator="," item="stu">
        (#{stu.id}, #{stu.name}, #{stu.email}, #{stu.age})
    </foreach>
</insert>

Test class:

@Test
public void insertStudentBatch() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    List<Student> list = new ArrayList<>();
    Student student = new Student();
    student.setId(22);
    student.setName("test20");
    student.setAge(20);
    student.setEmail("test20@qq.com");
    Student student1 = new Student();
    student1.setId(23);
    student1.setName("test21");
    student1.setAge(21);
    student1.setEmail("test21@qq.com");
    list.add(student);
    list.add(student1);
    if (CollectionUtils.isNotEmpty(list)) {
        int count = mapper.insertStudentBatch(list);
        System.out.println(count);
    }
    sqlSession.commit();
    sqlSession.close();
}
2. foreach implements dynamic update

Here we mainly introduce how foreach implements dynamic update when the parameter is map.

When the parameter is of Map type, the index attribute value of foreach tag does not correspond to the index value, but the key in the Map. Dynamic update can be realized by using this key.

Interface:

int updateStudentByMap(Map<String, Object> map);

mapper file:

<update id="updateStudentByMap" >
    update student set
    <foreach collection="_parameter" item="val" index="key" separator=",">
        ${key} = #{val}
    </foreach>
    where id = #{id}
</update>

Note that the parameter name can be specified through the @ param annotation, or the default _parameterin mybatis can be used as the key of the parameter, and _parameteris also used in all XML.

Test class:

@Test
public void updateStudentByMap() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    StudentDao mapper = sqlSession.getMapper(StudentDao.class);
    Map<String, Object> map = new HashMap<>();
    map.put("id", 3);
    map.put("name", "test3");
    map.put("email", "test3@qq.com");
    if (MapUtils.isNotEmpty(map)) {
        int count = mapper.updateStudentByMap(map);
        System.out.println(count);
    }
    System.out.println(mapper.selectById(3));
    sqlSession.commit();
    sqlSession.close();
}

The results are as follows:

Tags: Java SQL

Posted on Mon, 20 Sep 2021 20:47:24 -0400 by thessoro