當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MyBatis-14MyBatis动态SQL之【foreach】

發(fā)布時(shí)間：2025/3/21 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MyBatis-14MyBatis动态SQL之【foreach】小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

概述
foreach實(shí)現(xiàn)in集合
- - 1.需求
  - 2.UserMapper接口增加接口方法
  - 3.UserMapper.xml增加動(dòng)態(tài)SQL
  - 4.單元測試
foreach實(shí)現(xiàn)批量插入
前提
- - 1.需求
  - 2.UserMapper接口增加接口方法
  - 3.UserMapper.xml增加動(dòng)態(tài)SQL
  - 4.單元測試
foreach實(shí)現(xiàn)動(dòng)態(tài)update
- 不使用@Param注解指定參數(shù)名的情況
  - 1.UserMapper接口
  - 2.UserMapper.xml動(dòng)態(tài)SQL
- 使用@Param注解指定參數(shù)名的情況
  - 1.UserMapper接口
  - 2.UserMapper.xml動(dòng)態(tài)SQL
  - 3,單元測試

概述

SQL語句中有時(shí)候會(huì)使用IN關(guān)鍵字，比如 id in (1,2,3,4)。

雖然可以使用${ids}方式直接獲取值，但${ids}不能防止SQL注入，想要避免SQL注入就需要用#{}的方式，這時(shí)就要配合使用foreach標(biāo)簽來滿足需求.

foreach可以對數(shù)組、Map或者實(shí)現(xiàn)了Iterable接口（比如List、Set）的對象進(jìn)行遍歷。數(shù)組在處理的時(shí)候可以轉(zhuǎn)換為List對象。因此foreach遍歷的對象可以分為兩大類

Iterable類型
Map類型。

這兩種類型在遍歷循環(huán)時(shí)情況是不一樣的，我們通過如下3個(gè)示例來講解foreach的用法

foreach實(shí)現(xiàn)in集合

foreach實(shí)現(xiàn)in集合（或者數(shù)組）是最簡單和常見的一種情況

1.需求

根據(jù)id集合查出所有符合條件的用戶

2.UserMapper接口增加接口方法

/*** * * @Title: selectSysUserByIdList* * @Description: 根據(jù)用戶ID集合查詢用戶* * @param ids* @return* * @return: List<SysUser>*/List<SysUser> selectSysUserByIdList(List<Long> ids);

3.UserMapper.xml增加動(dòng)態(tài)SQL

<select id="selectSysUserByIdList" resultType="com.artisan.mybatis.xml.domain.SysUser">SELECTa.id,a.user_name userName,a.user_password userPassword,a.user_email userEmail,a.user_info userInfo,a.head_img headImg,a.create_time createTimeFROMsys_user aWHERE id in <foreach collection="list" item="userId" open="(" close=")" separator="," index="i">#{userId}</foreach></select>

foreach的屬性

collection 必填，值為要迭代循環(huán)的屬性名。情況有很多種
item 變量名，值為從迭代對象中取出的每一個(gè)值
index 索引的屬性名，在集合數(shù)組請魯昂下為當(dāng)前索引值，的那個(gè)迭代循環(huán)的對象是Map類型時(shí)，這個(gè)值為Map的key(鍵值）
open 整個(gè)循環(huán)內(nèi)容開頭的字符串
close 整個(gè)循環(huán)內(nèi)容結(jié)尾的字符串
separator 每次循環(huán)的分隔符

4.單元測試

@Testpublic void selectSysUserByIdListTest() {logger.info("selectSysUserByIdListTest");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);// 模擬idListList<Long> idList = new ArrayList<Long>();idList.add(1L);idList.add(1001L);// 調(diào)用接口方法List<SysUser> userList = userMapper.selectSysUserByIdList(idList);// userList不為空Assert.assertNotNull(userList);// userList > 0Assert.assertTrue(userList.size() > 0);// 期望返回2條數(shù)據(jù)，符合數(shù)據(jù)庫中記錄Assert.assertEquals(2, userList.size());logger.info(userList);} catch (Exception e) {e.printStackTrace();} finally {sqlSession.close();logger.info("sqlSession close successfully ");}}

日志

2018-04-23 01:49:29,686 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-23 01:49:29,692 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-23 01:49:29,696 INFO [main] (UserMapperTest.java:729) - selectSysUserByIdListTest 2018-04-23 01:49:30,203 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE id in ( ? , ? ) 2018-04-23 01:49:30,267 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long), 1001(Long) 2018-04-23 01:49:30,295 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-23 01:49:30,296 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-23 01:49:30,304 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-23 01:49:30,305 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 2 2018-04-23 01:49:30,306 INFO [main] (UserMapperTest.java:747) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=測試用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]] 2018-04-23 01:49:30,314 INFO [main] (UserMapperTest.java:752) - sqlSession close successfully

foreach實(shí)現(xiàn)批量插入

前提

如果數(shù)據(jù)庫支持批量插入，就可以通過foreach實(shí)現(xiàn)。批量插入是SQL-92新增的特性，目前支持的數(shù)據(jù)庫有DB2、SQL Server 2008+、PostgreSql8.2+、MySQL、SQLite3.7.11+ 以及H2.

