JSP

JSP 웹 프로그래밍 - 웹 쇼핑몰 만들기 07 (데이터베이스)

록's 2023. 3. 8. 18:49
728x90
반응형

 

 

 

웹 쇼핑몰 만들기 01 >>>  https://rogi221.tistory.com/93

웹 쇼핑몰 만들기 02 >>>  https://rogi221.tistory.com/101

웹 쇼핑몰 만들기 03 >>>  https://rogi221.tistory.com/105

웹 쇼핑몰 만들기 04 >>>  https://rogi221.tistory.com/109

웹 쇼핑몰 만들기 05 >>>  https://rogi221.tistory.com/114

웹 쇼핑몰 만들기 06 >>>  https://rogi221.tistory.com/116

 

 

 

 

 

16. 데이터베이스

 

[웹 쇼핑몰] 상품 조회, 등록, 수정, 삭제하기

 

 

 

 

 

데이터베이스에 저장된 상품 목록을 가져와 출력하기

 

  • 데이터베이스 커넥션하기: 커넥션 이름은 Market_Conn이고 데이터베이스 이름은 WebMarketDB를 사용하여 Data Source Explorer에서 실행.
  • 생성한 product 테이블을 사용

 

 

 

  • 데이터베이스 연결 페이지 만들기
// dbconn.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
	Connection conn = null;
	
	try {
		String url = "jdbc:mysql://192.168.111.40:3306/WebMarketDB";
		String user = "root";
		String password = "1234";
		
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, password);
	
	} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패했습니다.<br>");
		out.println("SQLException : " + ex.getMessage());
	}		
%>

 

 

 

  • 메뉴 페이지 수정하기
// menu.jsp

<%@ page contentType="text/html; charset=utf-8" %>
<nav class = "navbar navbar-expand navbar-dark bg-dark">
   <div class = "container">
      <div class = "navbar-header">
         <a class = "navbar-brand" href="./welcome.jsp">Home</a>
      </div>
   </div>
   <div>
   		<ul class="navbar-nav mr-auto">
   			<li class="nav-item"><a class="nav-link" href="./products.jsp">상품 목록</a></li>
   		</ul>
   </div>
</nav>

 

 

  • 상품 목록 페이지 수정하기
// products.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="dto.Product" %>
<%@ page import="dao.ProductRepository" %>
<%@ page import="java.sql.*" %>
<jsp:useBean id="productDAO" class="dao.ProductRepository" scope="session"/>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet"
	href="./resources/css/bootstrap.min.css">
<meta charset="UTF-8">
<title>상품 목록</title>
</head>
<body>
	<jsp:include page="menu.jsp"/>
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">상품 목록</h1>
		</div>
	</div>
	<%-- 상품 목록 가져오기 --%>
	<%
		ProductRepository dao = ProductRepository.getInstance();
		ArrayList<Product> listOfProducts = dao.getAllProducts();
	%>
	<%-- 상품 목록 출력 --%>
	<div class="container">
		<div class="row" align="center">
			<%@ include file="dbconn.jsp" %>
			<% 
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				String sql = "select * from product";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next()) {
			%>
				<div class="col-md-4">
					<img src="./resources/images/<%=rs.getString("p_fileName")%>" style="width: 100%">
					<h3><%=rs.getString("p_name")%></h3>
					<p><%=rs.getString("p_description")%>
					<p><%=rs.getString("p_UnitPrice")%>원
					<p> <a href="./product.jsp?id=<%=rs.getString("p_id")%>" class="btn btn-secondary" role="button">상세 정보 &raquo;</a>
				</div>
			<%
				}
			%>
		</div>
	</div>
	<hr>
	<jsp:include page="footer.jsp"/>
</body>
</html>

 

 

 

데이터베이스에 새로운 상품 등록하기

 

  • 메뉴 페이지 수정하기
