개발 ON
  • [ ETC/정보처리산업기사 ]
    [정보처리산업기사 | 실기] 성적관리 프로그램
    2023년 08월 09일 22시 59분 13초에 업로드 된 글입니다.
    작성자: 이주여이

    DB.jsp

    <%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
Class.forName("oracle.jdbc.OracleDriver");
Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","1234");
%>

    과목별 성적 조회.jsp(INSERT)

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section style="width: 100%; height: 100%">
<h2 style="text-align: center">과목별 성적 조회</h2>
<form style="display: flex; justify-content: center; text-align: center">
<table border="1">
	<tr>
	<td>학번</td>
	<td>성명</td>
	<td>교과목</td>
	<td>교과목코드</td>
	<td>담당교수</td>
	<td>중간</td>
	<td>기말</td>
	<td>출석</td>
	<td>레포트</td>
	<td>기타</td>
	<td>평균</td>
	<td>총점</td>
	<td>학점</td>
	</tr>
<%
request.setCharacterEncoding("UTF-8");

try {
	String sql = "select stu.stuid, stu.sname, sub.subname, sub.subcode, sub.proname, sco.midscore, sco.finalscore, sco.attend, sco.report, sco.etc, " +
					"to_char((sco.midscore + sco.finalscore + sco.attend + sco.report + sco.etc) / 5, '999') 평균 , " +
					"(sco.midscore * 0.3) + (sco.finalscore * 0.3) + (sco.attend * 0.2) + (sco.report * 0.1) + (sco.etc * 0.1) 총점 " +
					"from tbl_student_202210 stu, tbl_score_202210 sco, tbl_subject_202210 sub " +
					"where stu.stuid = sco.sid and sco.subcode = sub.subcode " +
					"order by stu.sname";
PreparedStatement pstmt = c.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
	int all = rs.getInt(12);
	String grade = "";
	if(all > 95) {
		grade = "A+";
	} else if(all >= 90) {
		grade = "A";
	} else if(all >= 85) {
		grade = "B+";
	} else if(all >= 80) {
		grade = "B";
	} else if(all >= 75) {
		grade = "C+";
	} else if(all >= 70) {
		grade = "C";
	} else if(all >= 65) {
		grade = "D+";
	} else if(all >= 60) {
		grade = "D";
	} else if(all < 60) {
		grade = "F";
	}
%>
	<tr>
	<td><%=rs.getString(1) %></td>
	<td><%=rs.getString(2) %></td>
	<td><%=rs.getString(3) %></td>
	<td><%=rs.getString(4) %></td>
	<td><%=rs.getString(5) %></td>
	<td><%=rs.getString(6) %></td>
	<td><%=rs.getString(7) %></td>
	<td><%=rs.getString(8) %></td>
	<td><%=rs.getString(9) %></td>
	<td><%=rs.getString(10) %></td>
	<td><%=rs.getString(11) %></td>
	<td><%=rs.getString(12) %></td>
	<td><%=grade %></td>
	</tr>
<%
}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

    해당 페이지는 action.jsp를 통해 값이 들어간다.

    action.jsp

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
request.setCharacterEncoding("UTF-8");

String sid = request.getParameter("sid");
String subcode = request.getParameter("subcode");
String midscore = request.getParameter("midscore");
String finalscore = request.getParameter("finalscore");
String attend = request.getParameter("attend");
String report = request.getParameter("report");
String ect = request.getParameter("etc");

try {
	String sql="insert into tbl_score_202210 values(?, ?, ?, ?, ?, ?, ?)";
	PreparedStatement pstmt = c.prepareStatement(sql);
	pstmt.setString(1, sid);
	pstmt.setString(2, subcode);
	pstmt.setString(3, midscore);
	pstmt.setString(4, finalscore);
	pstmt.setString(5, attend);
	pstmt.setString(6, report);
	pstmt.setString(7, ect);
	pstmt.executeUpdate();
} catch(Exception e) {
	e.printStackTrace();
}
%>

<jsp:forward page="score_insert.jsp" />

    학생 정보 현황.jsp (SELLECT)

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section style="width: 100%; height: 100%">
<h2 style="text-align: center">학생 정보 현황</h2>
<form action="action.jsp" name="frm" method="post" style="display: flex; align-items: center; justify-content: center">
	<table border="1" style="text-align: center">
	<tr>
	<td>학번</td>
	<td>이름</td>
	<td>주민 번호</td>
	<td>학과명</td>
	<td>성별</td>
	<td>전화번호</td>
	<td>이메일</td>
	</tr>
	
	<%
	request.setCharacterEncoding("UTF-8");
	
	try {
		String sql = "select stuid, sname, jumin, dept_name, phone, email from tbl_student_202210";
		PreparedStatement pstmt = c.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			String jumin = rs.getString(3).substring(0,6) + "-" + rs.getString(3).substring(6,12);
			String jender = rs.getString(3).substring(6, 7);
			if(jender.equals("3")) {
				jender = "남자";
			} else {
				jender = "여자";
			}
	%>
	<tr>
	<td><%=rs.getString(1) %></td>
	<td><%=rs.getString(2) %></td>
	<td><%=jumin %></td>
	<td><%=rs.getString(4) %></td>
	<td><%=jender %></td>
	<td><%=rs.getString(5) %></td>
	<td><%=rs.getString(6) %></td>
	</tr>
	<%
		}
	} catch(Exception e) {
		e.printStackTrace();
	}
	%>
	
	</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

    성적 현황.jsp(SELECT)

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section style="width: 100%; height: 100%">
<h2 style="text-align: center">성적 현황</h2>
<form style="display: flex; justify-content: center; text-align: center">
<table border="1">
	<tr>
	<td>학번</td>
	<td>성명</td>
	<td>교과목</td>
	<td>교과목코드</td>
	<td>담당교수</td>
	<td>중간</td>
	<td>기말</td>
	<td>출석</td>
	<td>레포트</td>
	<td>기타</td>
	<td>평균</td>
	<td>총점</td>
	<td>학점</td>
	</tr>
