[Spring] 해시태그 검색 데이터베이스 설계 및 쿼리 구성

프로젝트에 해시태그 검색을 구현 중이다.

하나의 Study 객체가 여러 해시태그에 연결될 수 있어야 한다.

(여기서 스터디란 여러 사람들의 모임이다. 공부할 때 모이는 그 스터디 맞다.)

가장 먼저 떠오르는 것은 (스터디), (해시태그), (스터디-해시태그) 이렇게 3개의 테이블을 구성하는 것인데, 이 방식으로 구현 가능하리란 판단이 들었고 만들기 시작했다.

테이블	STUDY	HASHTAG_RELATION	HASHTAG
역할	스터디 entity만 담는 테이블	스터디 : 해시태그 1 to N 관계의 테이블	해시태그만 저장한 테이블

STUDY 테이블 :

스터디 번호, 이름, url 등 오로지 스터디 관련 정보만이 담겨있다. 해시태그 관련 정보를 담는 컬럼은 없다.

HASHTAG_RELATION 테이블 :

스터디 번호, 해시태그 단 두개의 컬럼만이 존재한다.

각 행은 스터디 번호 1개와 해시태그 1개를 저장하며, 하나의 스터디에 대해 여러 해시태그 열이 존재할 수 있다.

따라서 스터디 1개 : 해시태그 N개의 관계가 된다.

동일한 해시태그를 사용하는 스터디가 여럿 있을 수도 있다. 해시태그 1 : 스터디 N 이기도 하다.

HASHTAG 테이블 :

오로지 해시태그만을 저장하는 테이블이다. (시퀀스 컬럼과 함께)

해시태그는 UNIQUE 제약조건을 가졌으므로 중복이 불가능하다.

Mybatis를 이용해 insert까지는 예상대로 쉽게 구현했는데, 문제는 select였다. (insert 부분은 차후에 추가)

N개의 해시태그 입력후 검색하면, 각 스터디마다 연결된 해시태그를 함께 출력하고 싶었다.

검색결과 개요도 - #x 는 각각 하나의 해시태그이다.

{1번 스터디 내용} #a #b #c	{2번 스터디 내용} #a #c #d	{3번 스터디 내용} #c #e #f
{4번 스터디 내용} #c #d	{5번 스터디 내용} #b #d #e #f	{6번 스터디 내용} #c #d #f #g #h

따라서 select 결과값을 받아올 VO클래스는 아래와 같은 구성이어야 한다.

public class SearchResult {
    private Study study;
    private String[] hashtags;
}

private Study study - 하나의 스터디에 대한 모든 정보를 담고있다.

private String[] hashtags - study에 연결된 모든 해시태그를 담은 array이다.

바로 여기서 머리가 아파온다.

머리아픈 원인은 두 가지이다.

1. ResultMap의 중첩 문제

Study 클래스 내부에 여러 필드변수가 있기 때문에, Study 클래스에 대한 ResultMap 하나가 필요했다.

hashtag 배열 또한 여러 개의 String 값이다.

그리고 위 두 가지를 포함하는 SearchResultVO를 리스트 형태로 받아와야 한다.

따라서 N * (N + N)의 결과값을 가져올 수 있는 mybatis 쿼리를 짜야한다. (가능한가?)

2. 검색결과 테이블 중 특정 컬럼만을 하나의 VO에 넣는 문제

현재 테이블 설계는 스터디 1 : 해시태그 N의 구성이다.

따라서 해시태그 'java'를 입력하고 실행한 검색결과 테이블의 구조는 다음의 형태이다.

STUDY_NAME	STUDY_NUMBER	HASHTAG_NAME
'자바 스터디'	1	'java'
'자바 스터디'	1	'web'
'자바 스터디'	1	'eclipse'
'자바 스터디'	1	'maven'
'스프링 스터디'	2	'java'
'스프링 스터디'	2	'spring'

'java'만 입력했는데 왜 다른 해시태그가 등장했는가 생각할 수 있다.

해시태그 'java'를 포함한 스터디가 가진 모든 해시태그를 출력했기 때문이다.

그러므로 총 6개의 열이 출력된 상황에서

1 ~ 4번 열 : 1개의 VO에 받아와야 한다. ( STUDY_NAME과 STUDY_NUMBER는 한 번만 받아온다. )

5, 6번 열 : 1개의 VO에 받아와야 한다. (이 또한 마찬가지)

그러면서도 HASHTAG_NAME 컬럼의 각각 다른 값은 유지해야 한다.

따라서

1번 VO - {'java', 'web', 'eclipse', 'maven'} 배열 + '자바 스터디'

2번 VO - {'spring', 'framework'} 배열 + '스프링 스터디'

단 2개의 VO가 위 검색결과에서 리턴되어야 한다.

일단 쿼리를 짜보자면 다음과 같다.

