动态SQL

if

EmpMapper接口

 Employee queryEmpByPramas(Map map);

EmpMapper.xml

<resultMap id="emp" type="com.maple.entity.Employee"> <result property="empid" column="empid"/> <result property="last_name" column="last_name"/> <result property="email" column="email"/> <result property="gender" column="gender"/> <result property="birth" column="birth"/> <result property="deptid" column="deptid"/> <association property="department" javaType="com.maple.pojo.Department"> <result property="deptid" column="deptid"/> <result property="department_name" column="department_name"/> </association></resultMap><select id="queryEmpByPramas" resultMap="emp" parameterType="map"> select * from mybatis.employee,mybatis.department where employee.deptid=department.deptid <if test="empid != null"> and empid=#{empid} </if> <if test="last_name != null"> and last_name like #{last_name} </if> <if test="gender!=null"> and gender = #{gender} </if> <if test="deptid != null"> and deptid=#{deptid} </if></select>

where

有时候查询语句不一定会有where此时使用 标签智能添加where

<select id="queryDeptByPramas" resultType="Department"> select * from mybatis.department <where> <if test="deptid != null"> and deptid=#{deptid} </if> <if test="department_name != null"> and department_name=#{department_name} </if> </where> <!--where能自动添加where同时可以删除多余的and或or--></select>

choose

如果上面的查询只需要选择其中一个条件来执行，而不是所有满足条件的则可以使用 标签

<select id="queryDeptByChoose" resultType="com.maple.pojo.Department"> select * from mybatis.department <where> <choose> <when test="deptid!=null"> deptid=#{deptid} </when> <!-- and 会被where标签去除--> <when test="department_name != null"> and department_name like ‘%‘+#{department_name}+‘%‘ </when> <!-- 以上条件都不满足执行 <otherwise> 1=1 </otherwise> --> </choose> </where> </select>

set

set标签与where标签类似，它能智能的为update添加set或移除多余的逗号

<update id="updateEmp" parameterType="map"> update mybatis.employee <set> <if test="last_name!=null"> last_name=#{last_name}, </if> <if test="email!=null"> email=#{email}, </if> <if test="gender!=null"> gender=#{gender}, </if> <if test="deptid!=deptid"> email=#{email}, </if> <if test="birth!=birth"> email=#{email} </if> </set> where <if test="empid != null"> empid=#{empid} </if></update>

trim

定义前缀和覆盖前|后缀

和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND | OR "> ...</trim>

与 set 元素等价的自定义 trim 元素吧：

<trim prefix="SET" suffixOverrides=","> ...</trim>

SQL片段

可以使用 标签将一些重复的SQL语句抽取，以实现复用

抽取SQL片段

<sql id="if-empid"> <if test="empid != null"> and empid=#{empid} </if></sql>

引用SQL片段

<select id="queryEmpByPramas" resultMap="emp" parameterType="map"> select * from mybatis.employee,mybatis.department where employee.deptid=department.deptid<!-- <if test="empid != null">--><!-- and empid=#{empid}--><!-- </if>--><!-- <if test="last_name != null">--><!-- and last_name like #{last_name}--><!-- </if>--> <include refid="if-empid"></include> <include refid="if-last_name"/> <if test="gender!=null"> and gender = #{gender} </if> <if test="deptid != null"> and deptid=#{deptid} </if></select>

foreach

foreach用于集合的遍历，通常在构建IN条件语句的时候使用。

例：

EmpMapper

 List<Employee> queryEmpInDept(List list);

EmpMapper.xml

<!--查询指定几个部门的员工--><select id="queryEmpInDept" resultType="com.maple.pojo.Employee"> select * from mybatis.employee where deptid in <foreach item="did" index="index" collection="list" open="(" separator="," close=")"> #{did} </foreach></select>

• item:遍历的每个项
• collection：集合类型（list、map、set、数组)
• index:索引，list并没有用到index当collection为map时index是key
• open：开始标记
• separator：中间标记(多为","，"and"，"or")
• close：结束标记

测试

/** * 查询101和103部门的员工 */@Testpublic void test8(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class); List<Integer> list = new ArrayList<>(); list.add(101); list.add(103); empMapper.queryEmpInDept(list).forEach(emp-> System.out.println(emp));}