MyBatis动态SQL

动态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部门的员工
 */
@Test
public 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));
}