본문 바로가기
ICIA 수업일지

2021.08.31 수업일지(Spring, Servlet, MVC, Mysql, PL/SQL, JAVA)

by 주성씨 2021. 8. 31.

- 어제에 이어서 학사관리 프로젝트를 만들도록 하겠다.

- pl/sql(Procedural Languag/SQL)

- webdb - strored procedure

CREATE DEFINER=`haksa`@`localhost` PROCEDURE `list`(
in i_table nvarchar(1000),
in i_key nvarchar(50),
in i_word nvarchar(50),
in i_order nvarchar(50),
in i_desc nvarchar(5),
in i_page int,
in i_perPage int
)
BEGIN
set @v_word = concat('%',i_word,'%');
set @v_order = concat(i_order,' ',i_desc);
set @v_start=concat(i_page-1)*i_perPage;
set @v_perPage=i_perPage;
set @v_sql=concat('select * from ',i_table,' where ',i_key,' like ? order by ',@v_order,' limit ?,?');
prepare ps from @v_sql;
execute ps using @v_word, @v_start, @v_perPage;

set @v_sql=concat('select count(*) from ',i_table,' where ',i_key,' like ?');
prepare ps from @v_sql;
execute ps using @v_word;
END

 

- 학생등록란을 만들어주도록 하겠다.

- insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<style>
form{
	width:400px;
	margin: 0px auto;
}
</style>
<h1>학생 등록</h1>
<form name="frm">
	<span>학생번호 : </span><input name="scode" value="${code+1}"/><hr/>
	<span>학생이름 : </span><input name="sname" /><hr/>
	<span>학생학과 : </span>
	<select name="dept">
		<option value="전산">전산</option>
		<option value="전자">전자</option>
		<option value="건축">건축</option>
	</select> 
	<hr/>
	<span>학생학년 : </span>
	<input type="radio" value="1" name="year" checked/>1학년
	<input type="radio" value="2" name="year" />2학년
	<input type="radio" value="3" name="year" />3학년
	<input type="radio" value="4" name="year" />4학년<hr/>
	<span>생년월일 : </span>
	<input name="yy" size=4 value="${yy}" placeholder="yyyy"/>년
	<input name="mm" size=2 value="${mm}" placeholder="mm"/>월
	<input name="dd" size=2 value="${dd}" placeholder="dd"/>일
	<hr />
	<span>지도교수 : </span>
	<input name="pcode" size=2/>&nbsp;
	<input name="pname" readonly />
	<hr />
	<div style="margin: 0px center; text-align: center;">
	<input type="submit" value="학생등록" />
	<input type="reset" value="등록취소" />
	</div>
</form>
<script>
	$(frm).on("submit",function(e){
		e.preventDefault();
		var sname=$(frm.sname).val();
		if(sname==""){
			alert("학생 이름을 입력하세요.")
			$(frm.sname).focus();
			return;
		}
		
		if(!confirm("신규등록 하시겠습니까?")) return;
		frm.action="/student/insert";
		frm.method="post";
		frm.submit();
	})
</script>

 

- 학생코드 신규등록을 위한 students 메서드를 생성하겠다.

- StuDAO.java

	// 2. 학생 번호 maxCode
	public String maxCode(){
		String code = "";
		try {
			String sql = "select max(scode) from students";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				code = rs.getString("max(scode)");
			}
		} catch (Exception e) {
			System.out.println("StuDAO maxCode : " + e.toString());
		}
		return code;
	}

 

- stuservlet.java

		case "/student/insert":
			dis = request.getRequestDispatcher("/main.jsp");
			request.setAttribute("code", dao.maxCode());
			request.setAttribute("pageName", "/student/insert.jsp");
			request.setAttribute("index", 4);
			dis.forward(request, response);
			break;
		}

 

- insert.jsp

<form name="frm">
	<span>학생번호 : </span><input name="scode" value="${code+1}"/><hr/>
	<span>학생이름 : </span><input name="sname" /><hr/>

 

 

- 지도교수 선택은 팝업창이 뜨는 방식으로 하도록 하겠다. 메인과 연결되지 않은 단독 페이지이기 떄문에 jquery, handlebar 스크립트로 넣어주도록 하겠다.

