MyBatis-动态SQL语句Where、Choose、Set、Foreach

• 1.教师Dao

package com.edu.dao;
import java.util.List;
import com.edu.bean.Teacher;
public interface TeacherDao {
	public Teacher geTeacherById(Integer id);
	public List<Teacher> getTeachersLike(Teacher teacher);
	/**
	 * birth\name\course\id=1
	 * @param teacher
	 * @return
	 */
	public List<Teacher> chooseTeachersLike(Teacher teacher);
	public int updateTeacher(Teacher teacher);
		public List<Teacher> getTeachersByIdIn(@Param("ids")List<Integer> ids);
}

• 2.Dao xml配置

a.若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除
b.不想使用所有的条件，而只是想从多个条件中选择一个使用,此时选择choose，经测3.5.0版本choose中不能加and
c.set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.edu.dao.TeacherDao">
  	<select id="getTeachersLike" resultType="com.edu.bean.Teacher">
  		select * from t_teachers 
  		<where>
  			<if test="birth!=null">
  				and #{birth} > birth
  			</if>
	  		<if test="name!=null and name!=''">
	  			and name like #{name}
	  		</if>
	  		<if test="course!=null and course!=''">
	  			and course like #{course}
	  		</if>
  		</where>
  	</select>
  	
  	<select id="chooseTeachersLike" resultType="com.edu.bean.Teacher">
  		select * from t_teachers where
  		<choose>
  			<when test="birth!=null">
  				#{birth}>birth
  			</when>
  			<when test="name!=null and name!=''">
  				name like #{name}
  			</when>
  			<when test="course!=null and course!=''">
  				course like #{course}
  			</when>
  			<otherwise>
  				id = 1
  			</otherwise>
  		</choose>
  	</select>
  	<update id="updateTeacher">
  		update t_teachers 
  		<set>
	  		<if test="name!=null and name!=''">name=#{name},</if>
	  		<if test="course!=null and course!=''">course=#{course},</if>
	  		<if test="birth!=null">birth=#{birth},</if>
  		</set>
  		where id=#{id}
  	</update>
  	  	<select id="getTeachersByIdIn" resultType="com.edu.bean.Teacher">
  		select * from t_teachers where id in
  		<!-- collection 遍历指定集合的key， 
  			seperator 每次遍历元素的分隔符
  			item 每个遍历出来的元素的变量名
  			open 自动拼接集合时，以什么开始
  			close 自动拼接集合时，以什么结束
  			index 遍历索引，如果是List，那么index就是角标整数
  						 如果是Map，那么index就是key，item就是对应的value
  		-->
  		<foreach collection="ids" item="id_item" separator="," open="(" close=")">
  			#{id_item}
  		</foreach>
  	</select>
  </mapper>

• 3.测试代码

@Test
	public void TestDynamicSQL1() {
		SqlSession sqlSession = factory.openSession();
		TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
		Teacher teacher = new Teacher(null, "%r%", "%c%", new Date());
		List<Teacher> teachersLike = teacherDao.getTeachersLike(teacher);
		teachersLike.forEach(System.out::println);
	}
	//在前面的choose语句中name 的优先级更高，所以course不会被设置
	@Test
	public void TestDynamicSQL2() {
		SqlSession sqlSession = factory.openSession();
		TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
		Teacher teacher = new Teacher();
		teacher.setName("%er%");
		teacher.setCourse("%j%");
		List<Teacher> teachersLike = teacherDao.chooseTeachersLike(teacher);
		teachersLike.forEach(System.out::println);
	}
	//course设置为了空串儿，无法通过sql的条件判断，所以不会被设置
	@Test
	public void TestDynamicSQL3() {
		SqlSession sqlSession = factory.openSession();
		TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
		Teacher teacher = new Teacher();
		teacher.setId(3);
		teacher.setName("Merr");
		teacher.setCourse("");
		teacherDao.updateTeacher(teacher);
		sqlSession.commit();
		sqlSession.close();
	}

4.bind经常用于模糊查询

...
<bind name="coursePattern" value="'%'+course+'%'"/>
<if test="name !=null and name!=''">
	and course like #{coursePattern}
</if>
...

5.sql抽取可重用的sql语句

<sql id="selectSql">
	select * from t_teachers where
</sql>

<select id ="getTeacherById" resultMap="teacherMap">
	<include refid="selectSql"></include>
</select>

6.补充：

OGNL表达式中两个隐含的变量：
_parameter：
1）如果传入的是单个参数，那么_parameter就代表这个参数
2）如果传入的是多个参数，那么_parameter就代表这些参数组合起来的Map

_databaseId:
需要提前在mybatis全局xml环境中设置数据库厂商的名字和ID

	<databaseIdProvider type="DB_VENDOR">
		<property name="MySQL" value="mysql"/>
		<property name="Oracle" value="orcl"/>
	</databaseIdProvider>

使用：

<if test ="_databaseId='mysql'" >
巴拉巴拉
</if>
<if test ="_databaseId='orcl" >
巴拉巴拉
</if>