[JSP]p543 2

 

 

 

 

ProductDAO.java

 

 

package com.woori.dao;

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

import com.woori.dto.ProductVO;

import util.DBManager;

public class ProductDAO {

	private ProductDAO() { //**private 
	}

	private static ProductDAO instance = new ProductDAO(); 
	
	public static ProductDAO getInstance() { 
		return instance;
	}
	
	//c Read u d
	public List<ProductVO> selectAllProducts(){
		
		String sql = "select * from product order by code desc";
		List<ProductVO> list = new ArrayList<ProductVO>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				ProductVO pVo = new ProductVO();
				pVo.setCode(rs.getInt("code"));
				pVo.setName(rs.getString("name"));
				pVo.setPrice(rs.getInt("price"));
				pVo.setPrctureurl(rs.getString("prctureurl"));
				pVo.setDescription(rs.getString("description"));
				
				list.add(pVo);
			}//the end while
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn, pstmt,rs);
		}
		return list;
	}
	
	
	//create r u d
	public void insertProduct(ProductVO pVo) { 
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "insert into product values(product_seq.nextval,?,?,?,?)";
		
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,pVo.getName());
			pstmt.setInt(2, pVo.getPrice());
			pstmt.setString(3, pVo.getPrctureurl());
			pstmt.setString(4, pVo.getDescription());
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn,pstmt);
		}
		
	}
	
	
	//c Read u d
	public ProductVO selectProductByCode(String code) {
		String sql = "select * from product where code=?";
		
		ProductVO pVo = null;
		
		try {
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			try {
				conn = DBManager.getConnection();
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, code);
				rs = pstmt.executeQuery();
				
				if(rs.next()) {
					pVo = new ProductVO();
					pVo.setCode(rs.getInt("code"));
					pVo.setName(rs.getString("name"));
					pVo.setDescription(rs.getString("description"));
					pVo.setPrctureurl(rs.getString("prctureurl"));
					pVo.setPrice(rs.getInt("price"));
				}
			}catch(Exception e) {
				e.printStackTrace();
			}finally {
				DBManager.close(conn, pstmt, rs);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return pVo;
	}
	
	//c r update d
	public void updateProduct(ProductVO pVo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "update product set name=? , price=?";
		
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pVo.getName());
			pstmt.setInt(2, pVo.getPrice());
			pstmt.setString(3, pVo.getPrctureurl());
			pstmt.setString(4, pVo.getDescription());
			pstmt.setInt(5, pVo.getCode());
			
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn, pstmt);
		}
		
	}
}

 

 

ProductUpdateServlet.java

DB에서 상품 정보를 수정하는 작업을 위해서 productupdateservlet 클래스의 doPost 메소드에 다음과 같은 코드를 추가한다.

 

 

ProductDeleteServlet.java

상품삭제 버튼을 클릭하면 바로 삭제되는 것이 아니고, 이전에 등록했던 상품 정보를 확인 한 후에 삭제됨

이전에 입력되었던 상품 정보가 출력돼야 하기 때문에, 현재 선택된 상품 정보를 DB에서 찾아오기 위해서 상품 코드값을 매개 변수로 넘겨줬다.

삭제하기 위한 서블릿 만들기

 

package com.woori.controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
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 com.woori.dao.ProductDAO;
import com.woori.dto.ProductVO;

@WebServlet("/productDelete.do")
public class ProductDeleteServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String code = request.getParameter("code");
		
		ProductDAO pDao = ProductDAO.getInstance();
		ProductVO pVo = pDao.selectProductByCode(code);
		
		request.setAttribute("product", pVo);
		RequestDispatcher dispatcher = request.getRequestDispatcher("product/productDelete.jsp");
		dispatcher.forward(request, response);
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}

}

 

 

productDelete.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>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="css/shopping.css">
</head>
<body>
<div id="wrap" align="center">
<h1>delete the product - manager page</h1>
<form action="productDelete.do" method="post">
	<table>
		<tr>
			<td>
				<c:choose>
					<c:when test="${empty product.prctureUrl}">
						<img src="images/noimage.gif">
					</c:when>
				
					<c:otherwise>
						<img src="upload/${product.prctureUrl}">
					</c:otherwise>
				</c:choose>
			</td>
			<td>
				<table>
					<tr>
						<th style="width:80px">product name</th>
						<td>${product.name}</td>
					</tr>
					<tr>
						<th style="width:80px">product price</th>
						<td>${product.price}</td>
					</tr>
					<tr>
						<th style="width:80px">product description</th>
						<td>${product.description}</td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
	<br>
	<input type="hidden" name="code" value="${product.code}">
	<input type="submit" value="delete">
	<input type="button" value="list" onclick="location.href='productList.do'">