- insert.jsp

<script>
	$(frm.pcode).on('click',function(){
		window.open("/professor/search.jsp","advisor","width=500, height=350,top=200,left=900");
	})

 

- create new professor\search.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>교수검색</title>
</head>
<body>
	<h2>교수검색</h2>
	<button id="close">닫기</button>
</body>
<script>
	$("#close").on('click', function() {
		window.close();
	})
</script>
</html>

 

- ajax으로 데이터를 가지고 오고 template에 넣어주도록 하겠다.

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>교수검색</title>
<style>
body{
text-align: center;
}
table {
	border-collapse: collapse;
	margin: 0px auto;
	margin-bottom: 10px;
}

th {
	background: rgb(53, 120, 196);
	color: white;
}

th, td {
	border: 1px solid black;
	padding: 10px;
}

#pagination {
	text-align: center;
}

#prev, #next {
	padding: 5px 20px 5px 20px;
	font-size: 15px;
	font-weight: bold;
	color: white;
	border-radius: 5px 5px 5px 5px;
	background: rgb(53, 120, 196);
	border-radius: 5px 5px 5px 5px;
	color: white;
	border-radius: 5px 5px 5px 5px;
}

#condition {
	margin: 0px 20px 10px 20px;
}

#word, #total {
	height: 20px;
	margin-bottom: 10px;
	
}
.row:hover {
	cursor: pointer;
	background: rgb(242, 242, 242);
	color: black;
}
</style>
</head>
<body>
	<h2>교수검색</h2>
	검색어 : <input type="text" id="word" size=5 />
	/ 검색수 : <span id="total"></span> 건
	<table id="tbl"></table>
	<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<th width=100>교수번호</th>
		<th width=100>교수이름</th>
		<th width=100>교수학과</th>
		<th width=100>교수직급</th>
	</tr>
	{{#each array}}
	<tr class="row">
		<td>{{pcode}}</td>
		<td>{{pname}}</td>
		<td>{{dept}}</td>
		<td>{{title}}</td>
	</tr>
	{{/each}}
</script>
	<div style="text-align: center">
		<button id="prev">&lt;</button>
		<span id="page">[ 1/2 ]</span>
		<button id="next">&gt;</button>
		<hr />
		<button id="close">닫기</button>
	</div>
</body>
<script>
	var page = 1;

	$("#prev").on("click", function() {
		page--;
		getList();
	});
	$("#next").on("click", function() {
		page++;
		getList();
	})
	$("#word").on("keypress", function(e) {
		if (e.keyCode == 13) {
			page = 1;
			getList();
		}
	});

	// 창닫기
	$("#close").on('click', function() {
		window.close();
	})

	// 교수목록 출력
	getList();
	function getList() {
		var word = $('#word').val();
		var perPage = 3;
		$.ajax({
			type : 'get',
			url : '/professor.json',
			dataType : 'json',
			data : {
				"page" : page,
				"perPage" : perPage,
				"word" : word,
				"order" : "pcode",
				"desc" : "",
				"key" : "pname"
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$('#tbl').html(temp(data));
				$('#total').html(data.total);
				var lastPage = Math.ceil(data.total / perPage)
				$('#page').html("[ " + page + "/" + lastPage + " ]");
				if (page == 1) {
					$('#prev').attr('disabled', true)
					$('#prev').css('background', 'gray')
				} else {
					$('#prev').attr('disabled', false)
					$('#prev').css('background', 'rgb(53, 120, 196)')
				}
				if (page == lastPage) {
					$('#next').attr('disabled', true)
					$('#next').css('background', 'gray')
				} else {
					$('#next').attr('disabled', false)
					$('#next').css('background', 'rgb(53, 120, 196)')
				}
			}
		});
	};
</script>
</html>

 

출력물

 

- search.jsp란에 row를 클릭하면 데이터를 가지고 insert.jsp로 가도록 하겠다.

	{{#each array}}
	<tr class="row">
		<td class="pcode">{{pcode}}</td>
		<td class="pname">{{pname}}</td>
		<td>{{dept}}</td>
		<td>{{title}}</td>
	</tr>
	{{/each}}

 

	$('#tbl').on('click', '.row', function() {
		var pcode = $(this).find('.pcode').html();
		var pname = $(this).find('.pname').html();
		$(opener.frm.pcode).val(pcode);
		$(opener.frm.pname).val(pname);
		window.close();
	});

 

- insert.jsp submit 후

- post 위한 StuVO

- create new StuVO.java

package model;

public class StuVO {
	private String scode;
	private String sname;
	private String dept;
	private int year;
	private String advisor;
	private String birthday;

	public String getScode() {
		return scode;
	}

	public void setScode(String scode) {
		this.scode = scode;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getDept() {
		return dept;
	}

	public void setDept(String dept) {
		this.dept = dept;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public String getAdvisor() {
		return advisor;
	}

	public void setAdvisor(String advisor) {
		this.advisor = advisor;
	}

	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	@Override
	public String toString() {
		return "StuVO [scode=" + scode + ", sname=" + sname + ", dept=" + dept + ", year=" + year + ", advisor="
				+ advisor + ", birthday=" + birthday + "]";
	}

}

 

- StuServlet.java 에서 받는다.

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		StuVO vo = new StuVO();
		vo.setScode(request.getParameter("scode"));
		vo.setSname(request.getParameter("sname"));
		vo.setDept(request.getParameter("dept"));
		vo.setYear(Integer.parseInt(request.getParameter("year")));
		vo.setAdvisor(request.getParameter("pcode"));
		String yy = request.getParameter("yy");
		String mm = request.getParameter("mm");
		String dd = request.getParameter("dd");
		vo.setBirthday(yy+"-"+mm+"-"+dd);
		
		System.out.println(vo.toString());
	}

 

데이터 확인

 

- StuDAO.java

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		StuVO vo = new StuVO();
		vo.setScode(request.getParameter("scode"));
		vo.setSname(request.getParameter("sname"));
		vo.setDept(request.getParameter("dept"));
		vo.setYear(Integer.parseInt(request.getParameter("year")));
		vo.setAdvisor(request.getParameter("pcode"));
		String yy = request.getParameter("yy");
		String mm = request.getParameter("mm");
		String dd = request.getParameter("dd");
		vo.setBirthday(yy+"-"+mm+"-"+dd);
		switch (request.getServletPath()) {
		case "/student/insert":
			dao.insert(vo);
			response.sendRedirect("/student/list");
			break;
		}
	}

 

학생목록에서 확인가능하다.

 

- 이제 강좌 insert를 해보도록 하겠다.

- insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<style>
form{
	width:400px;
	margin: 0px auto;
}
#check{
	margin-left: 10px;
	height: 25px;
}
</style>
<h1>강좌 등록</h1>
<form name="frm">
	<span>강좌번호 : </span><input name="lcode" value="0" size="30"/><button id="check">중복체크</button><hr/>
	<span>강좌이름 : </span><input name="lname" size="30" /><hr/>
	<span>강의시간 : </span><input name="hours" value="0" size="5" maxLength="1" oninput="numberMaxLength(this);"/><hr />
	<span>강의호수 : </span><input name="room" value="0" size="5"/><hr />
	<span>수강인원 : </span><input name="persons" value="0" size="5" /><hr />
	<span>최대인원 : </span><input name="capacity" value="0" size="5" /><hr />
	<span>담당교수 : </span>
	<input name="pcode" value="" size=5/>&nbsp;
	<input name="pname" value="" size=5 readonly />
	<hr />
	<div style="margin: 0px center; text-align: center;">
	<input type="submit" value="강좌등록" />
	<input type="reset" value="등록취소" />
	</div>
</form>
<script>
	var check = 0;

	$(frm.pcode).on('click',function(){
		window.open("/professor/search.jsp","advisor","width=500, height=350,top=200,left=900");
	})

	function numberMaxLength(e){
		if(e.value.length>e.maxLength){
			e.value=e.value.slice(0,maxLength);
			alert("강의 시간을 확인해주세요.")
		}
	}

	$(frm).on("submit",function(e){
		e.preventDefault();
		
		if(check==0){
			alert("강좌코드 중복체크 해주시기 바랍니다.");
			return;
		}
		var lname=$(frm.lname).val();
		if(lname==""){
			alert("강좌 이름을 입력하세요.")
			$(frm.lname).focus();
			return;
		}
		
		if(!confirm("신규등록 하시겠습니까?")) return;
		frm.action="/student/insert";
		frm.method="post";
		frm.submit();
	})
</script>

 

- CouDAO.java

	// 2. 강좌코드 중복체크 메서드
	public int check(String lcode){
		// 없으면 check는 0, 있으면 1
		int check=0;
		try {
			String sql = "select * from courses where lcode=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, lcode);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				check=1;
			}
		} catch (Exception e) {
			System.out.println("CouDAO check : "+e.toString());
		}
		return check;
	}

 

- create new CheckServlet.java

package controller;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.CouDAO;

@WebServlet("/check_lcode")
public class CheckServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		CouDAO cdao = new CouDAO();
		out.println(cdao.check(request.getParameter("lcode")));
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}

}

 

