[JSP MVC2] 강의 정리 4 - 상세 페이지, 목록

별의개발자커비·2023년 7월 4일

MVC jsp

JSP

목록 보기

22/31

강좌 81 - 자세한 페이지 수정하기

현재는 상품을 클릭했을 때 서비스 사용하지 않고 컨트롤러가 직접 데이터 전달해주고 있음 -> 변경하기

1. ProductController 원래 - dao랑 바로 연결 버전

package controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.ProductRepository;
import dto.Product;
import entity.ProductDetail;

@WebServlet("/product")
public class ProductController extends HttpServlet {
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String id = request.getParameter("id");		
		ProductRepository dao = new ProductRepository();
		Product product = dao.getProductById(id);
		
		String filename =  product.getFilename() ;
		String pname=  product.getPname() ;
		String description = product.getDescription() ;
		String productId = product.getProductId() ;
		String manufacturer = product.getManufacturer() ;
		String category = product.getCategory(); 
		long unitsInStock = product.getUnitsInStock() ;
		Integer unitPrice = product.getUnitPrice() ;
		
		ProductDetail productDetail = new ProductDetail(  filename, pname, description, productId, manufacturer, category, unitsInStock, unitPrice);
		
		// redirect
		request.setAttribute("p", productDetail);


		// foward
		request.getRequestDispatcher("/WEB-INF/view/product.jsp").forward(request, response);
		// 얘를 호출해서 req,rep를 공유할 것이다

	}
}

2. 변경

package controller;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.ProductRepository;
import dto.Product;
import entity.ProductDetail;
import service.ProductsService;

@WebServlet("/product")
public class ProductController extends HttpServlet {
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String id = request.getParameter("id");	
		
		ProductsService productsService = new ProductsService();
		Product product = productsService.getProduct(id);
		
//		ProductRepository dao = new ProductRepository();
//		Product product = dao.getProductById(id);
//		
//		String filename =  product.getFilename() ;
//		String pname=  product.getPname() ;
//		String description = product.getDescription() ;
//		String productId = product.getProductId() ;
//		String manufacturer = product.getManufacturer() ;
//		String category = product.getCategory(); 
//		long unitsInStock = product.getUnitsInStock() ;
//		Integer unitPrice = product.getUnitPrice() ;
//		
//		// detail 도 지워? 그럼 왜 만들었어?
//		ProductDetail productDetail = new ProductDetail(  filename, pname, description, productId, manufacturer, category, unitsInStock, unitPrice);
		
		// redirect
		request.setAttribute("p", product);


		// foward
		request.getRequestDispatcher("/WEB-INF/view/product.jsp").forward(request, response);
		// 얘를 호출해서 req,rep를 공유할 것이다

	}
}

productService 관련 부분

public Product getProduct(String productId) {
		Product p = null;
		
		try {
		Connection con = ConnectionDb.getConnection();
		
		String sql = "select * from product where productId = ? ";
		PreparedStatement pstmt = con.prepareStatement(sql); // 쿼리 실행
		pstmt.setString(1, productId);
		
		ResultSet rs = pstmt.executeQuery();

		if (rs.next()) {
			p = setProductVo(rs);
		}
	} catch (ClassNotFoundException | SQLException e) {
		e.printStackTrace();
	}
		return p;
	}

강좌 82 - 목록에 댓글 수를 포함하려면?

product 테이블과 연결되는 comment 테이블 만들기

강좌 83 - 댓글 수를 포함하기 위한 쿼리 문제

join

select * from product P 
inner join comment C on P.productId = c.productId;

3개만 가져옴

select * from product P 
left join comment C on P.productId = c.productId;

select P.productId, P.pname , count(c.productId)  
from product P 
left join comment C on P.productId = c.productId
group by P.productId, P.pname
;

그룹바이에서는 p.* 못씀

-count(c.productId) cmt_count 이름 붙여주기

이거 바탕으로 댓글수까지 넣은 쿼리 만들기

select P.productId, P.pname , count(c.productId) cmt_count 
from product P 
left join comment C on P.productId = c.productId
group by P.productId, P.pname
;

 select *  from (select * from product where pname like '%갤%') N   
				 order by unitPrice desc 
				 limit 0, 6  
				;

강좌 84 - 목록의 댓글 수를 위한 View 생성하기

그냥 넣으면 너무 복잡 -> product_view를 만들어 넣기
: 이 때 데어터 정렬 order by 등은 빼주는 게 좋음
좌측 바에서 뷰만 확인 가능!
그렇다고 Product 객체에 또 cmt_count 변수를 추가하는 건 그러니
-> view 클래스를 entity에 만들어서

