The foreach tag of mybatis is often used to traverse collections and build in conditional statements or batch operation statements.
1, foreach build in conditionforeach 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"> # </ 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.
#: get the value of the collection member.
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=","> # </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=","> # </foreach> </if> </select>
As you can see, even if the collection is empty, there will be no syntax errors.
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=","> # </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:
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=","> # </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=","> # </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:
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"> (#, #, #, #) </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("[email protected]"); Student student1 = new Student(); student1.setId(23); student1.setName("test21"); student1.setAge(21); student1.setEmail("[email protected]"); 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=","> $ = # </foreach> where 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", "[email protected]"); 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:
