- pl/sql
- 수강인원과 수강정원이 같을 시 더이상 수강신청을 못하는 프로시저문을 추가하도록 하겠다.
CREATE DEFINER=`haksa`@`localhost` PROCEDURE `enroll`(
in i_scode char(8),
in i_lcode char(4),
out o_count int
)
BEGIN
declare space int;
select count(*) into o_count from enrollments
where scode=i_scode and lcode=i_lcode;
select capacity-persons into space from courses
where lcode=i_lcode;
IF (space = 0) THEN
SET o_count = 2;
END IF;
IF (o_count = 0) THEN
insert into enrollments (scode, lcode, edate, grade)
values(i_scode, i_lcode, now(), 0 );
update courses set persons = persons+1 where lcode=i_lcode;
END IF;
END
- 수강정원과 수강신청인원이 같다면 alert이 되도록 하겠다.
- read.jsp
$("#enroll").on('click',function(){
var lcode=$('#lcode').val();
if(!confirm(lcode + "을(를) 수강신청하시겠습니까?")) return;
$.ajax({
type:'get',
url:'/enroll',
data:{'scode':scode,'lcode':lcode},
success:function(data){
if(data==0){
alert("수강신청 되었습니다.")
getList();
}else if(data==1){
alert("이미 수강신청 되어있습니다.")
}else if(data==2){
alert("강좌가 마감되었습니다.")
}
}
})
});
- 강좌목록 란에 강좌정보에서 누가 수업을 신청했는지 뜨도록 하겠다.
- 우선 이용할 DB의 테이블을 확인해보도록 하겠다.
- mysql
-- 2021.09.02
create view view_enroll_stu as
select e.lcode, e.scode, e.edate, e.grade, s.sname, s.dept, s.year, p.pname
from enrollments e, students s, professors p
where e.scode=s.scode and s.advisor=p.pcode;
select * from view_enroll_stu where lcode='C401';
- 특정 강좌를 신청한 학생목록을 출력하는 메서드를 위의 정의한 view를 이용해서 만들겠다.
- CouDAO.java
// 7. 특정 강좌를 신청한 학생목록
public JSONObject stu_list(String lcode){
JSONObject object = new JSONObject();
try {
String sql = "select * from view_enroll_stu where lcode=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, lcode);
ResultSet rs = ps.executeQuery();
JSONArray array = new JSONArray();
while(rs.next()){
JSONObject obj = new JSONObject();
obj.put("scode", rs.getString("scode"));
obj.put("sname", rs.getString("sname"));
obj.put("dept", rs.getString("dept"));
obj.put("pname", rs.getString("pname"));
obj.put("year", rs.getInt("year"));
obj.put("edate", rs.getString("edate"));
obj.put("grade", rs.getString("grade"));
array.add(obj);
}
object.put("array",array);
} catch (Exception e) {
System.out.println("CouDAO stu_list : "+e.toString());
}
return object;
}
- checkServlet.java
@WebServlet(value={"/cou_enroll_list.json","/del_enroll","/enroll","/stu_enroll_list.json","/check_lcode","/pro_cou_list.json","/pro_stu_list.json"})
public class CheckServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
CouDAO cdao = new CouDAO();
ProDAO pdao = new ProDAO();
StuDAO sdao = new StuDAO();
switch (request.getServletPath()) {
case "/cou_enroll_list.json":
out.println(cdao.stu_list(request.getParameter("lcode")));
break;
- json에 담은 성적 데이터를 출력하도록 하겠다.
- course\read.jsp
<h1>성적입력</h1>
<table id="sctbl"></table>
<script id="sctemp" type="text/x-handlebars-template">
<tr class="title">
<th width=20><input type="checkbox"/></th>
<th width=100>학생번호</th>
<th width=100>학생이름</th>
<th width=100>학과</th>
<th width=100>지도교수</th>
<th width=100>학년</th>
<th width=120>수강신청일</th>
<th width=100>점수</th>
</tr>
{{#each array}}
<tr class="row">
<td><input type="checkbox"/></td>
<td>{{scode}}</td>
<td>{{sname}}</td>
<td>{{dept}}</td>
<td>{{pname}}</td>
<td>{{year}}</td>
<td>{{edate}}</td>
<td><input type="text" value="{{grade}}" size=1/> <button>수정</button></td>
</tr>
{{/each}}
</script>
<script>
getList();
function getList(){
var lcode="${vo.lcode}"
$.ajax({
type:'get',
url:'/cou_enroll_list.json',
dataType:'json',
data:{'lcode':lcode},
success:function(data){
var temp = Handlebars.compile($('#sctemp').html());
$('#sctbl').html(temp(data));
}
})
}
- 이제 가장 왼쪽의 최상단 체크박스를 이용해 전체수정할 수 있도록 하겠다.
- course\read.jsp
<h1>성적입력</h1>
<table id="sctbl"></table>
<script id="sctemp" type="text/x-handlebars-template">
<tr class="title">
<th width=20><input type="checkbox" id="chkAll"/></th>
<th width=100>학생번호</th>
<th width=100>학생이름</th>
<th width=100>학과</th>
<th width=100>지도교수</th>
<th width=100>학년</th>
<th width=120>수강신청일</th>
<th width=100>점수</th>
</tr>
{{#each array}}
<tr class="row">
<td><input type="checkbox" class="chk"/></td>
<td>{{scode}}</td>
<td>{{sname}}</td>
<td>{{dept}}</td>
<td>{{pname}}</td>
<td>{{year}}</td>
<td>{{edate}}</td>
<td><input type="text" value="{{grade}}" size=1/> <button>수정</button></td>
</tr>
{{/each}}
</script>
<script>
// chkAll 버튼을 클릭한 경우
$('#sctbl').on('click',".title #chkAll", function() {
if ($(this).is(':checked')) {
$('#sctbl tr input:checkbox').each(function() {
$(this).prop('checked', true);
});
} else {
$('#sctbl tr input:checkbox').each(function() {
$(this).prop('checked', false);
});
}
});
- 전체 선택 후 각 행의 checkbox를 선택한 경우
// 각행의 checkbox를 선택한 경우
$('#sctbl').on('click','.row td .chk', function(){
var cntAll = $('#sctbl .row td .chk').length; //첵박 개수
var cntChecked = $('#sctbl .row td .chk:checked').length; //첵박체크 개수
if(cntAll==cntChecked){
$('#chkAll').prop('checked',true);
}else{
$('#chkAll').prop('checked',false);
};
});
- 점수를 수정하는 메서드를 만들겠다.
- CouDAO.java
// 8. 특정 학생의 특정 과목의 점수를 수정하는 메서드
public void grade_update(String lcode, String scode, int grade){
try {
String sql = "update enrollments set grade=? where lcode=? and scode=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setInt(1, grade);
ps.setString(2, lcode);
ps.setString(3, scode);
ps.execute();
} catch (Exception e) {
System.out.println("CouDAO grade_update : "+e.toString());
}
}
@WebServlet(value={"/grade_update","/cou_enroll_list.json","/del_enroll","/enroll","/stu_enroll_list.json","/check_lcode","/pro_cou_list.json","/pro_stu_list.json"})
public class CheckServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
CouDAO cdao = new CouDAO();
ProDAO pdao = new ProDAO();
StuDAO sdao = new StuDAO();
switch (request.getServletPath()) {
case "/grade_update":
cdao.grade_update(request.getParameter("lcode"),
request.getParameter("scode"),
Integer.parseInt(request.getParameter("grade")));
break;
- read.jsp
<h1>성적입력</h1>
<table id="sctbl"></table>
<script id="sctemp" type="text/x-handlebars-template">
<tr class="title">
<th width=20><input type="checkbox" id="chkAll"/></th>
<th width=100>학생번호</th>
<th width=100>학생이름</th>
<th width=100>학과</th>
<th width=100>지도교수</th>
<th width=100>학년</th>
<th width=120>수강신청일</th>
<th width=100>점수</th>
</tr>
{{#each array}}
<tr class="row">
<td><input type="checkbox" class="chk"/></td>
<td class="scode">{{scode}}</td>
<td>{{sname}}</td>
<td>{{dept}}</td>
<td>{{pname}}</td>
<td>{{year}}</td>
<td>{{edate}}</td>
<td><input type="text" value="{{grade}}" class="grade" size=1/> <button>수정</button></td>
</tr>
{{/each}}
</script>
<script>
var lcode = "${vo.lcode}"
getList();
// 각행의 수정버튼을 클릭한 경우
$('#sctbl').on('click', '.row button', function() {
var row = $(this).parent().parent();
var scode = row.find(".scode").html();
var grade = row.find(".grade").val();
if (grade < 0 || grade > 100) {
alert("점수는 0 ~ 100 사이의 수를 입력하세요.");
row.find(".grade").focus();
return;
}
if(!confirm("수정하시겠습니까?"))return;
$.ajax({
type:'get',
url:'/grade_update',
data:{'lcode':lcode,'scode':scode,'grade':grade},
success:function(){
alert("수정완료");
}
})
})
- checkbox를 선택한 row만 수정해보도록 하겠다.
<h1>성적입력</h1>
<div style="margin-bottom: 10px;">
<button id="update">선택수정</button>
</div> //선택수정 버튼을 클릭한경우
$("#update").on("click", function() {
var checked = $("#sctbl .row .chk:checked").length;
if (checked == 0) {
alert("수정할 학생을 선택하세요!");
return;
}
//잘못입력한 점수가 있는지 체크
var isError = false;
$("#sctbl .row .chk:checked").each(function() {
var row = $(this).parent().parent();
var grade = row.find(".grade").val();
if (!(grade >= 0 && grade <= 100)) {
alert("점수를 0~100 수를 입력하세요!");
row.find(".grade").focus();
isError = true;
return false;
}
});
if (isError)
return;
if (!confirm("수정하실래요?"))
return;
$("#sctbl .row .chk:checked").each(function() {
var row = $(this).parent().parent();
var grade = row.find(".grade").val();
var scode = row.find(".scode").html();
$.ajax({
type : "get",
url : "/grade_update",
data : {
"lcode" : lcode,
"scode" : scode,
"grade" : grade
},
success : function() {
}
});
});
alert("수정완료!");
getList();
});
- 이제 로그인한 경우에만 특정 기능을 사용할 수 있도록 하겠다.
- 우선 메인페이지에 로그인 버튼을 만들고 로그인 페이지가 보여질 수 있도록 서블릿을 만들겠다.
- main.jsp
<h4>
<a href="/course/insert">강좌등록</a>
</h4>
<h4 style="float:right">
<a href="/user/login">로그인</a>
</h4>
</div>
- create new UserServlet.java
package 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;
@WebServlet(value={"/user/login"})
public class UserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
switch (request.getServletPath()) {
case "/user/login":
request.setAttribute("pageName", "/user/login.jsp");
RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
dis.forward(request, response);
break;
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
}
}
- create new user\login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<style>
.title {
background: rgb(53, 120, 196);
color: white;
text-align: center;
}
table, tr, td{
border: 1px solid rgb(53, 120, 196);
}
input[type=submit]{
background: rgb(53, 120, 196);
color: white;
padding: 10px 20px 10px 20px;
font-size: 20px;
border-radius: 5px;
border: 1px solid rgb(53, 120, 196);
}
</style>
<form id="frm">
<table style="margin:0px auto; margin-top:50px; margin-bottom:50px;">
<tr>
<td class="title" colspan=2><h1>로그인</h1></td>
</tr>
<tr>
<td class="title" width=100>아이디</td>
<td width="200"><input name="uid" placeholder="아이디를 입력하세요." size=25/></td>
</tr>
<tr>
<td class="title" width=100>비밀번호</td>
<td width="200"><input name="upw" type="password" placeholder="비밀번호를 입력하세요." size=25/></td>
</tr>
<tr style="text-align: center; margin-top: 10px; margin-bottom: 10px;">
<td colspan=2><input type="submit" value="로그인"/></td>
</tr>
</table>
</form>
<script>
$(frm).on('submit',function(e){
e.preventDefault();
var uid = $(frm.uid).val();
if(uid==""){
alert("아이디를 입력하세요.")
$(frm.uid).focus();
return;
}
var upw = $(frm.upw).val();
if(upw==""){
alert("비밀번호를 입력하세요.")
$(frm.upw).focus();
return;
}
})
</script>
- mysql
- "user" table 을 만들고 데이터를 몇개 넣어보자.
-- user table create
create table user(
uid nvarchar(20) not null primary key,
upw nvarchar(20) not null,
uname nvarchar(20) not null
);
insert into user values('user01','pass','이주성');
insert into user values('user02','pass','주성이');
insert into user values('user03','pass','성주이');
select * from user;
- create new UserVO.java
package model;
public class UserVO {
private String uid;
private String upw;
private String uname;
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public String getUpw() {
return upw;
}
public void setUpw(String upw) {
this.upw = upw;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
@Override
public String toString() {
return "UserVO [uid=" + uid + ", upw=" + upw + ", uname=" + uname + "]";
}
}
- create new UserDAO.java
package model;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class UserDAO {
// 1. 로그인 체크
public UserVO login(String uid){
UserVO vo = new UserVO();
try {
String sql = "select * from user where uid=?";
PreparedStatement ps = Database.CON.prepareStatement(sql);
ps.setString(1, uid);
ResultSet rs = ps.executeQuery();
if(rs.next()){
vo.setUid(rs.getString("uid"));
vo.setUpw(rs.getString("upw"));
vo.setUname(rs.getString("uname"));
}
} catch (Exception e) {
System.out.println("UserDAO login : "+e.toString());
}
return vo;
}
}
- UserServlet.java
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
switch (request.getServletPath()) {
case "/user/login":
UserVO vo = dao.login(request.getParameter("uid"));
System.out.println(vo.toString());
break;
}
}
- UserServlet.java
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter out = response.getWriter();
switch (request.getServletPath()) {
case "/user/login":
int result=0;
UserVO vo = dao.login(request.getParameter("uid"));
if(vo.getUid() != null){
if(vo.getUpw().equals(request.getParameter("upw"))){
result=1;
}else{
result=2;
}
}
out.println(result);
break;
}
}
- login.jsp
<script>
$(frm).on('submit', function(e) {
e.preventDefault();
var uid = $(frm.uid).val();
if (uid == "") {
alert("아이디를 입력하세요.")
$(frm.uid).focus();
return;
}
var upw = $(frm.upw).val();
if (upw == "") {
alert("비밀번호를 입력하세요.")
$(frm.upw).focus();
return;
}
$.ajax({
type : 'post',
url : '/user/login',
data : {
"uid" : uid,
"upw" : upw
},
success : function(data) {
if(data==0){
alert("존재하지 않는 아이디입니다.");
}else if(data==2){
alert("비밀번호를 화인하세요.");
}else{
alert("로그인되었습니다.");
location.href="/";
}
}
})
})
</script>
- 로그인에 성공하면 세션에 정보를 저장하고 로그인을 로그아웃으로 로그인한 사람의 아이디를 페이지에 보이도록 하겠다.
- userservlet.java
UserVO vo = dao.login(request.getParameter("uid"));
if(vo.getUid() != null){
if(vo.getUpw().equals(request.getParameter("upw"))){
// 로그인 성공
result=1;
HttpSession session = request.getSession();
session.setAttribute("vo", vo);
}else{
result=2;
}
- 세션에 저장한 vo값을 jstl c tag로 불러오도록 하겠다. 로그인을 하기전에는 메뉴 및 로그아웃 버튼 유저 id가 보이지 않고 로그인하면 보이는 방식으로 하겠다.
<div id="menu">
<c:if test="${vo.uid != null}">
<h4>
<a href="/professor/list">교수목록</a>
</h4>
<h4>
<a href="/professor/insert">교수등록</a>
</h4>
<h4>
<a href="/student/list">학생목록</a>
</h4>
<h4>
<a href="/student/insert">학생등록</a>
</h4>
<h4>
<a href="/course/list">강좌목록</a>
</h4>
<h4>
<a href="/course/insert">강좌등록</a>
</h4>
</c:if>
<c:if test="${vo.uid != null}">
<h4 style="float:right; color: white;">${vo.uid}님</h4>
<h4 style="float:right;"><a href="/user/logout" id="logout">로그아웃</a></h4>
</c:if>
<c:if test="${vo.uid == null}">
<h4 style="float:right">
<a href="/user/login">로그인</a>
</h4>
</c:if>
</div>
- 로그아웃을 누르면 session 초기화가 되도록 하겠다.
@WebServlet(value={"/user/login","/user/logout"})
public class UserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
UserDAO dao = new UserDAO();
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
switch (request.getServletPath()) {
case "/user/login":
request.setAttribute("pageName", "/user/login.jsp");
RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
dis.forward(request, response);
break;
case "/user/logout":
HttpSession session = request.getSession();
session.invalidate();
response.sendRedirect("/");
break;
}
}
- 로그인 상태 유지버튼을 누르면 페이지를 닫아도 로그인 상태가 유지되도록 하겠다.
- login.jsp
<tr style="text-align: center; margin-top: 10px; margin-bottom: 10px;">
<td colspan=2><input type="submit" value="로그인" /></td>
</tr>
<tr style="text-align: center; margin-top: 10px; border: none;">
<td colspan=2><input type="checkbox" id="chklogin" />로그인 상태유지</td>
</tr>
</table> var chklogin = $('#chklogin').is(":checked")? 1:0;
$.ajax({
type : 'post',
url : '/user/login',
data : {
"uid" : uid,
"upw" : upw,
"chklogin" : chklogin
},
success : function(data) {
if (data == 0) {
alert("존재하지 않는 아이디입니다.");
} else if (data == 2) {
alert("비밀번호를 화인하세요.");
} else {
alert("로그인되었습니다.");
location.href = "/";
}
}
})
- UserServlet.java
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter out = response.getWriter();
switch (request.getServletPath()) {
case "/user/login":
int result=0;
UserVO vo = dao.login(request.getParameter("uid"));
if(vo.getUid() != null){
if(vo.getUpw().equals(request.getParameter("upw"))){
// 로그인 성공
result=1;
HttpSession session = request.getSession();
session.setAttribute("userlogin", vo);
String lCheck = request.getParameter("chklogin");
if(lCheck.equals("1")){ // new
// cookie 생성자
Cookie cookie = new Cookie("user", vo.getUid());
// 저장 루트
cookie.setPath("/");
// 저장 시간 단위 초
cookie.setMaxAge(60*60*24);
response.addCookie(cookie);
}
}else{
result=2;
}
}
out.println(result);
break;
}
}
- 메인서블릿에서 저장된 쿠키를 가지고와서 새창 팝업시 로그인된 상태로 하겠다.
package controller;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/")
public class MainServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Cookie cookies[] = request.getCookies();
for(Cookie cookie : cookies){
if(cookie.getName().equals("user")){
request.getSession().setAttribute("user", cookie.getValue());
}
}
RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
request.setAttribute("pageName", "/about.jsp");
dis.forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
}
}
- 로그아웃을 하면 쿠키가 초기화 되도록 하겠다.
@WebServlet(value={"/user/login","/user/logout"})
public class UserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
UserDAO dao = new UserDAO();
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
switch (request.getServletPath()) {
case "/user/login":
request.setAttribute("pageName", "/user/login.jsp");
RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
dis.forward(request, response);
break;
case "/user/logout":
HttpSession session = request.getSession();
// 세션 삭제
session.invalidate();
// 쿠키 삭제
Cookie cookies[] = request.getCookies();
for(Cookie cookie : cookies){
if(cookie.getName().equals("user")){
cookie.setPath("/");
cookie.setMaxAge(0);
response.addCookie(cookie);
}
}
response.sendRedirect("/");
break;
}
}
'ICIA 수업일지' 카테고리의 다른 글
| 2021.09.06 수업일지(Mysql, Servlet, JSTL) (0) | 2021.09.06 |
|---|---|
| 2021.09.03 수업일지 (Servlet, Spring, MySQL, Template) (0) | 2021.09.03 |
| 2021.09.01 수업일지(Servlet, Mysql, pl/sql, Template) (0) | 2021.09.01 |
| 2021.08.31 수업일지(Spring, Servlet, MVC, Mysql, PL/SQL, JAVA) (0) | 2021.08.31 |
| 2021.08.30 수업일지(Servlet, Spring, Mysql) (0) | 2021.08.30 |