SELECT * FROM 
    (SELECT ROWNUM AS RNUM,
            ST_NO,
            MB_NO,
            ST_FILENAME,
            ST_NAME,
            ST_INTRODUCE,
            ST_MAX_PERSONNEL,
            ST_INSERTDATE,
            ST_STATUS,
            ST_URL,
            ST_MON,
            ST_TUE,
            ST_WED,
            ST_THU,
            ST_FRI,
            ST_SAT,
            ST_SUN,
            ST_START,
            ST_END,
            HA_NAME,
            HA_NO
    FROM (SELECT S.*, H.* FROM STUDY S 
        LEFT OUTER JOIN HASHTAG_RELATION R 
            ON S.ST_NO = R.ST_NO
        LEFT OUTER JOIN HASHTAG H
            ON R.HA_NAME = H.HA_NAME
        WHERE S.ST_NAME LIKE '%a%' -- 검색어 사용시 추가
        OR H.HA_NAME LIKE '%z%' -- 해시태그 갯수에 따라 반복
    ORDER BY S.ST_NO DESC))
WHERE RNUM BETWEEN 1 AND 6;

다음 값들은 테스트 과정에서 임의로 넣어준 것이다 -> '%a%' '%z%' 1 6

mapper에 삽입할 때는 파라미터 변수명으로 변경해준다.

오라클에서 쿼리를 실행하니 원하는 결과가 잘 출력되고 있다.

문제는 어떻게 ST_NO행 값이 315인 두 개의 열을 하나의 객체에 넣고, HA_NAME행 값은 그대로 유지하느냐인데,

( + 두 열을 하나의 객체에 넣었을 때 페이지에 출력될 갯수가 1개 줄어드는 문제도)

어떻게 Mybatis 구문을 짜야할지 감이 안 잡히는 상황이지만 질문글을 써본다.

[Stackoverflow] 질문

질문내용은 현재 내가 진행중인 스터디 프로젝트와 다르게 가게 이름으로 치환했다.

우리나라에서는 스터디라는 단어가 여럿이 모여 공부하는 것으로 쓰이지만, 영어로는 그런 뜻이 아닐 것이기에 의미 혼동을 줄이기 위한 것도 있고,

무엇보다 코드를 질문할 때 내 코드를 그대로 올리는 것을 안 좋아하는 편이다.

타인이 내 질문을 잘 이해할 수 있도록 임의의 내용을 만들어내서 질문하는 것을 좋아한다.

질문을 올려둔 후 일단은 급한대로 1개의 스터디 row만 반환하는 mybatis 코드를 짜서 이용하고 있다.

입력한 해시태그들 중 하나라도 일치할 경우 스터디 값만 리턴하는 상태이다. 해시태그 컬럼은 리턴값에서 제외했다.

위 질문글에 답변이 달리거나 다른 방법을 알아내면 아래에 내용을 추가.

~~(그리고 답변은 없었고 돌아오지 않았다고 한다.??)~~

2021. 05. 27 10:53pm

2021. 05. 28 10:06am

여러 해시태그를 ArrayList 형태로 묶은 하나의 VO로 가져오는데 성공했다.

여기서 SearchResult VO의 변경이 필요했는데, 기존에 예상했던

public class SearchResult {
    private Study study;
    private String[] hashtags;
}

의 구조에서

public class SearchResult {

    // 기존의 Hashtag[] 필드변수
    private ArrayList<Hashtag> hashtagList;

    // 기존의 Study 필드변수
    private int studyNo;
    private String studyName;
    // ...
}

로 변경되었다.

mybatis의 collection 태그를 사용하기 위해 Hashtag 클래스를 새로 만들어야 했다.

Hashtag 클래스의 전역변수는 단 두 가지이다.

public class Hashtag {
    private int hashtagNo;
    private String name;
}

hashtagNo - 해시태그 테이블의 시퀀스, name = 해시태그

또한기존 해시태그 Array 필드변수는 ArrayList가 되고, 기존 Study 필드변수는 Study 변수내의 모든 필드변수를 SearchResult 클래스의 필드변수로 가져왔다.

VO의 변경에 따라 resultMap또한 변경이 필요했는데, 기존에 시도했던

<resultMap id="studySearchMap" type="SearchResult">
    <result property="hashtagList" column="HA_NAME"></result>
    <association property="study" resultMap="studyResultMap"></association>
</resultMap>

의 구조가 (위 resultMap은 의도대로 동작하지 않는다)

<resultMap id="studySearchMap" type="SearchResult">
    <id property="studyNo" column="ST_NO"></id>
    <result property="studyName" column="ST_NAME"></result>
    <result ...
    			여러 Study 필드변수들
                                                   ...	</result>
    <collection property="hashtagList" ofType="Hashtag" autoMapping="true">
    	<id property="hashtagNo" column="HA_NO" javaType="int"></id>
    	<result property="name" column="HA_NAME" javaType="String"></result>
    </collection>
