쌍용교육 -JSP수업 66일차 - ch06_mvcPageMVC(11)

table.sql 추가명시

--상품
create table zitem(
item_num number not null,
name varchar2(30) not null,
price number(9) not null,
quantity number(7) not null,
photo1 varchar2(400) not null, --sql은 배열이없기 때문에 쉼표로 구분하고 나중에 자바에서 불러올 때는 split등을 써서 나눠야된다.
photo2 varchar2(400) not null,
detail clob not null,
reg_date date default sysdate not null,
modify_date date,
status number(1) not null, --표시 여부(판매가능 여부) 1:미표시(판매불가),2:표시(판매가능)
constraint zitem_pk primary key (item_num)
);
create sequence zitem_seq;

kr.item.vo => ItemVO

package kr.item.vo;

import java.sql.Date;

public class ItemVO {
	private int item_num;		//상품번호
	private String name;		//상품명
	private int price;			//가격
	private int quantity;		//수량
	private String photo1;		//상품사진1
	private String photo2;		//상품사진2
	private String detail;		//설명
	private Date reg_date;		//등록일
	private Date modify_date;	//수정일
	private int status;			//상품 판매 가능 여부 표시(1:미표시,2:표시)
	public int getItem_num() {
		return item_num;
	}
	public void setItem_num(int item_num) {
		this.item_num = item_num;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public int getQuantity() {
		return quantity;
	}
	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}
	public String getPhoto1() {
		return photo1;
	}
	public void setPhoto1(String photo1) {
		this.photo1 = photo1;
	}
	public String getPhoto2() {
		return photo2;
	}
	public void setPhoto2(String photo2) {
		this.photo2 = photo2;
	}
	public String getDetail() {
		return detail;
	}
	public void setDetail(String detail) {
		this.detail = detail;
	}
	public Date getReg_date() {
		return reg_date;
	}
	public void setReg_date(Date reg_date) {
		this.reg_date = reg_date;
	}
	public Date getModify_date() {
		return modify_date;
	}
	public void setModify_date(Date modify_date) {
		this.modify_date = modify_date;
	}
	public int getStatus() {
		return status;
	}
	public void setStatus(int status) {
		this.status = status;
	}
	
}

kr.item.dao => ItemDAO

package kr.item.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;

import kr.item.vo.ItemVO;
import kr.util.DBUtil;

public class ItemDAO {

	public static ItemDAO instance = new ItemDAO();



	public static ItemDAO getInstance() {

		return instance;

	}
	private ItemDAO() {}



	//관리자 - 상품 등록
	public void insertItem(ItemVO item) throws Exception{
		Connection conn = null;
	    PreparedStatement pstmt = null;
	    String sql =null;
	      try {
	         //커넥션풀로부터 커넥션 할당
	         conn = DBUtil.getConnection();
	         
	         //SQL문 작성
	         sql="INSERT INTO zitem (item_num,name,price,quantity,photo1,photo2,detail,status) "
	         		+ "VALUES (zitem_seq.nextval,?.?.?.?.?.?.?)";
	         
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         
	         //?에 데이터 바인딩
	         pstmt.setString(1, item.getName());
	         pstmt.setInt(2, item.getPrice());
	         pstmt.setInt(3, item.getQuantity());
	         pstmt.setString(4, item.getPhoto1());
	         pstmt.setString(5, item.getPhoto2());
	         pstmt.setString(6, item.getDetail());
	         pstmt.setInt(7, item.getStatus());
	         
	         //SQL문 실행
	         pstmt.executeUpdate();
	      }catch(Exception e) {
	         throw new Exception(e);
	      }finally {
	         DBUtil.executeClose(null, pstmt, conn);
	      }
	}
	//관리자 - 상품 수정

	//관리자 - 상품 삭제

	//관리자/사용자 - 전체 상품 개수/ 검색 상품 개수

