오늘 Oracle SQL 서브쿼리문을 MariaDB로 변환해 봤는데 나중에 필요할 것 같아 여기 정리해 둔다.
█ Oracle SQL 서브쿼리
먼저 Oracle SQL로 작성된 쿼리문이다.
서브쿼리가 적용되어 약간 복잡스럽다.
# Oracle SQL #
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
ROWNUM AS RNUM
, BD.*
FROM TB_BOARD_DOC BD
WHERE MAP_ID = 50
AND ROWNUM <= 10
ORDER BY DOC_ID DESC
) A
WHERE RNUM < 0 ;
일단 서브 쿼리 부분만 변환해 본다.
█ MariaDB - WHERE
MariaDB에는 오라클의 ROWNUM같은 쿼리가 없다.
따라서 MariaDB에서는 사용자 정의 변수를 이용하여 ROWNUM을 구현한다.
(SELECT @ROWNUM := 0) TMP
이는 @ROWNUM을 초기화하는 쿼리문이다.
사용자 정의 변수 @ROWNUM은 초기화해주지 않으면 NULL값을 갖으며 MariaDB에서 NULL + 1 = NULL이다.
# MariaDB로 변환 #1 #
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
,BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := 0) TMP
WHERE MAP_ID = 50
AND @ROWNUM < 5
ORDER BY DOC_ID DESC;
버전 10.2부터 MariaDB에도 ROWNUM과 흡사한 기능의 함수 ROW_NUMBER()가 도입될 거라고 한다.
현재 안정화된 최신 MariaDB 버전은 10.1.21이며 버전 10.2.*는 최종 베타 상태다.
█ MariaDB - LIMIT
페이징에는 WHERE @ROWNUM < 5 보다 LIMIT 0, 5가 적절해 보인다.
# MariaDB로 변환 #2 #
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
,BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := 0) TMP
WHERE MAP_ID = 50
ORDER BY DOC_ID DESC
LIMIT 0, 5;
█ 전체를 MariaDB로 변환
다섯 줄씩 보내주는 쿼리문이다.
다음과 같은 식으로 호출하면 되겠다.
페이지 #1 - @ROWNUM := 0 / LIMIT 0, 5
페이지 #2 - @ROWNUM := 5 / LIMIT 5, 5
페이지 #3 - @ROWNUM := 10 / LIMIT 10, 5
만약 열 줄씩 받길 원한다면...
페이지 #3 - @ROWNUM := 20 / LIMIT 20,10
# 전체 MariaDB로 변환 #
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
, BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := 0) TMP
WHERE MAP_ID = 50
ORDER BY DOC_ID DESC
LIMIT 0,5
) A
ORDER BY RNUM ASC;
█ MyBatis 쿼리 xml
# MyBatis 쿼리 xml #
<!-- 오라클 DB -->
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
ROWNUM AS RNUM
, BD.*
FROM TB_BOARD_DOC BD
WHERE MAP_ID = #{mapId}
AND ROWNUM <![CDATA[ <= ]]> (#{page}*#{rows})
ORDER BY DOC_ID DESC
) A
WHERE RNUM <![CDATA[ > ]]> (#{page}-1)*#{rows}
<!-- MariaDB -->
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
, BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := (#{page}-1)*#{rows}) TMP
WHERE MAP_ID = #{mapId}
ORDER BY DOC_ID DESC
LIMIT (#{page}-1)*#{rows}, #{rows}
) A
ORDER BY RNUM ASC
█ 오류 발생
한마디로 안된다.
(#{page}-1)*#{rows}, 이게 안된단다.
이게 되길래 될 줄 알았는데...
select * from TB_USER WHERE USER_ID = 2*3;
█ workaround
웹 검색으로 찾아보다 시간이 없어 나중에 더 찾아보기로 하고 당장은 변수를 하나 더 만들었다.
private int tempInt = 0;
# BoardDocDaoImpl.java #
_search.setTempInt((_search.getPage()-1)*_search.getRows());
List<BoardDocDTO> list = getSqlSession().selectList("BoardDoc.selectList", _search);
# BoardDoc.xml #
<!-- MariaDB -->
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
, BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := #{tempInt}) TMP
WHERE MAP_ID = #{mapId}
ORDER BY DOC_ID DESC
LIMIT #{tempInt}, #{rows}
) A
ORDER BY RNUM ASC
█ 쿼리에 검색 조건을 주다
제목에 '테스트'를 포함하고 있는 결과만 가져오려면 다음과 같이 한다.
첫 번째는 오라클, 두 번째는 MariaDB용 쿼리이며 빨간색은 검색 조건으로서 새로 더해진 쿼리다.
# Oracle DB #
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
ROWNUM AS RNUM
, BD.*
FROM TB_BOARD_DOC BD
WHERE MAP_ID = 50
AND ROWNUM <= 10
AND TITLE LIKE '%' || '테스트' || '%'
ORDER BY DOC_ID DESC
) A
WHERE RNUM > 0
# MariaDB #
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
, BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := 0) TMP
WHERE MAP_ID = 50
AND TITLE LIKE CONCAT('%', '테스트','%')
ORDER BY DOC_ID DESC
LIMIT 0, 10
) A
ORDER BY RNUM ASC
█ MyBatis 동적 쿼리
게시물 제목만 검색하는 것이 아니라, 내용, 내용과 제목 모두, 또는 작성자 검색 기능을 더하자.
각 경우에 따른 4개의 서로 다르지만, 무척 흡사한 쿼리문이 필요할 것이다.
하지만 Mybatis 동적 쿼리 기능을 이용하면 이들 4개를 하나로 묶을 수 있다.
# Oracle SQL #
<if test="searchType eq 'T'.toString()">
AND TITLE LIKE '%' || #{searchText} || '%'
</if>
<if test="searchType eq 'C'.toString()">
AND DOC_CONTENTS LIKE '%' || #{searchText} || '%'
</if>
<if test="searchType eq 'TC'.toString()">
AND (TITLE LIKE '%' || #{searchText} || '%'
OR DOC_CONTENTS LIKE '%' || #{searchText} || '%'
)
</if>
<if test="searchType eq 'R'.toString()">
AND USER_ID IN (
SELECT USER_ID
FROM TB_USER
WHERE NAME LIKE '%' || #{searchText} || '%'
)
</if>
# MariaDB #
<if test="searchType eq 'T'.toString()">
AND TITLE LIKE CONCAT('%', #{searchText},'%')
</if>
<if test="searchType eq 'C'.toString()">
AND DOC_CONTENTS LIKE CONCAT('%', #{searchText},'%')
</if>
<if test="searchType eq 'TC'.toString()">
AND (TITLE LIKE CONCAT('%', #{searchText},'%')
OR DOC_CONTENTS LIKE CONCAT('%', #{searchText},'%')
)
</if>
<if test="searchType eq 'R'.toString()">
AND USER_ID IN (
SELECT USER_ID
FROM TB_USER
WHERE NAME LIKE CONCAT('%', #{searchText},'%')
)
</if>
█ MyBatis 동적 쿼리 삽입 위치
조건을 더해준 것이니 WHERE 아래에 넣어주면 된다.
# MariaDB #
<select id="selectList" parameterType=“….BoardSearchDTO" resultType=“….BoardDocDTO">
SELECT
A.*
, (SELECT NAME FROM TB_USER WHERE USER_ID = A.USER_ID) AS NAME
FROM (
SELECT
@ROWNUM:=@ROWNUM+1 AS RNUM
, BD.*
FROM TB_BOARD_DOC BD,
(SELECT @ROWNUM := #{tempInt}) TMP
WHERE MAP_ID = #{mapId}
<if test="searchType eq 'T'.toString()">
AND TITLE LIKE CONCAT('%', #{searchText},'%')
</if>
<if test="searchType eq 'C'.toString()">
AND DOC_CONTENTS LIKE CONCAT('%', #{searchText},'%')
</if>
<if test="searchType eq 'TC'.toString()">
AND (TITLE LIKE CONCAT('%', #{searchText},'%')
OR DOC_CONTENTS LIKE CONCAT('%', #{searchText},'%')
)
</if>
<if test="searchType eq 'R'.toString()">
AND USER_ID IN (
SELECT USER_ID
FROM TB_USER
WHERE NAME LIKE CONCAT('%', #{searchText},'%')
)
</if>
ORDER BY DOC_ID DESC
LIMIT #{tempInt}, #{rows}
) A
ORDER BY RNUM ASC
</select>
- 끝 -
우연히, 로그아웃된 상태에서 내 글을 마우스 드래그하여 복사할려니 안된다.
난 마우스 우클릭 막은 적이 없는데... 왜 안되는 거야?