Mybatis 动态sql

1. <if>

public List<Employee> getEmpsByConditionIf();

<select id="getEmpsByConditionIf" resultType="...Employee">
	select * from employee
	where 1=1
	<!--test 判断表达式  OGNL-->
	<!--从参数中取值进行判断-->
	<if test="id!=null">
		and id=#{id}
	</if>
	<if test="lastName!=null and lastName!=''"> <!--&quot;&quot;-->
		and last_name like #{lastName}
	</if>
	<if test="email!=null and email.trim()!=''"> <!--&quot;&quot;-->
		and email=#{email}
	</if>
	<!--ognl会对数字和字符串进行转换-->
	<if test="gender==0 or gender==1">
		and gender=#{gender}
	</if>
</select>

2. <where>

只会去掉第一个多出来的 and

<select id="getEmpsByConditionIf" resultType="...Employee">
	select * from employee
	where 1=1
	<where>
		<if test="id!=null">
			and id=#{id}
		</if>
		...
	</where>
</select>

3. <trim>

<select id="getEmpsByConditionIf" resultType="...Employee">
	select * from employee
	<!--prefix 给拼接玩的结果加一个前缀-->
	<!--prefixOverrides 去掉前面指定的多余字符-->
	<!--suffix 给拼接完的结果加一个后缀-->
	<!--suffixOverrides 去掉后面指定的多余字符-->
	<trim prefix="where" suffixOverrides="and" >
		<if test="id!=null">
			id=#{id} and
		</if>
		...
	</trim>
</select>

4. <choose>

分支选择

<select id="getEmpsByConditionIf" resultType="...Employee">
	select * from employee
	where
	<where>
		<choose>
			<when test="id!=null">
				and id=#{id}
			</when>
			...
			<otherwise>
				and gender=0
			</otherwise>
		</choose>
	</where>
</select>

5. <set>

封装修改条件

<update id="uptEmp">
	update employee 
	<set>
		<if test="id!=null">
			id=#{id}
		</if>
	</set>
</update>

或者用<trim>

<trim prefix="set" suffixOverrides=",">
</trim>

6. <foreach>

<select id="getEmpsByConditionForeach" resultType="...Employee">
	select * from employee
	<!--collection指定要遍历的集合-->
	<!--list类型的参数会特殊处理封装在map中，map的key就叫list-->
	<!--item 将当前便利出的元素赋值给指定变量-->
	<!--separator 每个元素间的分隔符-->
	<!--open 遍历出所有结果拼接一个开始的字符-->
	<!--close 遍历出所有结果拼接一个结束的字符-->
	<!--index 索引；遍历map的时候是key；list是索引-->
	<foreach collection="ids" item="item_id" separator="," open="where id in (" close=")">
		#{item_id}
	</foreach>
</select>

批量保存

public void addEMps(@Param("emps") List<Employee> emps);

6.1 mysql批量保存

<insert id="addEMps">
	insert into employee (last_name, email, gender, d_id)
	values
	<!---->
	<foreach collection="emps" item="emp" separator=",">
		(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
	</foreach>
</insert>

或者 在 jdbc.url=jdbc:mysql://…:3306/db?allowMultiQueries=true 开启

<insert id="addEMps">
	<!--需要数据库连接属性allowMultiQueries=true-->
	<foreach collection="emps" item="emp" separator=";">
	insert into employee (last_name, email, gender, d_id)
	values
		(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
	</foreach>
</insert>

6.2 oracle批量保存
不支持values(), (), ()…

<insert id="addEMps">
	<foreach collection="emps" item="emp" separator=";" open="begin" close="end;">
	insert into employee (id, last_name, email, gender, d_id)
	values
		(employee_seq.nextval,#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
	</foreach>
</insert>

或

<insert id="addEMps">
	insert into employee (last_name, email, gender, d_id)
	<foreach collection="emps" item="emp" separator="union" open="select employee_seq.nextval，last_name,email, d_id from (" close=")">
		select #{emp.lastName} last_name,#{emp.email} email,#{emp.gender} gender,#{emp.dept.id} d_id from dual
	</foreach>
</insert>

7. 内置参数

• _parameter：代表整个参数
  • 单个参数：_parameter就是这个参数
  • 多个参数：参数会被封装为一个map，_parameter就是这个map
• _databaseId：如果配置了databaseIdProvider标签，_databaseId代表当前数据库别名

<!--测试-->
<select id="" returnType="">
	<if test="_databaseId=='mysql'">
		select ... from employee
		<if test="_parameter!=null">
			where last_name=#{_parameter.lastName}
		</if>
	</if>
	<if test="_databaseId=='oracle'">
		select ...
	</if>
</select>

7. <bind>

like %#{}% 报错
like %${}% 不报错

<select id="" returnType="">
<!--bind 将ognl表达式的值绑定到一个变量中 方便后来引用-->
	<bind name="_lastName" value="'%' + lastName + '%'" />
	<if test="_databaseId=='mysql'">
		select ... from employee
		<if test="_parameter!=null">
			where last_name like #{_lastName}
		</if>
	</if>
	<if test="_databaseId=='oracle'">
		select ...
	</if>
</select>

8. <sql>

抽取可重用的sql片段，方便后来引用

<sql id="musql">
	<if test="_databaseId=='mysql'">
	</if>
</sql>

<!--引用-->
<include refid="musql" />
<!--include 还可以自定义property，sql标签内部可以使用${prop}获得,不能用#{}-->
<include refid="musql">
	<property name="prop" value="abc" /> 
</include>