괴발개발 개발하다
[자격증] 정보처리산업기사 과정평가형 예제 풀이 본문
** 기능을 Servlet 파일로 분리해서 작성함.
1. 테이블 생성 및 데이터 입력
가) 회원정보 명세서
1) member_tbl_02 테이블 생성
CREATE TABLE member_tbl_02(
custno number(6) NOT NULL,
custname varchar2(20),
phone varchar2(13),
address varchar2(60),
joindate date,
grade char(1),
city char(2),
CONSTRAINT pk_member PRIMARY KEY(custno)
);2) member_tbl_02 데이터 입력
INSERT INTO member_tbl_02 VALUES (100001, '김행복', '010-1111-2222', '서울 동대문구 휘경1동', '20151202', 'A', '01');
INSERT INTO member_tbl_02 VALUES (100002, '김축복', '010-1111-3333', '서울 동대문구 휘경2동', '20151206', 'B', '01');
INSERT INTO member_tbl_02 VALUES (100003, '장믿음', '010-1111-4444', '울릉군 울릉읍 독도1리', '20151001', 'B', '30');
INSERT INTO member_tbl_02 VALUES (100004, '최사랑', '010-1111-5555', '울릉군 울릉읍 독도2리', '20151113', 'A', '30');
INSERT INTO member_tbl_02 VALUES (100005, '진평화', '010-1111-6666', '제주도 제주시 외나무골', '20151225', 'B', '60');
INSERT INTO member_tbl_02 VALUES (100006, '차공단', '010-1111-7777', '제주도 제주시 감나무골', '20151211', 'C', '60');
나) 회원매출정보 명세서
1) money_tbl_02 테이블 생성
CREATE TABLE money_tbl_02(
custno number(6) NOT NULL,
salenol number(8) NOT NULL,
pcost number(8),
amount number(4),
price number(8),
pcode varchar2(4),
sdate datE,
CONSTRAINT pk_money PRIMARY KEY(custno, salenol),
CONSTRAINT fk_money FOREIGN KEY(custno) REFERENCES member_tbl_02(custno) ON delete CASCADE
);2) money_tbl_02 데이터 입력
INSERT INTO money_tbl_02 VALUES (100001, '20160001', 500, 5, 2500, 'A001', '20160101');
INSERT INTO money_tbl_02 VALUES (100001, '20160002', 1000, 4, 4000, 'A002', '20160101');
INSERT INTO money_tbl_02 VALUES (100001, '20160003', 500, 3, 1500, 'A008', '20160101');
INSERT INTO money_tbl_02 VALUES (100002, '20160004', 2000, 1, 2000, 'A004', '20160102');
INSERT INTO money_tbl_02 VALUES (100002, '20160005', 500, 1, 500, 'A001', '20160103');
INSERT INTO money_tbl_02 VALUES (100003, '20160006', 1500, 2, 3000, 'A003', '20160103');
INSERT INTO money_tbl_02 VALUES (100004, '20160007', 500, 2, 1000, 'A001', '20160104');
INSERT INTO money_tbl_02 VALUES (100004, '20160008', 300, 1, 300, 'A005', '20160104');
INSERT INTO money_tbl_02 VALUES (100004, '20160009', 600, 1, 600, 'A006', '20160104');
INSERT INTO money_tbl_02 VALUES (100004, '20160010', 3000, 1, 3000, 'A007', '20160104');주의 > 테이블 생성, 데이터 입력후 꼭 commit; 을 입력해야 데이터가 저장됨!!!!!!!!!!!!!!!
다) 시작화면(index.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>
<header>쇼핑몰 회원관리 ver 1.0</header>
<nav>
<ul>
<li><a href="regMember.jsp">회원등록</a></li>
<li><a href="memberList.jsp">회원목록 조회/수정</a></li>
<li><a href="salary.jsp">회원매출조회</a></li>
<li><a href="index.jsp">홈으로</a></li>
</ul>
</nav>
<section>
<h1>쇼핑몰 회원관리 프로그램</h1><br>
쇼핑몰 회원정보와 회원매출정보 데이터베이스를 구축하고 회원관리 프로그램을 작성하는 프로그램이다.<br>
프로그램 작성 순서<br>
1.회원정보 테이블을 생성한다.<br>
2.매출정보 테이블을 생성한다.<br>
3.회원정보, 매출정보 테이블에 제시된 문제지의 참조데이터를 추가 생성한다.<br>
4.회원정보 입력 화면프로그램을 작성한다.<br>
5.회원정보 조회 프로그램을 작성한다.<br>
6.회원매출정보 조회 프로그램을 작성한다.<br>
</section>
<br><br><br>
<footer>HRDKOREA Copyright....저작권 정보</footer>
</body>
</html>
2. 회원등록, 회원목록조회/수정
가) 회원등록 코드
1) regMember.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="DBPKG.DBcon"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="header.jsp" %>
<%
Connection con = DBcon.getConnction();
String sql = "SELECT max(custno)+1 FROM member_tbl_02";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
rs.next();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>홈쇼핑 회원 등록</h1>
<form name="frm" action="Regist" method="post">
<table border="1">
<tr>
<th>회원번호(자동발생)</th>
<td><input type="text" name="custno" value="<%=rs.getInt(1) %>"></td>
</tr>
<tr>
<th>회원성명</th>
<td><input type="text" name="custname"></td>
</tr>
<tr>
<th>회원전화</th>
<td><input type="text" name="phone"></td>
</tr>
<tr>
<th>회원주소</th>
<td><input type="text" name="address"></td>
</tr>
<tr>
<th>가입일자</th>
<td><input type="text" name="joindate"></td>
</tr>
<tr>
<th>고객등급(A:VIP,B:일반,C:직원)</th>
<td><input type="text" name="grade"></td>
</tr>
<tr>
<th>도시코드</th>
<td><input type="text" name="city"></td>
</tr>
<tr>
<th colspan="2">
<input type="submit" value="등록" onclick="return inputCheck()">
<input type="button" value="조회" onclick="return moveList()">
</th>
</tr>
</table>
</form>
</body>
<script>
function inputCheck(){
if(document.frm.custname.value.length == 0){
alert("회원성명이 입력되지 않았습니다.");
document.frm.custname.focus();
return false;
}
if(document.frm.phone.value.length == 0){
alert("회원전화가 입력되지 않았습니다.");
document.frm.phone.focus();
return false;
}
if(document.frm.address.value.length == 0){
alert("회원주소가 입력되지 않았습니다.");
document.frm.address.focus();
return false;
}
if(document.frm.joindate.value.length == 0){
alert("가입일자가 입력되지 않았습니다.");
document.frm.joindate.focus();
return false;
}
if(document.frm.grade.value.length == 0){
alert("고객등급이 입력되지 않았습니다.");
document.frm.grade.focus();
return false;
}
if(document.frm.city.value.length == 0){
alert("도시코드가 입력되지 않았습니다.");
document.frm.city.focus();
return false;
}
alert("회원등록이 완료되었습니다!!");
return true;
}
function moveList(){
location.href = "memberList.jsp";
}
</script>
</html>
2) Regist.java (Servlet)
package controller;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
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.DBcon;
@WebServlet("/Regist")
public class Regist extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String custno = request.getParameter("custno");
String custname = request.getParameter("custname");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String joindate = request.getParameter("joindate");
String grade = request.getParameter("grade");
String city = request.getParameter("city");
String sql = "INSERT INTO member_tbl_02 VALUES(?,?,?,?,?,?,?)";
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DBcon.getConnction();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, custno);
pstmt.setString(2, custname);
pstmt.setString(3, phone);
pstmt.setString(4, address);
pstmt.setString(5, joindate);
pstmt.setString(6, grade);
pstmt.setString(7, city);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
response.sendRedirect("memberList.jsp");
}
}
나) 회원목록조회/수정 코드
1) memberList.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="DBPKG.DBcon"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="header.jsp" %>
<%
Connection con = DBcon.getConnction();
String sql ="SELECT * FROM member_tbl_02";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원목록조회/수정</h1>
<table border="1">
<tr>
<th>회원번호</th>
<th>회원성명</th>
<th>전화번호</th>
<th>주소</th>
<th>가입일자</th>
<th>고객등급</th>
<th>거주지역</th>
</tr>
<%
while(rs.next()){
String grade = rs.getString("grade");
switch(grade){
case "A":
grade="VIP"; break;
case "B":
grade="일반"; break;
case "C":
grade="직원"; break;
}
%>
<tr>
<td><a href="modify.jsp?custno=<%=rs.getInt("custno") %>"><%=rs.getInt("custno") %></a></td>
<td><%=rs.getString("custname") %></td>
<td><%=rs.getString("phone") %></td>
<td><%=rs.getString("address") %></td>
<td><%=rs.getDate("joindate") %></td>
<td><%=grade %></td>
<td><%=rs.getString("city") %></td>
</tr>
<%
}
%>
</table>
</body>
</html>
3. 회원매출조회, 회원정보 수정
가) 회원매출조회 코드
1) salary.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="DBPKG.DBcon"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="header.jsp" %>
<%
Connection con = DBcon.getConnction();
String sql = "SELECT mem.custno, mem.custname, mem.grade, sum(mon.price) price "
+ "FROM member_tbl_02 mem, money_tbl_02 mon "
+ "WHERE mem.custno = mon.custno "
+ "GROUP BY mem.custno, mem.custname, mem.grade "
+ "ORDER BY price DESC";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원매출조회</h1>
<table border="1">
<tr>
<th>회원번호</th>
<th>회원성명</th>
<th>고객등급</th>
<th>매출</th>
</tr>
<%
while(rs.next()){
String grade = rs.getString("grade");
switch(grade){
case "A":
grade="VIP"; break;
case "B":
grade="일반"; break;
case "C":
grade="직원"; break;
}
%>
<tr>
<td><%=rs.getInt("custno") %></td>
<td><%=rs.getString("custname") %></td>
<td><%=grade %></td>
<td><%=rs.getInt("price") %></td>
</tr>
<%
}
%>
</table>
</body>
</html>
나) 회원정보수정 코드
1) modify.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="DBPKG.DBcon"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="header.jsp" %>
<%
Connection con = DBcon.getConnction();
String custno = request.getParameter("custno");
String sql = "SELECT * FROM member_tbl_02 WHERE custno =" + custno;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
rs.next();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>홈쇼핑 회원 정보 수정</h1>
<form name="frm" action="Modify" method="post">
<table border="1">
<tr>
<th>회원번호</th>
<td><input type="text" name="custno" value="<%=rs.getInt("custno") %>"></td>
</tr>
<tr>
<th>회원성명</th>
<td><input type="text" name="custname" value="<%=rs.getString("custname") %>"></td>
</tr>
<tr>
<th>회원전화</th>
<td><input type="text" name="phone" value="<%=rs.getString("phone") %>"></td>
</tr>
<tr>
<th>회원주소</th>
<td><input type="text" name="address" value="<%=rs.getString("address") %>"></td>
</tr>
<tr>
<th>가입일자</th>
<td><input type="text" name="joindate" value="<%=rs.getDate("joindate") %>"></td>
</tr>
<tr>
<th>고객등급(A:VIP,B:일반,C:직원)</th>
<td><input type="text" name="grade" value="<%=rs.getString("grade") %>"></td>
</tr>
<tr>
<th>도시코드</th>
<td><input type="text" name="city" value="<%=rs.getString("city") %>"></td>
</tr>
<tr>
<th colspan="2">
<input type="submit" value="수정" onclick="return Check()">
<input type="button" value="조회" onclick="return moveList()">
</th>
</tr>
</table>
</form>
</body>
<script>
function Check(){
alert("회원정보수정이 완료되었습니다.");
return true;
}
function moveList(){
location.href="memberList.jsp";
}
</script>
</html>
2) Modify.java(Servlet)
package controller;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
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.DBcon;
@WebServlet("/Modify")
public class Modify extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String custno = request.getParameter("custno");
String custname = request.getParameter("custname");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String joindate = request.getParameter("joindate");
String grade = request.getParameter("grade");
String city = request.getParameter("city");
String sql = "UPDATE member_tbl_02 SET custname=?, phone=?, address=?, joindate=?, grade=?, city=? WHERE custno=?";
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DBcon.getConnction();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, custname);
pstmt.setString(2, phone);
pstmt.setString(3, address);
pstmt.setString(4, joindate);
pstmt.setString(5, grade);
pstmt.setString(6, city);
pstmt.setString(7, custno);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
response.sendRedirect("memberList.jsp");
}
}
4. 프로젝트 목록(결과)
<자료>
1. 정보처리산업기사 작업형 문제원형.pdf
2. 전체 코드
'기타' 카테고리의 다른 글
| [ Chrome ] 크롬 단축키 모음 (0) | 2022.06.13 |
|---|---|
| [ Oracle, MariaDB ] like 연산자 (0) | 2022.06.13 |