Mybatis 를 활용한 Oracle DB 와 Spring 연동

구현에 앞서 필요한 VO 정의

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="logImpl" value="LOG4J2"/>
    </settings>
    <typeAliases>
    	<typeAlias type="com.pcwk.miss.domain.MovieVO" alias="MovieVO"/>
    	<typeAlias type="com.pcwk.miss.cmn.SearchVO" alias="SearchVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.NowPlayingVO" alias="NowPlayingVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.PlayingSoonVO" alias="PlayingSoonVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.WatchMovieVO" alias="WatchMovieVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.MovieDetailVO" alias="MovieDetailVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.StillCutVO" alias="StillCutVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.MovieReviewVO" alias="MovieReviewVO"/>
    	<typeAlias type="com.pcwk.miss.domain.CouponVO" alias="CouponVO"/>
    	<typeAlias type="com.pcwk.miss.movie.domain.MovieListVO" alias="MovieListVO"/>
    	<typeAlias type="com.pcwk.miss.domain.TicketVO" alias = "TicketVO"/>
    	<typeAlias type="com.pcwk.miss.faq.domain.FaqVO" alias="FaqVO"/>
    	<typeAlias type="com.pcwk.miss.pay.domain.ReserveVO" alias="ReserveVO"/>
    	<typeAlias type="com.pcwk.miss.domain.MemberVO" alias="MemberVO"/>
    	<typeAlias type="com.pcwk.miss.mypage.domain.HistoryVO" alias="HistoryVO"/>
    	<typeAlias type="com.pcwk.miss.mypage.domain.MemberInfoVO" alias="MemberInfoVO"/>
    	<typeAlias type="com.pcwk.miss.domain.ImageVO" alias="ImageVO"/>
    </typeAliases>
</configuration>

결제 완료 후 쿠폰 발급, 회원 정보 업데이트, 예매 내역, 해당 영화 정보를 불러 오기위한 DB Query 구현

<?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="com.pcwk.miss.pay">

	<!-- 해당 영화 포스터 조회 -->
	<select id="posterSelect" parameterType = "MovieVO" resultType="String">
		SELECT im_route AS imRoute
		FROM image
		WHERE mv_num = #{mvNum} AND mv_seq = 1
	</select>

	<!-- 쿠폰 발급 -->
	<insert id="couponInsert" parameterType = "CouponVO">	
		
		INSERT INTO COUPON(
		  c_num,
		  mb_num,
		  c_name,
		  c_target,
		  c_ratio,
		  c_kind
		)
		VALUES(
        	COLUMN_SEQ.NEXTVAL,
			#{mbNum},
			#{cName},
			#{cTarge},
			#{cRatio},
			#{cKind}
		)
	</insert>

	<!-- 회원 업데이트 -->
	<update id="memberUpdate" parameterType = "MemberVO">
		UPDATE member
		SET mb_grade = mb_grade + 1
		WHERE mb_num = #{mbNum}
	</update>

	<select id="payCount" parameterType = "TicketVO" resultType="int">
		SELECT COUNT(*) AS cnt
		FROM ticket
		WHERE mb_num = #{mbNum} AND t_status = 1
	</select>

	<!-- 회원 정보 조회 -->
	<select id="memberSelete" parameterType="MemberVO" resultType="MemberVO">
		SELECT
		    mb_num AS mbNum,
		    mb_email AS mbEmail,
		    mb_name AS mbName,
		    mb_tel AS mbTel,
		    mb_birth AS mbBirth ,
		    mb_nickname AS mbNickname,
		    mb_grade AS mbGrade,
		    mb_point AS mbPoint
		FROM member
		WHERE mb_num =  #{mbNum}
	</select>
	
	<!-- ID에 해당 하는 영화 정보 조회 -->
	<select id="movieInfo" parameterType = "MovieVO" resultType="MovieVO">
		SELECT mv_num AS mvNum, 
			mv_title AS mvTitle,
	       mv_director AS mvDirector,
	       mv_actor AS mvActor,
	       mv_time AS mvTime,
	       mv_agelimit AS mvAgelimit,
	       mv_summary AS mvSummary
		FROM MOVIE
		WHERE mv_num = #{mvNum}
	</select>

	<!-- 쿠폰 사용 상태 업데이트 -->
	<update id="couponUpdate" parameterType="CouponVO">
		UPDATE coupon
		SET
		    c_kind = 0
		WHERE
		    c_num = #{cNum}
	
	</update>

	<!-- 포인트 업데이트 -->
	<update id="pointUpdate" parameterType="MemberVO">
		UPDATE member
		SET mb_point = #{mbPoint}
		WHERE mb_num = #{mbNum}
	</update>

	<!-- 포인트 조회 -->
	<select id="pointValue" parameterType="MemberVO" resultType="int">
		SELECT mb_point AS "mbPoint"
		FROM member
		WHERE mb_num = #{mbNum}

	</select>

	<!-- 할인 쿠폰 리스트 -->
	<select id="couponRetrieve" parameterType="CouponVO" resultType="CouponVO">
		SELECT c_num AS "cNum",
		    c_name AS "cName",
		    c_ratio AS "cRatio"
		FROM coupon
		WHERE mb_num = #{mbNum}
		AND c_kind = 1
	</select>
	
	<!-- 할인률 조회 -->
	<select id="couponSelete" parameterType="CouponVO" resultType="CouponVO">
	
		SELECT
		    c_num AS cNum,
		    mb_num AS mbNum,
		    c_name AS cName,
		    c_target AS cTarget,
		    c_ratio AS cRatio,
		    c_kind AS cKind
		FROM coupon
		WHERE c_num = #{cNum}
	</select>
	
	<!-- 예매 내역 insert -->
	<insert id="ticketInsert" parameterType="TicketVO">
		INSERT INTO ticket (
	    t_num,
	    mb_num,
	    t_price,
	    t_means,
	    t_time,
	    t_status,
	    mv_num
	) VALUES (
	    #{tNum},
	    #{mbNum},
	    #{tPrice},
	    #{tMeans},
	    #{tTime},
	    #{tStatus},
	    #{mvNum} 
	)
	</insert>

	<!-- 예매페이지  상영중인 영화 -->
	<select id="getMovieOn" resultType="ReserveVO">
		SELECT MV_NUM AS mvNum,
		    MV_TITLE AS mvTitle
		FROM MOVIE
		WHERE MV_ON = 1
	</select>
	
	<!-- 예매페이지 상영시간 조회 -->
	<select id="getMovieTime" parameterType="ReserveVO" resultType="ReserveVO">
		SELECT T1.MV_NUM AS mvNum,
		    TO_CHAR(T2.MI_TIME, 'YYYY-MM-DD HH24:MI') AS miTime,
		    T2.MI_QUALITY AS miQuality
		FROM MOVIE T1, MOVIE_INFO T2
		WHERE T1.MV_NUM = T2.MV_NUM
		AND T1.MV_NUM = #{mvNum}
		AND T2.MI_QUALITY LIKE '%' || #{miQuality} || '%'
<!-- 		AND MI_TIME BETWEEN SYSDATE AND SYSDATE + 1 -->
		ORDER BY T2.MI_TIME
	</select>
</mapper>