- 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 > |
댓글 없음:
댓글 쓰기