</resultMap>

로 변경되었다.

기존 SearchResult VO를 사용했을 때 문제점은, N개 열을 하나의 SearchResult VO에 합칠 때, 합칠 대상의 기준이 무엇인지 mybatis가 알 수 없었다. 예를 들어 해시태그 검색 결과가 아래와 같을 때

STUDY_NAME	STUDY_NUMBER	HASHTAG_NAME
'자바 스터디'	1	'java'
'자바 스터디'	1	'web'
'자바 스터디'	1	'eclipse'
'자바 스터디'	1	'maven'
'스프링 스터디'	2	'java'
'스프링 스터디'	2	'spring'

4개의 '자바 스터디' 열이 하나의 객체가 되어야 한다는 것을 내가 알고 있을 뿐, mybatis에게는 알려주지 않았다.

이를 mybatis에게 인식시키기 위해서는 id 태그가 resultMap태그 내부에 필요하다.

변경 이후의 resultMap에는 아래와 같이 id태그를 포함시켰다.

Study 테이블의 PK 시퀀스인 ST_NO가 id라는 것을 명시하였고, mybatis는 ST_NO필드값이 같은 열에 대해 하나의 VO로 인식하기 시작했다.

<resultMap id="studySearchMap" type="SearchResult">
    <id property="studyNo" column="ST_NO"></id>
    <result property="studyName" column="ST_NAME"></result>
    <result ...
    			여러 Study 필드변수들
                                                   ...	</result>
    <collection property="hashtagList" ofType="Hashtag" autoMapping="true">
    	<id property="hashtagNo" column="HA_NO" javaType="int"></id>
    	<result property="name" column="HA_NAME" javaType="String"></result>
    </collection>
</resultMap>

큰 산 하나를 넘었지만, 아직 문제가 남아있다.

6개를 한 페이지에 출력하기 위해 6개의 열을 select 한 상태에서, 시퀀스값이 같은 열들을 하나의 VO로 넣으니 최종적으로 출력할 갯수는 6개 미만이 되는 문제이다.

이 때 DENSE_RANK() 구문을 사용해준다.

SELECT * FROM (
	SELECT *,
    DENSE_RANK() OVER (ORDER BY COLUMN_1 DESC) RANK
    FROM TABLE_1
) WHERE RANK BETWEEN 1 AND 6

DENSE_RANK() OVER (정렬기준) RANK 를 이용하여 COLUMN_1 필드값 순서대로 줄을 세운 뒤, 동일한 값에 대해 동일한 RANK값을 매길 수 있다.

아래는 이를 STUDY 테이블에 적용한 쿼리이다.

SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY S.ST_NO DESC) RNUM, S.*, H.* FROM STUDY S 
    LEFT OUTER JOIN HASHTAG_RELATION R 
        ON S.ST_NO = R.ST_NO
    LEFT OUTER JOIN HASHTAG H
        ON R.HA_NAME = H.HA_NAME
    WHERE S.ST_NAME LIKE '%a%'
    OR H.HA_NAME LIKE '%z%')
WHERE RNUM BETWEEN 1 AND 6;

기존 쿼리에 비해 상당히 짧아졌다.

이전에 사용하던 쿼리는 ROWNUM이 최상단에서 매겨졌고, 테이블 조인은 내부에서 이루어졌기 때문에, 최상단에서는 * 를 이용해 컬럼 전체를 출력하면 missing expression 오류가 발생했다. 따라서 컬럼명을 하나하나 적어주어야 했는데, DENSE_RANK 구문을 사용하니 RNUM을 자연스럽게 매겨준다.

이로써 최초 설계했던 3개 테이블 기반의 해시태그 기능이 INSERT부터 SELECT까지 모두 완성되었다.

'Database' 카테고리의 다른 글

[Mybatis] select 문에 String 검색어 추가하기 (0)	2021.05.02
[MyBatis] insert한 컬럼의 id를 return하기 (0)	2021.05.02
[데이터베이스] DDL, DML, DCL, DQL, TCL 의 정의 및 주요함수 (0)	2021.03.12
[오라클] 일정 숫자만큼 규칙적으로 증가하는 입력 [시퀀스] - 생성, 사용, 수정, 초기화하기 (0)	2021.03.11
[오라클] 뷰 생성, 수정하기 (0)	2021.03.11

개발자와 코더 사이

[Spring] 해시태그 검색 데이터베이스 설계 및 쿼리 구성

'Database' 카테고리의 다른 글

댓글

티스토리툴바

[Spring] 해시태그 검색 데이터베이스 설계 및 쿼리 구성

'Database' 카테고리의 다른 글

관련글

댓글

티스토리툴바