7. 动态SQL
当映射文件中sql语句为:select * from `user` where username like '%${username}%' and sex='#{sex}'时,传入的参数没有经过验证,有可能会出现null或者‘’的情况,因此,结果会偏离我们的预期,所以我们要动态的使用sql,以避免此种情况。
7.1 IF标签
if标签相当于sql语句中的if关键字
作用:根据传入参数的情况,拼装sql语句
1. UserMapper.java
List<User> queryUserByUsernameAndSex(User user);
2. UserMapper.xml
<select id="queryUserByUsernameAndSex" parameterType="user" resultType="user">
select * from user where
<!-- if标签:判断用户名称不为空,且不为空字符串,才作为查询条件 -->
<if test="username != null and username != ''">
username like CONCAT('%', #{username}, '%')
</if>
<!-- if标签:判断用户性别不为空,且不为空字符串,才作为查询条件 -->
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</select>
3. 测试
@Test
public void queryUserByUsernameAndSex() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("Y");
List<User> users = userMapper.queryUserByUsernameAndSex(user);
System.out.println(users);
sqlSession.close();
}
4. 结果
7.2 Where标签
where标签相当于sql语句中的where关键字
作用:
- 根据传入的参数情况,智能的去掉多余的where关键字
- 根据传入的参数情况,智能的去掉多余的and,or关键字
1. UserMap.xml
<select id="queryUserByUsernameAndSex" parameterType="user" resultType="user">
select * from user <!-- where -->
<where>
<!-- if标签:判断用户名称不为空,且不为空字符串,才作为查询条件 -->
<if test="username != null and username != ''">
username like CONCAT('%', #{username}, '%')
</if>
<!-- if标签:判断用户性别不为空,且不为空字符串,才作为查询条件 -->
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
2. 测试
@Test
public void queryUserByUsernameAndSex() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
// user.setUsername("Y");
List<User> users = userMapper.queryUserByUsernameAndSex(user);
System.out.println(users);
sqlSession.close();
}
结果:
7.3 Set标签
Set标签相当于sql语句中的set关键字
作用:根据传入的参数情况,智能的去掉多余的逗号:“,”
1. UserMapper.java
void update(User user);
2. UserMapper.xml
<update id="update" parameterType="user" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
update user
<set>
<if test="username != null and username != ''&#