當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MyBatis-13MyBatis动态SQL之【where、set、trim】

發布時間：2025/3/21 数据库 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 MyBatis-13MyBatis动态SQL之【where、set、trim】小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

概述
where 作用及用法
- 作用
- 用法
set 作用及用法
- 作用
- 用法
trim 作用及用法

概述

where set trim 這3個標簽解決了類似的問題，并且where 和 set 都屬于trim的一種具體用法，下面我們通過具體的示例來學習下。

where 作用及用法

作用

作用：如果該標簽包含的元素中有返回值，就插入一個where ，如果where后面的字符串以AND 和 OR 開頭的，就將他們剔除。

用法

我們使用動態SQL if 元素中的例子，點擊跳轉到該示例

不使用where元素的SQL如下：

<select id="selectSysUsersAdvanced" 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 1=1<if test="userName != null and userName != '' ">and user_name like concat('%',#{userName},'%')</if><if test="userEmail != null and userEmail != '' ">and user_email = #{userEmail}</if></select>

我們在這里通過where元素來實現一遍

<select id="selectSysUsersAdvancedWithWhere" 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 a<where><if test="userName != null and userName != '' ">and user_name like concat('%',#{userName},'%')</if><if test="userEmail != null and userEmail != '' ">and user_email = #{userEmail}</if></where></select>

當if 條件都不滿足的時候where元素中沒有內容，所以SQL中不會出現Where。如果if條件滿足，where元素的內容就是以and開頭的條件，where會自動去掉開頭的and ,這也能保證where條件的正確。相比上個案例，這種情況下生成的SQL更干凈，不會在任何情況下都有where 1 = 1 這樣的條件。

增加接口方法

/*** * * @Title: selectSysUsersAdvancedWithWhere* * @Description: selectSysUsersAdvancedWithWhere* * @param sysUser* @return* * @return: List<SysUser>*/List<SysUser> selectSysUsersAdvancedWithWhere(SysUser sysUser);

單元測試

@Testpublic void selectSysUsersAdvancedWithWhere() {logger.info("selectSysUsersAdvancedWithWhere");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();List<SysUser> userList = null;try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);logger.info("===========1.當用戶只輸入用戶名時，需要根據用戶名模糊查詢===========");// 模擬前臺傳參 1.當用戶只輸入用戶名時，需要根據用戶名模糊查詢SysUser sysUser = new SysUser();sysUser.setUserName("ad");// 調用selectSysUsersAdvanced,根據查詢條件查詢用戶userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);// 根據數據庫sys_user表中的記錄,可以匹配到admin, 期望userList不為空Assert.assertNotNull(userList);// 根據查詢條件，期望只有1條數據Assert.assertTrue(userList.size() == 1);Assert.assertEquals("admin", userList.get(0).getUserName());logger.info("userList:" + userList);// 為了測試匹配多條記錄的情況，我們將id=1001這條數據的userName 由test 改為artisansysUser.setUserName("i");// 調用selectSysUsersAdvanced,根據查詢條件查詢用戶userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);// 根據數據庫sys_user表中的記錄,可以匹配到admin和artisan, 期望userList不為空Assert.assertNotNull(userList);// 根據查詢條件，期望只有2條數據Assert.assertTrue(userList.size() == 2);logger.info("userList:" + userList);logger.info("===========2.當用戶只輸入郵箱使，根據郵箱進行完全匹配===========");// 模擬前臺傳參 2.當用戶只輸入郵箱使，根據郵箱進行完全匹配sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);Assert.assertNotNull(userList);Assert.assertTrue(userList.size() == 1);logger.info(userList);sysUser.setUserEmail("1admin@artisan.com");userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);Assert.assertTrue(userList.size() == 0);logger.info("===========3.當用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶===========");// 模擬組合查詢條件,存在記錄的情況sysUser.setUserName("i");sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);Assert.assertNotNull(userList);Assert.assertEquals("admin@artisan.com", sysUser.getUserEmail());Assert.assertTrue(userList.size() == 1);logger.info(userList);logger.info("===========4.當用戶同時輸入無法匹配的用戶名和密碼===========");// 模擬組合查詢條件,不存在記錄的情況sysUser.setUserName("x");sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);Assert.assertTrue(userList.size() == 0);logger.info(userList);} catch (Exception e) {e.printStackTrace();} finally {sqlSession.close();logger.info("sqlSession close successfully ");}}

日志

2018-04-21 21:32:11,318 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-21 21:32:11,323 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-21 21:32:11,327 INFO [main] (UserMapperTest.java:601) - selectSysUsersAdvancedWithWhere 2018-04-21 21:32:11,357 INFO [main] (UserMapperTest.java:610) - ===========1.當用戶只輸入用戶名時，需要根據用戶名模糊查詢=========== 2018-04-21 21:32:11,962 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 user_name like concat('%',?,'%') 2018-04-21 21:32:12,046 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: ad(String) 2018-04-21 21:32:12,081 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-21 21:32:12,082 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:32:12,091 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-21 21:32:12,096 INFO [main] (UserMapperTest.java:622) - userList:[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]] 2018-04-21 21:32:12,096 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 user_name like concat('%',?,'%') 2018-04-21 21:32:12,097 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String) 2018-04-21 21:32:12,098 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-21 21:32:12,099 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:32:12,100 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:32:12,102 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 2 2018-04-21 21:32:12,104 INFO [main] (UserMapperTest.java:633) - userList:[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-21 21:32:12,105 INFO [main] (UserMapperTest.java:635) - ===========2.當用戶只輸入郵箱使，根據郵箱進行完全匹配=========== 2018-04-21 21:32:12,105 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 user_name like concat('%',?,'%') and user_email = ? 2018-04-21 21:32:12,106 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String) 2018-04-21 21:32:12,107 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-21 21:32:12,108 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:32:12,109 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-21 21:32:12,109 INFO [main] (UserMapperTest.java:641) - [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]] 2018-04-21 21:32:12,110 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 user_name like concat('%',?,'%') and user_email = ? 2018-04-21 21:32:12,111 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), 1admin@artisan.com(String) 2018-04-21 21:32:12,112 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-21 21:32:12,113 INFO [main] (UserMapperTest.java:647) - ===========3.當用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶=========== 2018-04-21 21:32:12,113 INFO [main] (UserMapperTest.java:655) - [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]] 2018-04-21 21:32:12,114 INFO [main] (UserMapperTest.java:657) - ===========4.當用戶同時輸入無法匹配的用戶名和密碼=========== 2018-04-21 21:32:12,115 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 user_name like concat('%',?,'%') and user_email = ? 2018-04-21 21:32:12,116 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: x(String), admin@artisan.com(String) 2018-04-21 21:32:12,117 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-21 21:32:12,117 INFO [main] (UserMapperTest.java:663) - [] 2018-04-21 21:32:12,119 INFO [main] (UserMapperTest.java:669) - sqlSession close successfully

