GDJ 24/04/22 (SQL 문법 - SQLD 대비, 쇼핑몰 프로젝트)

kimuki·2024년 4월 22일

오전 SQL (SQLD) 수업

DESC emp;
DESCRIBE emp;	// 테이블 스키마 출력

SELECT deptno, empno, ename, hiredate 
FROM emp;	// 열 순서는 지키지 않아도 된다

SELECT empno AS 번호, ename 이름 
FROM emp;	// 별칭 (AS), 컬럼 AS 키워드는 생략 가능

SELECT e.empno	// 별칭 사용 시 별칭만 가지고 작업
FROM emp e;	// 테이블 별칭에는 AS 키워드 사용 불가능

SELECT DISTINCT deptno 
FROM emp;	// SELECT절 컬럼 결과값에 중복값을 제거

SELECT DISTINCT deptno, empno 
FROM emp;	// deptno + empno 값의 중복을 제거

SELECT deptno 
FROM emp 
GROUP BY deptno;	// GROUP BY 집계함수를 사용하기 위함

SELECT ename, sal, sal*12 연봉 
FROM emp;	// 수식으로 컬럼 출력 가능

SELECT (ename || ',' || job) 직원정보 
FROM emp;	// 문자열 연결 연산자 " || "

SELECT ename 
FROM emp 
WHERE rownum < 7;	// 출력하고자 하는 행의 개수를 제한할 때 rownum 사용

CASE 컬럼 WHEN 값 THEN 조건 END	// 값 비교는 가능한데 조건 비교가 힘들다
SELECT ename, job, 
CASE job 
WHEN 'PRESIDENT' THEN '빨강' 
WHEN 'MANAGER' THEN '주황' 
WHEN 'ANALYST' THEN '노랑' 
WHEN 'CLERK' THEN '초록' 
ELSE '파랑' END COLOR 
FROM emp;

// ORDER BY절에 CASE문으로 조건주기
SELECT ename, job 
FROM emp 
ORDER BY(
CASE job WHEN 'PRESIDENT' THEN 1 
WHEN 'MANAGER' THEN 2 
WHEN 'ANALYST' THEN 3 
WHEN 'CLERK' THEN 4 
WHEN 'SALESMAN' THEN 5 END) ASC;

WHERE 절은 SELECT 절보다 먼저 실행된다
SELECT 절에서 AS를 사용해서 적은 것을 WHERE 절에서 사용할 수 없다는 뜻
(WHERE절이 먼저이기 때문에 SELECT의 AS를 인식할 수 없음)

/* 
함수
1. 단일행 함수 : 결과셋 컬럼값(한행) 하나에 대한 연산
    (문자함수, 숫자함수, 날짜함수, null함수)
2. 집계함수 : 결과셋이 그룹핑되어(계산셋이 결과셋이다) 있을때 그 집합에 (동일한)컬럼값들에 대한 연산
3. 분석함수 : 결과셋과 별도로 계산셋을 만들어 계산한다. 출력시 원본결과셋과 계산셋을 합쳐서 조회가능
*/

/* 숫자 단일행 함수 */
SELECT ename, sal 연봉, 
ROUND(sal/12, 1) 급여 /* ROUND(숫자, 반올림자릿수) 반올림 함수 */
FROM emp;

/* 문자 단일행 함수 */
SELECT SUBSTR(ename, 1, 2) || '****' /* SUBSTR(문자, 자릿수, 개수) */ 
FROM emp;

/* 날짜 단일행 함수 */
SELECT EXTRACT(YEAR from hiredate)
FROM emp;

/* 근속년도 구하기 */
SELECT ename, 
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hiredate) 근속년도
FROM emp
WHERE (EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM hiredate)) < 20 /* 별칭 사용(X) */
ORDER BY 근속년도 DESC; /* 별칭 사용(O) */

/* null 값 함수 */
SELECT ename, sal, comm, sal + comm 연봉 /* null과의 연산결과는 무조건 null이된다 */
FROM emp;

// NULL값 치환 함수 NVL(컬럼, 치환할 값)
SELECT ename, sal, comm, sal + NVL(comm, 0) 연봉 FROM emp;

shop Project

주문 취소 기능 구현(V)
	- /action/deleteOrdersAction.jsp
	- /form/deleteOrdersResult.jsp
	- 주문 취소 시 해당 제품 수량 +1
	- UPDATE goods SET goods_amount = goods_amount + 1 WHERE goods_no = ?


