[MyBatis] 상황에 따른 쿼리 문

실제 프로젝트에서 사용했던 파일들이기 때문에 대부분 동적쿼리로 작성되어있습니다..!!

✅ 최신글 n 개 표시하기 (Oracle ver.)

    <!-- MYREPLY 테이블에 저장된 댓글 중 가장 최근에 작성된 5개 댓글만 검색하는 SELECT 명령 등록 -->
	
	<!-- 회피 문자 사용 -->
	<select id="selectCountReplyList" resultType="MyReply">
		select rownum, reply.* from (select reply_no, reply_id, reply_content, reply_date
			, reply_comment_no from myreply order by reply_no desc) reply where rownum &lt;= 5
	</select>
	
	 
	<!-- CDATA 세션을 사용-->
	<select id="selectCountReplyList" resultType="MyReply">
		<![CDATA[
		select rownum, reply.* from (select reply_no, reply_id, reply_content, reply_date
			, reply_comment_no from myreply order by reply_no desc) reply where rownum <= 5
		]]>	
	</select>

---

✅ 댓글 관련 조인 (Oracle ver.)

방법 1

<!-- autoMapping 속성 : false 또는 true 중 하나를 속성값으로 설정 -->
<!-- => autoMapping 속성값을 [true]로 설정한 경우 검색행의 컬럼과 필드명이 같은 경우 자동 매핑 처리 -->	 
	<resultMap type="MyCommentReplyUser" id="myCommentReplyUserResultMap" autoMapping="true">
		<id column="comment_no" property="commentNo"/>
		<association property="user" javaType="MyUser" autoMapping="true"/>
		<collection property="replyUserList" select="selectReplyUser" column="comment_no"/>
	</resultMap>
	
	<resultMap type="MyReplyUser" id="myReplyUserResultMap">
		<association property="reply" javaType="MyReply" autoMapping="true"/>
		<association property="user" javaType="MyUser" autoMapping="true"/>
	</resultMap>
	
	<select id="selectReplyUser" parameterType="int" resultMap="myReplyUserResultMap">
		select reply_no, reply_id, reply_content, reply_date, reply_comment_no, user_id
			, user_name from myreply join myuser on reply_id=user_id where 
			reply_comment_no=#{replyCommentNo} order by reply_no desc
	</select>
	
	<select id="selectCommentReplyUser" parameterType="int" resultMap="myCommentReplyUserResultMap">
		select comment_no, comment_id, comment_content, comment_date, user_id, user_name
			from mycomment join myuser on comment_id=user_id where comment_no=#{commentNo}
	</select>

방법 2

    <!-- resultMap 엘리먼트의 하위 엘리먼트 작성 순서 -->
	<!-- => constructor, id, result, association, collection, discriminator -->
	<!-- 문제점)resultMap 엘리먼트의 첫번째 하위 엘리먼트로 association 엘리먼트를 사용한
	경우 내부적으로 selectOne() 메소드를 사용하여 하나의 검색행을 Java 객체로 생성하여 제공 -->
	<!-- => SELECT 명령으로 다수의 행이 검색된 경우 TooManyResultsException 발생  -->
	<!-- 해결법)다수의 행이 검색되는 경우 resultMap 엘리먼트의 첫번째 자식 엘리먼트로 
	association 엘리먼트가 아닌 다른 엘리먼트 사용 -->
	<resultMap type="MyCommentReply" id="myCommentReplyResultMap">
		<id column="comment_no" property="commentNo"/>
	
		<association property="comment" javaType="MyComment1">
			<id column="comment_no" property="commentNo"/>
			<result column="comment_id" property="commentId"/>
			<result column="comment_content" property="commentContent"/>
			<result column="comment_date" property="commentDate"/>
		</association>
		
		<!-- collection : 1:N 관계의 테이블 조인에서 0개 이상의 검색행을 List 객체로 생성하여 resultMap 
		엘리먼트의 type 속성값으로 설정된 클래스의 객체 필드에 저장되도록 매핑 처리하는 엘리먼트 -->
		<!-- => id 엘리먼트 또는 result 엘리먼트를 하위 엘리먼트로 사용하여 collection 엘리먼트로
		생성될 List 객체의 요소(객체) 필드에 검색행의 컬럼값이 저장되도록 매핑 처리 -->
		<!-- property 속성 : collection 엘리먼트로 생성된 List 객체가 저장될 클래스의 필드명을 속성값으로 설정 -->
		<!-- ofType 속성 : List 객체의 요소로 저장될 객체의 Java 자료형을 속성값으로 설정 -->
		<!-- => Java 자료형 대신 typeAlias로 설정된 별칭 사용 가능 -->
		<collection property="replyList" ofType="MyReply">
			<id column="reply_no" property="replyNo"/>
			<result column="reply_id" property="replyId"/>
			<result column="reply_content" property="replyContent"/>
			<result column="reply_date" property="replyDate"/>
			<result column="reply_comment_no" property="replyCommentNo"/>
		</collection>
	</resultMap>

	<!-- 게시글번호를 전달받아 MYCOMMENT 테이블에 저장된 게시글정보(1개)와 MYREPLY 테이블에
	저장된 댓글정보(0개 이상)를 검색하여 MyCommentReply 객체로 제공하는 엘리먼트 -->	
	<!-- => 1:N 관계의 테이블 조인에서는 OUTER JOIN를 사용하여 검색 -->
	<select id="selectCommentReply" parameterType="int" resultMap="myCommentReplyResultMap">
		<!-- 댓글이 없는 게시글을 무조건 검색하기 위해 LEFT OUTER JOIN 사용 -->
		select comment_no, comment_id, comment_content, comment_date, reply_no, reply_id
			, reply_content, reply_date, reply_comment_no from mycomment left join myreply
			on comment_no=reply_comment_no where comment_no=#{commentNo} order by reply_no desc 
	</select>

	

