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

数据库

MyBatis-15MyBatis动态SQL之【bind】

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

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

概述
用法
示例
- 1.增加接口方法
- 2.配置動態SQL
- 3.單元測試

概述

bind標簽可以使用OGNL表達式創建一個變量并將其綁定到上下文中。

用法

我們用之前的一個例子作為講解

<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>

使用concat函數連接字符串，在MySQL中，這個函數支持多個參數，但是在Oracle中只支持兩個參數。由于不同數據庫之間的語法差異，如果更換了數據庫，有些SQL語句可能就需要重寫。針對這種情況，可以使用bind標簽來避免由于更換數據庫帶來的一些麻煩。我們將上面的語句改為bind方式，如下

<select id="selectSysUserByAdvancedCondition" 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 != '' "><bind name="userNameLike" value=" '%' + userName + '%' "/>and user_name like #{userNameLike}</if><if test="userEmail != null and userEmail != '' ">and user_email = #{userEmail}</if></where></select>

bind標簽的兩個屬性都是不選項，name為綁定到上下文的變量名，value為OGNL表達式，創建一個bind標簽后，就可以在下面直接使用了。使用bind拼接字符串不僅可以避免因更換數據庫而修改SQL，也能預防SQL注入。

示例

1.增加接口方法

/*** * * @Title: selectSysUserByAdvancedCondition* * @Description: 演示bind用法* * @param sysUser* @return* * @return: List<SysUser>*/List<SysUser> selectSysUserByAdvancedCondition(SysUser sysUser);

2.配置動態SQL

3.單元測試

@Testpublic void selectSysUserByAdvancedConditionTest() {logger.info("selectSysUserByAdvancedConditionTest");// 獲取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");// 調用selectSysUserByAdvancedCondition,根據查詢條件查詢用戶userList = userMapper.selectSysUserByAdvancedCondition(sysUser);// 根據數據庫sys_user表中的記錄,可以匹配到admin, 期望userList不為空Assert.assertNotNull(userList);// 根據查詢條件，期望只有1條數據Assert.assertTrue(userList.size() == 1);logger.info("userList:" + userList);// 為了測試匹配多條記錄的情況，我們將id=1001這條數據的userName 由test 改為artisansysUser.setUserName("i");// 調用selectSysUserByAdvancedCondition,根據查詢條件查詢用戶userList = userMapper.selectSysUserByAdvancedCondition(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.selectSysUsersAdvanced(sysUser);Assert.assertNotNull(userList);Assert.assertTrue(userList.size() == 1);logger.info(userList);sysUser.setUserEmail("1admin@artisan.com");userList = userMapper.selectSysUserByAdvancedCondition(sysUser);Assert.assertTrue(userList.size() == 0);logger.info("===========3.當用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶===========");// 模擬組合查詢條件,存在記錄的情況sysUser.setUserName("i");sysUser.setUserEmail("admin@artisan.com");userList = userMapper.selectSysUserByAdvancedCondition(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.selectSysUserByAdvancedCondition(sysUser);Assert.assertTrue(userList.size() == 0);logger.info(userList);} catch (Exception e) {e.printStackTrace();} finally {sqlSession.close();logger.info("sqlSession close successfully ");}}

日志

2018-04-24 20:57:44,130 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-24 20:57:44,133 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-24 20:57:44,137 INFO [main] (UserMapperTest.java:861) - selectSysUserByAdvancedConditionTest 2018-04-24 20:57:44,155 INFO [main] (UserMapperTest.java:870) - ===========1.當用戶只輸入用戶名時，需要根據用戶名模糊查詢=========== 2018-04-24 20:57:44,840 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 ? 2018-04-24 20:57:44,931 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %ad%(String) 2018-04-24 20:57:44,983 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-24 20:57:44,985 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-24 20:57:44,995 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-24 20:57:45,001 INFO [main] (UserMapperTest.java:880) - 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-24 20:57:45,002 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 ? 2018-04-24 20:57:45,004 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String) 2018-04-24 20:57:45,006 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-24 20:57:45,006 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-24 20:57:45,008 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-24 20:57:45,010 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 2 2018-04-24 20:57:45,012 INFO [main] (UserMapperTest.java:891) - 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-24 20:57:45,012 INFO [main] (UserMapperTest.java:893) - ===========2.當用戶只輸入郵箱使，根據郵箱進行完全匹配=========== 2018-04-24 20:57:45,013 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 1=1 and user_name like concat('%',?,'%') and user_email = ? 2018-04-24 20:57:45,014 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String) 2018-04-24 20:57:45,016 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-24 20:57:45,016 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-24 20:57:45,017 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-24 20:57:45,018 INFO [main] (UserMapperTest.java:899) - [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-24 20:57:45,018 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 ? and user_email = ? 2018-04-24 20:57:45,019 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String), 1admin@artisan.com(String) 2018-04-24 20:57:45,020 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-24 20:57:45,021 INFO [main] (UserMapperTest.java:905) - ===========3.當用戶同時輸入用戶名和密碼時，用這兩個條件查詢匹配的用戶=========== 2018-04-24 20:57:45,022 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 ? and user_email = ? 2018-04-24 20:57:45,023 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %i%(String), admin@artisan.com(String) 2018-04-24 20:57:45,024 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-24 20:57:45,024 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0 2018-04-24 20:57:45,025 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-24 20:57:45,026 INFO [main] (UserMapperTest.java:914) - [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-24 20:57:45,026 INFO [main] (UserMapperTest.java:916) - ===========4.當用戶同時輸入無法匹配的用戶名和密碼=========== 2018-04-24 20:57:45,027 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 ? and user_email = ? 2018-04-24 20:57:45,028 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: %x%(String), admin@artisan.com(String) 2018-04-24 20:57:45,029 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0 2018-04-24 20:57:45,029 INFO [main] (UserMapperTest.java:922) - [] 2018-04-24 20:57:45,031 INFO [main] (UserMapperTest.java:928) - sqlSession close successfully

總結

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

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

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