마이바티스 리팩토링하기 2

시리즈물이기 때문에 글은 생략

---

Before

<select id="getCommuteRecordSelectList" resultType="com.tistory.vessel.api.vo.CommuteRecordVO">
    SELECT
        r.id
        , UNIX_TIMESTAMP(r.come) as come
        , UNIX_TIMESTAMP(r.leave) as `leave`
        , r.employee_id
        , r.division_id
        , r.manager_id
        , r.come_match
        , r.leave_match
        , r.pad
        , valid
        , e.wage
        , come_beacon
        , leave_beacon
    FROM commute_record as r
    inner join employee as e on r.employee_id = e.id
    left outer join grade as g on e.grade_id = g.id
    WHERE 1=1
    <if test="employee_id != null and employee_id != -1">
        AND e.id = #{employee_id}
    </if>
    <if test="company_id != -1">
        AND d.company_id = #{company_id}
    </if>
    <if test="valid != null">
        AND r.valid = #{valid}    
    </if>
    <if test="manager_id != null and manager_id != -1">
        AND (e.manager_id = #{manager_id} OR e.id = #{manager_id})
    </if>
    <if test="hourly_wage != null">
        AND e.hourly_wage = #{hourly_wage}
    </if>
    <if test="division_id != null and division_id != -1">
        AND r.division_id = #{division_id}
    </if>
    <if test="start != null and start != -1 and end != null and end != -1">
        <if test="is_come == null">
            <![CDATA[
            AND r.come >= from_unixtime(#{start}) AND r.come <= from_unixtime(#{end})
            AND (r.leave is null OR r.leave <= from_unixtime(#{end}))
            ]]>
        </if>
        <if test="is_come != null">
            <if test="is_come == true">
                <![CDATA[
                AND r.come >= from_unixtime(#{start}) AND r.come <= from_unixtime(#{end})
                AND r.come is not null
                ]]>
            </if>
            <if test="is_come == false">
                <![CDATA[
                AND r.leave >= from_unixtime(#{start}) AND r.leave <= from_unixtime(#{end})
                AND r.leave is not null
                ]]>
            </if>
        </if>
    </if>
    <if test="come_match > 0">
        <if test="is_upper == false">
            <![CDATA[
            AND (r.come_match < #{come_match} or r.come_match is null)
            ]]>
        </if>
        <if test="is_upper == null or is_upper != false">
            <![CDATA[
            AND r.come_match >= #{come_match}
            ]]>
        </if>
    </if>
    ORDER BY g.priority DESC, e.name, r.come DESC
</select>

after

    <select id="getCommuteRecordSelectList" resultType="com.tistory.vessel.api.vo.CommuteRecordVO">
        SELECT
            r.id
            , UNIX_TIMESTAMP(r.come) as come
            , UNIX_TIMESTAMP(r.leave) as `leave`
            , r.employee_id
            , r.division_id
            , r.manager_id
            , r.come_match
            , r.leave_match
            , r.pad
            , valid
            , e.wage
            , come_beacon
            , leave_beacon
        FROM commute_record as r
        inner join employee as e on r.employee_id = e.id
        left outer join grade as g on e.grade_id = g.id
        
        <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="employee_id != null">AND e.id = #{employee_id}</if>
        <if test="company_id != null">AND d.company_id = #{company_id}</if>
        <if test="valid != null">AND r.valid = #{valid}</if>
        <if test="manager_id != null">AND (e.manager_id = #{manager_id} OR e.id = #{manager_id})</if>
        <if test="hourly_wage != null">AND e.hourly_wage = #{hourly_wage}</if>
        <if test="division_id != null">AND r.division_id = #{division_id}</if>
        <if test="come_gt != null"><![CDATA[AND r.come >= from_unixtime(#{come_gt})]]></if>
        <if test="come_lt != null"><![CDATA[AND r.come <= from_unixtime(#{come_lt})]]></if>
        <if test="leave_gt != null"><![CDATA[AND r.leave >= from_unixtime(#{leave_gt})]]></if>
        <if test="leave_lt != null"><![CDATA[AND r.leave <= from_unixtime(#{leave_lt})]]></if>
        </trim>
        ORDER BY r.come DESC
        <if test="limit != null">limit #{limit}</if>
    </select>