마이바티스 샘플 코드
auth_aql.xml
<?xml version=”1.0″ encoding=”UTF-8″?>
<!DOCTYPE mapper
PUBLIC “-//mybatis.org//DTD Mapper 3.0//EN”
“http://mybatis.org/dtd/mybatis-3-mapper.dtd”>
<mapper namespace=”auth”>
<select id=”selectUidFromphoneNumber” parameterType=”java.lang.String” resultType=”java.lang.String”>
select UID
from MY_TABLE
where phone_num = #{phoneNumber}
and admin_auth is null
</select>
<select id=”selectUidFromphoneNumber2″ parameterType=”java.util.HashMap” resultType=”java.lang.String”>
select UID
from MY_TABLE
where phone_num = #{phoneNumber}
and admin_auth is null
and id = LTRIM(RTRIM(#{loginId}))
</select>
<select id=”interlockingDeviceAuthCheck” parameterType=”java.util.HashMap” resultType=”java.lang.Integer”>
select /*auth.interlockingDeviceAuthCheck*/ COUNT(1) from MY_TABLE_AUTHKEY
where UID = #{uid}
and AutyKeyValue = #{uidKey}
and GoalType = #{goalType}
</select>
<delete id=”deleteInterlockingDeviceAuthCheck” parameterType=”java.util.HashMap” >
delete from MY_TABLE_AUTHKEY
where UID = #{uid}
and AutyKeyValue = #{uidKey}
and GoalType = #{goalType}
</delete>
<insert id=”insertAuthSmsInfo” parameterType=”java.util.HashMap” >
<selectKey resultType=”java.lang.Long” keyProperty = “send_id” >
insert into MY_TABLE_SMS(send_code,title, cont, receiver_gubun, crt_dt, sender_uid, sender_ctn, apm_gubun, apm_dt, is_lms, par_send_id, use_period)
values(206,’인증번호’, #{cont}, 0, GETDATE(), #{uid}, #{phoneNumber}, 1, Convert(VARCHAR(16),GETDATE(),120),0, 0, 0)
select SCOPE_IDENTITY() as send_id
</selectKey>
</insert>
<update id=”mergeSmsAuthNumber” parameterType=”java.util.HashMap” >
merge into MY_TABLE_TKN as a
USING (select count(*) as cnt from MY_TABLE_TKN where uid = #{uid}) as b
on (b.cnt > 0 and a.uid = #{uid})
WHEN MATCHED THEN
update set
pin_code = #{authNumber}
, pin_crt_dt = GETDATE()
, person_info_status =’S’
, chg_uid = #{uid}
, chg_dt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
insert(uid, pin_code, pin_crt_dt, crt_uid, person_info_status, crt_dt, chg_uid, chg_dt) values (#{uid}, #{authNumber}, GETDATE(), #{uid}, ‘S’ , GETDATE(), #{uid}, GETDATE())
;
</update>
<select id=”selectVerifySmsInfo” parameterType=”java.lang.Long” resultType=”java.util.HashMap”>
select PIN_CODE, DATEDIFF(SS, pin_crt_dt, GETDATE()) as TIME_GAB
from MY_TABLE_TKN
where uid = #{uid}
</select>
<update id=”updateAuthToken” parameterType=”java.util.HashMap” >
update MY_TABLE_TKN set
tkn_val = #{authToken}
, platform_type = #{platform}
, pub_dt = GETDATE()
, expir_yn = ‘N’
, person_info_status =’S’
, chg_uid = #{uid}
, chg_dt = GETDATE()
where uid = #{uid}
</update>
<select id=”selectVerifyAuthToken” parameterType=”java.lang.Long” resultType=”java.lang.String”>
select tkn_val
from MY_TABLE_TKN
where uid = #{uid}
and expir_yn = ‘N’
</select>
<update id=”updateDeleteAuthToken” parameterType=”java.lang.Long” >
<!–
update MY_TABLE_TKN set
tkn_val = NULL
, expir_dt = GETDATE()
, expir_yn = ‘Y’
, expir_reason = ‘log out’
, logoutYn =’Y’
, chg_uid = #{uid}
, chg_dt = GETDATE()
where uid = #{uid}
–>
update MY_TABLE_TKN set
logoutYn =’Y’
, chg_uid = #{uid}
, chg_dt = GETDATE()
where uid = #{uid}
</update>
<insert id=”insertAuthSmsBas” parameterType=”java.util.HashMap” >
insert into MY_TABLE_SEND(send_kind, send_id, uid, receiver_ctn)
values(1, #{send_id}, #{uid}, #{phoneNumber})
</insert>
<insert id=”insertMemUseLog” parameterType=”java.util.HashMap” >
insert into MY_TABLE_USE_LOG(crt_dt, uid, comp_id, platform_type, install)
values(GETDATE(), #{uid}, (select top 1 comp_id from MY_TABLE where uid = #{uid}), #{platform}, #{install})
</insert>
<select id=”selectUidFromEmailAddress” parameterType=”java.lang.String” resultType=”java.lang.String”>
select UID from MY_TABLE where email = #{email} and admin_auth is null
</select>
<select id=”insertRealTimeSmsProc” parameterType=”java.util.HashMap” statementType=”CALLABLE”>
{call UBfortune.dbo.SP_CERTIFICATED_SMS(
#{cont, mode=IN, jdbcType=VARCHAR}
,#{phoneNumber, mode=IN, jdbcType=VARCHAR}
,#{uid, mode=IN, jdbcType=INTEGER}
)}
</select>
<select id=”selectUidFromEmailAddress2″ parameterType=”java.util.HashMap” resultType=”java.lang.String”>
select UID
from MY_TABLE
where email = #{email}
and admin_auth is null
and id = LTRIM(RTRIM(#{loginId}))
</select>
<select id=”findUserId” parameterType=”java.lang.String” resultType=”java.lang.String”>
select TOP 1 id from MY_TABLE where id = LTRIM(RTRIM(#{loginId})) and admin_auth is null
</select>
<select id=”encryptAuthKey” parameterType=”java.util.HashMap” resultType=”java.lang.String”>
select Checkup.[dbo].[F_ENCRYPT] ( #{uid} + ‘/’ + convert(char(16), getdate(),121) + ‘/’ + #{statCode})
</select>
<select id=”encryptAuthNewKey” parameterType=”java.util.HashMap” resultType=”java.lang.String”>
select Checkup.[dbo].[F_ENCRYPT] ( #{uid} + ‘/’ + convert(char(16), getdate(),121) + ‘/’ + #{statCode}+’/NEW’ )
</select>
<select id=”getNewUserYn” parameterType=”java.util.HashMap” resultType=”java.lang.String” statementType=”CALLABLE”>
{call well_app.SP_GET_CHECK( #{UID, mode=IN, jdbcType=BIGINT} )}
</select>
<select id=”selectChkBPTop10Info” parameterType=”java.lang.Long” resultType=”java.util.HashMap”>
select COUNT(A.bp_lv) as cnt
, isnull(cast(ROUND(convert(float,SUM(isnull(A.bp_low,0)))/10,0) as int),0) as average_bp_low
, isnull(cast(ROUND(convert(float,SUM(isnull(A.bp_high,0)))/10,0) as int),0) as average_bp_high
from (
select top 10 bp_lv, bp_low, bp_high
from TABLE_CHECK_BP
where uid = #{uid}
and chk_id != (select top 1 chk_id from well_app.TABLE_CHECK_BP where uid = #{uid} order by chk_id desc)
order by chk_id desc
) AS A
</select>
<update id=”mergeChkActivity” parameterType=”java.util.HashMap” >
<foreach collection=”activityList” item = “activity” separator=” ” >
merge into TABLE_CHECK_ACTIVITY as a
USING (select count(*) as cnt from TABLE_CHECK_ACTIVITY where uid = #{activity.uid} and chk_gubun = #{activity.chk_gubun} and crt_dt = #{activity.act_date} and activity_id = #{activity.activity_id} ) as b
on (b.cnt > 0 and a.uid = #{activity.uid} and a.chk_gubun = #{activity.chk_gubun} and a.crt_dt = #{activity.act_date} and a.activity_id = #{activity.activity_id} )
WHEN MATCHED THEN
update set
cal = cal + #{activity.cal}
WHEN NOT MATCHED BY TARGET THEN
insert(crt_dt, uid, chk_gubun, cal, activity_id) values (#{activity.act_date},#{activity.uid}, #{activity.chk_gubun}, #{activity.cal}, #{activity.activity_id})
;
</foreach>
</update>
<select id=”retsList_BAK_” parameterType=”java.util.HashMap” resultType=”java.util.HashMap”>
SELECT *
FROM (
SELECT SCT.USER_ID AS consultantId ,
SUM(SCT.CNT) AS consultantCnt ,
WELL_APP.FNC_GET_CONSULTANT_NAME(SCT.USER_ID) AS consultantNm ,
ROW_NUMBER() OVER(ORDER BY SCT.USER_ID ASC) AS no
FROM TABLE_STAT SCT
WHERE SCT.STAT_CYCL = ‘D’
AND SCT.STAT_CYCL_DT BETWEEN REPLACE(#{startDt},’.’,”) AND REPLACE(#{endDt},’.’,”)
<if test=”searchType == ‘UID’ “>
AND SCT.UID = #{uid}
</if>
<if test=”searchType == ‘PERIODE’ “>
AND SCT.UID IN
(SELECT UID
FROM TABLE_MEM
WHERE UID = SCT.UID
AND PERIOD_ID = #{periodId}
)
</if>
GROUP BY USER_ID
) X
<if test=”XLS != ‘XLS’ “>
WHERE X.no between ((#{pageNum} – 1) * 10) + 1 and ((#{pageNum} – 1) * 10) + 10
</if>
ORDER BY no
</select>
</mapper>