</form>
</div>
</body>
</html>

 

 

 

ProductDeleteServlet.java

jsp에서 삭제할 정보 확인 후 삭제 버튼을 클릭하여 실제 DB에서 상품 정보를 삭제하는 과정을 진행한다. 

 

 

package com.woori.controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
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 com.woori.dao.ProductDAO;
import com.woori.dto.ProductVO;

@WebServlet("/productDelete.do")
public class ProductDeleteServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String code = request.getParameter("code");
		
		ProductDAO pDao = ProductDAO.getInstance();
		pDao.deleteProduct(code);
		
		response.sendRedirect("productList.do");
	}

}

 

 

ProductDAO.java

상품 삭제 메소드 deleteProduct 만들기

 

package com.woori.dao;

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

import com.woori.dto.ProductVO;

import util.DBManager;

public class ProductDAO {

	private ProductDAO() { //**private 
	}

	private static ProductDAO instance = new ProductDAO(); //자기 자신이 자기 자신을 만든다.
	
	public static ProductDAO getInstance() { //private 한 instance를 외부에서 사용하기 위해 필요하다.
		return instance;
	}
	
	//c Read u d
	public List<ProductVO> selectAllProducts(){
		//최근에 등록한 상품 먼저 출력하기
		
		String sql = "select * from product order by code desc";
		List<ProductVO> list = new ArrayList<ProductVO>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			//쿼리문 실행 전 Connection 객체를 얻기 위해 DBManager 클래스의 정적 메소드인 getConnection 호출
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				ProductVO pVo = new ProductVO();
				//ResultSet 객체로 code 컬럼 값을 얻어와서, 컬럼 값을 ProductVO 객체의 code 필드에 저장하기 위해 setCode() 메소드 호출
				pVo.setCode(rs.getInt("code"));
				pVo.setName(rs.getString("name"));
				pVo.setPrice(rs.getInt("price"));
				pVo.setPrctureurl(rs.getString("prctureurl"));
				pVo.setDescription(rs.getString("description"));
				
				//ㅁrrayList 객체에 ProductVO 객체를 추가하기
				list.add(pVo);
			}//the end while
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			//사용이 끝난 객체를 해제하기 위해 DBManager 클래스의 정적 메소드인 close() 호출
			DBManager.close(conn, pstmt,rs);
		}
		//selectAllProducts() 를 호출하면 product 테이블의 모든 정보를 ArrayList 객체를 통해 얻을 수 있다.
		return list;
	}
	
	
	//create r u d
	public void insertProduct(ProductVO pVo) { 
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "insert into product values(product_seq.nextval,?,?,?,?)";
		
		try {
			conn = DBManager.getConnection(); //connection 객체 얻기
			pstmt = conn.prepareStatement(sql); //쿼리문 실행을 위해 statement 객체 필요. connection 객체로부터 statement 객체 얻어오기.
			
			pstmt.setString(1,pVo.getName());
			pstmt.setInt(2, pVo.getPrice());
			pstmt.setString(3, pVo.getPrctureurl());
			pstmt.setString(4, pVo.getDescription());
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn,pstmt);
		}
		
	}
	
	
	//c Read u d
	public ProductVO selectProductByCode(String code) {
		String sql = "select * from product where code=?";
		
		ProductVO pVo = null;
		
		try {
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			try {
				conn = DBManager.getConnection();
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, code);
				rs = pstmt.executeQuery();
				
				if(rs.next()) {
					pVo = new ProductVO();
					pVo.setCode(rs.getInt("code"));
					pVo.setName(rs.getString("name"));
					pVo.setDescription(rs.getString("description"));
					pVo.setPrctureurl(rs.getString("prctureurl"));
					pVo.setPrice(rs.getInt("price"));
				}
			}catch(Exception e) {
				e.printStackTrace();
			}finally {
				DBManager.close(conn, pstmt, rs);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return pVo;
	}
	
	
	//c r update d
	public void updateProduct(ProductVO pVo) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "update product set name=? , price=?, prctureurl=?, description=? where code=?";
		
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pVo.getName());
			pstmt.setInt(2, pVo.getPrice());
			pstmt.setString(3, pVo.getPrctureurl());
			pstmt.setString(4, pVo.getDescription());
			pstmt.setInt(5, pVo.getCode());
			
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn, pstmt);
		}
		
	}
	
	public void deleteProduct(String code) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "delete from product where code=?";
		
		try {
			conn = DBManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, code);
			pstmt.executeQuery();
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBManager.close(conn, pstmt); 
		}
	}
}