미니 프로젝트 회고(?)

혀니·2024년 7월 22일

LG U+ 유레카 백엔드 과정

목록 보기

12/17

미니 프로젝트를 하면서 고민했던 부분, 공부했던 부분에 대해 정리해보고자 한다.

자바만 사용해서 swing과 jdbc, 그리고 MySQL로 2일만에 개발한 프로젝트였다. (기간이 짧았음. 사실상 24시간...?)

흔한 쇼핑몰보다는 그래도 어느정도 색다른 쇼핑몰을 하고 싶어서 "미니 스팀" 게임 쇼핑몰을 만들고자 하였다.

기획

요구사항 명세

서비스는 회원가입 후 이용 가능하다.
회원가입 시 아이디, 비밀번호, 이름, 전화번호, 주소, 나이 등의 정보를 기입해야 한다.
고객은 검색 및 카테고리 별로 원하는 제품에 접근할 수 있어야 한다.
카테고리는 메인 카테고리, 서브 카테고리로 구성된다.
세부 카테고리를 클릭하면 해당 카테고리에 관한 제품 목록이 표시되어야 한다.
제품을 상세보기 하면 제품 리뷰, 좋아요 개수, 이미지, 가격, 브랜드와 같은 상세 내용을 확인할 수 있어야 한다.
제품을 장바구니에 담을 수 있어야 한다.
장바구니에 있는 상품의 수량을 변경할 수 있어야 하고, 삭제가 가능해야 한다.
장바구니에 있는 상품은 주문을 할 수 있어야 한다.
주문 내역을 보여줄 때 총 가격, 주문 날짜를 볼 수 있어야 하고 상세보기를 누르면 구매한 물건을 볼 수 있어야 한다.
고객은 상품에 대해 좋아요, 리뷰 등록, 삭제를 할 수 있다.
결제 정보는 카드, 무통장 입금 등 정보를 담고 status는 결제 상태에 대한 정보를 담는다.
판매자 권한을 가진 사용자는 게임 판매 등록을 할 수 있다.

ERD

고민 & 공부

1. 카테고리 계층 구조

위와 같이 계층이 있는 카테고리는 카테고리 테이블에 모든 카테고리를 넣고 셀프 조인을 하며 부모 카테고리를 찾아가는 방법으로 쓴다고 한다.

상위 카테고리가 선택되면 해당 카테고리를 부모로 갖는 정보를 불러와서 하위 카테고리 콤보박스에 띄워준다.
하위 카테고리가 선택되면 상위 카테고리와 하위 카테고리에 맞는 정보를 불러와 상품을 검색하는 로직으로 구현하였다.

여기서 나는 상위 카테고리, 하위 카테고리는 셀프 조인으로 계층이 있는데 어떻게 구현해야 하는건지 고민이었다.
그래서 찾아본 결과 계층이 있는 데이터를 처리할 때 WITH RECURSIVE와 UNION를 사용한다고 한다.

WITH RECURSIVE를 사용하는 계층형 쿼리 구조
WITH RECURSIVE 임시테이블명 AS (
기저쿼리
UNION_ALL
임시 테이블을 활욯하는 쿼리
) 최종쿼리

이를 프로젝트에 적용시키면 아래와 같은 구조를 갖게된다.

WITH RECURSIVE를 사용하는 계층형 쿼리

WITH RECURSIVE 임시테이블명 AS (
	입력한 카테고리 이름을 받아와서 카테고리 정보 추출하는 쿼리
UNION ALL
	-- 재귀가 이루어지는 곳
	위에서 받아온 카테고리 아이디로 부모 카테고리를 찾는 쿼리
) 부모와 자식 카테고리를 통해 상품 검색하는 쿼리