ProductView.java

package entity;

public class ProductView extends Product {

	private int cmtCount ;
	
	
	public int getCmtCount() {
		return cmtCount;
	}

	public void setCmtCount(int cmtCount) {
		this.cmtCount = cmtCount;
	}

	public ProductView() {
	}
	
	public ProductView(String productId, String pname, Integer unitPrice, String description, String manufacturer,
			String category, long unitsInStock, String filename, int cmtCount) {
		super(productId, pname, unitPrice, description, manufacturer, category, unitsInStock, filename);
		this.cmtCount = cmtCount;
	}
}

영상에서는 description 같은 긴 걸 제외했는데, 나중에 필요하면!

ProductsController

주석 부분 냅뒀었는데 지워도 되네?

	ProductsService productsService = new ProductsService();
		List<ProductView> listOfProducts = productsService.getProductList(field, query, page);
		
		int count = productsService.getProuductCount(field, query);
		request.setAttribute("count", count);
		
//		List<Product> plist = new ArrayList<Product>();
//		for (Product p : listOfProducts) {
//			String filename =  p.getFilename() ;
//			String pname=  p.getPname() ;
//			String description = p.getDescription() ;
//			String productId = p.getProductId() ;
//			String manufacturer = p.getManufacturer() ;
//			String category = p.getCategory(); 
//			long unitsInStock = p.getUnitsInStock() ;
//			Integer unitPrice = p.getUnitPrice() ;
//		
//			ProductView product = new ProductView(productId, pname, unitPrice, description, manufacturer, category, unitsInStock, filename, count);
//			plist.add(product);
//		}
		
		request.setAttribute("plist", listOfProducts);

ProductsService

ProductsRepository

Product -> ProductView로 바꾸고

public List<ProductView> getProductList(){
		return getProductList("pname", "", 1);
	}
	
	public List<ProductView> getProductList(int page){
		return getProductList("pname", "", page);
	}
	
	public List<ProductView> getProductList(String field/*pname, descriptions*/, String query/*갤럭시*/, int page){
		List<ProductView> listOfProducts = new ArrayList<>();
		

		String sql = " select * "
				+ "  from (select * from product_view where "+ field + " like ? ) N   "
				+ "	 order by unitPrice desc "
				+ "	 limit ?, 6 "	 // 0, 6, 12 번째부터 6개 -> ? = (page-1)*6
				;

		
		try {
		Connection con = ConnectionDb.getConnection();
		
		PreparedStatement stmt = con.prepareStatement(sql); // 쿼리 실행
		
		stmt.setString(1, "%" + query + "%");
		stmt.setInt(2, (page-1)*6 );
		
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			ProductView p = setProductVoView(rs);
			listOfProducts.add(p);
		}
	} catch (Exception e) {
		System.out.println(e.getMessage());
		System.out.println(e.getStackTrace());
	}
		
		return listOfProducts;
	}

rs 처리도 view 버전으로 하나 더 만듦

	private Product setProductVo(ResultSet rs) throws SQLException {
		Product p = new Product(rs.getString("productId"), rs.getString("pname"), rs.getInt("unitPrice"),
				rs.getString("descriptions"), rs.getString("manufacturer"), rs.getString("category"),
				rs.getLong("unitsInStock"), rs.getString("filename"));
		return p;
	}
	
	private ProductView setProductVoView(ResultSet rs) throws SQLException {
		ProductView p = new ProductView(rs.getString("productId"), rs.getString("pname"), rs.getInt("unitPrice"),
				rs.getString("descriptions"), rs.getString("manufacturer"), rs.getString("category"),
				rs.getLong("unitsInStock"), rs.getString("filename"), rs.getInt("cmtCount"));
		return p;
	}

ProductRepository

Product -> ProductView로 바꾸고

	public List<ProductView> getAllProducts() {
		ProductsService productsService  = new ProductsService();
		List<ProductView> listOfProducts = productsService.getProductList();
		return listOfProducts;
	}

products.jsp

${p.cmtCount}로 넣음

<div class="card-body p-4">
	<div class="text-center">
		<!-- Product name-->
		<h5 class="fw-bolder">${p.pname}</h5>
		<!-- Product price-->
		<p><fmt:formatNumber value="${p.unitPrice}" />원</p>
		
		<p style="color: gray; font-size: small;" >리뷰 ${p.cmtCount}</p>
	</div>
</div>