생일에 따른 연령계산하기 insert, update

두가지 경우가있을것이다.

수정일때와, 등록일때.

수정일경우 그 인물에 해당하는 고유 EMPID값을 넘겨 수정을 수행하고, 

등록일경우 EMPID를 우선 INSERT해 생성한 이후 EMPID에 따라 등록을 수행해야할것이다.

 

 

생년월일과 연령을 표현해줄 HTML을 우선 작성해보자.

<c:forEach var="emp_info_data" items="${selectMpList}">
<p>age</p>
<th><label for="birthday">생년월일</label></th>
<td><input type="date" name="birthday" id="birthday" value="${emp_info_data.birthday}" onchange="calculateAge()"></td>
<input type="text" name="age" value="${emp_info_data.age}" id="age">
</c:forEach>

 

 

인물의 생일값이 없을경우, 0살 있을경우 계산에 따라 표시한다.

UPDATE emp_info
SET AGE = 
		<if test="birthday != null and birthday != ''">
		    TIMESTAMPDIFF(YEAR, #{birthday}, CURDATE())
		</if>
		<if test="birthday == null or birthday == ''">
		    0
		</if>
WHERE EMP_ID = #{empId}

 

 

등록시에는 수령받은 EMPID값을 받아 수행한다.

<insert id="insertEmployee" parameterType="com.example.Employee">
    <![CDATA[
        INSERT INTO emp_info (BIRTHDAY, AGE)
        VALUES (
            #{birthday},
            <if test="birthday != null and birthday != ''">
                TIMESTAMPDIFF(YEAR, #{birthday}, CURDATE())
            </if>
            <if test="birthday == null or birthday == ''">
                0
            </if>
        )
    ]]>
    <selectKey keyProperty="empId" order="AFTER" resultType="int">
        SELECT LAST_INSERT_ID() AS empId
    </selectKey>
</insert>

 

 

 

 

 

실제 코드에는 연령 뿐만 아닌 모든 기본정보를 받게 된다.

실코드 예시를 첨부한다.

<insert id="insertInfo" parameterType="com.mp.web.bean.MpVO" useGeneratedKeys="true" keyProperty="empId">
    <!-- emp_info 삽입 -->
    INSERT INTO emp_info (
        NAME,
        EMP_STATUS_YN,
        EMP_AVBLTY,
        GENDER,
        BIRTHDAY,
        AGE,
        MILIT_STATUS,
        SKILLS,
        H_NUM_AREA,
        H_NUM_MID,
        H_NUM_SEQ,
        EMAIL_BASIC,
        POST_CODE,
        POST_ADDRESS,
        DETAIL_ADDRESS,
        EMP_CO_NAME,
        DEPT_NAME,
        POS_NAME,
        EXP_YEARS,
        EXP_GRADE,
        JOIN_DATE
    ) VALUES (
        #{name},
        #{empStatusYn},
        #{empAvblty},
        #{gender},
        <if test="birthday != null and birthday != ''">
            #{birthday}
        </if>
        <if test="birthday == null or birthday == ''">
            NULL
        </if>,
        <if test="birthday != null and birthday != ''">
		    TIMESTAMPDIFF(YEAR, #{birthday}, CURDATE())
		</if>
		<if test="birthday == null or birthday == ''">
		    0
		</if>,
        #{militStatus},
        #{empSkills},
        #{h_NumArea},
        #{h_NumMid},
        #{h_NumSeq},
        #{emailBasic},
        #{postCode},
        #{postAddress},
        #{detailAddress},
        #{empCoName},
        #{deptName},
        #{posName},
        #{expYears},
        #{expGrade},
        <if test="joinDate != null and joinDate != ''">
            #{joinDate}
        </if>
        <if test="joinDate == null or joinDate == ''">
            NULL
        </if>
    )
    <selectKey keyProperty="empId" order="AFTER" resultType="int">
        SELECT LAST_INSERT_ID() AS empId
    </selectKey>
</insert>

<update id="updateInfo" parameterType="com.mp.web.bean.MpVO">
    <!-- emp_info 업데이트 -->
    UPDATE emp_info
    SET
    NAME = #{name},
    EMP_STATUS_YN = #{empStatusYn},
    EMP_AVBLTY = #{empAvblty},
    GENDER = #{gender},
    BIRTHDAY = 
                <if test="birthday != null and birthday != ''">
		            #{birthday}
		        </if>
		        <if test="birthday == null or birthday == ''">
		            NULL
		        </if>,
	AGE = 
				<if test="birthday != null and birthday != ''">
		            TIMESTAMPDIFF(YEAR, #{birthday}, CURDATE())
		        </if>
		        <if test="birthday == null or birthday == ''">
		            0
		        </if>,
    MILIT_STATUS = #{militStatus},
    SKILLS = #{empSkills},
    H_NUM_AREA = #{h_NumArea},
    H_NUM_MID = #{h_NumMid},
    H_NUM_SEQ = #{h_NumSeq},
    EMAIL_BASIC = #{emailBasic},
    POST_CODE = #{postCode},
    POST_ADDRESS = #{postAddress},
    DETAIL_ADDRESS = #{detailAddress},
    EMP_CO_NAME = #{empCoName},
    DEPT_NAME = #{deptName},
    POS_NAME = #{posName},
    EXP_YEARS = #{expYears},
    EXP_GRADE = #{expGrade},
    JOIN_DATE = 
    <if test="joinDate != null and joinDate != ''">
		            #{joinDate}
		        </if>
		        <if test="joinDate == null or joinDate == ''">
		            NULL
		        </if>,
    UPD_DATE_ID = CURRENT_TIMESTAMP()
    WHERE EMP_ID = #{empId}
</update>