그래서 아래와 같이 먼저 상위 카테고리 목록을 불러오고, 상위 카테고리가 선택되면 하위 카테고리 목록을 불러오고, 하위 카테고리가 선택되면 상위, 하위 카테고리들을 사용하여 상품을 조회하는 계층 쿼리를 사용하게 된다.

    public List<String> getTopCategories() {
        List<String> categories = new ArrayList<>();
        String sql = "SELECT name FROM category WHERE parent_categoryid IS NULL";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                categories.add(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.releaseConnection(resultSet, preparedStatement, connection);
        }

        return categories;
    }

    public List<String> getSubCategories(String parentCategory) {
        List<String> subCategories = new ArrayList<>();
        String sql = "SELECT c.name FROM category c JOIN category p ON c.parent_categoryid = p.categoryid WHERE p.name = ?; ";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, parentCategory);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                subCategories.add(resultSet.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.releaseConnection(resultSet, preparedStatement, connection);
        }

        return subCategories;
    }
    
    // 카테고리로 제품을 검색하는 메서드
    public List<Product> searchProductsCategory(String topCategory, String subCategory, String userId) {
        List<Product> productList = new ArrayList<>();
        String sql = "WITH RECURSIVE CategoryHierarchy AS (" +
                "  SELECT categoryid, name, parent_categoryid " + // 입력한 카테고리 이름 받아와서 조회
                "  FROM category " +
                "  WHERE name = ? " +
                "  UNION ALL " +
                "  SELECT c.categoryid, c.name, c.parent_categoryid " + // 재귀 - 부모 카테고리를 조회
                "  FROM category c " +
                "  JOIN CategoryHierarchy ch ON c.parent_categoryid = ch.categoryid " +
                ") " +
                "SELECT p.*, b.brandname FROM product p " + // 부모와 자식 카테고리를 통해 상품 검색
                "JOIN brand b ON p.brand_brandid = b.brandid " +
                "JOIN CategoryHierarchy ch ON p.category_categoryid = ch.categoryid " +
                "WHERE ch.name = ?";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBManager.getConnection();
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, topCategory);
            preparedStatement.setString(2, subCategory);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Product product = new Product();
                product.setProductId(resultSet.getInt("productid"));
                product.setPdname(resultSet.getString("pdname"));
                product.setPrice(resultSet.getInt("price"));
                product.setContent(resultSet.getString("content"));
                product.setImage(resultSet.getString("image"));
                product.setBrandName(resultSet.getString("brandname"));

                boolean flag = checkPurchased(userId, product.getProductId());

                if (flag) {
                    product.setHas("보유중");
                } else {
                    product.setHas("구매가능");
                }

                productList.add(product);
            }

        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.releaseConnection(resultSet, preparedStatement, connection);
        }

        return productList;
    }

}

참고자료
https://velog.io/@qwerty1434/WITH-RECURSIVE%EB%A1%9C-%EA%B3%84%EC%B8%B5%EA%B5%AC%EC%A1%B0-%ED%83%90%EC%83%89%ED%95%98%EA%B8%B0

2. 상태, role 등 코드 테이블

수업을 들었을 때 이러한 상태 같은 컬럼은 데이터를 추가하고 삭제하는 등의 경우를 고려하여 확장성 면에서 테이블을 분리하는게 좋다고 배워서 분리하였다.

role의 경우에도 다른 역할을 추가할 수도 있는 것이고, status도 새로운 상태를 추가하거나 기존 상태의 이름을 바꾼다면 위와 같이 하는 것이 좋다고 생각하였다.

하지만 개발을 진행하다보니 위와 같은 구조는 1:1 구조이기 때문에 불필요한 쿼리가 많아진다는 것을 느꼈다.
테이블을 나누지 않으면 select 쿼리를 한번만 써도 될 것을 2번 써야 됐기 때문이다.

또한 1:1 관계는 정말 필요한건지 고민해봐야 한다고 배웠기 때문에 코드 테이블을 나누는 것이 정말 좋은건지 고민이 되었다.

그래서 강사님께 여쭤보았다.

1:1 관계 테이블은 처음부터 하나로 만들어도 되지만
위와 같이 코드 테이블을 따로 나누는 건 메인, 서브 테이블 개념이라 일반 데이터는 메인에, 부가 데이터는 서브에 넣고 따로 관리하는 꼴이 된다.
이는 위 데이터가 모두 동시에 필요하면 부담이지만, 일반 데이터만 필요한 경우가 많다면 이득이 된다.

그래서 공통 코드 테이블은 분리해서 사용하는게 좋다는 것 같다.