<%@ page contentType="text/html; charset=utf-8" %>
<nav class = "navbar navbar-expand navbar-dark bg-dark">
   <div class = "container">
      <div class = "navbar-header">
         <a class = "navbar-brand" href="./welcome.jsp">Home</a>
      </div>
   </div>
   <div>
   		<ul class="navbar-nav mr-auto">
   			<li class="nav-item"><a class="nav-link" href="./products.jsp">상품 목록</a></li>
추가>>>	  		<li class="nav-item"><a class="nav-link" href="./addProduct.jsp">상품 등록</a></li>
   		</ul>
   </div>
</nav>

 

 

 

  • 상품 등록 처리 페이지 수정하기
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="com.oreilly.servlet.*"%>
<%@ page import="com.oreilly.servlet.multipart.*"%>
<%@ page import="java.util.*" %>
<%@ page import="dto.Product" %>
<%@ page import="dao.ProductRepository" %>
<%@ page import="java.sql.*"%>			<< 추가
<%@ include file="dbconn.jsp"%>			<< 추가
<%
		... (생략) ...
        
	Enumeration files = multi.getFileNames();
	String fname = (String)files.nextElement();
	String fileName = multi.getFilesystemName(fname);
	
	PreparedStatement pstmt = null;
	
	String sql = "insert into product values(?,?,?,?,?,?,?,?,?)";
	pstmt = conn.prepareStatement(sql);
	pstmt.setString(1, productId);
	pstmt.setString(2, name);
	pstmt.setInt(3, price);
	pstmt.setString(4, description);
	pstmt.setString(5, category);
	pstmt.setString(6, manufacturer);
	pstmt.setLong(7, stock);
	pstmt.setString(8, condition);
	pstmt.setString(9, fileName);
	pstmt.executeUpdate();
	
	if(pstmt != null)
		pstmt.close();
	if(conn != null)
		conn.close();
	
	response.sendRedirect("products.jsp");
%>

 

 

 

데이터베이스에 저장된 상품 수정하기

  • 메뉴 페이지 수정하기
<%@ page contentType="text/html; charset=utf-8" %>

	... 생략 ...
   			<li class="nav-item"><a class="nav-link" href="./addProduct.jsp">상품 등록</a></li>
   			<li class="nav-item"><a class="nav-link" href="./editProduct.jsp?edit=update">상품 수정</a>  << 추가
   		</ul>
   </div>
</nav>

 

 

  • 웹 애플리케이션 배포 설명자 web.xml 수정하기
// web.xml

<web-app>

... 생략 ...

<url-pattern>/addProduct.jsp</url-pattern>
<url-pattern>/editProduct.jsp</url-pattern>	<< 추가

... 생략 ...

 

  • 상품 편집 페이지 작성하기
// editProduct.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="./resources/css/bootstrap.min.css">
<meta charset="UTF-8">
<title>상품 편집</title>
<%
	String edit = request.getParameter("edit");
%>
</head>
<body>
	<jsp:include page="menu.jsp" />
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">상품 편집</h1>
		</div>
	</div>
	<div class="container">
		<div class="row" align="center">
			<%@ include file="dbconn.jsp" %>
			<% 
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				
				String sql = "select * from product";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next()) {
			%>
			<div class="col-md-4">
				<img src="./resources/images/<%=rs.getString("p_fileName")%>" style="width: 100%">
				<h3><%=rs.getString("p_name")%></h3>
				<p><%=rs.getString("p_description")%>
				<p><%=rs.getString("p_UnitPrice")%>원
				<p><%
						if(edit.equals("update")) {
					%>
					<a href="./updateProduct.jsp?id=<%=rs.getString("p_id")%>" class="btn btn-success" role="button"> 수정 &raquo;></a>
					<%
						}
					%>
			</div>
			<%
				}
					if(rs != null)
						rs.close();
					if (pstmt != null)
						pstmt.close();
					if (conn != null)
						conn.close();
			%>
		</div>
		<hr>
	</div>
	<jsp:include page="footer.jsp"/>
