29-3. (데이터베이스) 쇼핑몰 프로그램 만들기 (심화)_최종

1. model 패키지

1️⃣ Cart 클래스

package com.mtcoding.shop.model;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * cart_tb
 */

@AllArgsConstructor
@Data
public class Cart {
    private Integer cId;  // 숫자증가(PK)
    private Integer uId; // 장바구니에 담은 유저 ID
    private Integer pId; // 장바구니에 담은 상품 ID
    private Integer qty; // 장바구니에 담은 상품 개수
}

2️⃣ Order 클래스

package com.mtcoding.shop.model;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * order_tb
 */

@AllArgsConstructor
@Data
public class Order {
    private Integer oId; // 숫자증가(PK)
    private Integer uId; // 주문한 유저 ID
    private Integer pId; // 주문한 상품 ID
    private Integer qty; // 주문한 상품 개수
    private Integer totalPrice; // 총 금액
}

3️⃣ Product 클래스

package com.mtcoding.shop.model;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * product_tb
 */
@AllArgsConstructor
@Data
public class Product {
    private Integer pId; // 숫자증가(PK)
    private String pName; // 상품명
    private Integer pPrice; // 상품가격
    private Integer pStock; // 상품재고
}

4️⃣ User 클래스

package com.mtcoding.shop.model;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * user_tb
 */

@AllArgsConstructor
@Data
public class User {
    private Integer uId; // 숫자증가(PK)
    private String uName; // 아이디
    private String uPassword; // 패스워드
    private String uEmail; // 이메일
}

2. repository 패키지

1️⃣ CartRepository 클래스

package com.mtcoding.shop.repository;