강좌코드가 A109인 것을 확인해보았다.

 

- course\insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<style>
form {
	width: 400px;
	margin: 0px auto;
}

#check {
	margin-left: 10px;
	height: 25px;
}
</style>
<h1>강좌 등록</h1>
<form name="frm">
	<span>강좌번호 : </span><input name="lcode" value="" size="30" /> <input
		type="button" id="check" value="중복체크">
	<hr />
	<span>강좌이름 : </span><input name="lname" size="30" />
	<hr />
	<span>강의시간 : </span><input name="hours" value="0" size="5"
		maxLength="1" oninput="numberMaxLength(this);" />
	<hr />
	<span>강의호수 : </span><input name="room" value="0" size="5" />
	<hr />
	<span>수강인원 : </span><input name="persons" value="0" size="5" />
	<hr />
	<span>최대인원 : </span><input name="capacity" value="0" size="5" />
	<hr />
	<span>담당교수 : </span> <input name="pcode" value="" size=5 />&nbsp; <input
		name="pname" value="" size=5 readonly />
	<hr />
	<div style="margin: 0px center; text-align: center;">
		<input type="submit" value="강좌등록" /> <input type="reset" value="등록취소" />
	</div>
</form>
<script>
	$(frm.pcode).on(
			'click',
			function() {
				window.open("/professor/search.jsp", "advisor",
						"width=500, height=350,top=200,left=900");
			})

	function numberMaxLength(e) {
		if (e.value.length > e.maxLength) {
			e.value = e.value.slice(0, maxLength);
		}
	}
	
	var check = 0;


	$(frm.lcode).on('change', function() {
		check = 0;
	})

	$("#check").on('click', function() {
		var lcode = $(frm.lcode).val();
		if(lcode==""){
			alert("강좌코드를 입력하세요.");
			$(frm.lcode).focus();
			return;
		}
		$.ajax({
			type : "get",
			url : "/check_lcode",
			data : {
				"lcode" : lcode
			},
			success : function(data) {
				if (data == 1) {
					alert("이미 등록된 강좌코드입니다.");
					check=2;
				} else {
					alert("사용해도 좋은 강좌코드입니다.");
					check=1;
				}
			}
		})
	})

	$(frm).on("submit", function(e) {
		e.preventDefault();

		var lcode = $(frm.lcode).val();
		
		if (lcode == "") {
			alert("강좌번호를 입력하세요.");
			$(frm.lcode).focus();
			return;
		}

		if (check == 0) {
			alert("강좌코드 중복체크 해주시기 바랍니다.");
			return;
		}

		var lname = $(frm.lname).val();
		if (lname == "") {
			alert("강좌 이름을 입력하세요.")
			$(frm.lname).focus();
			return;
		}

		if (!confirm("신규등록 하시겠습니까?"))
			return;
		frm.action = "/student/insert";
		frm.method = "post";
		frm.submit();
	})