</body>
</html>

 

 
  • 상품 수정 페이지 작성하기
// updateProduct.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="./resources/css/bootstrap.min.css"/>
<meta charset="UTF-8">
<title>상품 수정</title>
</head>
<body>
	<jsp:include page="menu.jsp" />
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">상품 수정</h1>
		</div>
	</div>
	<%@ include file="dbconn.jsp" %>
	<%
		String productId = request.getParameter("id");
	
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select * from product where p_id = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, productId);
		rs = pstmt.executeQuery();
		if (rs.next()) {
	%>
	<div class="container">
		<div class="row">
			<div class="col-md-5">
				<img src="./resources/images/<%=rs.getString("p_filename")%>" alt="image" style="width: 100%"/>
			</div>
			<div class="col-md-7">
				<form name="newProduct" action="./processUpdateProduct.jsp" class="form-horizontal" method="post" enctype="multipart/form-data">
					<div class="form-group row">
						<label class="col-sm-2">상품 코드</label>
						<div class="col-sm-3">
							<input type="text" id="productId" name="productId" class="form-control" value='<%=rs.getString("p_id")%>'>
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">상품명</label>
						<div class="col-sm-3">
							<input type="text" id="name" name="name" class="form-control" value="<%=rs.getString("p_name")%>">
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">가격</label>
						<div class="col-sm-3">
							<input type="text" id="unitPrice" name="unitPrice" class="form-control" value="<%=rs.getInt("p_unitPrice")%>">
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">상세 설명</label>
						<div class="col-sm-5">
							<textarea name="description" cols="50" rows="2" class="form-control"><%=rs.getString("p_description")%></textarea>
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">제조사</label>
						<div class="col-sm3">
							<input type="text" name="manufacturer" class="form-control" value="<%=rs.getString("p_manufacturer")%>">
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">분류</label>
						<div class="col-sm-3">
							<input type="text" name="category" class="form-control" value="<%=rs.getString("p_category")%>">
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">재고 수</label>
						<div class="col-sm-3">
							<input type="text" id="unitsInStock" name="unitsInStock" class="form-control" value="<%=rs.getLong("p_unitsInStock")%>">
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">상태</label>
						<div class="col-sm-3">
							<input type="radio" name="condition" value="New">신규 제품
							<input type="radio" name="condition" value="Old">중고 제품
							<input type="radio" name="condition" value="Refurbished">재생 제품
						</div>
					</div>
					<div class="form-group row">
						<label class="col-sm-2">이미지</label>
						<div class="col-sm-5">
							<input type="file" name="productImage" class="form-control">
						</div>
					</div>
					<div class="form-group row">
						<div class="col-sm-offset-2 col-sm-10">
							<input type="submit" class="btn btn-primary" value="등록">
						</div>
					</div>
				</form>
			</div>
		</div>
	</div>
	<%
		}
		if (rs != null)
			rs.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();
	%>
</body>
</html>

 

 

  • 상품 수정 처리 페이지 작성하기
// processUpdateProduct.jsp

