概述用法示例
1.增加接口方法2.配置动态SQL3.单元测试
概述
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(
'%',
</
if>
<
if test=
"userEmail != null and userEmail != '' ">
and user_email =
</
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 != '' ">
<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 != '' ">
<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:
