MyBatis 动态Sql

• 动态Sql
• where-if标签
• set-if标签
• foreach标签
• choose、when、otherwise

动态Sql

通过MyBatis提供的各种标签方法实现动态拼接Sql。
这些标签类似于JSTL标签，可以写控制语句动态的拼接Sql。

where-if标签

用于查找语句。
如果输入的时候添加了属性，if标签生效；如果if标签里的内容是添加的第一个会自动把其中的and去掉。

@Test
    public void testIfWhere(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Student student = new Student();
        student.setName("张");
        student.setAge(23);
        student.setGender("女");
        List<Student> list = sqlSession.selectList("student.selectByCondition",student);
        for (Student student1 : list) {
            System.out.println(student1);
        }
    }

<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
		select
		<include refid="studentColumn"/>
		from
		student
		<where>
			<if test="name!=null and name!='' ">
				and name like concat('%',#{name},'%')
			</if>
			<if test="age!=null">
				and age=#{age}
			</if>
			<if test="gender!=null and gender!='' ">
				and gender=#{gender}
			</if>
		</where>
	</select>

set-if标签

用于更新语句。
与whwere-if相似，如果输入的时候添加了属性，if标签生效；如果if标签里的内容是添加的最后一个会自动把后面的“，”去掉。

<select id="updateByCondition" parameterType="Student">
		update
			student
		<set>
			<if test="name != null and name != ''">
				name = #{name},
			</if>
			<if test="age != null">
				age = #{age},
			</if>
			<if test="gender != null and gender != ''">
				gender = #{gender},
			</if>
		</set>
		where
			id = #{id}
	</select>

@Test
    public void testUpdateByCondition(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        Student student = new Student();
        student.setId(31);
        student.setName("白七1");
        student.setAge(33);
        student.setGender("nan");
        int count = sqlSession.update("student.updateByCondition",student);
        System.out.println(count);
        sqlSession.commit();
    }

foreach标签

处理数组或者集合，MyBatis使用foreach标签解析。

<delete id="deleteAllByArray">
		DELETE FROM `student`
		WHERE id in
		<foreach collection="array" open="(" close=")" item="id" separator=",">
			#{id}
		</foreach>
	</delete>

	<delete id="deleteAllByList">
		DELETE FROM `student`
		WHERE id in
		<foreach collection="list" open="(" close=")" item="id" separator=",">
			#{id}
		</foreach>
	</delete>

@Test
    public void testDeleteAllByArray() {
        int[] array = {66};
        SqlSession sqlSession = MyBatisUtil.getSqlSession();;
        int count = sqlSession.update("student.deleteAllByArray", array);
        System.out.println("count: " + count);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testDeleteAllByList() {
        List<Integer> list = new ArrayList<Integer>();
        list.add(68);
        list.add(69);
        SqlSession sqlSession = MyBatisUtil.getSqlSession();;
        int count = sqlSession.update("student.deleteAllByList", list);
        System.out.println("count: " + count);
        sqlSession.commit();
        sqlSession.close();
    }

choose、when、otherwise

相当于 if----else if----else if ----else
如果用户填写了名字，这个搜索就按照名字来搜索，你即使填写了别的条件也不去拼接，
如果没有填写名字，填写了年龄，就按照年龄来搜索，
如果名字和年龄都没有填写，但是填写了性别就按照性别来查找。
name、age、gender搜索时候只能按照其中一个来搜索，但是优先级name>age>gender。

<select id="selectByCondition" parameterType="Student" resultMap="studentMap">
   SELECT
      <include refid="studentColumns"/>
   FROM
      `student`
   WHERE
      <choose>
         <when test="name!=null and name!=''">
            name LIKE concat('%', #{name}, '%')
         </when>
         <when test="age!=null">
            age=#{age}
         </when>
         <when test="gender!=null and gender!=''">
            gender=#{gender}
         </when>
         <otherwise>
         </otherwise>
      </choose>
</select>

@Test
public void testSelectByCondition() {
   Student student = new Student();
   student.setName("王");
   student.setAge(23);
   student.setGender("女");
   List<Student> list = studentMapper.selectByCondition(student);
   for (Student s : list) {
      System.out.println(s);
   }
}