Pages

2014년 2월 18일 화요일

[SPRING] Spring mvc에서 Quartz 적용하기 - (3)

4. SqlMap  만들기
- iBatis에서 사용할 sql을 만들어보자.
- 실제적으로 사용할 부분은 update 부분과 getList 부분이다. 상태코드에 맞는 데이터를 가져오고, 생태코드를 갱신하면 되는것이다. iBatis와 별로 안친하신분들을 JDBC를 직접 이용해서 구현하시면 되겠다.
 - 원래 의도는 select for update를 이용해서 동일한 데몬이 동시에 여러개 실행될때 데이터를 어떻게 가지고 오는지도 설명해보려고 했으나, 오라클 종속(?)적이고, postman의 성격과는 별로 안 맞는거 같아서 넘어가도록 한다. 
001<?xml version="1.0" encoding="UTF-8" ?>
002<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
003<sqlMap namespace="kr.kangwoo.postman.repository.MailDao" >
004     
005    <resultMap id="Mail" class="kr.kangwoo.postman.domain.Mail" >
006        <result column="MAIL_NO" property="mailNo" jdbcType="NUMBER" />
007        <result column="TEMPLATE_ID" property="templateId" jdbcType="CHAR" />
008        <result column="STATUS_CODE" property="statusCode" jdbcType="CHAR" />
009        <result column="TO_ADDRESS" property="toAddress" jdbcType="VARCHAR2" />
010        <result column="TO_NAME" property="toName" jdbcType="VARCHAR2" />
011        <result column="FROM_ADDRESS" property="fromAddress" jdbcType="VARCHAR2" />
012        <result column="FROM_NAME" property="fromName" jdbcType="VARCHAR2" />
013        <result column="SUBJECT_DATA" property="subjectData" jdbcType="VARCHAR2" />
014        <result column="COTENT_DATA" property="cotentData" jdbcType="VARCHAR2" />
015        <result column="SENT_BY" property="sentBy" jdbcType="VARCHAR2" />
016        <result column="SENT_DATE" property="sentDate" jdbcType="DATETIME" />
017        <result column="CREATED_BY" property="createdBy" jdbcType="VARCHAR2" />
018        <result column="CREATION_DATE" property="creationDate" jdbcType="DATETIME" />
019        <result column="UPDATED_BY" property="updatedBy" jdbcType="VARCHAR2" />
020        <result column="UPDATED_DATE" property="updatedDate" jdbcType="DATETIME" />
021    </resultMap>
022     
023    <insert id="insert" parameterClass="kr.kangwoo.postman.domain.Mail" >
024        insert into MAIL (MAIL_NO, TEMPLATE_ID, STATUS_CODE, TO_ADDRESS, TO_NAME, FROM_ADDRESS, FROM_NAME,
025        SUBJECT_DATA, COTENT_DATA, SENT_BY, SENT_DATE, CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATED_DATE)
026        values (#mailNo:NUMBER#, #templateId:CHAR#, #statusCode:CHAR#, #toAddress:VARCHAR2#,
027        #toName:VARCHAR2#, #fromAddress:VARCHAR2#, #fromName:VARCHAR2#, #subjectData:VARCHAR2#,
028        #cotentData:VARCHAR2#, #sentBy:VARCHAR2#, #sentDate:DATETIME#, #createdBy:VARCHAR2#,
029        #creationDate:DATETIME#, #updatedBy:VARCHAR2#, #updatedDate:DATETIME#)
030    </insert>
031     
032    <update id="update" parameterClass="kr.kangwoo.postman.domain.Mail" >
033        update MAIL
034        set TEMPLATE_ID = #templateId:CHAR#,
035            STATUS_CODE = #statusCode:CHAR#,
036            TO_ADDRESS = #toAddress:VARCHAR2#,
037            TO_NAME = #toName:VARCHAR2#,
038            FROM_ADDRESS = #fromAddress:VARCHAR2#,
039            FROM_NAME = #fromName:VARCHAR2#,
040            SUBJECT_DATA = #subjectData:VARCHAR2#,
041            COTENT_DATA = #cotentData:VARCHAR2#,
042            SENT_BY = #sentBy:VARCHAR2#,
043            SENT_DATE = #sentDate:DATETIME#,
044            CREATED_BY = #createdBy:VARCHAR2#,
045            CREATION_DATE = #creationDate:DATETIME#,
046            UPDATED_BY = #updatedBy:VARCHAR2#,
047            UPDATED_DATE = #updatedDate:DATETIME#
048        where MAIL_NO = #mailNo:NUMBER#
049    </update>
050     
051    <update id="updateSelective" parameterClass="kr.kangwoo.postman.domain.Mail" >
052        update MAIL
053        <dynamic prepend="set" >
054            <isNotNull prepend="," property="templateId" >
055                    TEMPLATE_ID = #templateId:CHAR#
056            </isNotNull>
057            <isNotNull prepend="," property="statusCode" >
058                    STATUS_CODE = #statusCode:CHAR#
059            </isNotNull>
060            <isNotNull prepend="," property="toAddress" >
061                    TO_ADDRESS = #toAddress:VARCHAR2#
062            </isNotNull>
063            <isNotNull prepend="," property="toName" >
064                    TO_NAME = #toName:VARCHAR2#
065            </isNotNull>
066            <isNotNull prepend="," property="fromAddress" >
067                    FROM_ADDRESS = #fromAddress:VARCHAR2#
068            </isNotNull>
069            <isNotNull prepend="," property="fromName" >
070                    FROM_NAME = #fromName:VARCHAR2#
071            </isNotNull>
072            <isNotNull prepend="," property="subjectData" >
073                    SUBJECT_DATA = #subjectData:VARCHAR2#
074            </isNotNull>
075            <isNotNull prepend="," property="cotentData" >
076                    COTENT_DATA = #cotentData:VARCHAR2#
077            </isNotNull>
078            <isNotNull prepend="," property="sentBy" >
079                    SENT_BY = #sentBy:VARCHAR2#
080            </isNotNull>
081            <isNotNull prepend="," property="sentDate" >
082                    SENT_DATE = #sentDate:DATETIME#
083            </isNotNull>
084            <isNotNull prepend="," property="createdBy" >
085                    CREATED_BY = #createdBy:VARCHAR2#
086            </isNotNull>
087            <isNotNull prepend="," property="creationDate" >
088                    CREATION_DATE = #creationDate:DATETIME#
089            </isNotNull>
090            <isNotNull prepend="," property="updatedBy" >
091                    UPDATED_BY = #updatedBy:VARCHAR2#
092            </isNotNull>
093            <isNotNull prepend="," property="updatedDate" >
094                    UPDATED_DATE = #updatedDate:DATETIME#
095            </isNotNull>
096        </dynamic>
097        where MAIL_NO = #mailNo:NUMBER#
098    </update>
099     
100    <delete id="delete" parameterClass="kr.kangwoo.postman.domain.Mail" >
101        delete from MAIL
102        where MAIL_NO = #mailNo:NUMBER#
103    </delete>
104     
105    <select id="getMail" parameterClass="kr.kangwoo.postman.domain.Mail" resultMap="Mail" >
106        select MAIL_NO, TEMPLATE_ID, STATUS_CODE, TO_ADDRESS, TO_NAME, FROM_ADDRESS, FROM_NAME,
107        SUBJECT_DATA, COTENT_DATA, SENT_BY, SENT_DATE, CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATED_DATE
108        from MAIL
109        where MAIL_NO = #mailNo:NUMBER#
110    </select>
111     
112    <select id="getList" parameterClass="java.util.Map" resultMap="Mail" >
113        select MAIL_NO, TEMPLATE_ID, STATUS_CODE, TO_ADDRESS, TO_NAME, FROM_ADDRESS, FROM_NAME,
114        SUBJECT_DATA, COTENT_DATA, SENT_BY, SENT_DATE, CREATED_BY, CREATION_DATE, UPDATED_BY, UPDATED_DATE
115        from MAIL
116        <dynamic prepend="where" >
117            <isNotNull prepend="and" property="statusCode" >
118                    STATUS_CODE = #statusCode:CHAR#
119            </isNotNull>
120            <isNotNull prepend="and" property="listSize" >
121                    ROWNUM <= #listSize:NUMBER#
122            </isNotNull>
123        </dynamic>
124        <dynamic prepend="order by" >
125            <isNotNull property="orderBy" >
126                    $orderBy$
127            </isNotNull>
128        </dynamic>
129    </select>
130      
131    <select id="count" parameterClass="java.util.Map" resultClass="java.lang.Integer" >
132        select count(*) cnt from MAIL
133    </select>
134</sqlMap>

댓글 없음:

댓글 쓰기