메뉴 권한 관리 구조 쿼리 (MariaDB - WITH RECURSIVE)

MyBatis와 MySQL을 사용하여 메뉴 기반 권한 관리 시스템의 구조를 설정하고 쿼리를 작성해 보겠습니다. 역할(Role)과 메뉴(Menu)를 연결하여 특정 역할에 따라 접근할 수 있는 메뉴를 관리하는 구조입니다.

1. 데이터베이스 테이블 구조 예시

다음은 메뉴(Menu), 역할(Role), 권한(Permission)을 관리하기 위한 기본 테이블 구조입니다.

1.1. 메뉴 테이블 (Menus)

CREATE TABLE Menus (
    menu_id INT PRIMARY KEY AUTO_INCREMENT,
    menu_name VARCHAR(100) NOT NULL,
    parent_menu_id INT,
    url VARCHAR(255),
    order_index INT,
    FOREIGN KEY (parent_menu_id) REFERENCES Menus(menu_id) ON DELETE SET NULL
);

1.2. 역할 테이블 (Roles)

CREATE TABLE Roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) NOT NULL UNIQUE
);

1.3. 메뉴 권한 테이블 (Role_Menus)

Role_Menus 테이블은 특정 역할이 어떤 메뉴에 접근할 수 있는지를 관리합니다.

CREATE TABLE Role_Menus (
    role_menu_id INT PRIMARY KEY AUTO_INCREMENT,
    role_id INT,
    menu_id INT,
    FOREIGN KEY (role_id) REFERENCES Roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (menu_id) REFERENCES Menus(menu_id) ON DELETE CASCADE
);

2. 재귀 쿼리 작성 (MyBatis Mapper)

MyBatis에서 역할에 따른 메뉴 트리 구조를 조회하는 쿼리를 작성합니다. 예를 들어, 특정 역할 ID를 입력 받아 해당 역할의 메뉴 트리 구조를 반환하도록 하겠습니다.

MenuMapper.xml

<?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.example.mapper.MenuMapper">

    <!-- 특정 역할(role_id)에 따른 메뉴 트리 구조 쿼리 -->
    <select id="getMenuTreeByRole" parameterType="int" resultType="com.example.model.Menu">
        WITH RECURSIVE MenuTree AS (
            -- 최상위 메뉴 선택
            SELECT 
                m.menu_id,
                m.menu_name,
                m.parent_menu_id,
                m.url,
                m.order_index,
                CAST(LPAD(m.order_index, 2, '0') as CHAR(256)) AS full_idx,
                CAST(m.menu_name AS CHAR(1000)) AS path,
                1 AS depth
            FROM Menus m
            JOIN Role_Menus rm ON m.menu_id = rm.menu_id
            WHERE rm.role_id = #{roleId} AND m.parent_menu_id = '0'

            UNION ALL

            -- 하위 메뉴 재귀적으로 조회
            SELECT 
                m.menu_id,
                m.menu_name,
                m.parent_menu_id,
                m.url,
                m.order_index,
                CONCAT(mt.full_idx, '-', LPAD(m.order_index, 2, '0')) AS full_idx,
                CONCAT(mt.path, ' > ', m.menu_name) AS path,
                mt.depth + 1 AS depth
            FROM Menus m
            JOIN MenuTree mt ON m.parent_menu_id = mt.menu_id
            JOIN Role_Menus rm ON m.menu_id = rm.menu_id
            WHERE rm.role_id = #{roleId}
        )
        
        -- 결과 정렬
       SELECT menu_id,
              menu_name,
              parent_menu_id,
              url,
              full_idx,
              order_idx,
              path,
              depth
         FROM MenuTree
        ORDER BY full_idx;
    </select>
</mapper>

3. 매핑할 자바 모델 클래스 (예: Menu.java)

이 쿼리 결과를 받을 Menu 클래스에는 menu_id, menu_name, parent_menu_id, url, order_index, path, depth 필드를 포함합니다.

package com.example.model;

public class Menu {
    private int menuId;
    private String menuName;
    private Integer parentMenuId;
    private String url;
    private int orderIndex;
    private String path;
    private int depth;

    // Getters and Setters
    // ...
}

4. 매퍼 인터페이스 (예: MenuMapper.java)

MyBatis에서 쿼리를 호출하기 위한 매퍼 인터페이스를 정의합니다.

package com.example.mapper;

import com.example.model.Menu;
import java.util.List;

public interface MenuMapper {
    List<Menu> getMenuTreeByRole(int roleId);
}

5. 서비스에서 역할 기반 메뉴 트리 호출하기

서비스 계층에서 MenuMapper의 getMenuTreeByRole() 메서드를 호출하여 특정 역할에 따른 메뉴 목록을 가져올 수 있습니다.

package com.example.service;

import com.example.mapper.MenuMapper;
import com.example.model.Menu;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class MenuService {

    @Autowired
    private MenuMapper menuMapper;

    public List<Menu> getMenuTreeByRole(int roleId) {
        return menuMapper.getMenuTreeByRole(roleId);
    }
}

6. 결과 예시

getMenuTreeByRole(roleId) 메서드를 호출하면 roleId에 따라 접근 가능한 메뉴 트리 구조를 반환합니다. 예를 들어, roleId = 1로 조회했을 때 다음과 같은 결과가 나올 수 있습니다.

menu_id	menu_name	parent_menu_id	full_idx	order_index	path	depth
1	Dashboard	NULL	01	1	Dashboard	1
2	Reports	NULL	02	2	Reports	1
3	Sales	2	02_01	1	Reports > Sales	2
4	Marketing	2	02_02	2	Reports > Marketing	2
5	SEO	4	02_02_01	1	Reports > Marketing > SEO	3

이와 같이 특정 역할에 따라 접근할 수 있는 메뉴의 트리 구조를 가져올 수 있습니다.