mybatis动态SQL语句

1建接口和映射文件
2在核心配置文件中一次读取多个mapper映射文件

//通过包扫描的形式一次性加载所有的mapper映射文件

if标签

SQL语句

<select id="findEmpByCondition" resuitType="emp">
	select *from emp where 1=1
	<if test="empno != null">
		and empno=#{empno}
	</if>
	<if test="ename != null and ename !=' ' ">
		and ename= #{ename}
	</if>
	<if test="job" !=null and job !=' ' >
		and job = #{job}
	</if>
	<if test="mgr != null">
		and mgr= #{mgr}
	</if>
	<if test="hiredate !=null">
		and hiredate = #{hiredate}
	</if>
	<if test=" sal != null">
		and sal = #{sal}
	</if>
	<if test="comm != null">
		<if test = "comm== 0">
			and(comm is null or comm = 0)
		</if>
		<if  test="comm != 0 ">
			and comm =#{comm}
		</if>			
	</if>
	<if test="deptno != null">
		and deptno = #{deptno}
	</if>
	</select>	

Where标签

用于处理where关键字和and

<select id = "findEmpByCondition" result Type="emp">
	select * from emp
	<where>
	<if test="empno != null">
		and empno = #{empno}
	</if>
	<if test="ename !=null and ename !='' ">
		 and ename = #{ename}
	</if>	
	<if test="job != null and job !='' ">
		and job = #{job}
	</if>
	<if test="mgr != null">
		and mgr = #{mgr}
	</if>
	<if test="hiredate != null">
		and hiredate = #{hiredate}
	</if>
	<if test="sal != null">
		and sal = #{sal}
	</if>
	<if test="comm != null">
		<if test = "comm == 0">
		 	and (comm is null or comm=0)
		</if>
		<if test="comm !=0">
			and comm = #{comm}
		</if>
	</if>
	<if test = "deptno !=null">
	        and deptno = #{deptno}
	</if>
	</where>
	</select>

Choose标签

前面的when条件成立 后面的when就不再判断了

<select id ="findEmpByCondition2" resultType="emp">
	select*from emp
	<where>
		<choose>
			<when test="empno != null">
				and empno= #{empno}
			</when>
			<when test="ename !=null and ename !='' ">
			 	and ename = #{ename}
			</when>
			<when test"job != null and job !='' ">
				and job = #{job}			
			</when>
			<when test="mgr != null">
				and mgr = #{mgr}
			</when>
			<when test="hiredate !=null">
				and hiredate = #{hiredate}
			</when>
			<when test="sal != null">
				and sal=#{sal} 
			</when>
			<when test = "comm != null">
				<if test="comm ==0">
					and(comm is null or comm = 0)
				</if>
				<if test="comm != 0 ">
					and comm =#{comm}
				</if>
			</when>
			<when test="deptno !=null">
				and deptno=#{deptno}
			</when>
		</choose>
	</where>
</select>

Set接口

接口
int updateEmp(Emp emp);

SQL语句

<update id="updateEmp">
	update emp
	<set>
		<if test="ename !=null and ename !='' ">
			sname=#{ename},
		</if>
		<if test="job !=null and job !='' ">
			job=#{job},
		</if>
		<if test="mgr !=null">
			mgr= #{mgr},
		</if>
		<if test="hiredate !=null">
			hiredate=#{hiredate},
		</if>
		<if test="sal != null">
			sal = #{sal},
		</if>
		<if test="comm != null">
			comm=#{comm},
		</if>
		<if test="deptno !=null">
			deptno=#{deptno}
		</if>	
	</set>
	where empno =#{empno}
</update>

Trim 标签

Trim 标签处理set接口

接口
int updateEmp2(Emp emp);
SQL语句

<update id = "updateEmp2">
	update emp
	//prefix 要增加什么前缀
	//prefixOverrides要去除什么前缀
	//suffix要增加什么后缀
	//suffixOverrides要去除什么后缀
	//set是trim的一种特殊情况
	<trim prefix="set" suffixOverrides=",">
		<if test="ename !=null and ename !='' ">
			ename = #{ename},
		</if>
		<if test="job != null and job !='' ">
			job = #{job},
		</if>
		<if test="mgr != null">
			mgr= #{mgr}.
		</if>
		<if test="hiredate !=null">
			hiredate = #{hiredate}
		</if>
		<if test="sal !=null">
			sal= #{sal}
		</if>
		<if test="comm != null">
			comm =#{comm}
		</if>
		<if test="deptno != null">
			deptno =#{deptno}
		</if>
	</trim>
	where empno = #{empno}
</update>

Trim标签 处理where

<select id="findEmpByCondition" resultType="emp">
	select *from emp
		<trim prefix="where" prefixOverrides="and">
			<if test="empno !=null">
					and empno = #{empno}
			</if>
			<if test="job !=null and job !=' ' ">
					and job= #{job}
			</if>
			<if test="mgr !=null">
					and mgr= #{mgr}
			</if>
			<if test="hiredate != null">
			 		and hiredate= #{hiredate}
			</if>
			<if test="sal != null">
					and sal= #{sal}
			</if>
			<if test="comm !=null">
						<if test="comm == 0 ">
							and(comm is null or comm =0)
						</if>
						<if test="comm != 0">
							and comm=#{comm}
						</if>
			</if>
			<if test="deptno != null">
					and deptno=#{deptno}
			</if>
		</trim>
	</select>	

Foreach标签

接口
public interface EmpMapper3{
List findEmpByDeptno(int … deptnos);
List findEmpByDeptno2(Listdeptnos);
}

SQL语句

```java
<mapper namespace="xx.xxx.xxx.EmpMapper3">
	//数值或者List集合作为方法参数 会默认将数据放入一个map集合中
	//数组作为值 array作为键   <array,{10,20,30}>
	//集合作为值 list作为键
	<list,[10,20]>
	<select id="findEmpByDeptno" resultType="emp">
		select *from emp where deptno in
		//foreach循环 collection存储一个元素中的集合，  item代表集合中的一个元素，separator默认名称分隔符
		<foreach collection="array" item="item" open="("separator=","close=")">
			#{item}
		</foreach>
	</select>
	<select id= "findEmpByDeptno2" resultType="emp">
		Select *from emp where deptno in
		<foreach collection="list" item="item" open="(" separator=","close=")">
			#{item}
		</foreach>
	</select>	
</mapper>

## Bind标签
*一般用于处理模糊查询的模板*
**接口**
List<Emp> findEmpByEname(@Param("a")String name)
**SQL语句**


```java
<select id="findEmpByEname" resultType="emp">
		<bind name= "likePattern" value =" '%'+paraml+'%' ">
		</bind>
		select*from emp where ename like #{likePattern}
</select>

**SQL标签**


<select id="findEmpByDeptno" resultType="emp">
	<include refid = "baseEmpQuery"/>
		where deptno in
		<foreach collection="array" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</select>
	<select id="findEmpByDeptno2" resultType="emp">
		<include refid="baseEmpQuery"/>
		where deptno in
		<foreach collection="list" item="item" open=“（” separator="," close=")">
			#{item}
		</foreach>
	</select>
	<select id="findEmpByEname" resultType="emp">
		<bind name="likePattern" value=" '%'+paraml +'%' " ></bind>
		<include refid="baseEmpQuery"/>
		where ename like #{likePattern}
	</select>
	
	//定义一个可以被引用的sql片段
	<sql id="empColumns">
		empno,ename,job,mgr,hiredate,sal,comm,deptno
	</sql>
	<sql id = "baseEmpQuery">
	 	select
	 	<include refid="empColumns/">	
	 	from emp
	 </sql>