語法

insert into tablename(column-a,[column-b,....])values('value-1a',['value-1b',...]),('value-2a',['value-2b',...]),('value-3a',['value-3b',...]),......

從上述語法部分可以看到，后面是一個(gè)值的循環(huán)，因此可以通過foreach來實(shí)現(xiàn)循環(huán)插入。

1.需求

批量插入用戶

2.UserMapper接口增加接口方法

/*** * * @Title: insertSysUserList* * @Description: 批量新增用戶* * @param sysUserList* @return* * @return: int*/int insertSysUserList(List<SysUser> sysUserList);

3.UserMapper.xml增加動(dòng)態(tài)SQL

<insert id="insertSysUserList" keyProperty="id" useGeneratedKeys="true">insert into sys_user(user_name, user_password, user_email, user_info, head_img, create_time)values<foreach collection="list" item="sysUser" separator=",">(#{sysUser.userName}, #{sysUser.userPassword}, #{sysUser.userEmail}, #{sysUser.userInfo}, #{sysUser.headImg, jdbcType=BLOB},#{sysUser.createTime, jdbcType=TIMESTAMP})</foreach></insert>

通過item指定了循環(huán)變量名后，在引用值的時(shí)候使用的是“屬性.屬性”的方式，如上所示sysUser.userName。

4.單元測試

@Testpublic void insertSysUserListTest() {logger.info("insertSysUserListTest");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);// 模擬userListList<SysUser> userList = new ArrayList<SysUser>();for (int i = 0; i < 5; i++) {SysUser sysUser = new SysUser();sysUser.setUserName("artisanTest_" + i);sysUser.setUserPassword("123456_" + i);sysUser.setUserEmail("artisan_" + i + "@artisan.com");sysUser.setUserInfo("測試用戶" + i);// 模擬頭像sysUser.setHeadImg(new byte[] { 1, 2, 3 });sysUser.setCreateTime(new Date());// 添加到SysUseruserList.add(sysUser);}// 新增用戶 ,返回受影響的行數(shù)int result = userMapper.insertSysUserList(userList);// 返回批量的自增主鍵配合 keyProperty="id" useGeneratedKeys="true" 這兩個(gè)屬性for (SysUser sysUser : userList) {logger.info(sysUser.getId());}// 只插入一條數(shù)據(jù) ,期望是5Assert.assertEquals(5, result);// 重新查詢List<SysUser> sysUserList = userMapper.selectAll();// 根據(jù)數(shù)據(jù)庫之前的2條記錄，加上本次新增的5條（雖未提交但還是在一個(gè)會(huì)話中，所以可以查詢的到）Assert.assertNotNull(sysUserList);Assert.assertEquals(7, sysUserList.size());} catch (Exception e) {e.printStackTrace();} finally {// 為了保持測試數(shù)據(jù)的干凈，這里選擇回滾// 由于默認(rèn)的sqlSessionFactory.openSession()是不自動(dòng)提交的// 除非顯式的commit，否則不會(huì)提交到數(shù)據(jù)庫sqlSession.rollback();logger.info("為了保持測試數(shù)據(jù)的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成");sqlSession.close();logger.info("sqlSession close successfully ");}}

日志

2018-04-23 15:31:28,500 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-23 15:31:28,505 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-23 15:31:28,508 INFO [main] (UserMapperTest.java:761) - insertSysUserListTest 2018-04-23 15:31:29,091 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_user( user_name, user_password, user_email, user_info, head_img, create_time) values ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ? ) 2018-04-23 15:31:29,183 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: artisanTest_0(String), 123456_0(String), artisan_0@artisan.com(String), 測試用戶0(String), java.io.ByteArrayInputStream@2f0496f0(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_1(String), 123456_1(String), artisan_1@artisan.com(String), 測試用戶1(String), java.io.ByteArrayInputStream@56517ead(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_2(String), 123456_2(String), artisan_2@artisan.com(String), 測試用戶2(String), java.io.ByteArrayInputStream@53bc21(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_3(String), 123456_3(String), artisan_3@artisan.com(String), 測試用戶3(String), java.io.ByteArrayInputStream@79641ab1(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp), artisanTest_4(String), 123456_4(String), artisan_4@artisan.com(String), 測試用戶4(String), java.io.ByteArrayInputStream@1b1498ba(ByteArrayInputStream), 2018-04-23 15:31:28.526(Timestamp) 2018-04-23 15:31:29,190 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 5 2018-04-23 15:31:29,191 INFO [main] (UserMapperTest.java:789) - 1032 2018-04-23 15:31:29,191 INFO [main] (UserMapperTest.java:789) - 1033 2018-04-23 15:31:29,191 INFO [main] (UserMapperTest.java:789) - 1034 2018-04-23 15:31:29,192 INFO [main] (UserMapperTest.java:789) - 1035 2018-04-23 15:31:29,192 INFO [main] (UserMapperTest.java:789) - 1036 2018-04-23 15:31:29,196 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id , a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime from sys_user a 2018-04-23 15:31:29,197 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 2018-04-23 15:31:29,231 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-23 15:31:29,231 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-23 15:31:29,243 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-23 15:31:29,247 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1032, artisanTest_0, 123456_0, artisan_0@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-23 15:31:29.0 2018-04-23 15:31:29,248 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1033, artisanTest_1, 123456_1, artisan_1@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-23 15:31:29.0 2018-04-23 15:31:29,249 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1034, artisanTest_2, 123456_2, artisan_2@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-23 15:31:29.0 2018-04-23 15:31:29,250 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1035, artisanTest_3, 123456_3, artisan_3@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-23 15:31:29.0 2018-04-23 15:31:29,251 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1036, artisanTest_4, 123456_4, artisan_4@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-23 15:31:29.0 2018-04-23 15:31:29,251 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 7 2018-04-23 15:31:29,255 INFO [main] (UserMapperTest.java:807) - 為了保持測試數(shù)據(jù)的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成 2018-04-23 15:31:29,256 INFO [main] (UserMapperTest.java:810) - sqlSession close successfully

foreach實(shí)現(xiàn)動(dòng)態(tài)update

這部分我們主要介紹當(dāng)參數(shù)類型是Map的時(shí)候，foreach如何實(shí)現(xiàn)動(dòng)態(tài)UPDATE

當(dāng)參數(shù)是Map類型的時(shí)候，foreach標(biāo)簽的index屬性值對應(yīng)的不是索引值，而是Map中的key, 利用這個(gè)key就可以動(dòng)態(tài)實(shí)現(xiàn)UPDATE了。

不使用@Param注解指定參數(shù)名的情況

1.UserMapper接口

void updateSysUserByMap(Map<String, Object> map);

這里沒有使用@Parma注解指定參數(shù)名，因而MyBatis在內(nèi)部的上線文中使用默認(rèn)值 _parameter 最為該參數(shù)的key ,所以xml中也必須使用_parameter。

2.UserMapper.xml動(dòng)態(tài)SQL

<update id="updateSysUserByMap">update sys_user set <foreach collection="_parameter" item="value" index="key" separator=",">${key} = #{value}</foreach>where id = #{id}</update>

這里的key作為列名，對應(yīng)的值作為該列的值，通過foreach將需要更新的字段拼接在SQL語句中。

使用@Param注解指定參數(shù)名的情況

1.UserMapper接口

void updateSysUserByMapWithParam(@Param("userMap") Map<String, Object> map);

2.UserMapper.xml動(dòng)態(tài)SQL

<update id="updateSysUserByMapWithParam">update sys_user set <foreach collection="userMap" item="value" index="key" separator=",">${key} = #{value}</foreach>where id = #{userMap.id}</update>

3,單元測試

@Testpublic void updateSysUserByMapTest() {logger.info("updateSysUserByMapTest");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);// 模擬MapMap<String, Object> userMap = new HashMap<String, Object>();// 查詢條件，同時(shí)也是where后面的更新字段，必須存在userMap.put("id", 1L);// 更新其他字段userMap.put("user_email", "map@artisan.com");userMap.put("user_name", "ARTISAN_ADMIN");// 調(diào)用接口,更新數(shù)據(jù)// userMapper.updateSysUserByMap(userMap);// 或者userMapper.updateSysUserByMapWithParam(userMap);// 根據(jù)當(dāng)前id 查詢用戶SysUser sysUser = userMapper.selectSysUserById(1L);Assert.assertEquals("map@artisan.com", sysUser.getUserEmail());Assert.assertEquals("ARTISAN_ADMIN", sysUser.getUserName());} catch (Exception e) {e.printStackTrace();} finally {// 為了保持測試數(shù)據(jù)的干凈，這里選擇回滾// 由于默認(rèn)的sqlSessionFactory.openSession()是不自動(dòng)提交的// 除非顯式的commit，否則不會(huì)提交到數(shù)據(jù)庫sqlSession.rollback();logger.info("為了保持測試數(shù)據(jù)的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成");sqlSession.close();logger.info("sqlSession close successfully ");}} 2018-04-23 16:27:06,658 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-23 16:27:06,661 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-23 16:27:06,664 INFO [main] (UserMapperTest.java:820) - updateSysUserByMapTest 2018-04-23 16:27:07,243 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: update sys_user set id = ? , user_name = ? , user_email = ? where id = ? 2018-04-23 16:27:07,319 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long), ARTISAN_ADMIN(String), map@artisan.com(String), 1(Long) 2018-04-23 16:27:07,325 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1 2018-04-23 16:27:07,327 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 2018-04-23 16:27:07,328 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long) 2018-04-23 16:27:07,364 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-23 16:27:07,365 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, ARTISAN_ADMIN, 123456, map@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-23 16:27:07,370 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-23 16:27:07,373 INFO [main] (UserMapperTest.java:852) - 為了保持測試數(shù)據(jù)的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成 2018-04-23 16:27:07,374 INFO [main] (UserMapperTest.java:855) - sqlSession close successfully

總結(jié)

以上是生活随笔為你收集整理的MyBatis-14MyBatis动态SQL之【foreach】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： MyBatis-13MyBatis动态S
下一篇： MyBatis-15MyBatis动态S