[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>