열심히 끝까지

[코딩 과제] - 8/4 강사님 문제 본문

디바이스 융합 자바(Java)기반 풀스택 개발자 양성과정(과제)

[코딩 과제] - 8/4 강사님 문제

노유림 2022. 8. 4. 17:45

--------------------
과제
1. .jsp 내부의 JAVA 코드를 
   "자바빈즈"로 바꿔서 호출하기!
   HINT ) 
       JAVA를 먼저 완성한 이휴에
       JSP에서 호출하여 사용!!!
              => 변경해서 동작하는 것을 보여주면 됨
       2시에 신호출결하면서 보여주면 됨
------------------------------
++++++++필수 아님 위에꺼는 필수
2. selectOne() 페이지
3. update() 버튼 추가하기
4. delete() 버튼 추가하기

 

======내 풀이

>> JDBCUtil은 생략함.

>> 내 풀이는 그냥 잘 작동하는지 아닌지를 보는 것이기에 이쁨은 추구하지 않길 바람

 

-------MemberVO

package model.vo;

public class MemberVO {
	private int mpk;
	private String name;
	private int score;
	
	public int getMpk() {
		return mpk;
	}
	public void setMpk(int mpk) {
		this.mpk = mpk;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getScore() {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	
}

-------MemberDAO

package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.util.JDBCUtil;
import model.vo.MemberVO;

public class MemberDAO {
	
	Connection conn;
	PreparedStatement pstmt;
	final String sql_insert="INSERT INTO MEMBER VALUES((SELECT NVL(MAX(MPK),0)+1 FROM MEMBER),?,?)";
	final String sql_selectAll ="SELECT * FROM MEMBER";
	final String sql_selectOne="SELECT * FROM MEMBER WHERE MPK=?";
	final String sql_delete="DELETE FROM MEMBER WHERE MPK=?";
	final String sql_update="UPDATE MEMBER SET SCORE=? WHERE MPK=?";
	
	public ArrayList<MemberVO> selectAll(MemberVO vo){
		
		ArrayList<MemberVO> datas = new ArrayList<MemberVO>(); // 저장할 ArrayList
		conn=model.util.JDBCUtil.connect(); // 연결
		
		try{ // 불러오기
			pstmt=conn.prepareStatement(sql_selectAll);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				MemberVO data = new MemberVO();
				
				data.setMpk(rs.getInt("MPK"));
				data.setName(rs.getString("NAME"));
				data.setScore(rs.getInt("SCORE"));
				datas.add(data);
			}
			
	    } catch(Exception e){
	    	e.printStackTrace();
	    } finally {
	    	model.util.JDBCUtil.disconnect(pstmt, conn);
	    }
		return datas;
	}
	
	public boolean insert(MemberVO vo) {
		conn=JDBCUtil.connect(); // JDBC 연결
		try {
			pstmt=conn.prepareStatement(sql_insert); 
			
			pstmt.setString(1, vo.getName()); // 이름
			pstmt.setInt(2, vo.getScore()); // 평점
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false; // 값을 못 넣은 것 > 실패로 판단
		}finally {
			JDBCUtil.disconnect(pstmt, conn); // JDBC 연결 해제
		}
		return true;
	}
	
	public MemberVO selectOne(MemberVO vo){
		conn=JDBCUtil.connect();
		ResultSet rs = null; // scope 공부
		try {
			pstmt = conn.prepareStatement(sql_selectOne);
			pstmt.setInt(1, vo.getMpk());
			rs = pstmt.executeQuery();

			if(rs.next()) {
                vo.setName(rs.getString("NAME"));
                vo.setScore(rs.getInt("SCORE"));
            }
			
			else {
				return null;
			}
		}catch(SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			JDBCUtil.disconnect(pstmt, conn);
		}
		return vo;
	}
	
	public boolean update(MemberVO vo) {
		conn=JDBCUtil.connect();
		try {
			pstmt = conn.prepareStatement(sql_update);
			pstmt.setInt(2, vo.getMpk()); // 값 가져오기 eid
			pstmt.setInt(1, vo.getScore()); // 값 설정 price
			int res = pstmt.executeUpdate();

			if(res == 0) {
				return false;
			}
		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	
	public boolean delete(MemberVO vo) {
		conn=JDBCUtil.connect();
		try {
			pstmt = conn.prepareStatement(sql_delete);
			pstmt.setInt(1, vo.getMpk());
			int res = pstmt.executeUpdate();

			if(res == 0) {
				return false;
			}
		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	
}

-------NewFile.jsp(메인 페이지)

<%@page import="model.vo.MemberVO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.sql.*"%>
<%request.setCharacterEncoding("UTF-8");// 인코딩%>
<%-- mdao, mvo 객체 new --%>
<jsp:useBean id="mdao" class="model.dao.MemberDAO"/>
<jsp:useBean id="mvo" class="model.vo.MemberVO"/>
<jsp:setProperty property="*" name="mvo"/>
<%
	if(request.getMethod().equals("POST")){
		if(mdao.insert(mvo)){
			out.println("<SCRIPT>alert('"+request.getParameter("name")+" 등록 완료!')</SCRIPT>");
		}
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>데이터 등록</title>
<style type="text/css">
	a{
		color:black;
		text-decoration:none;
	}
</style>
</head>
<body>

<form action="NewFile.jsp" method="post">
이름:<input type="text" name="name"><br>
점수:<input type="number" min="0" max="100" name="score"><br>
<input type="submit" value="데이터 등록하기">
<a href ="search.jsp"><input type="button" value="학생 검색"></a>
</form>
<hr>
<h2>데이터 목록</h2>
<%
    // DAO 작업 
    // 자바 빈즈로 작업하면 편함
	ArrayList<MemberVO> datas=mdao.selectAll(mvo);
	if(datas.size()>0){
		for(MemberVO m : datas){
%>		
<%= m.getMpk()%>. <%=m.getName() %> <%=m.getScore() %>점	<br>
<% 		
		}
	}
	else{
		out.println("<H3>출력할 데이터가 없습니다...</H3>");
	}
%>
<%--
<table border="2">
	<tr>
		<th>학생 이름</th>
		<th>점수</th>
	</tr>
<%
	for(MemberVO v : mdao.selectAll(mvo)){
		out.println("<TR>");
		out.println("<TH><A HREF='NewFile.jsp'></A></TH>");
		out.println("/<TR>");
	}
%>
</table>
 --%>
</body>
</html>

출력--------------------------------------------------

>> 유리 90점 추가했을 때

 

-------search.jsp

<%@page import="java.util.ArrayList"%>
<%@page import="model.vo.MemberVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("UTF-8");// 인코딩%>
<jsp:useBean id="mdao" class="model.dao.MemberDAO"/>
<jsp:useBean id="mvo" class="model.vo.MemberVO"/>
<jsp:setProperty property="*" name="mvo"/>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>학생 검색</title>
</head>
<body>

<form action="search.jsp" method="post">
번호 검색 :<input type="text" name="mpk"><br>
<input type="submit" value="학생 검색">
<a href ="update.jsp"><input type="button" value="점수 업데이트"></a>
<a href ="delete.jsp"><input type="button" value="학생 삭제"></a><br>
<a href="NewFile.jsp">메인으로</a>
<br><br>
<%
	mdao.selectOne(mvo);
	ArrayList<MemberVO> datas=mdao.selectAll(mvo);
%>
<%
if(mvo.getMpk()>datas.size()){
	out.println("<H4>검색할 학생 번호를 입력하세요.</H4>");
}else{
%>
	<%=mvo.getMpk() %>. <%=mvo.getName()%> <%=mvo.getScore() %>점<br>
<% 
}
%>
</form>
</body>
</html>

출력--------------------------------------------------

 

-------update.jsp

<%@page import="model.vo.MemberVO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("UTF-8");// 인코딩%>
<jsp:useBean id="mdao" class="model.dao.MemberDAO"/>
<jsp:useBean id="mvo" class="model.vo.MemberVO"/>
<jsp:setProperty property="*" name="mvo"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>점수 업데이트</title>
</head>
<body>
<form action="update.jsp" method="post">
점수를 바꿀 학생 번호 입력 :<input type="text" name="mpk"><br>
바꿀 점수 :<input type="number" name="score"><br>
<input type="submit" value="점수 업데이트"><br>
</form>

<%
	ArrayList<MemberVO> datas=mdao.selectAll(mvo);
	if(mvo.getMpk()>datas.size()){
		out.println("존재하지 않는 학생 번호입니다.");
	}else{
		if(mdao.update(mvo)){
		%>
		<%=mvo.getMpk() %>번 학생의 점수를 <%=mvo.getScore() %>점<br>
		<%
			out.println("으로 변경 완료");
		}
		%>
		<br>
		<a href="NewFile.jsp">메인으로</a>
		<% 
	}
%>
<br>

</body>
</html>

출력--------------------------------------------------

>> 50점에서 80점으로 바뀜

 

-------delete.jsp

<%@page import="model.vo.MemberVO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("UTF-8");// 인코딩%>
<jsp:useBean id="mdao" class="model.dao.MemberDAO"/>
<jsp:useBean id="mvo" class="model.vo.MemberVO"/>
<jsp:setProperty property="*" name="mvo"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>학생 삭제</title>
</head>
<body>
<form action="delete.jsp" method="post">
삭제할 학생 번호 입력 :<input type="text" name="mpk"><br>
<input type="submit" value="학생 삭제"><br>
</form>

<%
	ArrayList<MemberVO> datas=mdao.selectAll(mvo);
	if(mvo.getMpk()>datas.size()){
		out.println("존재하지 않는 학생 번호입니다.");
	}else{
		if(mdao.delete(mvo)){
			out.println("학생 삭제 완료");
		}
		%>
		<br>
		<a href="NewFile.jsp">메인으로</a>
		<%
	}
%>

</body>
</html>

출력--------------------------------------------------

>> 8번 삭제 완료

 

 

===========강사님 풀이
코드가 중요한 것이 아니라
순서가 중요!!
메모장 열어서 어떻게 풀이하는지 볼 것

1. 데이터 목록을 출력하기 위해
           datas를 필요
        for(MemberVO v : datas){
              v.getName()
        }
2. datas=dao.selectAll(vo)
        dao객체 new -> jsp:useBean 액션
        vo객체 new -> jsp:useBean 액션
3.POST 요청
        dao.insert(vo)
        dao객체 new
        vo객체 new
        vo 객체 setter->이름(name), 점수(score)

 

>> 강사님 방법

>> mpk를 누르면 상세페이지 화면으로 이동

메인 화면------------------------------

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="model.vo.MemberVO" %>
<%
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="mdao" class="model.dao.MemberDAO" />
<jsp:useBean id="mvo" class="model.vo.MemberVO" />
<jsp:setProperty property="*" name="mvo" />
<%
	if(request.getMethod().equals("POST")){		
		if(mdao.insert(mvo)){
			out.println("<SCRIPT>alert('"+request.getParameter("name")+" 등록완료!');</SCRIPT>");
		}
		else{
			out.println("<SCRIPT>alert('등록실패...');</SCRIPT>");
		}
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
	a {
		color: black;
		text-decoration: none;
	}
</style>
</head>
<body>

<form action="NewFile.jsp" method="post">
이름: <input type="text" name="name"> <br>
점수: <input type="number" min="0" max="100" name="score"> <br>
<input type="submit" value="데이터 등록하기">
</form>

<hr>

<h2>데이터 목록</h2>
<table border="2">
	<tr>
		<th>학생 번호</th>
		<th>학생 이름</th>
	</tr>
<%
	for(MemberVO v:mdao.selectAll(mvo)){
		out.println("<TR>");
		out.println("<TH><A HREF='NewFile1.jsp?mpk="+v.getMpk()+"'>"+v.getMpk()+"</A></TH>");
		out.println("<TD>"+v.getName()+"</TD>");
		out.println("</TR>");
	}
%>
</table>

</body>
</html>

상세페이지------------------------------

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="mDAO" class="model.dao.MemberDAO" />
<jsp:useBean id="mVO" class="model.vo.MemberVO" />
<jsp:setProperty property="*" name="mVO"/>
<%
	mVO=mDAO.selectOne(mVO);
	if(mVO==null){		
		response.sendRedirect("NewFile.jsp");
	}
	else{
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1><%=mVO.getName()%> 학생 정보</h1>
<form action="NewFile2.jsp" method="post">
<table border="1">
	<tr>
		<td>학번</td>
		<td><input type="text" name="mpk" value="<%=mVO.getMpk()%>" readonly></td>
	</tr>
	<tr>
		<td>이름</td>
		<td><input type="text" name="name" value="<%=mVO.getName()%>"></td>
	</tr>
	<tr>
		<td>점수</td>
		<td><input type="number" name="score" min="0" max="100" value="<%=mVO.getScore()%>"></td>
	</tr>
	<tr>
		<td colspan="2" align="right">
			<input type="submit" value="변경">&nbsp;<input type="button" id="btn" onclick="del(<%=mVO.getMpk()%>);" value="삭제">
		</td>
	</tr>
</table>
</form>
<script type="text/javascript">
	function del(mpk){
		if(confirm("정말 삭제하시겠습니까?")){
			location.href="NewFile3.jsp?mpk="+mpk;
		}
		else{
			return false;
		}
	}
</script>
<%
	}
%>

</body>
</html>

 

>> 변경할 때 뜨는 창

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="mDAO" class="model.dao.MemberDAO" />
<jsp:useBean id="mVO" class="model.vo.MemberVO" />
<jsp:setProperty property="*" name="mVO" />
<%
	if(mDAO.update(mVO)){
		%>
		<script type="text/javascript">
			alert('<%=mVO.getMpk()%>번 데이터 변경 완료!');
			location.href="NewFile.jsp";
		</script>
		<%
	}
	else{
	%>
		<script type="text/javascript">
			alert('데이터 변경에 문제가 발생했습니다...');
			location.href="NewFile.jsp";
		</script>
	<%
	}
%>

 

>> 삭제시 뜨는 창

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="mDAO" class="model.dao.MemberDAO" />
<jsp:useBean id="mVO" class="model.vo.MemberVO" />
<jsp:setProperty property="*" name="mVO" />
<%
	if(mDAO.delete(mVO)){
		%>
		<script type="text/javascript">
			alert('<%=mVO.getMpk()%>번 데이터 삭제 완료!');
			location.href="NewFile.jsp";
		</script>
		<%
	}
	else{
	%>
		<script type="text/javascript">
			alert('데이터 삭제에 문제가 발생했습니다...');
			location.href="NewFile.jsp";
		</script>
	<%
	}
%>

'디바이스 융합 자바(Java)기반 풀스택 개발자 양성과정(과제)' 카테고리의 다른 글

[코딩 과제] - 9/13 강사님 문제  (1) 2022.09.14
능력단위평가 - 오류처리  (0) 2022.08.11
[코딩 과제] - 8/2 강사님 문제  (0) 2022.08.02
[코딩 과제] - 7/26 주사위 게임  (0) 2022.07.26
[코딩 과제] - 7/26 강사님 문제  (0) 2022.07.26
'디바이스 융합 자바(Java)기반 풀스택 개발자 양성과정(과제)' Related Articles more