---

✅ 이전글 다음글 (MySQL ver.)

<select id="selectPreNumNextNum" resultType="com.project.dto.Notice">
		SELECT 
			(SELECT notice_idx 
		  		FROM notice 
		  		WHERE notice_idx <![CDATA[<]]> #{noticeIdx} AND notice_status = 1 
			  	ORDER BY notice_regDate DESC LIMIT 1) AS prevnum
			  	
			  	, (SELECT notice_idx 
			  	   FROM notice 
			       WHERE notice_idx <![CDATA[>]]> #{noticeIdx} AND notice_status = 1
			       ORDER BY notice_regDate ASC LIMIT 1) AS nextnum
			)
		FROM notice
		WHERE notice_idx = #{noticeIdx} AND notice_status = 1
		ORDER BY notice_regDate DESC
	</select>

---

✅ 검색어 처리 ROWNUM 정렬 (MySQL ver.)

<select id="selectNoticeList" resultType="com.project.dto.Notice">

	    SELECT 
			notice_idx
            , notice_title
            , notice_content 
            , date_format(notice_regdate, '%y-%m-%d') as notice_regdate
            , notice_viewcnt
            , notice_status 
            , ROWNUM 
		FROM (
		
          SELECT @ROWNUM := @ROWNUM + 1 AS ROWNUM
          		 , RN.* 
          FROM (
          
          	SELECT
                  notice_idx
                  , notice_title
                  , notice_content
                  , notice_regdate
                  , notice_viewcnt
                  , notice_status
           FROM notice
	        <choose>
	            <when test="selectKeyword != null and selectKeyword != ''">
	                 WHERE (notice_title LIKE CONCAT('%', #{selectKeyword}, '%') 
	                 OR notice_content LIKE CONCAT('%', #{selectKeyword}, '%'))
	                 AND notice_status = 1
	            </when>
	            <otherwise>
	                WHERE notice_status=1 
	            </otherwise>
	        </choose>
	              ORDER BY notice_regDate DESC
          ) RN, (SELECT @ROWNUM := 0) tmp
      ) SUB 
      WHERE SUB.ROWNUM BETWEEN #{startRow} and #{endRow}

</select>

✅ 검색어 처리 ROWNUM 정렬 (Oracle ver.)

<select id="selectNoticeList" resultType="com.project.dto.Notice">

	    SELECT * FROM (
          SELECT ROWNUM RN, BOARD.* FROM (
          	SELECT
                  notice_idx
                  , notice_title
                  , notice_content
                  , notice_regdate
                  , notice_viewcnt
                  , notice_status
           FROM notice
           WHERE notice_status = 1 AND
        <choose>
	        <when test="selectKeyword != null and selectKeyword != ''">
	            WHERE (notice_title LIKE CONCAT('%', #{selectKeyword}, '%') 
	            OR notice_content LIKE CONCAT('%', #{selectKeyword}, '%'))
	             AND notice_status = 1
	        </when>
	        <otherwise>
	            WHERE notice_status=1 
	        </otherwise>
	    </choose>
              ORDER BY notice_regdate DESC
          ) BOARD
      ) WHERE RN BETWEEN #{startRow} and #{endRow}

</select>

---

✅ 검색어 처리 (Oracle ver.)

<select id="selectNoticeCount" resultType="int">
		SELECT COUNT(*) 
		
		FROM notice
		
        <choose>
        <!-- 검색어 조건 처리 -->
            <when test="selectKeyword != null and selectKeyword != ''">
                 WHERE notice_title LIKE '%' || #{selectKeyword} || '%' OR
                 WHERE notice_content LIKE '%' || #{selectKeyword} || '%'
            </when>
            <otherwise>
                WHERE 1=1 <!-- 검색어가 없는 경우 모든 데이터 검색 -->
            </otherwise>
        </choose>
	</select>

✅ 검색어 처리 (MySQL ver.)

MySQL에서 Like 연산자에서 논리연산자 사용이 불가능 합니다.

<select id="selectNoticeCount" resultType="int">
		SELECT COUNT(*) 
		
		FROM notice
		
        <choose>
        <!-- 검색어 조건 처리 -->
            <when test="selectKeyword != null and selectKeyword != ''">
                 WHERE notice_title LIKE CONCAT('%', #{selectKeyword}, '%') OR
                 WHERE notice_content LIKE CONCAT('%', #{selectKeyword}, '%')
            </when>
            <otherwise>
                WHERE 1=1 <!-- 검색어가 없는 경우 모든 데이터 검색 -->
            </otherwise>
        </choose>
	</select>

---

✅ 중복 데이터 확인 후 insert (MySQL ver.)

방법 1

WHERE NOT EXISTS 사용

<insert id="insertUserinfo">
		INSERT INTO userinfo (
			id
			, pw
			, name
			, nickname
			, address
			, email
			, regdate
			, status  
			, enabled
		)
		VALUES (
			#{id}
			, #{pw}
			, #{name}
			, #{nickname}
			, #{address}
			, #{email}
			, Now()
			, 1
			, 0
		)
		WHERE NOT EXISTS (
			SELECT *
			FROM userinfo
			WHERE id = #{id}
		)
	</insert>

방법 2

INSERT IGNORE 사용

<insert id="insertUserinfo">
		INSERT IGNORE INTO userinfo (
			id
			, pw
			, name
			, nickname
			, address
			, email
			, regdate
			, status  
			, enabled
		)
		VALUES (
			#{id}
			, #{pw}
			, #{name}
			, #{nickname}
			, #{address}
			, #{email}
			, Now()
			, 1
			, 0
		)
	</insert>

---

✅ 날짜 표시 형식 (MySQL ver.)

yyy-mm-dd 형식

<select id="selectPostList" resultType="com.project.dto.Post">
	SELECT 
		post_idx
		, post_loc
		, post_daytype
		, post_title
		, post_content
		, date_format(post_regdate, '%y-%m-%d') as post_regdate
		, post_tag
		, post_viewcnt
		, post_likes
		, post_status
		, post_comment
	FROM post
	ORDER BY post_regdate DESC
</select>

~초 전, ~분 전, ~시간 전, ~일 전, ~주 전, ~개월 전, ~년 전 형식

<select id="selectPostList" resultType="com.project.dto.Post">
	SELECT 
		post_idx
		, post_loc
		, post_daytype
		, post_title
		, post_content
		, CASE
            WHEN TIMESTAMPDIFF(SECOND, p.post_regdate, NOW()) <![CDATA[<]]> 60 THEN CONCAT(TIMESTAMPDIFF(SECOND, p.post_regdate, NOW()), '초 전')
       	    WHEN TIMESTAMPDIFF(MINUTE, p.post_regdate, NOW()) <![CDATA[<]]> 60 THEN CONCAT(TIMESTAMPDIFF(MINUTE, p.post_regdate, NOW()), '분 전')
			WHEN DATEDIFF(NOW(), post_regdate) <![CDATA[=]]> 0 THEN CONCAT(TIMESTAMPDIFF(HOUR, post_regdate, NOW()), '시간 전')
			WHEN DATEDIFF(NOW(), post_regdate) <![CDATA[=]]> 1 THEN '어제'
			WHEN DATEDIFF(NOW(), post_regdate) <![CDATA[<]]> 7 THEN CONCAT(DATEDIFF(NOW(), post_regdate), '일 전')
			WHEN DATEDIFF(NOW(), post_regdate) <![CDATA[<]]> 30 THEN CONCAT(DATEDIFF(NOW(), post_regdate) DIV 7, '주 전')
			WHEN DATEDIFF(NOW(), post_regdate) <![CDATA[<]]> 365 THEN CONCAT(DATEDIFF(NOW(), post_regdate) DIV 30, '개월 전')
			ELSE CONCAT(DATEDIFF(NOW(), post_regdate) DIV 365, '년 전')
		END as post_regdate
		, post_tag
		, post_viewcnt
		, post_likes
		, post_status
		, post_comment
	FROM post
	ORDER BY post_regdate DESC
</select>

• DATEDIFF(date1, date2) 를 이용해 두 날짜 사이의 일수를 계산합니다.
• TIMESTAMPDIFF(단위, datetime1, datetime2) : 날짜 표현식의 차이를 계산합니다.
  • 단위 : SECOND, MINUTE, HOUR, DAY, MONTH, YEAR 등
  • datetime1, datetime2 : 날짜 표현식
• CONCAT : 문자를 연결해줍니다.

---

✅ offset, limit 사용하기 (MySQL ver.)

<select id="selectPostList" resultType="com.project.dto.Post">
	SELECT 
		post_idx
		, post_loc
		, post_daytype
		, post_title
		, post_content
		, date_format(post_regdate, '%y-%m-%d') as post_regdate
		, post_tag
		, post_viewcnt
		, post_likes
		, post_status
		, post_comment
	FROM post
	ORDER BY post_regdate DESC
    LIMIT #{offset}, #{limit}
</select>

offset : 가져올 데이터의 초기 위치값을 지정합니다.
ex) offset 10을 지정하면 11번째 데이터부터 가져오게 됩니다.

limit : 가져올 데이터의 개수를 지정합니다.
ex) limit 20을 지정하면 20개의 데이터를 가져오게 됩니다.

---