728x90
수업내용
1교시 (09:30-10:20)
- 쿼리 공부
-- 인라인 뷰(Inline view) 서브쿼리
-- 입사 순으로 빠른 사람 5명 조회
SELECT * FROM (
SELECT *
FROM EMP
ORDER BY HIREDATE)
WHERE ROWNUM <= 5;2교시 (10:30-11:20)
- 쿼리공부
-- 부서번호 20의 평균 급여보다 크고,
-- 부서번호 20에 속하지 않는 관리자를 조회하시오
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT *
FROM EMP A
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20)
AND DEPTNO != 20;
-- 직업이 MANAGER인 사원의 사원명, 부서명을 조회하시오
SELECT A.ENAME, B.DNAME
FROM EMP A
JOIN DEPT B ON A.DEPTNO = B.DEPTNO
WHERE A.JOB = 'MANAGER';
SELECT A.ENAME,
A.DEPTNO,
(SELECT B.DNAME FROM DEPT B
WHERE B.DEPTNO = A.DEPTNO) DNAME
FROM EMP A
WHERE A.JOB = 'MANAGER';
SELECT A.DEPTNO, A.DNAME, MAX(B.SAL)
FROM DEPT A, EMP B
WHERE A.DEPTNO = B.DEPTNO(+)
GROUP BY A.DEPTNO, A.DNAME
ORDER BY A.DEPTNO;
SELECT A.DEPTNO,
A.DNAME,
(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = A.DEPTNO) SAL
FROM DEPT A;
-- UNION : 합집합, 중복 미포함
SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM DEPT;
-- UNION ALL : 중복포함 합집합
SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT;
-- INTERSECT : 교집합
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;3교시 (11:30-12:20)
메인 페이지 만들기
- KosmoMainVO.java
package a.b.c.com.kosmo.main.vo;
public class KosmoMainVO {
private String chkpop;
public KosmoMainVO() {
}
public KosmoMainVO(String chkpop) {
this.chkpop = chkpop;
}
// getter/setter
public String getChkpop() {
return chkpop;
}
public void setChkpop(String chkpop) {
this.chkpop = chkpop;
}
}- KosmoMainController.java
package a.b.c.com.kosmo.main.controller;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import a.b.c.com.kosmo.login.controller.KosmoLoginController;
import a.b.c.com.kosmo.mem.vo.KosmoMemberVO;
import a.b.c.com.kosmo.main.vo.KosmoMainVO;
@Controller
public class KosmoMainController {
Logger logger = LogManager.getLogger(KosmoLoginController.class);
@RequestMapping(value="kosmoMainPage", method=RequestMethod.GET)
public String kosmoMainPage(HttpServletRequest req, KosmoMemberVO kmvo, Model model) {
logger.info("KosmoMainController kosmoMainPage 함수 진입 >>> : ");
return "main/kosmoMainPage";
}
@RequestMapping(value="kosmoPopup", method=RequestMethod.GET)
public String kosmoPopup(HttpServletRequest req, KosmoMemberVO kmvo, Model model) {
logger.info("KosmoMainController kosmoMainPage 함수 진입 >>> : ");
return "main/kosmoPopup";
}
@RequestMapping(value="kosmoPopupCookie", method=RequestMethod.GET)
@ResponseBody
public String kosmoPopupCookie(HttpServletRequest req, HttpServletResponse res, KosmoMainVO kmainvo) {
logger.info("KosmoMainController kosmoPopupCookie 함수 진입 >>> : ");
String chkpop = kmainvo.getChkpop();
if (chkpop !=null && chkpop.equals("01")) {
logger.info("KosmoMainController kosmoPopupCookie 함수 진입 chkpop >>> : " + chkpop);
Cookie ck = new Cookie("onedayPopup", "off");
ck.setPath(req.getContextPath());
ck.setMaxAge(24*60*60);
res.addCookie(ck);
logger.info("KosmoMainController kosmoPopupCookie >>> : 팝업이 하루 동안 열리지 않음");
};
return chkpop;
}
}4교시 (12:30-13:20)
메인 페이지 만들기
- kosmoPopup.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>공지사항 팝업</title>
<!-- 뷰포트 : device에 맞게 화면 크기 조정 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('#popBtn').on('click', function(){
window.self.close();
let chkVal = $("#chkpop").is(":checked");
if (chkVal){
// Ajax 처리 루틴
let urlV = "#/kosMember/kos_script/popup_cookie.jsp";
let typeV = 'GET';
let dataV = $("#chkpop").val();
if (chkVal){
$.ajax({
url: urlV,
type: typeV,
data: { "chkpop": dataV },
success : whenSuccess,
error : whenError
});
}
function whenSuccess(resData){
var bool = $(resData).find('result').text();
console.log("bool >>> : " + bool);
}
function whenError(){
console.log("에러가 >>> : ");
}
}
});
});
</script>
</head>
<body>
<%
boolean popState = true;
%>
<h2>NOTICE POPUP</h2>
<hr>
<%
if (popState){
%>
<div id="noticePop">
<form name="noticePopForm" id="noticePopForm">
<img class="img_1" src="/kosmoSpring/img/img_kakaofriends/08.png"><br>
<input type="checkbox" class="chkpop" name="chkpop" id="chkpop" value="01" />
하루 동안 열지 않음
<input type="button" id="popBtn" value="닫기" />
</form>
</div>
<%
}else{
%>
<%= "공지사항 팝업 없음" %>
<%
}
%>
</body>
</html>5교시 (14:30-15:20)
메인 페이지 만들기
- kosmoPopupLayer.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>공지사항 팝업</title>
<!-- 뷰포트 : device에 맞게 화면 크기 조정 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<!--
레이어 팝업 드래그 하기 위해서 jquery ui draggable 라이브러리 사용
https://jqueryui.com/draggable/
-->
<link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
<!-- <link rel="stylesheet" href="//resources/demos/style.css"> -->
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
<!--
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
-->
<script type="text/javascript">
$(document).ready(function(){
$("#noticePop").draggable();
$('#popBtn').on('click', function(){
alert("popBtn >>> : ");
let chkVal = $("#chkpop").is(":checked");
alert("checkbox checked >>> : " + chkVal);
if (chkVal){
// Ajax 처리 루틴
let urlV = "#/kosMember/kos_script/popup_cookie.jsp";
let typeV = 'GET';
let dataV = $("#chkpop").val();
alert("dataV >>> : " + dataV);
if (chkVal){
$.ajax({
url: urlV,
type: typeV,
data: { "chkpop": dataV },
success : whenSuccess,
error : whenError
});
}
function whenSuccess(resData){
alert("리턴 데이터 >>> : \n" + resData);
var bool = $(resData).find('result').text();
alert("bool >>> : " + bool);
}
function whenError(){
alert("에러가 >>> : ");
}
}
$('#noticePop').hide();
$('.imgPop').hide();
});
});
</script>
<style>
div#noticePop {
potition: absolute;
top: 50px;
left: 50px;
color: black;
border: 1px solid red;
width: 300px;
height: 300px;
text-align: center;
background-color: cyan; /* #fff */
}
div#noticePop > div {
position: relative;
top: 0px;
border: 1px solid gray;
padding: 10px;
color: black;
background-color: cyan; /* #fff */
}
</style>
</head>
<body>
<%
boolean popState = true;
%>
<h2>NOTICE POPUP</h2>
<hr>
<%
if (popState){
%>
<div id="noticePop">
<h4>공지사항 팝업</h4>
<hr>
<div class="imgPop">
<form name="noticePopForm" id="noticePopForm">
<img class="img_1" src="/kosmoSpring/img/img_kakaofriends/08.png"><br>
<input type="checkbox" class="chkpop" name="chkpop" id="chkpop" value="01" />
하루 동안 열지 않음
<input type="button" id="popBtn" value="닫기" />
</form>
</div>
</div>
<%
}else{
%>
<%= "공지사항 팝업 없음" %>
<%
}
%>
</body>
</html>6교시 (15:30-16:20)
메인 페이지 만들기
- kosmoMainPage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="a.b.c.com.kosmo.mem.vo.KosmoMemberVO"%>
<%@page import="java.util.List"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>MAIN PAGE</title>
<!-- 뷰포트 : device에 맞게 화면 크기 조정 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<script type="text/javascript" src="/kosmoSpring/js/common.js"></script>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$(function(){
// 팝업
var win = window.open( "kosmoPopup.k",
"_blank" ,
"left=150,top=150,width=300,height=300",
false);
function popup_close(){
// alert('3초 후 팝업 자동으로 닫기.');
win.close();
}
setTimeout(popup_close, 30000);
// 현재 시간
function printTime(){
let days = ["일", "월", "화", "수", "목", "금", "토"];
let md = new Date();
let yy = md.getFullYear();
let mm = md.getMonth() + 1;
let dd = md.getDate();
let ii = md.getDay();
let di = days[ii];
let hh = md.getHours();
let mi = md.getMinutes();
let ss = md.getSeconds();
let time = yy + "년" +
numpad(mm) + "월" +
numpad(dd) + "일" +
di + "요일" +
numpad(hh) + ":" +
numpad(mi) + ":" +
numpad(ss);
document.getElementById("timer").innerHTML=time;
}
setInterval(printTime, 1000);
// 수료일
function getDay(y, m, d) {
let today = new Date();
let dday = new Date(y, m-1, d);
let cnt = dday.getTime() - today.getTime();
let n = Math.ceil(cnt / (24 * 60 * 60 * 1000));
return n;
}
let date = new Date();
let y = date.getFullYear();
let dday = getDay(y, 7, 20);
console.log("dday >>> : " + dday);
document.getElementById("dday").innerHTML=dday;
});
// 상품 이벤트
$(document).on('click', '#img_1', function(){
alert("#상품아이디가지고 상품검색하기");
});
});
</script>
<link rel="stylesheet" href="/kosmoSpring/css/main.css">
<style type="text/css">
.m {
text-align: center;
}
table{
margin : auto;
border-collapse: separate;
border-spacing: 20px 40px;
}
img {
width:200px;
height:300px;
}
a {
text-decoration-line: none;
/* text-decoration-line: underline; */
/* text-decoration-line: overline; */
/* text-decoration-line: line-through; */
/* text-decoration-line: underline line-through overline; */
}
</style>
</head>
<body>
<% request.setCharacterEncoding("UTF-8"); %>
<%
String kmid = "";
Object obj = request.getAttribute("listLogin");
if (obj !=null) {
List<KosmoMemberVO> list = (List<KosmoMemberVO>)obj;
int nCnt = list.size();
if (nCnt == 1) {
kmid = list.get(0).getKmid();
}
%>
<p align="right">
<%= kmid %> 님 로그인 중 <a href="kosmoLogout.k">로그아웃</a>
</p>
<%
}else{
%>
<p align="right">
<a href="kosmoLoginForm.k">로그인</a>
</p>
<%
}
%>
<h3 class="m">MAIN PAGE :
<span style="color:red">수료일 D-</span><span id="dday" style="color:red">
</span> : <span id="timer"></span>
</h3>
<hr>
<div class="container">
<nav class="navi">
<ul>
<li><a href="kosmoProductInsertForm.k">상품등록</a></li>
<li><a href="kosmoProductSelectAll.k">상품조회</a></li>
<li><a href="javascript:alert('준비중입니다.');" onfocus="this.blur()">게시판</a></li>
<li><a href="kosmoMemberInsertForm.k">회원가입</a></li>
</ul>
</nav>
</div>
<div id="contents">
<h1>BookStore</h1>
</div>
</body>
</html>7교시 (16:30-17:20)
메인 페이지 만들기
- index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>index.jsp</h3>
<hr>
<a href="kosmoProductInsertForm.k">코스모 상품 입력</a><br>
<a href="kosmoProductSelectAll.k">코스모 상품 전체조회</a><br>
<p>
<a href="kosmoMemberInsertForm.k">코스모 회원 입력</a><br>
<a href="kosmoMemberSelectAll.k">코스모 회원 전체조회</a><br>
<p>
<a href="kosmoLoginForm.k">코스모 로그인</a><br>
<p>
<a href="kosmoMainPage.k">코스모 메인</a><br>
</body>
</html>
8교시 (17:30-18:30)
- 쿠키와 세션에 대한 설명
Notes
728x90