<%
request.setCharacterEncoding("UTF-8");

try {
	String sql = "select stu.stuid, stu.sname, sub.subname, sub.subcode, sub.proname, sco.midscore, sco.finalscore, sco.attend, sco.report, sco.etc, " +
					"to_char((sco.midscore + sco.finalscore + sco.attend + sco.report + sco.etc) / 5, '999') 평균 , " +
					"(sco.midscore * 0.3) + (sco.finalscore * 0.3) + (sco.attend * 0.2) + (sco.report * 0.1) + (sco.etc * 0.1) 총점 " +
					"from tbl_student_202210 stu, tbl_score_202210 sco, tbl_subject_202210 sub " +
					"where stu.stuid = sco.sid and sco.subcode = sub.subcode " +
					"order by stu.sname";
PreparedStatement pstmt = c.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
	int all = rs.getInt(12);
	String grade = "";
	if(all > 95) {
		grade = "A+";
	} else if(all >= 90) {
		grade = "A";
	} else if(all >= 85) {
		grade = "B+";
	} else if(all >= 80) {
		grade = "B";
	} else if(all >= 75) {
		grade = "C+";
	} else if(all >= 70) {
		grade = "C";
	} else if(all >= 65) {
		grade = "D+";
	} else if(all >= 60) {
		grade = "D";
	} else if(all < 60) {
		grade = "F";
	}
%>
	<tr>
	<td><%=rs.getString(1) %></td>
	<td><%=rs.getString(2) %></td>
	<td><%=rs.getString(3) %></td>
	<td><%=rs.getString(4) %></td>
	<td><%=rs.getString(5) %></td>
	<td><%=rs.getString(6) %></td>
	<td><%=rs.getString(7) %></td>
	<td><%=rs.getString(8) %></td>
	<td><%=rs.getString(9) %></td>
	<td><%=rs.getString(10) %></td>
	<td><%=rs.getString(11) %></td>
	<td><%=rs.getString(12) %></td>
	<td><%=grade %></td>
	</tr>
<%
}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

    과목별 성적 조회.jsp(SELECT)

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section style="width: 100%; height: 100%">
<h2 style="text-align: center">과목별 성적 조회</h2>
<form action="score_list.jsp" style="display: flex; justify-content: center; text-align: center">
<table border="1">
<tr>
<td>과목 코드</td>
<td><input type="text" name="subcode"></td>
<td><input type="submit" value="조회"></td>
</tr>
</table>
</form>

<br>

<form style="display: flex; justify-content: center; text-align: center">
<table border="1">
<tr>
<td>학번</td>
<td>성명</td>
<td>학과</td>
<td>과목</td>
<td>중간</td>
<td>기말</td>
<td>출석</td>
<td>레포트</td>
<td>기타</td>
<td>총점</td>
<td>학점</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
String code = request.getParameter("subcode");
System.out.println(code);

try {
	String sql = "select stu.stuid, stu.sname, stu.dept_name, sub.subname, sco.midscore, sco.finalscore, sco.attend, sco.report, sco.etc, " +
		       		"(sco.midscore * 0.3) + (sco.finalscore * 0.3) + (sco.attend * 0.2) + (sco.report * 0.1) + (sco.etc * 0.1) 총점 " +
		       		"from tbl_student_202210 stu, tbl_score_202210 sco, tbl_subject_202210 sub " +
		       		"where stu.stuid = sco.sid and sco.subcode = sub.subcode and sub.subcode = ? " +
		       		"order by stu.sname";
	PreparedStatement pstmt = c.prepareStatement(sql);
	pstmt.setString(1, code);
	ResultSet rs = pstmt.executeQuery();
	while(rs.next()) {
		while(rs.next()) {
			int all = rs.getInt(10);
			String grade = "";
			if(all > 95) {
				grade = "A+";
			} else if(all >= 90) {
				grade = "A";
			} else if(all >= 85) {
				grade = "B+";
			} else if(all >= 80) {
				grade = "B";
			} else if(all >= 75) {
				grade = "C+";
			} else if(all >= 70) {
				grade = "C";
			} else if(all >= 65) {
				grade = "D+";
			} else if(all >= 60) {
				grade = "D";
			} else if(all < 60) {
				grade = "F";
			}
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=rs.getString(4) %></td>
<td><%=rs.getString(5) %></td>
<td><%=rs.getString(6) %></td>
<td><%=rs.getString(7) %></td>
<td><%=rs.getString(8) %></td>
<td><%=rs.getString(9) %></td>
<td><%=rs.getString(10) %></td>
<td><%=grade %></td>
</tr>
<%
	}
		}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

    과목 코드를 입력하면 해당 과목에 해당하는 성적이 조회되는 검색 기능을 가진다.

     

    저작자표시 비영리 동일조건

    'ETC > 정보처리산업기사' 카테고리의 다른 글

    [정보처리산업기사 | 필기] pdf  (0) 2023.08.10
    [정보처리산업기사 | 실기] 골프연습장 회원관리 프로그램  (0) 2023.08.10
    [정보처리산업기사 | 실기] 수강신청 싸이트  (0) 2023.08.08
    [정보처리산업기사 | 필기] 3. 화면 구현  (4) 2023.08.06
    [정보처리산업기사 | 필기] 2. 응용 SW 기초 기술 활용  (0) 2023.08.06
      댓글

    티스토리툴바