<%@ page contentType="text/html; charset=utf-8" %>
<%@ page import="com.oreilly.servlet.*"%>
<%@ page import="com.oreilly.servlet.multipart.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<%@ include file="dbconn.jsp"%>
<%
	String filename = "";
	String realFolder = "C:\\Users\\codepc\\JSP-workspace\\WebMarket\\src\\main\\webapp\\resources\\images";  << 절대경로
	String encType = "utf-8";
	   int maxSize = 5 * 1024 * 1024;
	   
	   MultipartRequest multi = new MultipartRequest(request, realFolder, maxSize, encType, new DefaultFileRenamePolicy());
	   String productId = multi.getParameter("productId");
	   String name = multi.getParameter("name");
	   String unitPrice = multi.getParameter("unitPrice");
	   String description = multi.getParameter("description");
	   String manufacturer = multi.getParameter("manufacturer");
	   String category = multi.getParameter("category");
	   String unitsInStock = multi.getParameter("unitsInStock");
	   String condition = multi.getParameter("condition");
	   
	   Integer price;
	   
	   if(unitPrice.isEmpty())
	      price = 0;
	   else
	      price = Integer.valueOf(unitPrice);
	   
	   long stock;
	   
	   if(unitsInStock.isEmpty())
	      stock = 0;
	   else
	      stock = Long.valueOf(unitsInStock);
	   
	   Enumeration files = multi.getFileNames();
	   String fname = (String) files.nextElement();
	   String fileName = multi.getFilesystemName(fname);
	   
	   PreparedStatement pstmt = null;
	   ResultSet rs = null;
	   
	   String sql = "SELECT * FROM product WHERE p_id = ?";
	   pstmt = conn.prepareStatement(sql);
	   pstmt.setString(1, productId);
	   rs = pstmt.executeQuery();
	   
	   if(rs.next()) {
	      if(fileName != null) {
	         sql = "UPDATE product SET p_name=?, p_unitPrice=?, p_description=?, p_manufacturer=?, p_category=?, p_unitsInStock=?, p_condition=?, p_fileName=? WHERE p_id=?";
	         pstmt = conn.prepareStatement(sql);
	         pstmt.setString(1, name);
	         pstmt.setInt(2, price);
	         pstmt.setString(3, description);
	         pstmt.setString(4, manufacturer);
	         pstmt.setString(5,category);
	         pstmt.setLong(6, stock);
	         pstmt.setString(7, condition);
	         pstmt.setString(8, fileName);
	         pstmt.setString(9, productId);
	         pstmt.executeUpdate();
	      }
	      else {
	         sql = "UPDATE product SET p_name=?, p_unitPrice=?, p_description=?, p_manufacturer=?, p_category=?, p_unitsInStock=?, p_condition=? WHERE p_id=?";
	         pstmt = conn.prepareStatement(sql);
	         pstmt.setString(1, name);
	         pstmt.setInt(2, price);
	         pstmt.setString(3, description);
	         pstmt.setString(4, manufacturer);
	         pstmt.setString(5,category);
	         pstmt.setLong(6, stock);
	         pstmt.setString(7, condition);
	         pstmt.setString(8, productId);
	         pstmt.executeUpdate();
	      }
	   }
	   
	   if(rs != null)
	      rs.close();
	   if(pstmt != null)
	      pstmt.close();
	   if(conn != null)
	      conn.close();
	   
	   response.sendRedirect("editProduct.jsp?edit=update");
	%>

 

 

 

 

데이터베이스에 저장된 상품 삭제하기

  • 메뉴 페이지 수정하기
// menu.jsp

<%@ page contentType="text/html; charset=utf-8" %>
<nav class = "navbar navbar-expand navbar-dark bg-dark">
   <div class = "container">
      <div class = "navbar-header">
         <a class = "navbar-brand" href="./welcome.jsp">Home</a>
      </div>
   </div>
   <div>
   		<ul class="navbar-nav mr-auto">
   			<li class="nav-item"><a class="nav-link" href="./products.jsp">상품 목록</a></li>
   			<li class="nav-item"><a class="nav-link" href="./addProduct.jsp">상품 등록</a></li>
   			<li class="nav-item"><a class="nav-link" href="./editProduct.jsp?edit=update">상품 수정</a></li>
   			<li class="nav-item"><a class="nav-link" href="./editProduct.jsp?edit=delete">상품 삭제</a></li>
   		</ul>
   </div>
</nav>
  • 상품 편집 페이지 작성하기
// editProduct.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="./resources/css/bootstrap.min.css"/>
<meta charset="UTF-8">
<title>상품 편집</title>
<script type="text/javascript">
	function deleteConfirm(id) {
		if (confirm("해당 상품을 삭제합니다!!") == true)
				location.href = "./deleteProduct.jsp?id=" + id;
	 else 
		return;
}
</script>
<%
	String edit = request.getParameter("edit");
