본문 바로가기
STUDY/JSP

Jsp 7일차 - Score 추가작성

by Anne of Green Galbes 2019. 2. 20.

① scoreDAO 추가 작성

package com.score;


import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

public class ScoreDAO {

private Connection conn;

//의존성 주입

public ScoreDAO(Connection conn){

this.conn = conn;

}

//입력

public int insertData(ScoreDTO dto){

int result = 0;

PreparedStatement pstmt = null;

String sql;

try {

sql = "insert into score (hak,name,kor,eng,mat) ";

sql+= "values (?,?,?,?,?)";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, dto.getHak());

pstmt.setString(2, dto.getName());

pstmt.setInt(3, dto.getKor());

pstmt.setInt(4, dto.getEng());

pstmt.setInt(5, dto.getEng());

result = pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

//데이터 가져오기

public List<ScoreDTO> getList(){

List<ScoreDTO> lists = new ArrayList<ScoreDTO>();

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql;

try {

sql = "select hak,name,kor,eng,mat,(kor+eng+mat) tot, (kor+eng+mat)/3 ave,";

sql+= "rank() over(order by (kor+eng+mat) desc) rank ";

sql+= "from score";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while(rs.next()){

ScoreDTO dto = new ScoreDTO();

dto.setHak(rs.getString("hak"));

dto.setName(rs.getString("name"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

dto.setTot(rs.getInt("tot"));

dto.setAve(rs.getInt("ave"));

dto.setRank(rs.getInt("rank"));

lists.add(dto);

}

pstmt.close();

rs.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return lists;

}

//학번 조희

public ScoreDTO getReadData(String hak){

ScoreDTO dto = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql;

try {

sql = "select hak,name,kor,eng,mat from score ";

sql+= "where hak=?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, hak);

rs = pstmt.executeQuery();

if(rs.next()){

dto = new ScoreDTO();

dto.setHak(rs.getString("hak"));

dto.setName(rs.getString("name"));

dto.setKor(rs.getInt("kor"));

dto.setEng(rs.getInt("eng"));

dto.setMat(rs.getInt("mat"));

}

rs.close();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return dto;

}

//데이터 수정

public int updateData(ScoreDTO dto){

int result=0;

PreparedStatement pstmt = null;

String sql;

try {

sql = "update score set kor=?,eng=?,mat=? ";

sql+= "where hak=?";

pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, dto.getKor());

pstmt.setInt(2, dto.getEng());

pstmt.setInt(3, dto.getMat());

pstmt.setString(4, dto.getHak());

result = pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

//데이터 삭제

public int deleteData(String hak){

int result = 0;

PreparedStatement pstmt = null;

String sql;

try {

sql = "delete score where hak=?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, hak);

result = pstmt.executeUpdate();

pstmt.close();

} catch (Exception e) {

System.out.println(e.toString());

}

return result;

}

}


② list.jsp 수정

<%@page import="com.score.ScoreDTO"%>

<%@page import="java.util.List"%>

<%@page import="com.score.ScoreDAO"%>

<%@page import="com.util.DBConn"%>

<%@page import="java.sql.Connection"%>

<%@ page contentType="text/html; charset=UTF-8"%>

<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

Connection conn = DBConn.getConnection();

ScoreDAO dao = new ScoreDAO(conn);

List<ScoreDTO> lists = dao.getList();

DBConn.close();

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>성적처리 리스트</title>

<style type="text/css">

*{

padding: 0px;

margin: 0px;

}

body {

font-size: 10pt;

}

td{

font-size: 10pt;

}

.btn {

font-size: 10pt;

background: #e6e6e6;

}

</style>

</head>


<body>


</br></br>


<table width="700" cellpadding="0" cellspacing="3" align="center" bgcolor="#e4e4e4">

<tr height="50">

<td bgcolor="#ffffff" style="padding-left: 10px">

<b>성적처리 리스트 화면</b>

</td>

</tr>

</table>


</br>


<table width="650" cellpadding="0" cellspacing="3" align="center">

<tr height="35">

<td align="right">

<input type="button" class="btn" value="글쓰기"

onclick="javascript:location.href='<%=cp%>/score/write.jsp';">

</td>

</tr>

</table>


<table width="650" cellpadding="0" cellspacing="1" align="center" bgcolor="#cccccc">

<tr height="30">

<td align="center" bgcolor="#e6e6e6" width="80">학번</td>

<td align="center" bgcolor="#e6e6e6" width="80">이름</td>

<td align="center" bgcolor="#e6e6e6" width="60">국어</td>

<td align="center" bgcolor="#e6e6e6" width="60">영어</td>

<td align="center" bgcolor="#e6e6e6" width="60">수학</td>

<td align="center" bgcolor="#e6e6e6" width="60">총점</td>

<td align="center" bgcolor="#e6e6e6" width="60">평균</td>

<td align="center" bgcolor="#e6e6e6" width="60">석차</td>

<td align="center" bgcolor="#e6e6e6" width="130">수정</td>

</tr>

<%for(ScoreDTO dto : lists) {%>

<tr height="30">

<td align="center" bgcolor="#ffffff"><%=dto.getHak()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getName()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getKor()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getEng()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getMat()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getTot()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getAve()%></td>

<td align="center" bgcolor="#ffffff"><%=dto.getRank()%></td>

<td align="center" bgcolor="#ffffff">

<a href="update.jsp?hak=<%=dto.getHak()%>" style="text-decoration: none">[수정] </a>/

<a href="delete_ok.jsp?hak=<%=dto.getHak()%>" style="text-decoration: none">[삭제]</a>

</td>

<%} %>

</tr>

</table>


</body>


</html>



③ update.jsp 작성

<%@page import="com.score.ScoreDTO"%>

<%@page import="com.score.ScoreDAO"%>

<%@page import="com.util.DBConn"%>

<%@page import="java.sql.Connection"%>

<%@ page contentType="text/html; charset=UTF-8"%>

<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

Connection conn = DBConn.getConnection();

ScoreDAO dao = new ScoreDAO(conn);

String hak = request.getParameter("hak");

ScoreDTO dto = dao.getReadData(hak); //학번으로 score정보 가져옴

DBConn.close();

if(dto==null)

response.sendRedirect("list.jsp");

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>성적처리 수정</title>

<script type="text/javascript">

function sendIt(){

var f = document.myForm;

f.action = "<%=cp%>/score/update_ok.jsp";

f.submit();

}

</script>

<style type="text/css">

*{

padding: 0px;

margin: 0px;

}

body {

font-size: 10pt;

}

td{

font-size: 10pt;

}

.txtField {

font-size: 10pt;

border: 1px solid;

}

.btn {

font-size: 10pt;

background: #e6e6e6;

}

</style>

</head>


<body>


</br></br>


<table width="500" cellpadding="0" cellspacing="3" align="center" bgcolor="#e4e4e4">

<tr height="50">

<td bgcolor="#ffffff" style="padding-left: 10px;">

<b>성적처리 수정화면</b>

</td>

</tr>

</table>


</br>


<form action="" method="post" name="myForm">

<table width="500" cellpadding="0" cellspacing="0" align="center">

<tr height="2"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="30">

<td align="center" width="100" bgcolor="#e6e6e6">학번</td>

<td style="padding-left: 5px;">

<%=dto.getHak()%>

</td>

</tr>

<tr height="1"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="30">

<td align="center" width="100" bgcolor="#e6e6e6">이름</td>

<td style="padding-left: 5px;">

<%=dto.getName()%>

</td>

</tr>

<tr height="1"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="30">

<td align="center" width="100" bgcolor="#e6e6e6">국어</td>

<td style="padding-left: 5px;">

<input type="text" name="kor" value="<%=dto.getKor()%>" size="10" maxlength="3" class="txtField">

</td>

</tr>

<tr height="1"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="30">

<td align="center" width="100" bgcolor="#e6e6e6">영어</td>

<td style="padding-left: 5px;">

<input type="text" name="eng" value="<%=dto.getEng()%>" size="10" maxlength="3" class="txtField">

</td>

</tr>

<tr height="1"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="30">

<td align="center" width="100" bgcolor="#e6e6e6">수학</td>

<td style="padding-left: 5px;">

<input type="text" name="mat" value="<%=dto.getMat()%>" size="10" maxlength="3" class="txtField">

</td>

</tr>

<tr height="2"><td colspan="2" bgcolor="#cccccc"></td></tr>

<tr height="35">

<td align="center" colspan="2">

<input type="hidden" name="hak" value="<%=dto.getHak()%>">

<input type="button" class="btn" value="수정완료" onclick="sendIt();">

<input type="button" class="btn" value="수정취소"

onclick="javascript:location.href='<%=cp%>/score/list.jsp'">

</table>

</form>


</body>


</html>

write.jsp와 똑같다.

다만, 수정하고자 하는 데이터의 값이 입력창에 미리 들어가 있다.


④ update_ok.jsp 작성

<%@page import="com.score.ScoreDAO"%>

<%@page import="com.util.DBConn"%>

<%@page import="java.sql.Connection"%>

<%@ page contentType="text/html; charset=UTF-8"%>

<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

%>

<jsp:useBean id="dto" class="com.score.ScoreDTO" scope="page"/>

<jsp:setProperty property="*" name="dto"/>

<%

Connection conn = DBConn.getConnection();

ScoreDAO dao = new ScoreDAO(conn);

int result = dao.updateData(dto);

DBConn.close();

response.sendRedirect("list.jsp");

%>


⑤ delete_ok.jsp 작성

<%@page import="com.score.ScoreDAO"%>


<%@page import="com.util.DBConn"%>

<%@page import="java.sql.Connection"%>

<%@ page contentType="text/html; charset=UTF-8"%>


<%

request.setCharacterEncoding("UTF-8");

String cp = request.getContextPath();

String hak = request.getParameter("hak");

Connection conn = DBConn.getConnection();

ScoreDAO dao = new ScoreDAO(conn);

int result = dao.deleteData(hak);

DBConn.close();

response.sendRedirect("list.jsp");

%>

결과

① 입력

② 리스트 화면

③ 수정 화면

    이세영의 데이터가 올라가 value에 미리 들어가 있음

④ 수정 성공

⑤삭제

'STUDY > JSP' 카테고리의 다른 글

Jsp 8일차 - 게시판(자바스크립트)  (0) 2019.02.21
Jsp 8일차 - 게시판(css작성)  (0) 2019.02.21
Jsp 6일차 - 간단한 웹을 만드는 방법  (0) 2019.02.19
Jsp 6일차 - 포워드  (0) 2019.02.19
Jsp 6일차 - 액션태그  (0) 2019.02.19

관련글

댓글

티스토리툴바