쇼핑몰 상품 카테고리 3단 계층 구조 구현

CREATE TABLE category (
  category_sn   BIGINT PRIMARY KEY,
  parent_sn     BIGINT,        -- NULL이면 대분류
  depth         INT,           -- 1/2/3
  category_nm   VARCHAR(100),
  sort_order    INT
);

List<Category> roots = mapper.selectByDepth(1);
for (Category root : roots) {
    root.setChildren(mapper.selectByParent(root.getSn()));
    for (Category mid : root.getChildren()) {
        mid.setChildren(mapper.selectByParent(mid.getSn()));
    }
}