set 作用及用法

作用

作用：如果該標簽包含的元素中有返回值，就插入一個set, 如果set后面的字符串是以逗號結尾的，就將這個逗號剔除。

用法

我們來改下動態SQL if 元素中的例子跳轉到改示例

修改之前的SQL如下

<update id="updateSysUserByIdSelective">update sys_user set<if test="userName != null and userName != ''">user_name = #{userName},</if><if test="userPassword != null and userPassword != ''">user_password = #{userPassword},</if><if test="userEmail != null and userEmail != ''">user_email = #{userEmail},</if><if test="userInfo != null and userInfo != ''">user_info = #{userInfo},</if><if test="headImg != null">head_img = #{headImg, jdbcType=BLOB},</if><if test="createTime != null">create_time = #{createTime, jdbcType=TIMESTAMP},</if>id = #{id}where id = #{id}</update>

使用set改造后的SQL如下

<update id="updateSysUserByIdWithSetSelective">update sys_user <set><if test="userName != null and userName != ''">user_name = #{userName},</if><if test="userPassword != null and userPassword != ''">user_password = #{userPassword},</if><if test="userEmail != null and userEmail != ''">user_email = #{userEmail},</if><if test="userInfo != null and userInfo != ''">user_info = #{userInfo},</if><if test="headImg != null">head_img = #{headImg, jdbcType=BLOB},</if><if test="createTime != null">create_time = #{createTime, jdbcType=TIMESTAMP},</if>id = #{id},</set>where id = #{id}</update>

增加接口方法

/*** * * @Title: updateSysUserByIdWithSetSelective* * @Description: 根據主鍵更新SysUser* * @param sysUser* @return* * @return: int*/int updateSysUserByIdWithSetSelective(SysUser sysUser);

單元測試

