Obsidian-Knowledge-Base/X-Knowledge-Base/raw/Joplin/编程学习项目/Java学习笔记/Java项目常用配置/mapper.xml.md

EmpExprMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpExprMapper">
    <!--    insertBatch 批量插入员工工作经历 -->
    <insert id="insertBatch">
        insert into emp_expr(emp_id, begin, end, company, job) values
        <foreach collection="exprList" item="expr" separator=",">
            (#{expr.empId}, #{expr.begin}, #{expr.end}, #{expr.company}, #{expr.job})
        </foreach>
    </insert>
    <delete id="deleteByEmpIds">
        delete from emp_expr where emp_id in
        <foreach collection="empIds" item="empId" separator="," open="(" close=")">
            #{empId}
        </foreach>
    </delete>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpExprMapper">
    <!--    insertBatch 批量插入员工工作经历 -->
    <insert id="insertBatch">
        insert into emp_expr(emp_id, begin, end, company, job) values
        <foreach collection="exprList" item="expr" separator=",">
            (#{expr.empId}, #{expr.begin}, #{expr.end}, #{expr.company}, #{expr.job})
        </foreach>
    </insert>
    <delete id="deleteByEmpIds">
        delete from emp_expr where emp_id in
        <foreach collection="empIds" item="empId" separator="," open="(" close=")">
            #{empId}
        </foreach>
    </delete>
</mapper>

 EmpMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
    <!-- 定义insert方法, 设置主键返回值 -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into emp(username, name, gender, phone, job, salary, image, entry_date, dept_id, create_time,
                        update_time)
        values (#{username}, #{name}, #{gender}, #{phone}, #{job}, #{salary}, #{image}, #{entryDate}, #{deptId},
                #{createTime}, #{updateTime});
    </insert>
    <!-- 定义updateById方法, 根据id更新员工基本信息 -->
    <!-- set标签会会自动生成set关键字;会自动的删除掉更新字后多余的逗号-->
    <update id="updateById">
        update emp
        <set>
            <if test="username != null and username != ''">username = #{username},</if>
            <if test="password != null and password != ''">password = #{password},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="phone != null and phone != ''">phone = #{phone},</if>
            <if test="job != null">job = #{job},</if>
            <if test="salary != null">salary = #{salary},</if>
            <if test="image != null and image != ''">image = #{image},</if>
            <if test="entryDate != null">entry_date = #{entryDate},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="updateTime != null">update_time = #{updateTime}</if>
        </set>
        where id = #{id}
    </update>

    <!--批量删除员工基本信息-->
    <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <select id="list" resultType="com.itheima.pojo.Emp">
        select e.*, d.name as deptName
        from emp e
        left join dept d on e.dept_id = d.id
        <where>
            <if test="name!=null and name!=''">
                e.name like concat('%', #{name}, '%')
            </if>
            <if test="gender!=null">
                and e.gender = #{gender}
            </if>
            <if test="begin!=null and end!=null">
                and entry_date between #{begin} and #{end}
            </if>
        </where>
        order by e.update_time desc
    </select>
    <!-- 定义resultMap 用于封装查询结果 -->
    <resultMap id="empResultMap" type="com.itheima.pojo.Emp">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="phone" property="phone"/>
        <result column="job" property="job"/>
        <result column="salary" property="salary"/>
        <result column="image" property="image"/>
        <result column="entry_date" property="entryDate"/>
        <result column="dept_id" property="deptId"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <!-- 封装exprList -->
        <collection property="exprList" ofType="com.itheima.pojo.EmpExpr">
            <id column="ee_id" property="id"/>
            <result column="ee_emp_id" property="empId"/>
            <result column="ee_begin" property="begin"/>
            <result column="ee_end" property="end"/>
            <result column="ee_company" property="company"/>
            <result column="ee_job" property="job"/>
        </collection>
    </resultMap>
    <select id="getById" resultType="com.itheima.pojo.Emp" resultMap="empResultMap">
        select e.*,
               ee.id      as ee_id,
               ee.emp_id  as ee_emp_id,
               ee.begin   as ee_begin,
               ee.end     as ee_end,
               ee.company as ee_company,
               ee.job     as ee_job
        from emp e
                 left join emp_expr ee on e.id = ee.emp_id
        where e.id = #{id}
    </select>
</mapper>