07_动态SQL与模糊查询

3 ConditionUser(查询条件实例类）

private String name;
private int minAge;
private int maxAge;

4 userMapper.xml(映射文件)

<mapper namespace="cn.imentors.mybatis.test7.userMapper">
	<!-- 
	实现多条件查询用户(姓名模糊匹配, 年龄在指定的最小值到最大值之间)
	 -->
	<select id="getUser" parameterType="ConditionUser" resultType="User">
		select * from d_user where 
		<if test=‘name != "%null%"‘>
			 name like #{name} and 
		</if>
		age between #{minAge} and #{maxAge}
	</select>
</mapper>

5 测试

@Test
	public void test(){
		SqlSessionFactory factory = MybatisUtils.getFactory();
		SqlSession session = factory.openSession();
		
		String statement = "cn.imentors.mybatis.test7.userMapper.getUser";
		
		String name = "o";
		name = null;
		
		ConditionUser parameter = new ConditionUser("%"+name+"%", 13, 18);
		List<User> list = session.selectList(statement, parameter);
		System.out.println(list);
		
		
		session.close();

6 MyBatis中可用的动态SQL标签

6.1 if

6.2 choose

<select id=”findActiveBlogLike”parameterType=”Blog” resultType=”Blog”>
    SELECT * FROM BLOG WHERE state = ?ACTIVE?
    <choose>
        <when test=”title != null”>
           AND title like #{title}
       </when>
       <when test=”author != null and author.name != null”>
           AND title like #{author.name}
       </when>
    <otherwise>
        AND featured = 1
    </otherwise>
  </choose>
</select>

6.3 where

<select id=”findActiveBlogLike” parameterType=”Blog” resultType=”Blog”>
    SELECT * FROM BLOG
<where>
<if test=”state != null”>
    state = #{state}
</if>
<if test=”title != null”>
    AND title like #{title}
</if>
<if test=”author != null and author.name != null”>
   AND title like #{author.name}
</if>
</where>
</select>

6.4 set

<update id="updateAuthorIfNecessary"
parameterType="domain.blog.Author">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>

6.5 foreach

<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>

07_动态SQL与模糊查询

标签：