MySQL에서 계층형 쿼리 문제

이 게시판은 자바 웹을 다루는 기술을 참고해서 만들었다. 처음부터 난관이었던 게 책은 Oracle SQL을 사용하는 반면 나는 MySQL을 쓰고 싶었다. MySQL을 고집한 이유는 첫 프로젝트 때 사용했던 DB가 MySQL이었기 때문에... 라고는 하지만 딱히 없다. MySQL을 잘 다루는 것도 아니므로 Oracle을 썼어도 상관 없었겠지만 왠지 그냥 난 책이랑 다르게 하고 싶어! 책을 참고하되, 베끼고 싶지 않은 마음... 아주 작은 부분이라도 차별되는 지점을 만들고 싶었다. 물론 어디까지나 변명일 뿐이다.
하여간.
오라클은 자체적으로 계층형 쿼리를 다룰 수 있는 명령어(?) START WITH, CONNECT BY를 제공한다. 그러나 MYSQL은 그런 게 없었으므로... 열심히 검색 또 검색을 했다. 뭔가 굉장히 복잡해 보이는 쿼리들이 길게 나열되어 있었는데 겁부터 났다. 그날 밤은 이것저것 따라해보다가 삽질만 하다 끝났다. 다음 날 다시 차근차근 해보자고 복붙이 아닌, 직접 예제를 따라 쳐보면서 조금이라도 이해하려고 애를 썼다. 물론 다 이해하지는 못했으나 계층형 쿼리를 흉내낼 수 있는 함수를 만드는 데 성공했다.

/*
구문 내에서 ';'이 자주 사용되므로
DELIMITER $$ 와 $$ DELIMITER로 시작과 끝을 명시해준다.
필수 요소는 아니다.
*/
DELIMITER $$
CREATE DEFINER=`servlet`@`%` FUNCTION `fnc_hierarchy`() RETURNS int
   READS SQL DATA
BEGIN
	DECLARE v_id INT;
   DECLARE v_parent INT;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
   
   SET v_parent = @id;
   SET v_id = -1;
   
   IF @id IS NULL THEN
		RETURN NULL;
	END IF;
   
   LOOP
   
   SELECT MIN(bno)
   INTO @id
   FROM t_board
   WHERE p_bno = v_parent
   AND bno > v_id;
   
   IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
		SET @level = @level + 1;
	RETURN @id;
   END IF;
   
   SET @level := @level - 1;
   
   SELECT bno, p_bno
   INTO v_id, v_parent
   FROM t_board
   WHERE bno = v_parent;
   
   END LOOP;
   
END

$$ DELIMITER ;

-- 호출
SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),'ㄴ'), t.title)
                ELSE t.title
          END AS title
    , t.bno
    , t.p_bno
    ,t.content
    ,t.id
    ,regdate
    , fnc.level
 FROM
    (SELECT fnc_hierarchy() AS id, @level AS level
       FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars
         JOIN t_board
        WHERE @id IS NOT NULL) fnc
 JOIN t_board t ON fnc.id = t.bno

물론 이것이 왜, 어떻게 해서 작동하는지는 차근차근 살펴봐야 할 것이다.

🥵

혹시

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 0.000 sec

함수를 만들다가 이런 에러가 발생했다면 Root계정으로 접속해서

SET Global log_bin_trust_function_creators='ON'

입력하면 해결 된다. 참고

🥶

DAO테스트만 진행하고 Service테스트는 진행하지 않았다.
서버를 돌리지 않는 상황에서 톰캣 커넥션 풀을 이용할 수 없어서 JDBC로 테스트 했다.

우선 이 부분이 의심스러웠다.
실제 프로젝트에서는 커넥션 풀을 이용하는데, 테스트에서는 실제 프로젝트에서 사용하지 않는 JDBC를 가지고 테스트한다?
물론, 연결하는 선만 다르고 다른 것은 똑같으니 하지 않는 것보다는 낫다는 생각에 DAO 테스트를 진행했다.
Service 테스트는 커넥션 풀을 돌릴 수 없으니 아예 시도조차 할 수 없었다. HashTable을 이용해서 서버 없이 커넥션 풀을 사용할 수 있는 것 같아서 한 시간 정도 삽질 했지만 해결하지 못했다. MySQL계층형 쿼리 부분에서 이미 많은 시간 삽질을 했기 때문에 Service계층부터는 테스트 없이 진행하기로 결정했다.