概述
用法
示例
bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。
用法
我们用之前的一个例子作为讲解
<select id="selectSysUsersAdvancedWithWhere" resultType="com.artisan.mybatis.xml.domain.SysUser">
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>
<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">
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>
<if test="userName != null and userName != '' ">
<!-- and user_name like concat('%',#{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
<select id="selectSysUserByAdvancedCondition" resultType="com.artisan.mybatis.xml.domain.SysUser">
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>
<if test="userName != null and userName != '' ">
<!-- and user_name like concat('%',#{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> 3.单元测试
@Test
public void selectSysUserByAdvancedConditionTest() {
logger.info("selectSysUserByAdvancedConditionTest");
// 获取SqlSession
SqlSession 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 改为artisan
sysUser.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