import com.mtcoding.shop.DBConnection;
import com.mtcoding.shop.view.CartListView;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CartRepository {

    // 장바구니 담기
    public int insert(int uId, int pId, int qty) {
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "insert into cart_tb (u_id, p_id, qty) values (?, ?, ?)";
        try {
            // 2. 버퍼달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uId);
            pstmt.setInt(2,pId);
            pstmt.setInt(3,qty);

            // 3. 쿼리 전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 1;
    }

    // 장바구니 보기
    public CartListView selectJoin(int uId){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = """
                select ct.c_id, ut.u_name, pt.p_name, ct.qty
                from cart_tb ct left outer join user_tb ut
                on ct.u_id = ut.u_id
                left outer join product_tb pt
                on ct.p_id = pt.p_id
                where ct.u_id=?
                """;
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uId);

            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                int cId = rs.getInt("c_id");
                String  uName = rs.getString("u_name");
                String  pName = rs.getString("p_name");
                int qty = rs.getInt("qty");
                CartListView cartListView = new CartListView(cId, uName, pName, qty);
                return cartListView;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}

2️⃣ OrderRepository 클래스

package com.mtcoding.shop.repository;

import com.mtcoding.shop.DBConnection;
import com.mtcoding.shop.view.OrderListView;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OrderRepository {

    // 주문하기
    public int insert(int uId, int pId, int qty, int totalPrice){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "insert into order_tb (u_id, p_id, qty, total_price) values (?, ?, ?, ?)";
        try {
            // 2. 버퍼 달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uId);
            pstmt.setInt(2,pId);
            pstmt.setInt(3,qty);
            pstmt.setInt(4,totalPrice);

            // 3. 쿼리 전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return -1;
    }

    // 주문 이력 보기
    public List<OrderListView> selectJoin(int uId){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = """
                select ot.o_id, ut.u_name, pt.p_name, ot.qty, pt.p_price, ot.total_price
                from order_tb ot left outer join user_tb ut
                on ot.u_id = ut.u_id
                left outer join product_tb pt
                on ot.p_id = pt.p_id
                where ot.u_id = ?
                """;
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,uId);

            ResultSet rs = pstmt.executeQuery();

            List<OrderListView> list = new ArrayList<>();
            while (rs.next()) {
                int oId = rs.getInt("o_id");
                String uName = rs.getString("u_name");
                String pName = rs.getString("p_name");
                int qty = rs.getInt("qty");
                int pPrice = rs.getInt("p_price");
                int totalPrice = rs.getInt("total_price");
                OrderListView orderListView = new OrderListView(oId,uName,pName,qty,pPrice,totalPrice);
                list.add(orderListView);
            }

            return list;

        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }
}

3️⃣ ProductRepository 클래스

package com.mtcoding.shop.repository;

import com.mtcoding.shop.DBConnection;
import com.mtcoding.shop.model.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductRepository {

    // 주문 후 상품 재고 수정하기
    public int update(int pId, int qty){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "update product_tb set p_stock = p_stock - ? where p_id = ?";
        try {
            // 2. 버퍼 달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(2, pId);
            pstmt.setInt(1, qty);

            // 3. 쿼리 전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return -1;
    }

    // 주문하기 위해 상품 금액 찾기
    public int selectOne(int pID) {
        Connection conn = DBConnection.getConnection();

        String sql = "select p_price from product_tb where p_id=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pID);

            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                int pPrice = rs.getInt("p_price");
                return pPrice;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 1;
    }

    // 상품 목록 보기
    public List<Product> selectAll(){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "select * from product_tb order by p_id desc";
        try {
            // 2. 버퍼 달기
            PreparedStatement pstmt = conn.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();

            List<Product> list = new ArrayList<>();
            while (rs.next()){
                int id = rs.getInt("p_id");
                String pName = rs.getString("p_name");
                int pPrice = rs.getInt("p_price");
                int pStock = rs.getInt("p_stock");
                Product product = new Product(id, pName, pPrice, pStock);
                list.add(product);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    // 상품 등록하기
    public int insert(String pName, int pPrice, int pStock){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "insert into product_tb(p_name, p_price, p_stock) values(?,?,?)";
        try {
            // 2. 버퍼달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pName);
            pstmt.setInt(2, pPrice);
            pstmt.setInt(3, pStock);

            // 3. 쿼리전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
}

4️⃣ UserRepository 클래스

package com.mtcoding.shop.repository;

import com.mtcoding.shop.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class UserRepository {

    // 회원 가입
    public int insert(String uName, String uPassword, String uEmail){
        // 1. DBMS와 연결된 소켓
        Connection conn = DBConnection.getConnection();

        String sql = "insert into user_tb(u_name, u_password, u_email) values(?, ?, ?)";
        try {
            // 2. 버퍼 달기
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, uName);
            pstmt.setString(2, uPassword);
            pstmt.setString(3, uEmail);

            // 3. 쿼리 전송
            int result = pstmt.executeUpdate();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }
}

3. service 패키지

1️⃣ CartService 클래스

package com.mtcoding.shop.service;

import com.mtcoding.shop.repository.CartRepository;
import com.mtcoding.shop.view.CartListView;

import java.util.List;

public class CartService {

    CartRepository cartRepository;

    public CartService(CartRepository cartRepository) {
        this.cartRepository = cartRepository;
    }

    // 장바구니 담기 트랜잭션
    public void 장바구니담기(int uId, int pId, int qty){
        // 1. cart_tb에 insert
        cartRepository.insert(uId,pId,qty);
    }

    // 장바구니 보기 트랜잭션
    public CartListView 장바구니보기(int uId){
        // 1. cart selectJoin(uId)
        return cartRepository.selectJoin(uId);
    }
}

2️⃣ OrderService 클래스

package com.mtcoding.shop.service;

import com.mtcoding.shop.repository.OrderRepository;
import com.mtcoding.shop.repository.ProductRepository;
import com.mtcoding.shop.view.OrderListView;

import java.util.List;

/**
 * 서비스는 트랜잭션 관리 책임
 */
public class OrderService {

    ProductRepository productRepository;
    OrderRepository orderRepository;

    public OrderService(ProductRepository productRepository, OrderRepository orderRepository) {
        this.productRepository = productRepository;
        this.orderRepository = orderRepository;
    }

    // 주문하기 트랜잭션
    public void 주문하기(int uId, int pId, int qty){
        // 1. product selectOne(pId) -> 가격
        productRepository.selectOne(pId);

        // 2. order insert
        orderRepository.insert(uId, pId, qty, productRepository.selectOne(pId)*qty);

        // 3. product update(pId, qty)
        productRepository.update(pId, qty);
    }

    // 유저별 주문 이력 보기
    public List<OrderListView> 주문이력보기(int uId){
        // 1. selectJoin();
        return orderRepository.selectJoin(uId);
    }
}

3️⃣ ProductService 클래스

package com.mtcoding.shop.service;

import com.mtcoding.shop.model.Product;
import com.mtcoding.shop.repository.ProductRepository;

import java.util.List;

public class ProductService {

    ProductRepository productRepository;

    public ProductService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }

    // 상품 등록 트랜잭션
    public void 상품등록(String pName, int pPrice, int pStock){
        // 1. product_tb에 insert
        productRepository.insert(pName, pPrice, pStock);
    }

    // 상품 목록 보기 트랜잭션
    public List<Product> 상품목록보기(){
        // 1. 전체조회 (order by pId desc)
        return productRepository.selectAll();
    }
}

4️⃣ UserService 클래스

package com.mtcoding.shop.service;

import com.mtcoding.shop.repository.UserRepository;

public class UserService {

    UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    // 회원 가입 트랜잭션
    public void 회원가입(String uName, String uPassword, String uEmail){
        // 1. user_tb에 insert
        userRepository.insert(uName, uPassword, uEmail);
    }
}

4. view 패키지

1️⃣ CartListView 클래스

package com.mtcoding.shop.view;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * View 결과를 아래 클래스에 파싱하기
 */

@AllArgsConstructor
@Data
public class CartListView {
    private Integer cId; // 장바구니 번호
    private String uName; // uId 대신 유저 이름 가져오기 (조인)
    private String pName; // pId 대신 상품 이름 가져오기 (조인)
    private Integer qty; // 장바구니에 담은 상품 개수
}

2️⃣ OrderListView 클래스

package com.mtcoding.shop.view;

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * View 결과를 아래 클래스에 파싱하기
 */

@AllArgsConstructor
@Data
public class OrderListView {
    private Integer oId; // 주문번호
    private String uName; // uId 대신 유저 이름 가져오기 (조인)
    private String pName; // pId 대신 상품 이름 가져오기 (조인)
    private Integer qty; // 주문한 상품 개수
    private Integer pPrice; // 상품 개별 가격
    private Integer totalPrice; // 주문한  상품 전체 금액
}

5. DBConnection 클래스

package com.mtcoding.shop;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * shop 데이터베이스에서 작업하시오!
 * create database shopdb;
 */
public class DBConnection {

    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/shopdb";
        String username = "root";
        String password = "bitc5600!";

        try {
            // new 클래스명();
            Class.forName("com.mysql.cj.jdbc.Driver");
            // conn =  프로토콜이 적용된 소켓
            Connection conn = DriverManager.getConnection(url, username, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

6. ShopApp 클래스

package com.mtcoding.shop;

import com.mtcoding.shop.model.Product;
import com.mtcoding.shop.model.User;
import com.mtcoding.shop.repository.CartRepository;
import com.mtcoding.shop.repository.OrderRepository;
import com.mtcoding.shop.repository.ProductRepository;
import com.mtcoding.shop.repository.UserRepository;
import com.mtcoding.shop.service.CartService;
import com.mtcoding.shop.service.OrderService;
import com.mtcoding.shop.service.ProductService;
import com.mtcoding.shop.service.UserService;

import java.util.List;

public class ShopApp {
    public static void main(String[] args) {
        ProductRepository productRepository = new ProductRepository();
        CartRepository cartRepository = new CartRepository();
        UserRepository userRepository = new UserRepository();
        OrderRepository orderRepository = new OrderRepository();

        // 1. 상품등록
        // ProductService ps = new ProductService(productRepository);
        // ps.상품등록("바나나", 100, 5);
        // ps.상품등록("딸기",150,10);
        // ps.상품등록("복숭아",200,20);

        // 2. 회원가입
        // UserService us = new UserService(userRepository);
        // us.회원가입("ssar","ssar0000","ssar@naver.com");
        // us.회원가입("cos","cos0000","cos@naver.com");
        // us.회원가입("love","love0000","love@naver.com");

        // 3. 상품 목록 보기
        // ProductService ps = new ProductService(productRepository);
        // List<Product> list = ps.상품목록보기();
        // for (Product p : list) {System.out.println(p);}

        // 4. 장바구니 담기
        // CartService cs = new CartService(cartRepository);
        // cs.장바구니담기(1,1,1);

        // 5. 장바구니 보기
        // CartService cs = new CartService(cartRepository);
        // System.out.println(cs.장바구니보기(1));

        // 6. 주문하기
        // OrderService os = new OrderService(productRepository,orderRepository);
        // os.주문하기(1,1,1);

        // 7. 주문 이력 보기
        // OrderService os = new OrderService(productRepository, orderRepository);
        // System.out.println(os.주문이력보기(1));;
    }
}