</script>

 

출력물

 

- 객체를 클래스로 주고 받을 CouVO 생성

- create new CouVO.java

package model;

public class CouVO {
	private String lcode;
	private String lname;
	private int hours;
	private String room;
	private int capacity;
	private int persons;
	private String instructor;

	public String getLcode() {
		return lcode;
	}

	public void setLcode(String lcode) {
		this.lcode = lcode;
	}

	public String getLname() {
		return lname;
	}

	public void setLname(String lname) {
		this.lname = lname;
	}

	public int getHours() {
		return hours;
	}

	public void setHours(int hours) {
		this.hours = hours;
	}

	public String getRoom() {
		return room;
	}

	public void setRoom(String room) {
		this.room = room;
	}

	public int getCapacity() {
		return capacity;
	}

	public void setCapacity(int capacity) {
		this.capacity = capacity;
	}

	public int getPersons() {
		return persons;
	}

	public void setPersons(int persons) {
		this.persons = persons;
	}

	public String getInstructor() {
		return instructor;
	}

	public void setInstructor(String instructor) {
		this.instructor = instructor;
	}

	@Override
	public String toString() {
		return "CouVO [lcode=" + lcode + ", lname=" + lname + ", hours=" + hours + ", room=" + room + ", capacity="
				+ capacity + ", persons=" + persons + ", instructor=" + instructor + "]";
	}

}

 