주문 하기 기능 추가(V)
	- /action/addOrdersAction.jsp
	- 주문 완료 시 해당 제품 수량 -1
	- UPDATE goods SET goods_amount = goods_amount - 1 WHERE goods_no = ?

리뷰 작성, 별점 기능 구현

CommentDAO.java 파일 추가
/form/addGoodsCommentForm.jsp 파일 추가
/action/addGoodsCommentAction.jsp 파일 추가


배송상태 변경 기능 구현

OrdersDAO.java 내용 추가
/emp/ordersStateList.jsp 파일 추가
/emp/action/modifyStateAction.jsp 파일 추가


리뷰 작성 시 버튼 비활성화 기능 구현

CommentDAO.jav 내용 추가
/customer/form/ordersInfoOne.jsp 내용 추가
/customer/form/goodsOne.jsp 리뷰 목록 추가

CommentDAO.java

package shop.dao;

import java.sql.*;
import java.util.*;

public class CommentDAO {
	
	public static boolean addGoodsComment(int ordersNo, int goodsNo, int score, String comment) throws Exception{
		
		Connection conn = DBHelper.getConnection();
		
		boolean addGoodsComment;
		
		String sql = "INSERT INTO comment(orders_no, goods_no, score, comment) VALUES(?, ?, ?, ?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, ordersNo);
		stmt.setInt(2, goodsNo);
		stmt.setInt(3, score);
		stmt.setString(4, comment);
		
		int row = stmt.executeUpdate();
		if(row == 1) {
			addGoodsComment = true;
		}else {
			addGoodsComment = false;
		}
		
		conn.close();
		return addGoodsComment;

	}
	
	public static ArrayList<HashMap<String, Object>> getGoodsCommentList(int goodsNo) throws Exception{
		
		Connection conn = DBHelper.getConnection();
		
		ArrayList<HashMap<String, Object>> goodsCommentList = new ArrayList<HashMap<String, Object>>();
		
		String sql = "SELECT orders_no ordersNo, score, comment, create_date createDate FROM comment WHERE goods_no = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, goodsNo);
		
		ResultSet rs = stmt.executeQuery();
		while(rs.next()) {
			HashMap<String, Object> m = new HashMap<String, Object>();
			m.put("ordersNo", rs.getInt("ordersNo"));
			m.put("score", rs.getInt("score"));
			m.put("comment", rs.getString("comment"));
			m.put("createDate", rs.getString("createDate"));
			goodsCommentList.add(m);
		}
		
		conn.close();
		return goodsCommentList;
	}
	
	public static boolean checkGoodsComment(int ordersNo) throws Exception {
		
		Connection conn = DBHelper.getConnection();
		
		boolean checkGoodsComment;
		
		String sql = "SELECT comment FROM comment WHERE orders_no = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, ordersNo);
		
		ResultSet rs = stmt.executeQuery();
		if(rs.next()) {
			checkGoodsComment = false;
		}else{
			checkGoodsComment = true;
		}
		
		conn.close();
		return checkGoodsComment;
	}
}

addGoodsCommentForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder"%>
<%
	// 로그인 인증 분기
	if(session.getAttribute("loginCustomer") == null){
	response.sendRedirect("/shop/customer/form/loginForm.jsp");
	return;
}
%>

<%
	// 요청값 분석
	int ordersNo = Integer.parseInt(request.getParameter("ordersNo"));
	int goodsNo = Integer.parseInt(request.getParameter("goodsNo"));
	
	System.out.println("ordersNo : " + ordersNo);
	System.out.println("goodsNo : " + goodsNo);

%>
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>리뷰 작성</title>
	<!-- Latest compiled and minified CSS -->
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<style>
		@import url('https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@100..900&display=swap')
	</style>
	<style>
		.container{
			font-family: "Noto Sans KR", sans-serif;
  			font-optical-sizing: auto;
  			font-weight: <weight>;
  			font-style: normal;
		}
	
		.main{
			text-align: center;
		}
		
		.table{
			text-align: center;
		}
		
		a{
			text-decoration: none;
		}
		
		a:hover{
			color: gray;
		}
		
		a.page-link{
			color: #000000;
		}
		
		a.page-link:hover{
			background-color: #000000;
			color: #FFFFFF;
		}
		
		span.add{
			text-align: right;
		}
		
		div.header{
			padding-top: 50px;
			margin-bottom: 50px;
		}
		
		.table td{
			height: 70px;
			vertical-align: middle;
		}
	</style>
