This blog post focuses on using foreach tags to generate dynamic Sql, including the following three scenarios:
- Foreach implements the in collection
- Foreach implements bulk inserts
- Foreach implements dynamic update
1. Foreach implements in collection
Assume that there is a requirement to query all qualified users according to the set of user ids passed IN. At this time, we need to use IN IN Sql, such as ID IN (1,1001).
First, we add the following methods to the interface SysUserMapper:
/** * Query user ** based on user ID set@param idList
* @return* /
List<SysUser> selectByIdList(List<Long> idList);
Copy the codeThen add the following code to the corresponding sysusermapper.xml:
<select id="selectByIdList" resultType="com.zwwhnly.mybatisaction.model.SysUser">
SELECT id,
user_name,
user_password,
user_email,
create_time
FROM sys_user
WHERE id IN
<foreach collection="list" open="(" close=")" separator=","
item="id" index="i">
#{id}
</foreach>
</select>
Copy the codeFinally, add the following test methods to the SysUserMapperTest test class:
@Test
public void testSelectByIdList(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<Long> idList = new ArrayList<Long>();
idList.add(1L);
idList.add(1001L);
List<SysUser> userList = sysUserMapper.selectByIdList(idList);
Assert.assertEquals(2, userList.size());
} finally{ sqlSession.close(); }}Copy the codeRun the test code, the test passes, the output log is as follows:
DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id IN ( ? ,?)
DEBUG [main] – ==> Parameters: 1(Long), 1001(Long)
TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time
TRACE [main] – <== Row: 1, admin, 123456, [email protected], 2019-06-27 18:21:07.0
TRACE [main] – <== Row: 1001, test, 123456, [email protected], 2019-06-27 18:21:07.0
DEBUG [main] – <== Total: 2
The log shows that the contents of the foreach element eventually generated an Sql statement of (1,1001).
Foreach contains attributes:
- Open: string at the beginning of the loop content.
- Close: String at the end of the loop.
- Separator: Separates each loop.
- Item: Each value taken from the iterator.
- Index: If the parameter is set or array, the value is the current index value. If the parameter is Map, the value is the Map key.
- Collection: The name of the property to iterate through.
You might wonder, why is the value of collection a list? How do I set this value?
In the example above, there is only one collection parameter. We set the collection property to list, which could also be written as collection. Let’s take a look at the default processing logic in DefaultSqlSession:
private Object wrapCollection(Object object) {
DefaultSqlSession.StrictMap map;
if (object instanceof Collection) {
map = new DefaultSqlSession.StrictMap();
map.put("collection", object);
if (object instanceof List) {
map.put("list", object);
}
return map;
} else if(object ! =null && object.getClass().isArray()) {
map = new DefaultSqlSession.StrictMap();
map.put("array", object);
return map;
} else {
returnobject; }}Copy the codeAlthough the code works fine with default values, it is recommended to use @param to specify the parameter name, as shown below:
List<SysUser> selectByIdList(@Param("idList") List<Long> idList);
Copy the code<foreach collection="idList" open="(" close=")" separator=","
item="id" index="i">
#{id}
</foreach>
Copy the codeIf the argument is an array argument, collection can be set to array by default.
/** * Query user ** from user ID array@param idArray
* @return* /
List<SysUser> selectByIdArray(Long[] idArray);
Copy the code<select id="selectByIdArray" resultType="com.zwwhnly.mybatisaction.model.SysUser">
SELECT id,
user_name,
user_password,
user_email,
create_time
FROM sys_user
WHERE id IN
<foreach collection="array" open="(" close=")" separator=","
item="id" index="i">
#{id}
</foreach>
</select>
Copy the codeAlthough the code works fine with default values, it is recommended to use @param to specify the parameter name, as shown below:
List<SysUser> selectByIdArray(@Param("idArray")Long[] idArray);
Copy the code<foreach collection="idArray" open="(" close=")" separator=","
item="id" index="i">
#{id}
</foreach>
Copy the code2. Foreach implements batch inserts
Suppose you have a requirement to batch write an incoming set of users to the database.
First, we add the following methods to the interface SysUserMapper:
/** * Insert user information ** in batches@param userList
* @return* /
int insertList(List<SysUser> userList);
Copy the codeThen add the following code to the corresponding sysusermapper.xml:
<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
VALUES
<foreach collection="list" item="user" separator=",">(#{user.userName},#{user.userPassword},#{user.userEmail},#{user.userInfo},#{user.headImg,jdbcType=BLOB},#{user.createTim e,jdbcType=TIMESTAMP})</foreach>
</insert>
Copy the codeFinally, add the following test methods to the SysUserMapperTest test class:
@Test
public void testInsertList(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
List<SysUser> sysUserList = new ArrayList<SysUser>();
for (int i = 0; i < 2; i++) {
SysUser sysUser = new SysUser();
sysUser.setUserName("test" + i);
sysUser.setUserPassword("123456");
sysUser.setUserEmail("[email protected]");
sysUserList.add(sysUser);
}
int result = sysUserMapper.insertList(sysUserList);
for (SysUser sysUser : sysUserList) {
System.out.println(sysUser.getId());
}
Assert.assertEquals(2, result);
} finally{ sqlSession.close(); }}Copy the codeRun the test code, the test passes, the output log is as follows:
DEBUG [main] – ==> Preparing: INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time) VALUES (? ,? ,? ,? ,? ,?) . (? ,? ,? ,? ,? ,?)
DEBUG [main] – ==> Parameters: test0(String), 123456(String), [email protected](String), null, null, null, test1(String), 123456(String), [email protected](String), null, null, null
DEBUG [main] – <== Updates: 2
1035
1036
3. Foreach implements dynamic update
Suppose you have a requirement to update user information based on the Map parameter passed in.
First, we add the following methods to the interface SysUserMapper:
/** * Update column ** with Map@param map
* @return* /
int updateByMap(Map<String, Object> map);
Copy the codeThen add the following code to the corresponding sysusermapper.xml:
<update id="updateByMap">
UPDATE sys_user
SET
<foreach collection="_parameter" item="val" index="key" separator=",">
${key} = #{val}
</foreach>
WHERE id = #{id}
</update>
Copy the codeFinally, add the following test methods to the SysUserMapperTest test class:
@Test
public void testUpdateByMap(a) {
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("id".1L);
map.put("user_email"."[email protected]");
map.put("user_password"."12345678");
Assert.assertEquals(1, sysUserMapper.updateByMap(map));
SysUser sysUser = sysUserMapper.selectById(1L);
Assert.assertEquals("[email protected]", sysUser.getUserEmail());
Assert.assertEquals("12345678", sysUser.getUserPassword());
} finally{ sqlSession.close(); }}Copy the codeRun the test code, the test passes, the output log is as follows:
DEBUG [main] – ==> Preparing: UPDATE sys_user SET user_email = ? , user_password = ? , id = ? WHERE id = ?
DEBUG [main] – ==> Parameters: [email protected](String), 12345678(String), 1(Long), 1(Long)
DEBUG [main] – <== Updates: 1
DEBUG [main] – ==> Preparing: SELECT id, user_name, user_password, user_email, create_time FROM sys_user WHERE id = ?
DEBUG [main] – ==> Parameters: 1(Long)
TRACE [main] – <== Columns: id, user_name, user_password, user_email, create_time
TRACE [main] – <== Row: 1, admin, 12345678, [email protected], 2019-06-27 18:21:07.0
DEBUG [main] – <== Total: 1
In the example above, collection uses the default value _parameter, or @param can be used to specify the parameter name, as shown below:
int updateByMap(@Param("userMap") Map<String, Object> map);
Copy the code<update id="updateByMap">
UPDATE sys_user
SET
<foreach collection="userMap" item="val" index="key" separator=",">
${key} = #{val}
</foreach>
WHERE id = #{userMap.id}
</update>
Copy the code4. Source code and reference
Source code address: github.com/zwwhnly/myb… Welcome to download.
MyBatis from Entry to Mastery by Liu Zenghui