- CouServlet.java

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		CouVO vo = new CouVO();
		vo.setLcode(request.getParameter("lcode"));
		vo.setLname(request.getParameter("lname"));
		vo.setHours(Integer.parseInt(request.getParameter("hours")));
		vo.setRoom(request.getParameter("room"));
		vo.setCapacity(Integer.parseInt(request.getParameter("capacity")));
		vo.setPersons(Integer.parseInt(request.getParameter("persons")));
		vo.setInstructor(request.getParameter("pcode"));
		
		System.out.println(vo.toString());
	}

 

콘솔에서 확인가능하다.

 

- 강좌등록을 위한 메서드를 생성하겠다.

- CouDAO.java

	// 3. 강좌 등록 메서드
	public void insert(CouVO vo) {
		try {
			String sql = "insert into courses(lcode,lname,room,capacity,instructor,hours) values(?,?,?,?,?,?)";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getLcode());
			ps.setString(2, vo.getLname());
			ps.setString(3, vo.getRoom());
			ps.setInt(4, vo.getCapacity());
			ps.setString(5, vo.getInstructor());
			ps.setInt(6, vo.getHours());
			ps.execute();
		} catch (Exception e) {
			System.out.println("CouVO insert : " + e.toString());
		}
	}

 

- CouServlet.java

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		CouVO vo = new CouVO();
		vo.setLcode(request.getParameter("lcode"));
		vo.setLname(request.getParameter("lname"));
		vo.setHours(Integer.parseInt(request.getParameter("hours")));
		vo.setRoom(request.getParameter("room"));
		vo.setCapacity(Integer.parseInt(request.getParameter("capacity")));
		vo.setPersons(Integer.parseInt(request.getParameter("persons")));
		vo.setInstructor(request.getParameter("pcode"));
		
		switch (request.getServletPath()) {
		case "/course/insert":
			dao.insert(vo);
			response.sendRedirect("/course/list");
			break;
		}
	}

 

입력된 데이터를 확인할 수 있다.

 

- DB에 학생목록에 있는 학생들이 듣고 있는 강좌의 수강인원을 기준으로 강좌 수강인원을 변경해주도록 하겠다.

- MySQL

-- 방법 1
update courses set persons=2 where lcode='A109';
UPDATE COURSES SET PERSONS=6 WHERE LCODE='C301';
UPDATE COURSES SET PERSONS=3 WHERE LCODE='C312';
UPDATE COURSES SET PERSONS=5 WHERE LCODE='C401';
UPDATE COURSES SET PERSONS=4 WHERE LCODE='C421';
UPDATE COURSES SET PERSONS=4 WHERE LCODE='E221';

-- 방법 2
update courses
set persons=(select count(*) from enrollments where courses.lcode=enrollments.lcode)
where lcode>='';

 

persons가 변경되었다.

 

- 교수정보를 클릭하여 교수 정보, 담당학생, 담당강좌를 출력하는 페이지로 만들겠다.

- create new professor\read.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<h1>교수정보</h1>

 

