Notice
Recent Posts
Recent Comments
Link
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
Tags
- jsp
- ciscopacket
- w3school
- NCS
- php
- 네트워크
- jsp연결
- ospf
- 리눅스
- 참조타입
- javaee
- 원형그래프
- rinux
- 이것이 자바다
- 정처기필기
- 라우터
- cisco packet
- 정보처리기사
- Cisco
- Java
- 자바
- 버추얼머신
- html
- autoset
- 네트워크관리사
- 오라클
- sql
- VLAN
- Oracle
- 데이터베이스
Archives
- Today
- Total
기록해! 정리해!
JSP - 학사관리프로그램 본문
1. 구조
2. ExamtblDao
package DBPKG.dao;
import java.util.List;
import DBPKG.ExamtblVo;
public interface ExamtblDao {
void insert(ExamtblVo vo);
void update(ExamtblVo vo);
void delete(String sno);
List<ExamtblVo> selectAll();
ExamtblVo edit(String sno);
}3. DaoImpl 쿼리문 (insert, update, delete, select, edit)
+ 데이터베이스 커넥션 , try-catch
package DBPKG.dao;
import java.sql.*;
import java.util.*;
import DBPKG.*;
public class ExamtblDaoImpl implements ExamtblDao{
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;
private static final String INSERT_SQL = "insert into Examtbl"
+ "(sno,sname,kor,eng,math,hist) values (?,?,?,?,?,?)";
private static final String UPDATE_SQL = "update Examtbl set "
+ "sname=?, kor=?, eng=?, math=?, hist=? where sno=?";
private static final String DELETE_SQL = "delete from Examtbl where sno=? ";
private static final String SELECT_SQL = "select * from Examtbl ";
private static final String EDIT_SQL = "select * from Examtbl where sno=? ";
@Override
public void insert(ExamtblVo vo) {
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(INSERT_SQL);
pstmt.setString(1, vo.getSno());
pstmt.setString(2, vo.getSname());
pstmt.setInt(3, vo.getKor());
pstmt.setInt(4, vo.getEng());
pstmt.setInt(5, vo.getMath());
pstmt.setInt(6, vo.getHist());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void update(ExamtblVo vo) {
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(UPDATE_SQL);
pstmt.setString(1, vo.getSname());
pstmt.setInt(2, vo.getKor());
pstmt.setInt(3, vo.getEng());
pstmt.setInt(4, vo.getMath());
pstmt.setInt(5, vo.getHist());
pstmt.setString(6, vo.getSno());
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void delete(String sno) {
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(DELETE_SQL);
pstmt.setString(1, sno);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
@Override
public List<ExamtblVo> selectAll() {
List<ExamtblVo> li = null;
try {
li = new ArrayList<ExamtblVo>();
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(SELECT_SQL);
rs = pstmt.executeQuery();
ExamtblVo m =null;
while(rs.next()) {
m = new ExamtblVo();
m.setSno(rs.getString("sno"));
m.setSname(rs.getString("sname"));
m.setKor(rs.getInt("kor"));
m.setEng(rs.getInt("eng"));
m.setMath(rs.getInt("math"));
m.setHist(rs.getInt("hist"));
li.add(m);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
return li;
}
@Override
public ExamtblVo edit(String sno) {
ExamtblVo m =null;
try {
conn = DBConnection.getConnection();
pstmt = conn.prepareStatement(EDIT_SQL);
pstmt.setString(1, sno);
rs = pstmt.executeQuery();
rs.next();
m = new ExamtblVo();
m.setSno(rs.getString("sno"));
m.setSname(rs.getString("sname"));
m.setKor(rs.getInt("kor"));
m.setEng(rs.getInt("eng"));
m.setMath(rs.getInt("math"));
m.setHist(rs.getInt("hist"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
return m;
}
}4.form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>정보처리산업기사 기본 페이지 연습</title>
<style>
header { background-color:#3333ff; height:90px; line-height:90px; font-size:30px; text-align:center; }
nav { background-color:#ccbbcc; height:50px; line-height:50px; }
section{ background-color:#cccccc; min-height:470px; }
footer { background-color:#77ddbb; height:50px; text-align:center; line-height:50px;}
tr{text-align:center;}
</style>
</head>
<body>
<header>(과정평가형 정보처리기능사) 학사관리 프로그램</header>
<nav> <a href=form.jsp>성적입력</a>
 <a href=ListController>성적조회</a>
 <a href=index.jsp>처음으로</a> </nav>
<script>
function ck1(){
if(f1.sno.value==""){
alert("학번이 입력되지않았습니다");
f1.sno.focus();
return false;
}else if(f1.sname.value.length<=1){
alert("이름은 2글자 이상입력하세요");
f1.sname.focus();
return false;
}else if(f1.kor.value==""){
alert("국어점수를 입력하세요");
f1.kor.focus();
return false;
}else if(f1.eng.value==""){
alert("영여점수를 입력하세요");
f1.eng.focus();
return false;
}else if(f1.math.value==""){
alert("수학점수를 입력하세요");
f1.math.focus();
return false;
}else if(f1.hist.value==""){
alert("역사점수를 입력하세요");
f1.hist.focus();
return false;
}
}
</script>
<section>
<br>
<div align=center>
<h2><b>학생 성적 입력</b></h2>
<form name=f1 onSubmit="return ck1()" action=FormController>
<table border=1 width=300 height=280>
<tr><td width=80>학번</td><td><input type=text name=sno></td></tr>
<tr><td>성명</td><td><input type=text name=sname></td></tr>
<tr><td>국어</td><td><input type=text name=kor></td></tr>
<tr><td>영어</td><td><input type=text name=eng></td></tr>
<tr><td>수학</td><td><input type=text name=math></td></tr>
<tr><td>역사</td><td><input type=text name=hist></td></tr>
<tr><td colspan=2 align=center ><input type=submit value="성적저장"></td></tr>
</table>
</form>
</div>
</section>
<footer>
HRDKOREA Copyrightⓒ2018 ALL rights resources Development Service of Korea
</footer>
</body>
</html>
5. FormController 해서 성적 저장하면 오라클에 저장되고 인덱스로 넘어가기
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
int kor = Integer.parseInt(request.getParameter("kor"));
int eng = Integer.parseInt(request.getParameter("eng"));
int math = Integer.parseInt(request.getParameter("math"));
int hist = Integer.parseInt(request.getParameter("hist"));
ExamtblVo m = new ExamtblVo();
m.setSno(sno);
m.setSname(sname);
m.setKor(kor);
m.setEng(eng);
m.setMath(math);
m.setHist(hist);
ExamtblService service = new ExamtblServiceImpl();
service.insert(m);
RequestDispatcher dispatcher = request.getRequestDispatcher("index.jsp");
dispatcher.forward(request, response);
}6. ListController
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
ExamtblService examtblservice = new ExamtblServiceImpl();
List<ExamtblVo> li = examtblservice.selectAll();
request.setAttribute("li", li);
RequestDispatcher dispatcher=request.getRequestDispatcher("list.jsp");
dispatcher.forward(request, response);
}- List.jsp
- 학번 쪼개기
<td>반</td><td>번호</td>
<td><%=sno.substring(0, 1)%></td>
<td><%=sno.substring(1, 3)%></td>
<td><%=sno.substring(3, 5)%></td>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="DBPKG.*" %>
<%
List<ExamtblVo> li =(List<ExamtblVo>) request.getAttribute("li");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>정보처리산업기사 기본 페이지 연습</title>
<style>
header { background-color:#3333ff; height:90px; line-height:90px; font-size:30px; text-align:center; }
nav { background-color:#ccbbcc; height:50px; line-height:50px; }
section{ background-color:#cccccc; min-height:470px; }
footer { background-color:#77ddbb; height:50px; text-align:center; line-height:50px;}
</style>
</head>
<body>
<header>(과정평가형 정보처리기능사) 학사관리 프로그램</header>
<nav>
  <a href= form.jsp>성적입력</a>
 <a href=ListController>성적조회</a>
 <a href=index.jsp>처음으로</a>
</nav>
<section>
<br>
<div align=center><b>성적 조회 목록</b>
<table border=1 width=500>
<tr>
<th>학년</th><th>반</th><th>번호</th>
<th>이름</th><th>국어</th><th>영어</th><th>수학</th><th>역사</th><th>합계</th><th>평균</th>
</tr>
<%
int totalkor=0; int totaleng=0; int totalmath=0; int totalhist=0; int totalsum=0; double totalavg=0.0;
double Atotalkor=0; double Atotaleng=0; double Atotalmath=0; double Atotalhist=0; double Atotalsum=0; double Atotalavg=0.0;
int k=0;
for(int i=0; i<li.size(); i++){
k++;
ExamtblVo m=li.get(i);
String sno= m.getSno();
String sname= m.getSname();
int kor= m.getKor();
int eng= m.getEng();
int math= m.getMath();
int hist= m.getHist();
int sum=m.getKor()+m.getEng()+m.getMath()+m.getHist();
double avg= sum/4.0 ;
totalkor= totalkor+kor;
totaleng= totaleng+eng;
totalmath= totalmath+math;
totalhist= totalhist+hist;
totalsum= totalsum+sum;
totalavg= totalavg+avg;
Atotalkor=(double)totalkor/k;
Atotaleng= (double)totaleng/k;
Atotalmath= (double)totalmath/k;
Atotalhist= (double)totalhist/k;
Atotalsum= (double)totalsum/k;
Atotalavg= (double)totalavg/k;
%>
<tr>
<td><%=sno.substring(0, 1) %></td>
<td><%=sno.substring(1, 3) %></td>
<td><%=sno.substring(3, 5) %></td>
<td><%=sname%></td>
<td><%=kor %></td>
<td><%=eng %></td>
<td><%=math %></td>
<td><%=hist %></td>
<td><%=sum %></td>
<td><%=avg %></td>
</tr>
<%} %>
<tr>
<td colspan=4 align=center><b>총점</b></td>
<td><%=totalkor %></td>
<td><%=totaleng %></td>
<td><%=totalmath %></td>
<td><%=totalhist %></td>
<td><%=totalsum %></td>
<td><%=totalavg %></td>
</tr>
<tr>
<td colspan=4 align=center><b>총 평균</b></td>
<td><%=Math.round(Atotalkor*100)/100.0 %></td>
<td><%=Math.round(Atotaleng*100)/100.0 %></td>
<td><%=Math.round(Atotalmath*100)/100.0 %></td>
<td><%=Math.round(Atotalhist*100)/100.0 %></td>
<td><%=Math.round(Atotalsum*100)/100.0 %></td>
<td><%=Math.round(Atotalavg*100)/100.0 %></td>
</tr>
</table>
</div>
</section>
<footer>
HRDKOREA Copyrightⓒ2018 ALL rights resources Development Service of Korea
</footer>
</body>
</html>
7. EditController
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
String sno = request.getParameter("sno");
ExamtblService examtblservice = new ExamtblServiceImpl();
ExamtblVo m = examtblservice.edit(sno);
request.setAttribute("m", m);
RequestDispatcher dispatcher=request.getRequestDispatcher("edit.jsp");
dispatcher.forward(request, response);
}<script>
function ck1(){
if(f1.sno.value==""){
alert("학번이 입력되지않았습니다");
f1.sno.focus();
return false;
}else if(f1.sname.value.length<=1){
alert("이름은 2글자 이상입력하세요");
f1.sname.focus();
return false;
}else if(f1.kor.value==""){
alert("국어점수를 입력하세요");
f1.kor.focus();
return false;
}else if(f1.eng.value==""){
alert("영여점수를 입력하세요");
f1.eng.focus();
return false;
}else if(f1.math.value==""){
alert("수학점수를 입력하세요");
f1.math.focus();
return false;
}else if(f1.hist.value==""){
alert("역사점수를 입력하세요");
f1.hist.focus();
return false;
}else{
alert("성적 입력이 완료되었습니다");
}
}
</script>
<%
ExamtblVo m= (ExamtblVo) request.getAttribute("m");
String sno = m.getSno();
String sname = m.getSname();
int kor = m.getKor();
int eng = m.getEng();
int math = m.getMath();
int hist = m.getHist();
%>
<section>
<br>
<div align=center>
<h2><b>학생 성적 확인 및 수정</b></h2>
<form name=f1 onSubmit="return ck1()" action=UpdateController>
<table border=1 width=300 height=280>
<tr><td width=80>학번</td><td><input type=text name=sno value=<%=sno%>></td></tr>
<tr><td>성명</td><td><input type=text name=sname value=<%=sname%>></td></tr>
<tr><td>국어</td><td><input type=text name=kor value=<%=kor%>></td></tr>
<tr><td>영어</td><td><input type=text name=eng value=<%=eng%>></td></tr>
<tr><td>수학</td><td><input type=text name=math value=<%=math%>></td></tr>
<tr><td>역사</td><td><input type=text name=hist value=<%=hist%>></td></tr>
<tr><td colspan=2 align=center ><input type=submit value="성적수정"></td></tr>
</table>
</form>
8. UpdateController
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
int kor = Integer.parseInt(request.getParameter("kor"));
int eng = Integer.parseInt(request.getParameter("eng"));
int math = Integer.parseInt(request.getParameter("math"));
int hist = Integer.parseInt(request.getParameter("hist"));
ExamtblVo m = new ExamtblVo();
m.setSno(sno);
m.setSname(sname);
m.setKor(kor);
m.setEng(eng);
m.setMath(math);
m.setHist(hist);
ExamtblService service = new ExamtblServiceImpl();
service.update(m);
RequestDispatcher dispatcher = request.getRequestDispatcher("ListController");
dispatcher.forward(request, response);
}9. DeleteController
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
String sno = request.getParameter("sno");
ExamtblService service = new ExamtblServiceImpl();
service.delete(sno);
RequestDispatcher dispatcher = request.getRequestDispatcher("ListController");
dispatcher.forward(request, response);
}10. Totalcontroller
컨트롤러 다 합치기
package DBPKG.controller;
import java.io.IOException;
import java.util.List;
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;
import DBPKG.ExamtblVo;
import DBPKG.service.ExamtblService;
import DBPKG.service.ExamtblServiceImpl;
/**
* Servlet implementation class TotalController
*/
@WebServlet("/TotalController")
public class TotalController extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public TotalController() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
String sw = request.getParameter("sw");
ExamtblVo m = new ExamtblVo();
ExamtblService service = new ExamtblServiceImpl();
String str = "";
if(sw.equals("select")) {
List<ExamtblVo> li = service.selectAll();
request.setAttribute("li", li);
str = "list.jsp";
}else if(sw.equals("insert")) {
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
int kor = Integer.parseInt(request.getParameter("kor"));
int eng = Integer.parseInt(request.getParameter("eng"));
int math = Integer.parseInt(request.getParameter("math"));
int hist = Integer.parseInt(request.getParameter("hist"));
m.setSno(sno);
m.setSname(sname);
m.setKor(kor);
m.setEng(eng);
m.setMath(math);
m.setHist(hist);
service.insert(m);
str="TotalController?sw=select";
}else if(sw.equals("update")) {
String sno = request.getParameter("sno");
String sname = request.getParameter("sname");
int kor = Integer.parseInt(request.getParameter("kor"));
int eng = Integer.parseInt(request.getParameter("eng"));
int math = Integer.parseInt(request.getParameter("math"));
int hist = Integer.parseInt(request.getParameter("hist"));
m.setSno(sno);
m.setSname(sname);
m.setKor(kor);
m.setEng(eng);
m.setMath(math);
m.setHist(hist);
service.update(m);
str="TotalController?sw=select";
}else if(sw.equals("delete")) {
String sno = request.getParameter("sno");
service.delete(sno);
str="TotalController?sw=select";
}else if(sw.equals("edit")) {
String sno = request.getParameter("sno");
m = service.edit(sno);
request.setAttribute("m", m);
str = "edit.jsp";
}
RequestDispatcher dispatcher = request.getRequestDispatcher(str);
dispatcher.forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}각 jsp 마다 링크 바꿔주기
 <a href=TotalController?sw=select>성적조회</a>
- edit.jsp
<form name=f1 onSubmit="return ck1()" action=TotalController>
<input type=hidden name=sw value=update>
<table border=1 width=300 height=280>: form 안에는 하나밖에 못불러오기때문에 update는 hidden으로 불러줘야한다
-form.jsp
<form name=f1 onSubmit="return ck1()" action=TotalController>
<table border=1 width=300 height=280>
<input type=hidden name=sw value=insert>-list.jsp 이름, 평균에 링크건것도 바꿔주기
<td><a href=TotalController?sw=edit&sno=<%=sno%>><%=sname%></a></td>
<td><a href=TotalController?sw=delete&sno=<%=sno%>><%=avg %></td>
'JAVA > JSP' 카테고리의 다른 글
| JSP - HRD 싱글톤 (0) | 2022.07.22 |
|---|---|
| JSP - 학사관리프로그램(2) 학번/include/서치(검색창)/조회수 (0) | 2022.07.21 |
| 서블릿(3) (0) | 2022.07.19 |
| 서블릿(2) (0) | 2022.07.19 |
| 서블릿 (1) (0) | 2022.07.19 |
'JAVA/JSP' Related Articles
more
Comments