Contents
1. model 패키지1️⃣ Cart 클래스2️⃣ Order 클래스3️⃣ Product 클래스4️⃣ User 클래스2. repository 패키지1️⃣ CartRepository 클래스2️⃣ OrderRepository 클래스3️⃣ ProductRepository 클래스4️⃣ UserRepository 클래스3. service 패키지1️⃣ CartService 클래스2️⃣ OrderService 클래스3️⃣ ProductService 클래스4️⃣ UserService 클래스4. view 패키지1️⃣ CartListView 클래스2️⃣ OrderListView 클래스5. DBConnection 클래스6. ShopApp 클래스1. model 패키지
DB에서 SELECT한 정보 담을 클래스 생성
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 패키지
DB 테이블의 정보를 수정할 함수 담을 클래스 생성(INSERT, UPDATE, DELETE, SELECT)
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 패키지
트랜잭션 하나를 수행하기 위한 repository의 함수들을 담을 클래스 생성
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));;
}
}Share article