</head>
<body>
	<div class="container">
		<div class="header"></div>
		<div class="row">
			<div class="col"></div>
			<div class="main col-8">
			<!-- 메인 내용 시작 -->
				<h1>Write Your Review</h1>
				<img src="/shop/img/logo.png" width="300px" height="300px">
				<br>
				<h3>구매하신 상품 및 배송 서비스가 만족스러우셨나요?</h3>
				<br>
				<h3>상품평을 작성하고 모두에게 공유해보세요!</h3>
				<br><br>
				<form method="post" action="/shop/customer/action/addGoodsCommentAction.jsp?ordersNo=<%=ordersNo%>&goodsNo=<%=goodsNo%>">
					<div>
						<h4>
						만족도 : &nbsp;&nbsp;&nbsp;
						<input type="radio" name="score" value="5"> 5점&nbsp;&nbsp;&nbsp;
						<input type="radio" name="score" value="4"> 4점&nbsp;&nbsp;&nbsp;
						<input type="radio" name="score" value="3"> 3점&nbsp;&nbsp;&nbsp;
						<input type="radio" name="score" value="2"> 2점&nbsp;&nbsp;&nbsp;
						<input type="radio" name="score" value="1"> 1점
						</h4>
					</div>
					<br>
					<div>
						<textarea rows="7" cols="60" name="comment"></textarea>
					</div>
					<br>
					<button class="btn btn-outline-dark" type="submit">등록하기</button>
				</form>
			<!-- 메인 내용 끝 -->
			</div>
			<div class="col"></div>
		</div>
	</div>
</body>
</html>

addGoodsCommentAction.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder"%>
<%@ page import="shop.dao.CommentDAO"%>
<%
	// 로그인 인증 분기
	if(session.getAttribute("loginCustomer") == null){
	response.sendRedirect("/shop/customer/form/loginForm.jsp");
	return;
}
%>

<%
	// 요청값 분석
	int ordersNo = Integer.parseInt(request.getParameter("ordersNo"));
	int goodsNo = Integer.parseInt(request.getParameter("goodsNo"));
	int score = Integer.parseInt(request.getParameter("score"));
	String comment = request.getParameter("comment");
	
	System.out.println("ordersNo : " + ordersNo);
	System.out.println("goodsNo : " + goodsNo);
	System.out.println("score : " + score);
	System.out.println("comment : " + comment);
	
	// 상품 리뷰 내용 DB Table에 추가하기
	boolean addGoodsComment = CommentDAO.addGoodsComment(ordersNo, goodsNo, score, comment);
	
	if(addGoodsComment){
		System.out.println("성공적으로 리뷰를 작성하였습니다.");
		response.sendRedirect("/shop/customer/form/goodsOne.jsp?goodsNo=" + goodsNo);
	}
%>

/emp/ordersStateList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder"%>
<%@ page import="shop.dao.OrdersDAO"%>
<!-- Controller Layer -->
<%
	// 로그인 인증 분기
	if(session.getAttribute("loginEmp") == null){
	response.sendRedirect("/shop/emp/form/empLoginForm.jsp");
	return;
}
%>

<%
	// 주문 완료된 상품의 배송현황 리스트 가져오기
	ArrayList<HashMap<String, Object>> ordersStateList = OrdersDAO.getOrdersStateList();
	System.out.println("ordersStateList : " + OrdersDAO.getOrdersStateList());
	
	
%>


<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>배송 상태 관리</title>
	<!-- Latest compiled and minified CSS -->
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<style>
		@import url('https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@100..900&display=swap')
	</style>
	<style>
		.container{
			font-family: "Noto Sans KR", sans-serif;
  			font-optical-sizing: auto;
  			font-weight: <weight>;
  			font-style: normal;
		}
	
		.main{
			text-align: center;
		}
		
		.table{
			text-align: center;
		}
		
		a{
			text-decoration: none;
		}
		
		a.page-link{
			color: #000000;
		}
		
		a.page-link:hover{
			background-color: #000000;
			color: #FFFFFF;
		}
		
		div.header{
			padding-top: 20px;
			margin-bottom: 50px;
		}
	</style>