- ProServlet.java

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		switch (request.getServletPath()) {
		case "/professor/read":
			RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
			request.setAttribute("pageName", "/professor/read.jsp");
			dis.forward(request, response);
			break;

 

- list.jsp

{{#each array}}
<tr class="row">
	<td>{{pcode}}</td>
	<td>{{pname}}</td>
	<td>{{dept}}</td>
	<td>{{title}}</td>
	<td>{{hiredate}}</td>
	<td>{{salary}} 원</td>
	<td><button onClick="location.href='/professor/read?pcode={{pcode}}'">교수정보</button></td>
</tr>
{{/each}}

 

- 교수 정보를 읽어오는 메서드

- read.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;
}
</style>
<h1>교수정보</h1>
<form name="frm">
	<table>
		<tr>
			<td class="title" width=100>교수번호</td>
			<td width=100><input name="pcode" value="${vo.pcode}" size=3 /></td>
			<td class="title" width=100>교수학과</td>
			<td width=100><select name="dept">
					<option value="전산">전산학과</option>
					<option value="전자">전자학과</option>
					<option value="건축">건축학과</option>
			</select></td>
			<td class="title" width=100>임용일자</td>
			<td width=250>
			<input name="yy" size=1 />&nbsp;년 
			<input name="mm" size=1 />&nbsp;월 
			<input name="dd" size=1 />&nbsp;일
			</td>
		</tr>
		<tr>
			<td class="title" width=100>교수이름</td>
			<td width=100><input name="pname" value="${vo.pname}" size=5 /></td>
			<td class="title" width=100>교수급여</td>
			<td width=100><input name="salary" value="${vo.salary}" size=5 />&nbsp;원</td>
			<td class="title" width=100>교수직급</td>
			<td width=250>
			<input type="radio" name="title">정교수 
			<input type="radio" name="title">부교수 
			<input type="radio" name="title">조교수
			</td>
		</tr>
	</table>
</form>

 

https://becomefullstackdev.tistory.com/64

 

2021.08.18 수업일지(Spring, Servlet)

- spring, servlet 을 이용한 웹페이지 만들기. 1. dynamic web project 2. apache tomcat target runtime check 3. make a view on WebContent 4. make a controller on java resources src https://becomefullst..

becomefullstackdev.tistory.com

 

- 테그에서 반복문을 사용할 수 있게 JSTL을 넣어준다. JSTL(JSP Standard Tag Library)는 여러 프로그램이 공통으로 사용하는 코드를 모아놓은 코드의 집합의 약어이다. 자신만의 태그를 추가할 수 있는 기능을 제공한다. 주로 JSTL의 Core에서 c를 사용한다.

 

jstl.jar
0.02MB
standard.jar
0.38MB

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="f" uri="http://java.sun.com/jsp/jstl/functions"%>

 

- 삼항연산자를 이용해 전산, 전자, 건축을 선택하는 core tag를 적용하겠다.

			<td class="title" width=100>교수학과</td>
			<td width=100><select name="dept">
					<option value="전산" <c:out value="${vo.dept=='전산'?'selected':''}"/> >전산학과</option>
					<option value="전자" <c:out value="${vo.dept=='전자'?'selected':''}"/> >전자학과</option>
					<option value="건축" <c:out value="${vo.dept=='건축'?'selected':''}"/> >건축학과</option>
			</select></td>

 

- 문자를 추출하는 substring을 이용하여 hiredate에서 년, 월, 일을 추출하겠다.

			<td class="title" width=100>임용일자</td>
			<td width=250>
			<input name="yy" size=1 value="${f:substring(vo.hiredate,0,4)}"/>&nbsp;년 
			<input name="mm" size=1 value="${f:substring(vo.hiredate,5,7)}"/>&nbsp;월 
			<input name="dd" size=1 value="${f:substring(vo.hiredate,8,10)}"/>&nbsp;일
			</td>

 

- 삼항연산자를 이용해 정교수, 부교수, 조교수의 radio box checked를 해주겠다.

			<td class="title" width=100>교수직급</td>
			<td width=250>
			<input type="radio" name="title" <c:out value="${vo.title=='정교수'?'checked':'' }" />>정교수 
			<input type="radio" name="title" <c:out value="${vo.title=='부교수'?'checked':'' }" />>부교수 
			<input type="radio" name="title" <c:out value="${vo.title=='조교수'?'checked':'' }" />>조교수
			</td>

 

교수학과, 임용일자, 교수직급이 위와 같이 나왔다.

 

+ 추가

	<div style="text-align: center; margin-top:10px;">
		<input type="submit" value="정보수정" />
		<input type="reset" value="수정취소" />
	</div>
</form>

<script>
	$(frm).on('submit',function(e){
		e.preventDefault();
		if(!confirm("수정하시겠습니까?"))return;
		
		frm.action="/professor/update";
		frm.method="post";
		frm.submit();
		
	});
</script>

 

- 교수정보를 수정하는 메서드를 만들겠다.

- ProDAO.java

	// 5. 교수정보 수정 메서드
	public void update(ProVO vo){
		try {
			String sql = "update professors set pname=?, dept=?, hiredate=?, salary=?, title=? where pcode=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getPname());
			ps.setString(2, vo.getDept());
			ps.setString(3, vo.getHiredate());
			ps.setInt(4, vo.getSalary());
			ps.setString(5, vo.getTitle());
			ps.setString(6, vo.getPcode());
			ps.execute();
		} catch (Exception e) {
			System.out.println("ProVO update : "+e.toString());
		}
	}

 

- ProServlet.java

		switch (request.getServletPath()) {
		case "/professor/insert":
			pdao.insert(vo);
			break;
		case "/professor/update":
			pdao.update(vo);
			break;
		}
		response.sendRedirect("/professor/list");

 

513 교수의 이름과 임용일자 급여를 수정하였다.

 

DB에서도 확인할 수 있다.

 

- 학생 정보를 불러오는 메서드를 만들고 받아온 정보를 학생 정보 페이지에 넣는 작업을 하겠다.

- 우선 담당 교수 이름도 불러와줘야하기 때문에 StuVO 가 ProVO 를 상속하도록 하겠다.

- StuVO.java

public class StuVO extends ProVO {

 

- StuDAO.java

	// 4. 학생 정보 불러오는 메서드
	public StuVO read(String scode){
		StuVO vo = new StuVO();
		try {
			String sql = "select * from view_stu where scode=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, scode);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				vo.setScode(rs.getString("scode"));
				vo.setSname(rs.getString("sname"));
				vo.setDept(rs.getString("dept"));
				vo.setYear(rs.getInt("year"));
				vo.setBirthday(rs.getString("birthday"));
				vo.setAdvisor(rs.getString("advisor"));
				vo.setPname(rs.getString("pname"));
			}
		} catch (Exception e) {
			System.out.println("StuDAO read : "+e.toString());
		}
		return vo;
	}

 

- StuServlet.java

@WebServlet(value = { "/student/list", "/student/insert","/student/read" })
public class StuServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	StuDAO dao = new StuDAO();
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		switch (request.getServletPath()) {
		case "/student/read":
			RequestDispatcher dis = request.getRequestDispatcher("/main.jsp");
			request.setAttribute("pageName", "/student/read.jsp");
			request.setAttribute("vo", dao.read(request.getParameter("scode")));
			dis.forward(request, response);
			break;

 

- create new srudent\read.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="f" uri="http://java.sun.com/jsp/jstl/functions"%>
<style>
.title {
	background: rgb(53, 120, 196);
	color: white;
	text-align: center;
}
</style>
<h1>학생정보</h1>
<form name="frm">
	<table>
		<tr>
			<td class="title" width=100>학생번호</td>
			<td width=100><input name="scode" value="${vo.scode}" size=10
				readonly /></td>
			<td class="title" width=100>학생학과</td>
			<td width=100><select name="dept">
					<option value="전산" <c:out value="${vo.dept=='전산'?'selected':''}"/>>전산학과</option>
					<option value="전자" <c:out value="${vo.dept=='전자'?'selected':''}"/>>전자학과</option>
					<option value="건축" <c:out value="${vo.dept=='건축'?'selected':''}"/>>건축학과</option>
			</select></td>
		</tr>
		<tr>
			<td class="title" width=100>학생이름</td>
			<td width=100><input name="sname" value="${vo.sname}" size=10 /></td>
			<td class="title" width=100>생년월일</td>
			<td width=250><input name="yy" size=1
				value="${f:substring(vo.birthday,0,4)}" />&nbsp;년 <input name="mm"
				size=1 value="${f:substring(vo.birthday,5,7)}" />&nbsp;월 <input
				name="dd" size=1 value="${f:substring(vo.birthday,8,10)}" />&nbsp;일
			</td>
		</tr>
		<tr>
			<td class="title" width=100>학생학년</td>
			<td width=250><input type="radio" name="year" value="1"
				<c:out value="${vo.year=='1'?'checked':'' }" />>1학년 <input
				type="radio" name="year" value="2"
				<c:out value="${vo.year=='2'?'checked':'' }" />>2학년 <input
				type="radio" name="year" value="3"
				<c:out value="${vo.year=='3'?'checked':'' }" />>3학년 <input
				type="radio" name="year" value="4"
				<c:out value="${vo.year=='4'?'checked':'' }" />>4학년</td>
			<td class="title" width=100>지도교수</td>
			<td width=100><input name="pcode" value="${vo.advisor}" size=5 />&nbsp;
				<input name="pname" value="${vo.pname}" size=5 /></td>
		</tr>
	</table>
	<div style="text-align: center; margin-top: 10px;">
		<input type="submit" value="정보수정" /> <input type="reset" value="수정취소" />
	</div>
</form>

<script>
	$(frm.pcode).on(
			'click',
			function() {
				window.open("/professor/search.jsp", "advisor",
						"width=500, height=350,top=200,left=900");
			})

	$(frm).on('submit', function(e) {
		e.preventDefault();
		if (!confirm("수정하시겠습니까?"))
			return;

		frm.action = "/student/update";
		frm.method = "post";
		frm.submit();

	});
</script>

 

학생정보 페이지가 위와 같이 출력되었다.

 

- 이제 학생정보를 수정하는 메서드를 만들고 실제로 수정해보는 작업을 하겠다.

- StuDAO.java

	// 5. 학생 정보 수정하는 메서드
	public void update(StuVO vo){
		try {
			String sql = "update students set sname=?, dept=?, birthday=?, year=?, advisor=? where scode=?";
			PreparedStatement ps = Database.CON.prepareStatement(sql);
			ps.setString(1, vo.getSname());
			ps.setString(2, vo.getDept());
			ps.setString(3, vo.getBirthday());
			ps.setInt(4, vo.getYear());
			ps.setString(5, vo.getAdvisor());
			ps.setString(6, vo.getScode());
			ps.execute();
		} catch (Exception e) {
			System.out.println("StuDAO update : "+e.toString());
		}
	}

 

- StuServlet.java

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		StuVO vo = new StuVO();
		vo.setScode(request.getParameter("scode"));
		vo.setSname(request.getParameter("sname"));
		vo.setDept(request.getParameter("dept"));
		vo.setYear(Integer.parseInt(request.getParameter("year")));
		vo.setAdvisor(request.getParameter("pcode"));
		String yy = request.getParameter("yy");
		String mm = request.getParameter("mm");
		String dd = request.getParameter("dd");
		vo.setBirthday(yy+"-"+mm+"-"+dd);
		switch (request.getServletPath()) {
		case "/student/insert":
			dao.insert(vo);
			response.sendRedirect("/student/list");
			break;
		case "/student/update":
			dao.update(vo);
			response.sendRedirect("/student/list");
			break;
		}
	}

 

수정작업 gif

 

haksa.zip
1.07MB

저작자표시

'ICIA 수업일지' 카테고리의 다른 글

2021.09.02 수업일지(Mysql, Servlet, pl/sql)  (0) 2021.09.02
2021.09.01 수업일지(Servlet, Mysql, pl/sql, Template)  (0) 2021.09.01
2021.08.30 수업일지(Servlet, Spring, Mysql)  (0) 2021.08.30
2021.08.27 수업일지(Servlet, Spring, Mysql, Selenium)  (0) 2021.08.27
2021.08.26 수업일지(Servlet, Spring, Mysql)  (0) 2021.08.26

관련글

티스토리툴바