XML映射文件配置SQL语句

xml映射文件规范

例

<mapper namespace="com.itheima.mapper.EmpMapper">
<!--    result:单条记录返回的类型-->
    <select id="selectList" resultType="com.itheima.pojo.Emp">
        select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and
            entrydate between #{start} and #{end} order by update_time desc
    </select>
</mapper>

public List<Emp> selectList(String name, Short gender,  LocalDate start, LocalDate end);

动态SQL：

if标签

//动态条件查询
    public List<Emp> selectList(String name, Short gender,  LocalDate start, LocalDate end);
}

where标签

<mapper namespace="com.itheima.mapper.EmpMapper">
<!--    result:单条记录返回的类型-->
    <select id="selectList" resultType="com.itheima.pojo.Emp">
        select *
        from emp
      <!-- where标签用以判断if条件是否成立，若所有条件均不成立，
则不会有where产生，若第二个以后的if成立，第一个if不成立，
则会自动去除第一个成立的if条件SQL语句下的‘and’或‘or’关键字-->
        <where>
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="start != null and end != null">
                and entrydate between #{start} and #{end}
            </if>
            order by update_time desc
        </where>
    </select>
</mapper>

set标签

<mapper namespace="com.itheima.mapper.EmpMapper">
    <update id="update2">
        update emp
            <!-- set标签可以自动去除多余的逗号-->
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="name != null">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="image != null">image = #{image},</if>
            <if test="job != null">job = #{job},</if>
            <if test="entrydate != null">entrydate = #{entrydate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime}</if>
            where id = #{id}
        </set>
    </update>

总结

foreach标签

<delete id="deleteById">
        delete from emp where id in
                              <!--
                                collection:遍历的集合
                                item:遍历的元素名
                                separator:分隔符
                                open:遍历开始拼接的符号
                                close:遍历结束拼接的符号
                              -->
        <foreach collection="ids"  item="id"  separator="," open="(" close=")">
                        #{id}
        </foreach>
    </delete>

 @Test
    public void testdeleteByIds() {
        List<Integer> ids = Arrays.asList(13,14,15);
        empMapper.deleteById(ids);
    }

sql，include标签

<!--公共sql片段抽取 id：公共sql片段命名-->
    <sql id="commonSelect">
        select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
        from emp
    </sql>
    <!--    result:单条记录返回的类型-->
    <select id="selectList" resultType="com.itheima.pojo.Emp">
        <!-- 抽取id为refid的sql片段 -->
        <include refid="commonSelect"/>
            <!-- where标签用以判断if条件是否成立，若所有条件均不成立，则不会有where产生，
            若第二个以后的if成立，第一个if不成立，则会自动去除第一个成立的if条件SQL语句下的‘and’关键字-->
        <where>
            <if test="name != null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="start != null and end != null">
                and entrydate between #{start} and #{end}
            </if>
            order by update_time desc
        </where>
    </select>