%>
</head>
<body>
	<jsp:include page="menu.jsp" />
	<div class="jumbotron">
		<div class="container">
			<h1 class="display-3">상품 편집</h1>
		</div>
	</div>
	<div class="container">
		<div class="row" align="center">
			<%@ include file="dbconn.jsp" %>
			<% 
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				
				String sql = "select * from product";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next()) {
			%>
			<div class="col-md-4">
				<img src="./resources/images/<%=rs.getString("p_fileName")%>" style="width: 100%">
				<h3><%=rs.getString("p_name")%></h3>
				<p><%=rs.getString("p_description")%>
				<p><%=rs.getString("p_UnitPrice")%>원
				<p><%
						if(edit.equals("update")) {
					%>
					<a href="./updateProduct.jsp?id=<%=rs.getString("p_id")%>" class="btn btn-success" role="button"> 수정 &raquo;></a>
					<%
						} else if (edit.equals("delete")) {
					%>
					<a href="#" onclick="deleteConfirm('<%=rs.getString("p_id")%>')" class="btn btn-danger" role="button">삭제 &raquo;</a>
					<%
						}
					%>
			</div>
			<%
				}
					if(rs != null)
						rs.close();
					if (pstmt != null)
						pstmt.close();
					if (conn != null)
						conn.close();
			%>
		</div>
		<hr>
	</div>
	<jsp:include page="footer.jsp"/>
</body>
</html>
  • 상품 삭제 페이지 작성하기
// deleteProduct.jsp

<%@ page contentType="text/html; charset=utf-8" %>
<%@ page import="java.sql.*"%>
<%@ include file="dbconn.jsp"%>
<%
	String productId = request.getParameter("id");

	String sql = "select * from product";
	PreparedStatement pstmt = conn.prepareStatement(sql);
	ResultSet rs = pstmt.executeQuery();
	
	if (rs.next()) {
		sql = "delete from product where p_id = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, productId);
		pstmt.executeUpdate();
	} else
		out.println("일치하는 상품이 없습니다.");
	
	if (rs != null)
		rs.close();
	if (pstmt != null)
		pstmt.close();
	if (conn != null)
		conn.close();
	
	response.sendRedirect("editProduct.jsp?edit=delete");
%>

 

 

<수정해야할 부분이 있을수 있음 (아직 상품수정 등록할때 오류 있음)>

웹 쇼핑몰 만들기 08 >>>  https://rogi221.tistory.com/122

 

728x90
반응형

'JSP' 카테고리의 다른 글

JSP 웹 프로그래밍 - JSP 표준 태그 라이브러리 (JSTL의 개요, JSTL이 제공하는 태그의 종류와 사용법)  (0) 2023.03.09
JSP 웹 프로그래밍 - 웹 쇼핑몰 만들기 08 (회원 가입, 수정, 탈퇴하기)  (0) 2023.03.09
JSP 웹 프로그래밍 - JDBC로 데이터베이스와 JSP 연동 02 (데이터베이스 쿼리 실행, 쿼리문 실행 결과 값 가져오기)  (0) 2023.03.08
JSP 웹 프로그래밍 - JDBC로 데이터베이스와 JSP 연동 01 (JDBC의 개요, JDBC 드라이버 로딩 및 DBMS 접속)  (0) 2023.03.08
JSP 웹 프로그래밍 - 데이터베이스 개발 환경 구축 (데이터베이스의 개요, 통합 개발 환경과 데이터베이스 연동, MySQL 기본 명령어)  (0) 2023.03.08

'JSP'의 다른글

  • 현재글JSP 웹 프로그래밍 - 웹 쇼핑몰 만들기 07 (데이터베이스)

관련글

댓글

티스토리툴바