	//관리자/사용자 - 전체 상품 목록/ 검색 상품 목록

	//관리자/사용자 - 상품 상세
}

 

kr.item.action =>AdminWriteFormAction

package kr.item.action;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.controller.Action;

public class AdminWriteFormAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		HttpSession session = request.getSession();
		Integer user_num = (Integer)session.getAttribute("user_num");
		if(user_num == null) { //로그인이 되지 않는 경우
			return "redirect:/member/loginForm.do";
		}
		
		Integer user_auth = (Integer)session.getAttribute("user_auth");
		if(user_auth !=9) {//관리자로 로그인하지 않은 경우
			return "/WEB-INF/views/common/notice.jsp";
			
		}
		//관리자로 로그인한 경우
		
		return "/WEB-INF/views/item/admin_writeForm.jsp";
	}

}

WEB-INF => shop.properties 만들기

#상품관리 관리자

/item/adminWriteForm.do=kr.item.action.AdminWriteFormAction

 

web.xml에 내용추가 (9번라인)

<param-value>/WEB-INF/member.properties,/WEB-INF/board.properties,/WEB-INF/shop.properties</param-value>

 

item=>admin_writeForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>상품등록</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
<script type="text/javascript">
window.onload=function(){
	const myForm = document.getElementById('write_form');
	//이벤트 연결
	myForm.onsubmit=function(){
		const radio = document.querySelectorAll('.input[type=radio]:checked');
		
		if(radio.length < 1){
			alert('상품표시여부를 지정하세요');
			return false;
		}
		const items = document.querySelectorAll('.input-check');
		for(let i=0; i<items.length; i++){
			if(items[i].value.trim()==''){
				const label = document.querySelector('label[for="'+items[i].id+'"]');
				alert(label.textContent+' 항목은 필수 입력');
				items[i].value = '';
				items[i].focus();
				return false;
			}
		}
	};
};
</script>
</head>
<body>
<div class="page-main">
	<jsp:include page="/WEB-INF/views/common/header.jsp"/>
		<div class="content-main">
			<h2>상품등록</h2>
			<form id="write_form" action="adminWrite.do" method="post" enctype="multipart/form-data">
				   
				<ul>
					<li>
						<label>상품표시여부</label>
						<input type="radio" name="status" value="1" id="status">미표시
						<input type="radio" name="status" value="2" id="status">표시
					</li>   
					<li>
						<label for="name">상품명</label>
						<input type="text" name="name" id="name" maxlength="10" class="input-check">
					</li>
					<li>
						<label for="price">가격</label>
						<input type="number" name="price" id="price" min="1" max="999999999" class="input-check">
					</li>
					<li>
						<label for="quantity">수량</label>
						<input type="number" name="quantity" id="quantity" min="0" max="9999999" class="input-check">
					</li>
					<li>
						<label for="photo1">상품사진1</label>
						<input type="file" name="photo1" id="photo1" 
							class="input-check" accept="image/gif,image/png,image/jpeg">
					</li>
					<li>
						<label for="photo2">상품사진2</label>
						<input type="file" name="photo2" id="photo2" 
							class="input-check" accept="image/gif,image/png,image/jpeg">
					</li>
					<li>
						<label for="detail">상품설명</label>
						<textarea rows="5" cols="30" name="detail" id="detail" class="input-check"></textarea>
					</li>
					
				</ul>
				<div class="align-center">
					<input type="submit" value="등록">
					<input type="button" value="목록" onclick="location.href='adminlist.do'">
				</div>
			</form>
		</div>
</div>
</body>
</html>

common=>header.jsp (내용추가) 

<li>

<a href="${pageContext.request.contextPath}/item/adminWriteForm.do">상품관리</a>

</li>

AdminWriteAction

package kr.item.action;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.controller.Action;
import kr.item.dao.ItemDAO;
import kr.item.vo.ItemVO;
import kr.util.FileUtil;

