KDT 풀스택 국비 과정/파이널 프로젝트(미니)
[📚상품 구매 사이트 4단계] MySQL DB 연결 없이 관리자 페이지 구현하기 - 1. 프로젝트 세팅
인팁구름
2023. 4. 24. 17:49
⏬전체 코드는 아래의 깃 허브에😚⏬
GitHub - JungminK1m/Springboot-Product-Study-V4
Contribute to JungminK1m/Springboot-Product-Study-V4 development by creating an account on GitHub.
github.com
드디어 5단계 중 4단계로 넘어왔다!
아니 사실 한참 전인데 블로그 정리 하다가는 도저히 진행속도가 더딜 거 같아 정리 안한 채로 코드를 짰더니,
지금 블로그 쓸 때 더 고생이다😓🤤 모르던 거 또 모르고 또 다시 새로 공부....
아자아자 파이팅👊👊
3단계에서는 구매자와 판매자(관리자)의 서버를 아예 분리해서 썼다면 (포트번호 다르게 해서),
4단계는 서버는 동시에 사용하되, User 테이블에 role을 추가해 이 role로 구매자와 판매자(관리자)를 나누게 만들어 볼 것이다. 그래서 어느 계정으로 로그인했냐에 따라 보이는 페이지와 기능들에 차이를 둘 것이다.
이번에 관리자라는 기능을 처음 만들어 보는데, role이 그래서 뭔 기능인데..? String일 뿐인데 뭐지? 왜 User 테이블에 추가해..? 이러면서 생각을 오래했는데, 막상 만들고 나니 그렇게 심각하게 어려운 기능은 아니었다😋
앞 전 프로젝트들의 코드들을 거의 그대로 가져온 거라 깃에서 클론 받는 게 더 편하다!
application.yml
이번 4단게에서는 구매자/판매자 같은 포트번호 8080을 쓴다! (당연)
server:
port: 8080
servlet:
encoding:
charset: utf-8
force: true
spring:
mvc:
view:
prefix: /WEB-INF/view/
suffix: .jsp
datasource:
url: jdbc:h2:mem:test;MODE=MySQL
driver-class-name: org.h2.Driver
username: sa
password:
# sql:
# init:
# schema-locations:
# - classpath:db/table.sql
# data-locations:
# - classpath:db/data.sql
h2:
console:
enabled: true
output:
ansi:
enabled: always
mybatis:
mapper-locations:
- classpath:mapper/**.xml
configuration:
map-underscore-to-camel-case: truebuild.gradle
3단계에서 MySQL Driver를 썼다면 이제는 다시 H2 Console로 돌아왔다
dependencies {
testImplementation group: 'org.mybatis.spring.boot', name: 'mybatis-spring-boot-starter-test', version: '2.2.2'
implementation 'javax.servlet:jstl'
implementation 'org.apache.tomcat.embed:tomcat-embed-jasper'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.3.0'
compileOnly 'org.projectlombok:lombok'
developmentOnly 'org.springframework.boot:spring-boot-devtools'
runtimeOnly 'com.h2database:h2'
annotationProcessor 'org.projectlombok:lombok'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
💃 모델링 🕺
알아보기 쉽도록 폴더로 구분했다.
완성된 코드를 올려놓았기 때문에 JSP에 데이터 연결이 안되어 있다면 요청시 오류가 날 수도 있음!
Orders.java
package shop.mtcoding.productapp_v4.model.orders;
import java.sql.Timestamp;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Orders {
private Integer ordersId;
private String ordersName;
private Integer ordersPrice;
private Integer ordersQty;
private Integer productId;
private Integer userId;
private Timestamp createdAt;
}
OrdersRepository.java
package shop.mtcoding.productapp_v4.model.orders;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import shop.mtcoding.productapp_v4.dto.orders.OrdersDto;
@Mapper
public interface OrdersRepository {
// 2개 이상 @Param 붙이기
public void insert(@Param("ordersDto") OrdersDto ordersDto, @Param("userId") Integer userId);
public Orders findById(Integer ordersId);
public List<Orders> findAll(Integer usersId);
public void orderUpdatebyProductQty(Orders orders);
public void deleteById(Integer ordersId);
}
Product.java
package shop.mtcoding.productapp_v4.model.product;
import java.sql.Timestamp;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Product {
private Integer productId;
private String productName;
private Integer productPrice;
private Integer productQty;
private Timestamp createdAt;
}
ProductRepository.java
package shop.mtcoding.productapp_v4.model.product;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import shop.mtcoding.productapp_v4.dto.orders.OrdersDto;
import shop.mtcoding.productapp_v4.dto.product.ProductReqDto.ProductSaveDto;
import shop.mtcoding.productapp_v4.model.orders.Orders;
@Mapper
public interface ProductRepository {
public Product findById(Integer productId);
public List<Product> findAll();
public Product findByProductName(String productName);
public int insert(ProductSaveDto productSaveDto);
public int update(Product product);
public int deleteById(Integer productId);
// 구매 시에 product QTY가 차감 되어야 함
public void productQtyUpdate(OrdersDto ordersDto);
// 구매 취소시 prouduct QTY 다시 증가
public void productQtyReupdate(Orders orders);
// ajax 중복체크를 위한 메서드
public Product findByName(String productName);
}
User.java
package shop.mtcoding.productapp_v4.model.user;
import java.sql.Timestamp;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class User {
private Integer userId;
private String userName;
private String userPassword;
private String userEmail;
private Timestamp createdAt;
private String role;
public User(String userName, String userPassword, String userEmail, String role) {
this.userName = userName;
this.userPassword = userPassword;
this.userEmail = userEmail;
this.role = role;
// 거르기 위해서 사용!! -> JoinDto > toEntity에서 적어준 값으로 들어감
// 예시 ) this.role = "USER"; // 무조건 USER로 값이 들어감
}
}
UserRepository.java
package shop.mtcoding.productapp_v4.model.user;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import shop.mtcoding.productapp_v4.dto.user.AdminLoginDto;
import shop.mtcoding.productapp_v4.dto.user.JoinDto;
import shop.mtcoding.productapp_v4.dto.user.LoginDto;
@Mapper
public interface UserRepository {
public void insert(JoinDto joinDto);
public User findById(Integer userId);
public User findByUserName(String userName);
public User findByUserEmail(String userEmail);
public User findByUsernameAndPassword(LoginDto loginDto);
public List<User> findAll();
public void update(User user);
public void delete(Integer userId);
public User login(LoginDto loginDto);
public User adminLogin(AdminLoginDto adminLoginDto);
}
OrdersDto.java
package shop.mtcoding.productapp_v4.dto.orders;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class OrdersDto {
private String ordersName;
private Integer ordersPrice;
private Integer ordersQty;
private Integer productId;
}
ProductReqDto.java
package shop.mtcoding.productapp_v4.dto.product;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class ProductReqDto {
private Integer productId;
private String productName;
private Integer productPrice;
private Integer productQty;
@Getter
@Setter
public static class ProductSaveDto {
private String productName;
private Integer productPrice;
private Integer productQty;
}
@Getter
@Setter
public static class ProductUpdateDto {
private Integer productId;
private String productName;
private Integer productPrice;
private Integer productQty;
}
}
AdminLoginDto.java
package shop.mtcoding.productapp_v4.dto.user;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class AdminLoginDto {
private String userName;
private String userPassword;
private String role;
}
JoinDto.java
package shop.mtcoding.productapp_v4.dto.user;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class JoinDto {
private String userName;
private String userPassword;
private String userEmail;
}
LoginDto.java
package shop.mtcoding.productapp_v4.dto.user;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class LoginDto {
private String userName;
private String userPassword;
private String role;
}
OrdersController.java
package shop.mtcoding.productapp_v4.controller;
import java.util.List;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import shop.mtcoding.productapp_v4.dto.orders.OrdersDto;
import shop.mtcoding.productapp_v4.handler.exception.CustomException;
import shop.mtcoding.productapp_v4.model.orders.Orders;
import shop.mtcoding.productapp_v4.model.orders.OrdersRepository;
import shop.mtcoding.productapp_v4.model.product.Product;
import shop.mtcoding.productapp_v4.model.product.ProductRepository;
import shop.mtcoding.productapp_v4.model.user.User;
@Controller
public class OrdersController {
@Autowired
private OrdersRepository ordersRepository;
@Autowired
private ProductRepository productRepository;
@Autowired
private HttpSession session;
// 구매 목록 페이지
@GetMapping("/ordersList/{userId}")
public String orderListForm(@PathVariable Integer userId, Model model) {
User principal = (User) session.getAttribute("principal");
// 로그인 안한 사람이 주문목록 보려고 시도할 시
if (principal == null) {
throw new CustomException("구매목록을 볼 권한이 없습니다.", HttpStatus.FORBIDDEN);
}
// 로그인 했지만 나 아닌 다른 사람의 주문목록 보려고 시도할 시
// ! <- 논리 부정 연산자
if (!principal.getUserId().equals(userId)) {
throw new CustomException("구매목록을 볼 권한이 없습니다.", HttpStatus.FORBIDDEN);
}
List<Orders> ordersList = ordersRepository.findAll(userId);
model.addAttribute("orderedProduct", ordersList);
return "orders/ordersList";
}
// 상품 구매하기
// 어떤 상품을 구매했는 지 알아야해서 주소에 productId가 필요함(?) <--확인하기
@PostMapping("/orders/{productId}")
public String order(@PathVariable Integer productId, OrdersDto ordersDto) {
// 로그인 한 사람만 구매할 수 있음
User principal = (User) session.getAttribute("principal");
if (principal == null) {
throw new CustomException("로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
// 상품수량보다 구매수량이 더 많으면 안됨
Product productPS = productRepository.findById(productId);
if (productPS.getProductQty() - ordersDto.getOrdersQty() < 0) {
throw new CustomException("재고보다 더 많은 수량을 구매할 수 없습니다.", HttpStatus.FORBIDDEN);
}
// 구매를 하면 product qty가 차감되어야 함
productRepository.productQtyUpdate(ordersDto);
// principal.getUserId() 너무 길어서 변수로 만듦
int userId = principal.getUserId();
/*
* 구매버튼 누르면 insert 됨
* 누가 구매했는 지 필요하기 때문에 userId도 같이 insert 해야 함
*/
ordersRepository.insert(ordersDto, userId);
return "redirect:/ordersList/" + userId;
}
@PostMapping("/ordersList/delete")
public String deleteOrder(Integer ordersId) {
// 로그인 한 사람만
User principal = (User) session.getAttribute("principal");
if (principal == null) {
throw new CustomException("로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
int userId = principal.getUserId();
Orders orders = ordersRepository.findById(ordersId);
productRepository.productQtyReupdate(orders);
// 주문 정보 삭제
ordersRepository.deleteById(ordersId);
return "redirect:/ordersList/" + userId;
}
}
ProductController.java
package shop.mtcoding.productapp_v4.controller;
import java.util.List;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import shop.mtcoding.productapp_v4.dto.product.ProductReqDto.ProductSaveDto;
import shop.mtcoding.productapp_v4.dto.product.ProductReqDto.ProductUpdateDto;
import shop.mtcoding.productapp_v4.handler.exception.CustomException;
import shop.mtcoding.productapp_v4.model.product.Product;
import shop.mtcoding.productapp_v4.model.product.ProductRepository;
import shop.mtcoding.productapp_v4.model.user.User;
@Controller
public class ProductController {
@Autowired
private ProductRepository productRepository;
@Autowired
private HttpSession session;
// 상품 목록 페이지
@GetMapping({ "/product", "/" })
public String productList(Model model) {
List<Product> productList = productRepository.findAll();
model.addAttribute("productList", productList);
return "product/productList";
}
// 상품 상세 페이지
@GetMapping("/product/{productId}")
public String productDetail(@PathVariable Integer productId, Model model) {
Product product = productRepository.findById(productId);
model.addAttribute("product", product);
return "product/productDetail";
}
// 상품 등록 페이지
@GetMapping("/productSave")
public String productSave() {
// 관리자 로그인 한 사람만 구매할 수 있음
User principal = (User) session.getAttribute("principal");
if (principal == null || !principal.getRole().equals("ADMIN")) {
throw new CustomException("관리자 로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
return "product/productSave";
}
// 상품 수정 페이지
@GetMapping("/productUpdate")
public String productUpdate() {
// 관리자 로그인 한 사람만 업데이트 할 수 있음
User principal = (User) session.getAttribute("principal");
if (principal == null || !principal.getRole().equals("ADMIN")) {
throw new CustomException("관리자 로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
return "product/productUpdate";
}
// 상품 등록
@PostMapping("/product/save")
public String save(ProductSaveDto productSaveDto) {
// 관리자 로그인 한 사람만 상품 등록할 수 있음
User principal = (User) session.getAttribute("principal");
if (principal == null || !principal.getRole().equals("ADMIN")) {
throw new CustomException("관리자 로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
// 새로운 상품 등록(insert)
int result = productRepository.insert(productSaveDto);
// 디버깅
System.out.println("상품 이름 : " + productSaveDto.getProductName());
System.out.println("상품 가격 : " + productSaveDto.getProductPrice());
System.out.println("상품 재고 : " + productSaveDto.getProductQty());
// result 가 1이 아니면 업데이트 안된 것
if (result != 1) {
throw new CustomException("상품 등록을 실패했습니다.", HttpStatus.BAD_REQUEST);
}
// result == 1 업데이트 성공
return "redirect:/product";
}
// 상품명 중복체크 컨트롤러
@PostMapping("/productSave/checkName")
public ResponseEntity<?> checkProductName(@RequestParam String productName) {
// 디버깅
System.out.println("productName : " + productName);
// DB에 중복이 된 값이 있는 지 확인
Product pn = productRepository.findByName(productName);
if (pn != null) {
// pn이 있다면 flase 반환
return new ResponseEntity<>(false, HttpStatus.BAD_REQUEST);
}
// pn == null 기존에 없던 상품이기 때문에 true 반환
return new ResponseEntity<>(true, HttpStatus.OK);
}
// 상품 수정 페이지
@GetMapping("/product/{productId}/updateForm")
public String productUpdate(@PathVariable Integer productId, Model model) {
// 관리자 로그인 한 사람만 상품 수정 가능
User principal = (User) session.getAttribute("principal");
if (principal == null || !principal.getRole().equals("ADMIN")) {
throw new CustomException("관리자 로그인을 먼저 해 주세요.", HttpStatus.FORBIDDEN);
}
// Product product = productRepository.findById(id);
// model.addAttribute("product", product);
Product product = productRepository.findById(productId);
model.addAttribute("product", product);
return "product/productUpdate";
}
// 상품 수정
@PostMapping("/product/{productId}/update")
public String update(@PathVariable Integer productId, Model model, ProductUpdateDto productUpdateDto) {
System.out.println("디버깅 : " + productId);
Product p = productRepository.findById(productId);
model.addAttribute("product", p);
// 테스트
System.out.println("p 아이디: " + p.getProductId());
System.out.println("p 이름: " + p.getProductName());
System.out.println("p 가격: " + p.getProductPrice());
System.out.println("p 재고: " + p.getProductQty());
Product product = new Product();
product.setProductId(productUpdateDto.getProductId());
product.setProductName(productUpdateDto.getProductName());
product.setProductPrice(productUpdateDto.getProductPrice());
product.setProductQty(productUpdateDto.getProductQty());
System.out.println("데이터 담음");
// 업데이트
int result = productRepository.update(product);
// 테스트
System.out.println("product 아이디: " + product.getProductId());
System.out.println("product 이름: " + product.getProductName());
System.out.println("product 가격: " + product.getProductPrice());
System.out.println("product 재고: " + product.getProductQty());
System.out.println("result : " + result);
if (result != 1) {
System.out.println("업데이트 실패");
throw new CustomException("업데이트 실패", HttpStatus.BAD_REQUEST);
}
System.out.println("업데이트 완료");
return "redirect:/product/" + productId;
}
// 상품 삭제
@PostMapping("/product/{ProductId}/delete")
public String delete(@PathVariable Integer ProductId) {
int result = productRepository.deleteById(ProductId);
if (result != 1) {
throw new CustomException("삭제 실패", HttpStatus.BAD_REQUEST);
}
return "redirect:/product";
}
}
UserController.java
package shop.mtcoding.productapp_v4.controller;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import shop.mtcoding.productapp_v4.dto.user.AdminLoginDto;
import shop.mtcoding.productapp_v4.dto.user.JoinDto;
import shop.mtcoding.productapp_v4.dto.user.LoginDto;
import shop.mtcoding.productapp_v4.handler.exception.CustomException;
import shop.mtcoding.productapp_v4.model.user.User;
import shop.mtcoding.productapp_v4.model.user.UserRepository;
@Controller
public class UserController {
@Autowired
private HttpSession session;
@Autowired
private UserRepository userRepository;
// 구매자 로그인
@PostMapping("/login")
public String login(LoginDto loginDto) {
// 유효성 체크
if (loginDto.getUserName().isEmpty()) {
throw new CustomException("username을 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
if (loginDto.getUserPassword().isEmpty()) {
throw new CustomException("password를 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
// 가입된 유저인지 확인
User userPS = userRepository.login(loginDto);
if (userPS != null && userPS.getRole().equals("USER")) {
session.setAttribute("principal", userPS);
System.out.println("userName : " + userPS.getUserName());
System.out.println("userPassword : " + userPS.getUserPassword());
System.out.println("Role : " + userPS.getRole());
System.out.println("user 로그인 성공");
// 로그인 성공
return "redirect:/product";
}
// 로그인 실패
throw new CustomException("아이디와 비밀번호를 확인해 주세요", HttpStatus.BAD_REQUEST);
}
// 관리자 로그인
@PostMapping("/adminLogin")
public String adminLogin(AdminLoginDto adminLoginDto) {
// 유효성 체크
if (adminLoginDto.getUserName().isEmpty()) {
throw new CustomException("username을 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
if (adminLoginDto.getUserPassword().isEmpty()) {
throw new CustomException("password를 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
User userPS = userRepository.adminLogin(adminLoginDto);
if (userPS != null && userPS.getRole().equals("ADMIN")) {
session.setAttribute("principal", userPS);
System.out.println("adminName : " + userPS.getUserName());
System.out.println("adminPassword : " + userPS.getUserPassword());
System.out.println("Role : " + userPS.getRole());
System.out.println("admin 로그인 성공");
// 로그인 성공
return "redirect:/product";
}
// 로그인 실패
return "redirect:/adminLoginForm";
}
@PostMapping("/join")
public String join(JoinDto joinDto) {
// 유효성 체크
if (joinDto.getUserName().isEmpty()) {
throw new CustomException("username을 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
if (joinDto.getUserPassword().isEmpty()) {
throw new CustomException("password를 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
if (joinDto.getUserEmail().isEmpty()) {
throw new CustomException("email을 입력해 주세요.", HttpStatus.BAD_REQUEST);
}
// 기존 동일 유저 확인 (username,email만)
if (userRepository.findByUserName(joinDto.getUserName()) != null) {
throw new CustomException("이미 가입된 유저입니다.", HttpStatus.BAD_REQUEST);
}
if (userRepository.findByUserEmail(joinDto.getUserEmail()) != null) {
throw new CustomException("이미 가입된 이메일입니다.", HttpStatus.BAD_REQUEST);
}
userRepository.insert(joinDto);
return "redirect:/loginForm";
}
@GetMapping("/loginForm")
public String loginForm() {
return "user/loginForm";
}
@GetMapping("/joinForm")
public String joinForm() {
return "user/joinForm";
}
@GetMapping("/adminLoginForm")
public String adminLoginForm() {
return "user/adminLoginForm";
}
@GetMapping("/logout")
public String logout() {
session.invalidate();
return "redirect:/";
}
}
CustomException.java
package shop.mtcoding.productapp_v4.handler.exception;
import org.springframework.http.HttpStatus;
public class CustomException extends RuntimeException {
// 상태코드
private HttpStatus status;
// 생성자
public CustomException(String message, HttpStatus status) {
super(message);
this.status = status;
}
}
CustomExceptionHandler.java
package shop.mtcoding.productapp_v4.handler;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import shop.mtcoding.productapp_v4.handler.exception.CustomException;
@RestControllerAdvice
public class CustomExceptionHandler {
// 무조건 데이터를 응답할 거기 때문에 RestController
// 자바 스크립트를 응답
// 어노테이션 ()안에 타입을 적어줘야 함
// 런타임익셉션 - 모든 익셉션이 다 여기로 올 거임
// exception터지면 다 여기로 올 거임! 무조건 뒤로가기
@ExceptionHandler(CustomException.class)
public String basicException(Exception e) {
StringBuilder sb = new StringBuilder();
sb.append("<script>");
sb.append("alert('" + e.getMessage() + "');");
sb.append("history.back();");
sb.append("</script>");
return sb.toString();
}
// Dto를 응답
}
data.sql
INSERT INTO user_tb(user_name, user_password, user_email, role, created_at) VALUES ('ssar', '1234', 'ssar@nate.com','USER', NOW());
INSERT INTO user_tb(user_name, user_password, user_email, role, created_at) VALUES ('cos', '1234', 'cos@nate.com', 'USER', NOW());
INSERT INTO user_tb(user_name, user_password, user_email, role, created_at) VALUES ('admin', '1234', 'admin@nate.com', 'ADMIN', NOW());
INSERT INTO product_tb(product_name, product_price, product_qty, created_at) VALUES('바나나', 3000, 98, NOW());
INSERT INTO product_tb(product_name, product_price, product_qty, created_at) VALUES('딸기', 2000, 100, NOW());
INSERT INTO product_tb(product_name, product_price, product_qty, created_at) VALUES('키위', 4000, 85, NOW());
INSERT INTO product_tb(product_name, product_price, product_qty, created_at) VALUES('오렌지', 3500, 50, NOW());
INSERT INTO product_tb(product_name, product_price, product_qty, created_at) VALUES('사과', 1000, 200, NOW());
INSERT INTO orders_tb(orders_name, orders_price, orders_qty, product_id, user_id, created_at) VALUES ('바나나', 3000, 2, 1, 1, NOW());
INSERT INTO orders_tb(orders_name, orders_price, orders_qty, product_id, user_id, created_at) VALUES ('딸기', 2000, 5, 2, 2, NOW());
table.sql
CREATE TABLE user_tb(
user_id INT PRIMARY KEY auto_increment,
user_name VARCHAR(20) NOT null,
user_password VARCHAR(20) NOT null,
user_email VARCHAR(20) NOT null,
role VARCHAR(20) NOT null,
created_at TIMESTAMP NOT null
);
CREATE TABLE product_tb(
product_id INT PRIMARY KEY auto_increment,
product_name VARCHAR(20) NOT null,
product_price INT NOT null,
product_qty INT NOT null,
created_at TIMESTAMP NOT null
);
create table orders_tb(
orders_id int primary KEY auto_increment,
orders_name varchar(20) NOT null,
orders_price int NOT null,
orders_qty int NOT null,
product_id int NOT null,
user_id int NOT null,
created_at TIMESTAMP
);
orders.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="shop.mtcoding.productapp_v4.model.orders.OrdersRepository">
<select id="findById"
resultType="shop.mtcoding.productapp_v4.model.orders.Orders">
SELECT * FROM orders_tb WHERE orders_id=#{ordersId}
</select>
<select id="findAll"
resultType="shop.mtcoding.productapp_v4.model.orders.Orders">
SELECT *
FROM orders_tb
WHERE user_id=#{userId}
</select>
<insert id="insert">
INSERT INTO orders_tb (orders_name, orders_price, orders_qty, product_id, user_id, created_at)
VALUES(#{ordersDto.ordersName}, #{ordersDto.ordersPrice}, #{ordersDto.ordersQty}, #{ordersDto.productId}, #{userId}, NOW())
</insert>
<delete id="deleteById">
DELETE FROM orders_tb WHERE orders_id = #{ordersId}
</delete>
</mapper>
product.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="shop.mtcoding.productapp_v4.model.product.ProductRepository">
<select id="findById"
resultType="shop.mtcoding.productapp_v4.model.product.Product">
SELECT * FROM product_tb WHERE product_id = #{productId}
</select>
<select id="findByProductName"
resultType="shop.mtcoding.productapp_v4.model.product.Product">
SELECT * FROM product_tb WHERE product_name = #{productName}
</select>
<select id="findAll"
resultType="shop.mtcoding.productapp_v4.model.product.Product">
SELECT * FROM product_tb
</select>
<update id="productQtyUpdate">
Update product_tb
SET product_qty = product_qty - #{ordersQty}
where product_id = #{productId}
</update>
<update id="productQtyReupdate">
Update product_tb
SET product_qty = product_qty + #{ordersQty}
where product_id = #{productId}
</update>
<insert id="insert">
INSERT INTO product_tb (product_name, product_price, product_qty, created_at)
VALUES(#{productName}, #{productPrice}, #{productQty}, NOW())
</insert>
<update id="update">
UPDATE product_tb SET
product_name = #{productName},
product_price = #{productPrice},
product_qty = #{productQty}
WHERE product_id = #{productId}
</update>
<delete id="deleteById">
DELETE FROM product_tb WHERE product_id = #{productId}
</delete>
<select id = "findByName" resultType = "shop.mtcoding.productapp_v4.model.product.Product">
select product_name from product_tb where product_name = #{productName}
</select>
</mapper>
user.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="shop.mtcoding.productapp_v4.model.user.UserRepository">
<select id="findByUserName"
resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb WHERE user_name = #{userName}
</select>
<select id="findByUsernameAndPassword"
resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb WHERE user_name = #{userName} and user_password = #{userPassword}
</select>
<select id="findByUserEmail"
resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb WHERE user_email = #{userEmail}
</select>
<select id="findById"
resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb WHERE user_id = #{userId}
</select>
<!-- 구매자 로그인 -->
<!-- 구매자도 role을 적어줘야 구매자 페이지에서 관리자 로그인 시 튕김 -->
<select id="login" resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb
where user_name=#{userName}
AND user_password=#{userPassword}
AND role ='USER'
</select>
<!-- 관리자 로그인 -->
<select id="adminLogin" resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb
where user_name=#{userName}
AND user_password=#{userPassword}
AND role = 'ADMIN'
</select>
<insert id="insert">
INSERT INTO user_tb(user_name, user_password, user_email, role,
created_at)
VALUES(#{userName}, #{userPassword}, #{userEmail}, 'USER', now())
</insert>
<select id="findAll"
resultType="shop.mtcoding.productapp_v4.model.user.User">
SELECT * FROM user_tb ORDER BY user_id DESC
</select>
<update id="update">
UPDATE user_tb SET user_password = #{userPassword}, user_email =
#{userEmail}
WHERE user_id = #{userId}
</update>
<delete id="deleteById">
DELETE FROM user_tb WHERE user_id = #{userId}
</delete>
</mapper>
footer.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<br />
<hr />
<div class="jumbotron text-center" style="margin-bottom: 0">
<p>👾 Created by <a class="gitlink"
href="https://github.com/JungminK1m/Springboot-Product-Study-V4">JungminK1m</a></p>
<p>📞 010-1234-5678</p>
<p>🏴 부산 부산진구 XX동</p>
</div>
</body>
</html>
header.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Product</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
</head>
<style>
.gitlink {
text-decoration-line: none;
color: rgb(255, 105, 138);
font-weight: bolder;
background-color: rgb(255, 228, 154);
}
.center {
display: flex;
justify-content: center;
}
</style>
<body>
<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
<div class="container-fluid">
<h3 style="color: white;">🍌 쇼핑몰 🍓</h3>
<div class="collapse navbar-collapse" id="collapsibleNavbar">
<ul class="navbar-nav">
<%-- 공통 헤더 --%>
<li class="nav-item">
<a class="nav-link" href="/product">상품목록페이지</a>
</li>
<c:choose>
<%-- 로그인 하지 않았을 때 헤더 --%>
<c:when test="${empty principal}">
<li class="nav-item">
<a class="nav-link" href="/loginForm">구매자 로그인</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/adminLoginForm">관리자 로그인</a>
</li>
</c:when>
<%-- USER로 로그인 했을 때 헤더 --%>
<c:when test="${principal.role == 'USER'}">
<li class="nav-item">
<a class="nav-link" href="/ordersList/${principal.userId}">주문조회</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/logout">로그아웃</a>
</li>
</c:when>
<%-- ${principal.role=='ADMIN' } 일 때 헤더 --%>
<c:otherwise>
<li class="nav-item">
<a class="nav-link" href="/productSave">상품등록페이지</a>
</li>
<li class="nav-item">
<a class="nav-link" href="/logout">로그아웃</a>
</li>
</c:otherwise>
</c:choose>
</ul>
</div>
</div>
</nav>
</body>
</html>
ordersList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="text-center m-4">
<h1>구매목록 페이지</h1>
</div>
<div class="container">
<table class="table table-striped">
<thead>
<tr>
<th>주문번호</th>
<th>상품명</th>
<th>상품가격</th>
<th>구매수량</th>
<th>가격</th>
<th>비고</th>
</tr>
</thead>
<tbody>
<c:forEach items="${orderedProduct}" var="op" varStatus="status">
<tr>
<td>${status.count}</td>
<td>${op.ordersName}</a></td>
<td>${op.ordersPrice}원</td>
<td>${op.ordersQty}개</td>
<td>${op.ordersPrice * op.ordersQty}원</td>
<td>
<form action="/ordersList/delete" method="post">
<input name="ordersId" type="hidden" value="${op.ordersId}">
<button class="btn btn-success btn-sm" type="submit">취소하기</button>
</form>
</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
<%@ include file="../layout/footer.jsp" %>
productDetail.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="center">
<div style="margin: 20px;">
<form type="submit" action="/orders/${productId}" method="post" onsubmit="return qtyCheck()";>
<%-- productName 과 ordersName 연결하기 --%>
<input name="ordersId" type="hidden" value="${product.productId}">
<input name="ordersName" type="hidden" value="${product.productName}">
<input name="ordersPrice" type="hidden" value="${product.productPrice}">
<table border="1" style="width: 500px; height: 200px; text-align: center;">
<tr style="border: 1px solid">
<th style="background-color: rgb(185, 185, 185)">상품명</th>
<th>${product.productName}</th>
</tr>
<tr style="border: 1px solid">
<th style="background-color: rgb(185, 185, 185)">상품명</th>
<td>${product.productPrice}원</td>
</tr>
<tr style="border: 1px solid">
<th style="background-color: rgb(185, 185, 185)">상품명</th>
<td>
<span id="productQty">${product.productQty}</span>
<span>개</span>
</td>
</tr>
</table>
<c:choose>
<%-- 로그인 했을 때만 구매하기 버튼 뜨게 하기 --%>
<c:when test="${empty principal}" >
<div class="center" style="margin-top: 40px; text-align: center;">
<h5>상품을 구매하시려면 로그인 해주세요😀</h5>
</div>
</c:when>
<%-- USER일 때는 구매하기 버튼 뜨게 하기 --%>
<c:when test="${principal.role == 'USER'}">
<div class="center" style="margin-top: 20px; text-align: center;">
수량 :<input name="ordersQty" type="number" min="0" class="form-control mb-3"
style="width: 200px;">
<button
style="width: 240px; height: 50px; margin-right: 20px; background-color: rgb(255, 210, 199);">구매하기</button>
</div>
</c:when>
</c:choose>
</form>
<%-- ADMIN일 때는 수정하기/삭제하기 버튼 뜨게 하기 --%>
<c:if test="${principal.role == 'ADMIN'}">
<div class="center" style="margin-top: 20px; text-align: center;">
<form action="/product/${product.productId}/updateForm" method="get">
<button
style="width: 240px; height: 50px; margin-right: 20px; background-color: rgb(255, 210, 199);">수정하기</button>
</form>
<form action="/product/${product.productId}/delete" method="post">
<button
style="width: 240px; height: 50px; margin: auto; background-color: rgb(250, 255, 182);">삭제하기</button>
</form>
</div>
</c:if>
</div>
</div>
<script>
function qtyCheck() {
let ordersQty = parseInt(document.getElementsByName("ordersQty")[0].value); // 주문수량 150
let productQty = parseInt(document.getElementById("productQty").innerText); //재고수량 95
// 반복 코드 줄이기 위해 return false; 를 변수화
let ret = false;
console.log("orderQty : " + ordersQty);
console.log("productQty : " + productQty);
// 주문 수량이 undefined인지 여부
if (!isNaN(ordersQty) && !isNaN(productQty)) {
// 주문수량이 존재함
// 주문수량 > 재고
if (ordersQty > productQty) {
alert("재고 수량을 초과하여 구매할 수 없습니다.");
}
// 주문수량 = 0 or 주문수량 < 0
else if (ordersQty === 0 || ordersQty < 0) {
alert("1개 이상 구매할 수 있습니다.");
}
// 그게 아니라면 true로 반환 !ret 이니까 true임
else {
ret = !ret;
}
} else {
// 주문수량이 undefined임
alert("주문수량을 입력해 주세요.");
}
return ret;
}
</script>
<%@ include file="../layout/footer.jsp" %>
productList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<table class="table table-striped mt-4">
<thead>
<tr>
<th>상품 번호</th>
<th>상품 이름</th>
<th>상품 가격</th>
<th>상품 재고</th>
</tr>
</thead>
<tbody>
<c:forEach items="${productList}" var="p" varStatus="status">
<tr>
<td>${status.count}</td>
<td><a href="/product/${p.productId}">${p.productName}</a></td>
<td>${p.productPrice}원</td>
<td>${p.productQty}개</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
<%@ include file="../layout/footer.jsp" %>
productSave.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<form action="/product/save" method="post">
<div class="mb-3 mt-3">
상품명 : <input id="name" name="productName" type="text" placeholder="상품명을 적어주세요">
<button id="CheckproductName" type="button">중복확인</button>
</div>
<div class="mb-3 mt-3">
상품가격 : <input id="price" name="productPrice" type="text" placeholder="상품 가격을 적어주세요">
</div>
<div class="mb-3 mt-3">
상품수량 : <input id="qty" name="productQty" type="text" placeholder="상품 수량을 적어주세요">
</div>
<button id="submit" type="submit" class="btn btn-primary">상품등록완료</button>
</form>
</div>
<script>
// 중복체크 여부 = false - 아직 체크 안했으니까
let sameCheck = false;
// 상품명 중복체크
$('#CheckproductName').on('click', function () {
// 이렇게 데이터를 변수로 만들면 보기가 편하다
let data = { productName: $('#name').val() }
$.ajax({
url: '/productSave/checkName/',
type: 'post',
data: data,
contentType: "application/x-www-form-urlencoded; charset=utf-8"
}).done((res) => {
alert("등록 가능한 상품입니다")
// 콘솔창 확인용
console.log(res);
console.log("sameCheck : " + sameCheck);
// 등록 가능하니까 체크 여부를 true로 변경
sameCheck = true;
}).fail((err) => {
alert("이미 등록한 상품입니다")
// 콘솔창 확인용
console.log(err);
console.log("sameCheck : " + sameCheck);
// 등록 불가이기 때문에 중복체크를 안한 것으로 설정 (아래에 이벤트 처리를 위해)
sameCheck = false;
});
});
// 상품명을 입력하는 input 태그에 값이 변경될 때마다 sameCheck 를 false로 설정하는 이벤트
// => false가 됐으니 상품명을 다른 걸로 바뀌면 꼭 중복체크를 다시 해야되게 만든다.
$('#name').on('input', function (e) {
sameCheck = false;
console.log(sameCheck);
});
// 동일 상품명 등록하지 못하게 처리하는 이벤트 (최종 상품 등록 버튼)
// form이 submit 될 때 실행되는 이벤트
$('form').on('submit', function(e) {
// == 주의
if (sameCheck == false) {
alert("상품명 중복확인을 해 주세요.");
// e.preventDefault(); = 브라우저가 이벤트를 처리하는 동작을 중단시키는 메서드
// submit 이벤트를 중단시키기 위해 사용됨
e.preventDefault();
console.log(sameCheck);
}else if (sameCheck == true) {
alert("상품이 등록되었습니다.");
console.log(sameCheck);
}
});
</script>
<%@ include file="../layout/footer.jsp" %>
productUpdate.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<form action="/product/${productId}/update" method="post">
<div class="mb-3 mt-3">
상품명 :
<input id="name" name="productName" type="text" value="${product.productName}" placeholder="상품명을 적어주세요">
</div>
<div class="mb-3 mt-3">
상품가격 :
<input id="price" name="productPrice" type="text" value="${product.productPrice}" placeholder="상품 가격을 적어주세요">
</div>
<div class="mb-3 mt-3">
상품수량 :
<input id="qty" name="productQty" type="text" value="${product.productQty}" placeholder="상품 수량을 적어주세요">
</div>
<button type="submit" class="btn btn-primary">상품수정완료</button>
</form>
</div>
<%@ include file="../layout/footer.jsp" %>
adminLoginForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<form action="/adminLogin" method="post" name="form">
<div class="mb-3 mt-3">
<input type="text" class="form-control" placeholder="username" name="userName" value="admin" />
</div>
<div class="mb-3">
<input type="password" class="form-control" placeholder="password" name="userPassword" value="1234" />
</div>
<button type="submit" class="btn btn-primary">
관리자 로그인
</button>
</form>
</div>
<%@ include file="../layout/footer.jsp" %>
joinForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<form action="/join" method="post" name="form">
<div class="mb-3 mt-3">
<input
type="text"
class="form-control"
placeholder="아이디"
name="userName"
/>
</div>
<div class="mb-3">
<input
type="password"
class="form-control"
placeholder="비밀번호"
name="userPassword"
/>
</div>
<div class="mb-3">
<input
type="email"
class="form-control"
placeholder="이메일"
name="userEmail"
/>
</div>
<button
type="submit"
class="btn btn-primary">
회원가입
</button>
</form>
</div>
<%@ include file="../layout/footer.jsp" %>
loginForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="../layout/header.jsp" %>
<div class="container">
<form action="/login" method="post" name="form">
<div class="mb-3 mt-3">
<input type="text" class="form-control" placeholder="username" name="userName" value="ssar" />
</div>
<div class="mb-3">
<input type="password" class="form-control" placeholder="password" name="userPassword" value="1234" />
</div>
<button type="submit" class="btn btn-primary">
로그인
</button>
</form>
<button onclick="location.href='/joinForm'" ; class="btn btn-secondary mt-3">
아직 회원이 아니신가요?
</button>
</div>
<%@ include file="../layout/footer.jsp" %>