</head>
<body>
	<div class="container">
	<div class="header">
	<!-- empMenu.jsp include : 서버 기준으로 페이지 요청 vs redirect(클라이언트 기준) -->
	<!-- 주체가 서버이기 때문에 include할 때에는 절대주소가 /shop/... 으로 시작하지 않는다 -->
	<jsp:include page="/emp/inc/empMenu.jsp"></jsp:include>
	<span><a class="btn btn-outline-dark" href="/shop/emp/action/empLogout.jsp">로그아웃</a></span>
	<span><a class="btn btn-outline-dark" href="/shop/emp/empList.jsp">이전</a></span>
	</div>
		<div class="row">
			<div class="col"></div>
			<div class="main col-8">
			<!-- 메인 내용 시작 -->
				<h1>배송 상태 리스트</h1>
				<br>
				<table class="table" border=1>
					<tr>
						<td>주문번호</td>
						<td>상품번호</td>
						<td>상품명</td>
						<td>주문일시</td>
						<td>배송상태</td>
					</tr>
					<%
					for(HashMap<String, Object> m : ordersStateList){
					%>
					<tr>
						<td><%=(Integer)(m.get("ordersNo"))%></td>
						<td><%=(Integer)(m.get("goodsNo"))%></td>
						<td><%=(String)(m.get("goodsTitle"))%></td>
						<td><%=(String)(m.get("ordersDate"))%></td>
						<td>
					<%
						if(m.get("state").equals("결제완료")){
					%>
							<a class="btn btn-dark" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=<%=m.get("state")%>">결제완료</a>
							<a class="btn btn-outline-dark" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=배송중">배송중</a>
							<a class="btn btn-outline-dark disabled" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=배송완료">배송완료</a>
					<%
						}else if(m.get("state").equals("배송중")){
					%>
							<a class="btn btn-outline-dark disabled" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=결재완료%>">결제완료</a>
							<a class="btn btn-dark" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=<%=m.get("state")%>">배송중</a>
							<a class="btn btn-outline-dark" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=배송완료">배송완료</a>
					<%
						}else if(m.get("state").equals("배송완료")){
					%>
							<a class="btn btn-outline-dark disabled" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=결재완료">결제완료</a>
							<a class="btn btn-outline-dark disabled" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=배송중">배송중</a>
							<a class="btn btn-dark" href="/shop/emp/action/modifyStateAction.jsp?ordersNo=<%=(Integer)(m.get("ordersNo"))%>&state=<%=m.get("state")%>">배송완료</a>
					<%
						}
					%>
						</td>
					</tr>
					<%
					}
					%>
				</table>
			<!-- 메인 내용 끝 -->
			</div>
			<div class="col"></div>
		</div>
	</div>
</body>
</html>

/emp/modifyStateAction.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.URLEncoder"%>
<%@ page import="shop.dao.OrdersDAO"%>
<%
	// 로그인 인증 분기
	if(session.getAttribute("loginEmp") == null){
	response.sendRedirect("/shop/emp/form/empLoginForm.jsp");
	return;
}
%>

<%
	// 요청값 분석
	int ordersNo = Integer.parseInt(request.getParameter("ordersNo"));
	String state = request.getParameter("state");
	
	System.out.println("ordersNo : " + ordersNo);
	System.out.println("state : " + state);
	
	// 버튼을 클릭하면 ordersNo에 맞는 state가 변경되도록
	boolean modifyOrdersState = OrdersDAO.modifyOrdersState(ordersNo, state);
	
	if(modifyOrdersState){
		System.out.println("배송 현황이 성공적으로 변경되었습니다.");
		response.sendRedirect("/shop/emp/ordersStateList.jsp");
	}
%>

/emp/ordersStateList.jsp 출력결과

/customer/ordersInfoList.jsp 출력결과

/customer/goodsList.jsp (리뷰목록 추가) 출력결과

kimuki

Road OF Developer

이전 포스트

GDJ 24/04/19 (생성자, getter, setter, this, final)

다음 포스트

GDJ 24/04/22 (SQL 문법 - SQLD 대비, 쇼핑몰 프로젝트)

shop Project

GDJ 24/04/19 (생성자, getter, setter, this, final)

GDJ 24/04/23 (SQL 문법 - SQLD 대비, 쇼핑몰 프로젝트)

0개의 댓글