public class AdminWriteAction implements Action {

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		HttpSession session = request.getSession();
		Integer user_num = (Integer)session.getAttribute("user_num");
		if(user_num == null) { //로그인이 되지 않는 경우
			return "redirect:/member/loginForm.do";
		}
		
		Integer user_auth = (Integer)session.getAttribute("user_auth");
		if(user_auth !=9) {//관리자로 로그인하지 않은 경우
			return "/WEB-INF/views/common/notice.jsp";
			
		}
		//관리자로 로그인한 경우
		//전송된 데이터 인코딩 타입 지정
		request.setCharacterEncoding("utf-8");
		ItemVO item = new ItemVO();
		item.setName(request.getParameter("name"));
		item.setPrice(Integer.parseInt(request.getParameter("price")));
		item.setQuantity(Integer.parseInt(request.getParameter("quantity")));
		item.setPhoto1(FileUtil.createFile(request,"photo1"));
		item.setPhoto2(FileUtil.createFile(request,"photo2"));
		item.setDetail(request.getParameter("detail"));
		item.setStatus(Integer.parseInt(request.getParameter("status")));
		
		ItemDAO dao = ItemDAO.getInstance();
		dao.insertItem(item);
		
		//Refresh 정보를 응답 헤더에 추가
		String url = request.getContextPath()+"/item/adminList.do";
		response.addHeader("Refresh", "2;url="+url); //2초뒤에 url이 넘어간다.
		request.setAttribute("result_title", "상품 등록 완료");
		request.setAttribute("result_msg", "성공적으로 등록되었습니다.");
		request.setAttribute("result_url", url);
		
		return "/WEB-INF/views/common/result_view.jsp";
	}

}

kr.item.dao => ItemDAO (추가명시)

//관리자/사용자 - 전체 상품 목록/ 검색 상품 목록
	public List<ItemVO> getListItem(int start, int end, String keyfield, String keyword, int status)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql =null;
		ResultSet rs = null;
		List<ItemVO> list = null;
		String sub_sql = "";
		int cnt = 0;
		try {
			//커넥션풀로부터 커넥션 할당
			conn = DBUtil.getConnection();
			
			if(keyword != null && !"".equals(keyword)){
				//검색 처리
				if(keyfield.equals("1")) sub_sql +="AND name LIKE '%' || ? || '%'";
				else if(keyfield.equals("2")) sub_sql +="AND detail LIKE '%' || ? || '%'";
			}
					
			//SQL문 작성
			//status가 0이면, 1(미표시),2(표시)번 모두 호출 -> 관리자용
			//status가 1이면, 2(표시)만 호출 -> 사용자용
			sql="SELECT * FROM (SELECT a.*, rownum rnum FROM "
					+ "(SELECT * FROM zitem WHERE status > ? " + sub_sql
					+ " ORDER BY item_num DESC)a) "
					+ "WHERE rnum >=? AND rnum <=?";

			//PreparedStatment 객체 생성
			pstmt = conn.prepareStatement(sql);
			//?에 데이터 바인딩
			pstmt.setInt(++cnt, status);
			if(keyword!= null && !"".equals(keyword)) {
				pstmt.setString(++cnt, keyword);
			}
			pstmt.setInt(++cnt,start);
			pstmt.setInt(++cnt,end);
			//SQL문 실행
			rs = pstmt.executeQuery();
			list = new ArrayList<ItemVO>();
			while(rs.next()) {
				ItemVO item = new ItemVO();
				item.setItem_num(rs.getInt("item_num"));
				item.setName(rs.getString("name"));
				item.setPrice(rs.getInt("price"));
				item.setQuantity(rs.getInt("quantity"));
				item.setPhoto1(rs.getString("photo1"));
				item.setReg_date(rs.getDate("reg_date"));
				item.setStatus(rs.getInt("status"));
				
				list.add(item);
			}
		}catch(Exception e) {
			throw new Exception(e);
		}finally {
			DBUtil.executeClose(null, pstmt, conn);
		}
		return list;
	}

