정보처리산업기사
[정보처리산업기사] 과정평가형 실기 2_ 회원등록/조회/수정(배열ver)
YOU R PLANET
2022. 1. 4. 14:01
https://you-r-planet-b612.tistory.com/122
[정보처리산업기사] 과정평가형 실기 1_ 기본세팅
1.개발 환경 확인하기 (시험장에서도 동일합니다.) 언어는 JAVA 개발 환경은 ECLIPSE 서버는 톰캣 DB는 ORACLE 2. 요구 사항 확인하기 (실제 시험이 1.5배 정도 어렵다고 합니다.) 1. 구현할 기능: 등록, 조
you-r-planet-b612.tistory.com
전 단계 참고 ↑
1. 회원 등록
회원번호
회원성명
회원전화
통신사(라디오 버튼)
가입일자
고객등급(드롭다운)
도시코드
* 유효성 체크(각 항목별)
* 등록 누르면 목록 조회
03_RegisterForm.jsp
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
// 유효성 검사 부분
<script>
function check(){
if(document.f1.name.value == ""){
alert('이름을 입력하세요.');
document.f1.name.focus();
return false;
}
if(document.f1.tel.value == ""){
alert('전화번호를 입력하세요.');
document.f1.tel.focus();
return false;
}
if(document.f1.date.value == ""){
alert('날짜를 입력하세요.');
document.f1.date.focus();
return false;
}
if(document.f1.city.value == ""){
alert('도시코드를 입력하세요.');
document.f1.city.focus();
return false;
}
document.f1.submit();
}
</script>
</head>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DAO.getConnection();
int number = 0; // 새로 등록할 번호
try{
String sql = "select max(custno) from member_tbl_02";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
number = rs.getInt(1);
number++;
}
conn.close();
pstmt.close();
rs.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<body>
<form name="f1" action="03_RegisterPro.jsp" method="post">
<table border="1">
<tr>
<td>회원번호(자동발생)</td>
<td><input type="text" value=<%=number%>></td>
</tr>
<tr>
<td>회원성명</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>회원전화</td>
<td><input type="text" name="tel"></td>
</tr>
<tr>
<td>통신사</td>
<td>
<input type="radio" name="address" value="SK" checked>SK
<input type="radio" name="address" value="KT" >KT
<input type="radio" name="address" value="LG" >LG
</td>
</tr>
<tr>
<td>가입일자</td>
<td><input type="text" name="date"></td>
</tr>
<tr>
<td>고객등급</td>
<td>
<select name="grade">
<option value="A">A</option>
<option value="B">B</option>
<option value="C">C</option>
</select>
</td>
</tr>
<tr>
<td>도시코드</td>
<td><input type="number" name="city"></td>
</tr>
<tr align="center">
<td colspan="2">
<input type="hidden" name="number" value=<%=number%>>
<input type="button" onclick="check()" value="등록">
<button onclick="00_Main.jsp?center=04_MemberList.jsp">조회</button>
</td>
</tr>
</table>
</form>
</body>
</html>
03_RegisterPro.jsp
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<!--
custno number(6),
custname varchar2(20),
phone varchar2(13),
address varchar2(4),
joindate date,
grade char(1),
city char(2),
-->
<body>
<%
int state = 0;
request.setCharacterEncoding("UTF-8");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DAO.getConnection();
String custname = request.getParameter("name");
String phone = request.getParameter("tel");
String address = request.getParameter("address");
String date = request.getParameter("date");
String grade = request.getParameter("grade");
String city = request.getParameter("city");
// 시퀀스로 처리하므로 custno를 request 값으로 넘겨주지 않음
try{
String sql = "INSERT INTO member_tbl_02 values(member_seq.nextval, ?,?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, custname);
pstmt.setString(2, phone);
pstmt.setString(3, address);
pstmt.setString(4, date);
pstmt.setString(5, grade);
pstmt.setString(6, city);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}catch(Exception e){
state = -1;
e.printStackTrace();
}
%>
<%if(state == 0){%>
<script>
alert('회원 등록 완료');
location.href="00_Main.jsp?center=04_MemberList.jsp";
</script>
<%}else{%>
<script>
alert('회원 등록 실패');
history.go(-1);
</script>
<%}%>
</body>
</html>2. 회원 조회
04_MemberList.jsp
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.Date"%>
<%@page import="java.util.ArrayList"%>
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DAO.getConnection();
ArrayList<String[]> memberList = new ArrayList<>(); // DB에서 가져올 정보를 저장할 곳
try{
String sql = "SELECT * FROM member_tbl_02";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
String[] member = new String[7];
member[0] = rs.getInt(1) + "";
member[1] = rs.getString(2);
member[2] = rs.getString(3);
member[3] = rs.getString(4);
Date date = rs.getDate(5);
SimpleDateFormat sdf = new SimpleDateFormat("YYYY년 MM월 dd일");
member[4] = sdf.format(date); // 날짜
String gradeChar[] = {"A","B","C"};
String grade[] = {"VIP","일반","직원"};
for(int i = 0; i < gradeChar.length; i++){
if(gradeChar[i].equals(rs.getString(6))){
member[5] = grade[i]; // 등급
break;
}
}
String cityNum[] = {"01","10","20","30","40","60"};
String city[] = {"서울","인천","성남","대전","광주","부산"};
for(int i = 0; i < cityNum.length; i++){
if(cityNum[i].equals(rs.getString(7))){
member[6] = city[i]; // 도시
break;
}
}
memberList.add(member);
}
pstmt.close();
conn.close();
rs.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<div>
<h1>회원목록조회/수정</h1>
</div>
<table border="1">
<tr align="center">
<td>회원번호</td>
<td>회원성명</td>
<td>전화번호</td>
<td>통신사</td>
<td>가입일자</td>
<td>고객등급</td>
<td>거주지역</td>
</tr>
<%for(int i = 0; i < memberList.size(); i++){%>
<% String[] member = memberList.get(i); %>
<tr align="center">
<td><a href="00_Main.jsp?center=06_UpdateForm.jsp&number=<%=member[0]%>"><%=member[0]%></a></td>
<td><%=member[1]%></td>
<td><%=member[2]%></td>
<td><%=member[3]%></td>
<td><%=member[4]%></td>
<td><%=member[5]%></td>
<td><%=member[6]%></td>
</tr>
<%} %>
</table>
</body>
</html>
SimpleDateFormat sdf = new SimpleDateFormat("YYYY년 MM월 dd일");★ 날짜 형식 함수 알아 둘 것! |
3. 회원 수정
06_UpdateForm.jsp
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.Date"%>
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int number = Integer.parseInt(request.getParameter("number"));
conn = DAO.getConnection();
String[] member = new String[7];
try{
String sql = "Select * from member_tbl_02 where custno = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, number);
rs = pstmt.executeQuery();
if(rs.next()){
member[0] = rs.getInt(1) + "";
member[1] = rs.getString(2);
member[2] = rs.getString(3);
member[3] = rs.getString(4);
Date date = rs.getDate(5);
SimpleDateFormat sdf = new SimpleDateFormat("YYYYMMdd");
member[4] = sdf.format(date);
System.out.println(member[4]);
member[5] = rs.getString(6);
member[6] = rs.getString(7);
}
conn.close();
pstmt.close();
rs.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<body>
<form name="f1" action="06_UpdatePro.jsp" method="post">
<table border="1">
<tr>
<td>회원번호(자동발생)</td>
<td><input type="text" name="number" value=<%=member[0]%>></td>
</tr>
<tr>
<td>회원성명</td>
<td><input type="text" name="name" value=<%=member[1]%>></td>
</tr>
<tr>
<td>회원전화</td>
<td><input type="text" name="tel" value=<%=member[2]%>></td>
</tr>
<tr>
<td>통신사</td>
<td>
<input type="radio" name="address" value="SK" <%if(member[3].equals("SK")){%> checked <%}%>>SK
<input type="radio" name="address" value="KT" <%if(member[3].equals("KT")){%> checked <%}%> >KT
<input type="radio" name="address" value="LG" <%if(member[3].equals("LG")){%> checked <%}%>>LG
</td>
</tr>
<tr>
<td>가입일자</td>
<td><input type="text" name="date" value=<%=member[4]%>></td>
</tr>
<tr>
<td>고객등급</td>
<td>
<select name="grade">
<option value="A"<%if(member[5].equals("A")){%> selected <%}%>>A</option>
<option value="B"<%if(member[5].equals("B")){%> selected <%}%>>B</option>
<option value="C"<%if(member[5].equals("C")){%> selected <%}%>>C</option>
</select>
</td>
</tr>
<tr>
<td>도시코드</td>
<td><input type="number" name="city" value=<%=member[6]%>></td>
</tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="수정">
<button onclick="00_Main.jsp?center=04_MemberList.jsp">조회</button>
</td>
</tr>
</table>
</form>
</body>
</html>06_UpdatePro.jsp
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<!--
custno number(6),
custname varchar2(20),
phone varchar2(13),
address varchar2(4),
joindate date,
grade char(1),
city char(2),
-->
<%
int state = 0;
request.setCharacterEncoding("UTF-8");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DAO.getConnection();
int custno = Integer.parseInt(request.getParameter("number"));
String custname = request.getParameter("name");
String phone = request.getParameter("tel");
String address = request.getParameter("address");
String date = request.getParameter("date");
String grade = request.getParameter("grade");
String city = request.getParameter("city");
try{
String sql = "update member_tbl_02 set custname = ?, phone = ?, address =?, joindate = ?, grade=?, city=? where custno = ? ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, custname);
pstmt.setString(2, phone);
pstmt.setString(3, address);
pstmt.setString(4, date);
pstmt.setString(5, grade);
pstmt.setString(6, city);
pstmt.setInt(7,custno);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}catch(Exception e){
state = -1;
e.printStackTrace();
}
%>
<%if(state == 0){ %>
<script>
alert('수정완료!');
location.href="00_Main.jsp?center=04_MemberList.jsp";
</script>
<%}else{%>
<script>
alert('수정 실패');
history.go(-1);
</script>
<%} %>
</body>
</html>
4. 매출 조회
05_RankList.jsp
<%@page import="java.text.DecimalFormat"%>
<%@page import="java.util.ArrayList"%>
<%@page import="DBPKG.DAO"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DAO.getConnection();
ArrayList<int[]> money = new ArrayList<>();
ArrayList<String[]> rankList = new ArrayList<>();
try{
String sql = "SELECT custno, sum(price) from money_tbl_02 group by custno order by sum(price) desc";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()){
int[] temp = new int[2];
temp[0] = rs.getInt(1);
temp[1] = rs.getInt(2);
money.add(temp);
}
for(int i = 0; i < money.size(); i++){
String[] temp2 = new String[4];
temp2[0] = money.get(i)[0] + "";
temp2[3] = money.get(i)[1] + "";
sql = "SELECT custname, grade from member_tbl_02 where custno = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, money.get(i)[0]);
rs = pstmt.executeQuery();
if(rs.next()){
temp2[1] = rs.getString(1);
String gradeChar[] = {"A","B","C"};
String grade[] = {"VIP","일반","직원"};
for(int j = 0; j < gradeChar.length; j++){
if(gradeChar[j].equals(rs.getString(2))){
temp2[2] = grade[j];
break;
}
}
}
rankList.add(temp2);
}
pstmt.close();
pstmt.close();
rs.close();
}catch(Exception e){
e.printStackTrace();
}
DecimalFormat df = new DecimalFormat("###,###");
%>
<table border="1">
<tr>
<td><strong>회원번호</strong></td>
<td><strong>회원성명</strong></td>
<td><strong>고객등급</strong></td>
<td><strong>매출</strong></td>
</tr>
<%for(int i= 0; i < rankList.size(); i++){ %>
<%String[] tempList = rankList.get(i);
int total = Integer.parseInt(tempList[3]);
%>
<tr align="center">
<td><%=tempList[0]%></td>
<td><%=tempList[1]%></td>
<td><%=tempList[2]%></td>
<td><%=df.format(total)%></td>
</tr>
<%} %>
</table>
</body>
</html>
DecimalFormat df = new DecimalFormat("###,###");숫자 서식 함수 알아 둘 것! |
매출 조회는 다양한 방법이 있겠지만 나는 SQL 문으로 처리했다.