@Testpublic void updateSysUserByIdWithSetSelectiveTest() {logger.info("updateSysUserByIdWithSetSelective");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);// 先根據ID查詢出對應的sysuserSysUser sysUser = userMapper.selectSysUserById((long) 1);// 當前數據庫用戶的userName期望為adminAssert.assertEquals("admin", sysUser.getUserName());// 修改用戶名sysUser.setUserName("dynamicUpdate");// 修改郵件sysUser.setUserEmail("dynamicUpdate@artisan.com");// 修改用戶 ,返回受影響的行數int result = userMapper.updateSysUserByIdWithSetSelective(sysUser);// 只插入一條數據 ,期望是1Assert.assertEquals(1, result);logger.info("受影響的行數:" + result);// 重新查詢（雖然未提交但是在一個會話中）sysUser = userMapper.selectSysUserById((long) 1);// 期望的用戶名為dynamicUpdateAssert.assertEquals("dynamicUpdate", sysUser.getUserName());// 期望的郵箱為dynamicUpdate@artisan.comAssert.assertEquals("dynamicUpdate@artisan.com", sysUser.getUserEmail());// 檢查其他字段有沒有被更新為null 或者空值Assert.assertEquals("123456", sysUser.getUserPassword());Assert.assertEquals("管理員用戶", sysUser.getUserInfo());logger.info(sysUser);} catch (Exception e) {e.printStackTrace();} finally {// 為了保持測試數據的干凈，這里選擇回滾// 由于默認的sqlSessionFactory.openSession()是不自動提交的// 除非顯式的commit，否則不會提交到數據庫sqlSession.rollback();logger.info("為了保持測試數據的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成");sqlSession.close();logger.info("sqlSession close successfully ");}}

日志

2018-04-21 21:30:34,774 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-21 21:30:34,779 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-21 21:30:34,783 INFO [main] (UserMapperTest.java:675) - updateSysUserByIdWithSetSelective 2018-04-21 21:30:35,425 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-21 21:30:35,509 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long) 2018-04-21 21:30:35,551 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-21 21:30:35,552 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:30:35,560 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-21 21:30:35,621 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: update sys_user SET user_name = ?, user_password = ?, user_email = ?, user_info = ?, head_img = ?, create_time = ?, id = ? where id = ? 2018-04-21 21:30:35,627 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: dynamicUpdate(String), 123456(String), dynamicUpdate@artisan.com(String), 管理員用戶(String), java.io.ByteArrayInputStream@1969526c(ByteArrayInputStream), 2018-04-13 21:12:47.0(Timestamp), 1(Long), 1(Long) 2018-04-21 21:30:35,629 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1 2018-04-21 21:30:35,629 INFO [main] (UserMapperTest.java:696) - 受影響的行數:1 2018-04-21 21:30:35,630 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-21 21:30:35,631 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long) 2018-04-21 21:30:35,632 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-21 21:30:35,633 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, dynamicUpdate, 123456, dynamicUpdate@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-21 21:30:35,636 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-21 21:30:35,637 INFO [main] (UserMapperTest.java:707) - SysUser [id=1, userName=dynamicUpdate, userPassword=123456, userEmail=dynamicUpdate@artisan.com, userInfo=管理員用戶, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018] 2018-04-21 21:30:35,644 INFO [main] (UserMapperTest.java:716) - 為了保持測試數據的干凈，這里選擇回滾,不寫入mysql,請觀察日志，回滾完成 2018-04-21 21:30:35,646 INFO [main] (UserMapperTest.java:719) - sqlSession close successfully

trim 作用及用法

where 和 set 標簽的功能都可以用trim標簽實現，并且在底層就是通過TrimSqlNode 實現的。

where 標簽對應的trim的實現如下

這里”ADN |OR ” 后面的空格不能省略，為了避免匹配到 andes 、orders 等單詞。

set 標簽對應的trim的實現如下

trim標簽屬性

屬性名含義

prefix	當trim元素包含內容時，會給內容增加prefix指定的前綴
prefixOverrides	當trim元素包含內容時，會把內容中匹配的前綴字符串去掉
suffix	當trim元素包含內容時，會給內容增加suffix指定的后綴
suffixOverrides	當trim元素包含內容時，會把內容中匹配的后綴字符串去掉

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的MyBatis-13MyBatis动态SQL之【where、set、trim】的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： MyBatis-12MyBatis动态S
下一篇： MyBatis-14MyBatis动态S