kr.main.action => MainAction 내용추가

package kr.main.action;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import kr.controller.Action;
import kr.item.dao.ItemDAO;
import kr.item.vo.ItemVO;

public class MainAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		
		//상품 목록
		ItemDAO itemDAO = ItemDAO.getInstance();
												//start,end,		1은 status: 표시상품만 반환하기 위해서 1을 씀	
		List<ItemVO> itemList = itemDAO.getListItem(1, 5, null, null,1);
		
		request.setAttribute("itemList", itemList);
		//JSP 경로반환
		return "/WEB-INF/views/main/main.jsp";
	}

}

views/main/main.jsp 내용추가

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>메인</title>
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
<div class="page-main">
	<jsp:include page="/WEB-INF/views/common/header.jsp"/> 
	<div class="content-main">
		<h4>최신 상품</h4>
		<div class="image-space">
			<c:forEach var="item" items="${itemList}">
			<div class="horizontal-area"> 
				<a href="${pageContext.request.contextPath}/item/detail.do?item_num=${item.item_num}">
					<img src="${pageContext.request.contextPath}/upload/${item.photo1}">
					<span${item.name}></span>
					<br>
					<b><fmt:formatNumber value="${item.price}"/>원</b>
				</a>
			</div>
			</c:forEach>
			<div class="float-clear">
				<hr width="100%" size="1" noshade="noshade">
			</div>
		</div>
	</div>
</div>
</body>
</html>

kr.item.dao => ItemDAO (추가명시)

//관리자/사용자 - 상품 상세
	public ItemVO getItem(int item_num)throws Exception{
		Connection conn = null;
	      PreparedStatement pstmt = null;
	      String sql =null;
	      ResultSet rs = null;
	      ItemVO item = null;
	      try {
	         //커넥션풀로부터 커넥션 할당
	         conn = DBUtil.getConnection();
	         
	         //SQL문 작성
	         sql="SELECT * FROM zitem WHERE item_num=?";
	         
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         //?에 데이터바인딩
	         pstmt.setInt(1, item_num);
	         //SQL실행
	         rs=pstmt.executeQuery();
	         
	         if(rs.next()) {
	        	 item = new ItemVO();
	        	 item.setItem_num(rs.getInt("item_num"));
	        	 item.setName(rs.getString("name"));
	        	 item.setPrice(rs.getInt("price"));
	        	 item.setQuantity(rs.getInt("quantity"));
	        	 item.setPhoto1(rs.getString("photo1"));
	        	 item.setPhoto2(rs.getString("photo2"));
	        	 item.setDetail(rs.getString("detail"));
	        	 item.setReg_date(rs.getDate("reg_date")); //관리자에게만 보이게
	        	 item.setStatus(rs.getInt("status")); //관리자에게만 보이게
	         }
	      }catch(Exception e) {
	         throw new Exception(e);
	      }finally {
	         DBUtil.executeClose(null, pstmt, conn);
	      }
		
		return item;
	}

kr.item.action =>UserDetailAction

package kr.item.action;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import kr.controller.Action;
import kr.item.dao.ItemDAO;
import kr.item.vo.ItemVO;
import kr.util.StringUtil;

public class UserDetailAction implements Action {

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		//상품번호 반환
		int item_num = Integer.parseInt(request.getParameter("item_num"));
		
		ItemDAO dao = ItemDAO.getInstance();
		ItemVO item = dao.getItem(item_num);
		
		//상품설명 줄바꿈 처리 (HTML 태그 허옹) 관리자만 사용하기 때문에
		item.setDetail(StringUtil.useBrHtml(item.getDetail()));
		
		request.setAttribute("item",item);
		
		return "/WEB-INF/views/item/user_detail.jsp";
	}

}

/views/item/ =>user_detail.jsp