[정보처리산업기사 과정평가형 실기] 홈쇼핑 회원관리 - 전체소스

( 데이터베이스 👉🏻 서버 👉🏻 프론트 👉🏻 백엔드 ) [ OPEN ]

create table member_tbl_02 (
custno number(6) not null primary key,
custname varchar2(20),
phone varchar2(13),
address varchar2(60),
joindate date,
grade char(1),
city char(2)
);

create table money_tbl_02 (
custno number(6),
salenol number(8),
pcost number(8),
amount number(4),
price number(8),
pcode varchar2(4),
sdate date,
CONSTRAINT money_pk PRIMARY KEY (custno,salenol)
);

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');

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','20160106');

( 데이터베이스 👉🏻 서버 👉🏻 프론트 👉🏻 백엔드 ) [ OPEN ]

 

controller/Controller.java (*Servlet file 생성)

package controller;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;
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 DAO.MemberDAO;

@WebServlet("/")
public class Controller extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPro(request, response);		
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {	
		doPro(request, response);
	}
	protected void doPro(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String uri = request.getRequestURI();
		String context = request.getContextPath();
		String command = uri.substring(context.length());
		String site = null;
		
		System.out.println("command:"+command);
		
		MemberDAO member = new MemberDAO();
		
		switch(command) {
		case "/home" :
			
			site = "index.jsp";
			break;
		
		case "/add" :		
			site = member.nextCustno(request, response);
			break;
		
		case "/list" :			
			site = member.selectAll(request, response);
			break;
		
		case "/result" :		
			site = member.selectResult(request, response);
			break;
		case "/insert" :		
			site = member.insert(request, response);
			
			break;
		case "/modify" :		
			site = member.modify(request, response);
			break;
		case "/update" :
			int result1 = member.update(request, response);
			response.setContentType("text/html; charset=UTF-8");
			PrintWriter out = response.getWriter();
			if(result1 == 1) {
				out.println("<script>");
				out.println(" alert('회원수정이 완료 되었습니다!'); location.href='"+context+"';  ");
				out.println("</script>");
				out.flush();
			}else {
				out.println("<script>");
				out.println("alert('수정실패!'); location.href='"+context+"'; ");
				out.println("</script>");
				out.flush();
			}
			break;
		case "/delete" :
			int result2 = member.delete(request, response);
			response.setContentType("text/html; charset=UTF-8");
			out = response.getWriter();
			if(result2 == 1) {
				out.println("<script>");
				out.println(" alert('회원삭제가 완료 되었습니다!'); location.href='"+context+"';  ");
				out.println("</script>");
				out.flush();
			}else {
				out.println("<script>");
				out.println("alert('삭제실패!'); location.href='"+context+"'; ");
				out.println("</script>");
				out.flush();
			}
			break;
		}
		
		RequestDispatcher dispatcher = request.getRequestDispatcher(site);
		dispatcher.forward(request,  response);
	}
}

DTO/Member.java

package DTO;

public class Member {

	int custno;
	String custname;
	String phone;
	String address;
	String joindate;
	String grade;
	String city;

	public int getCustno() {
		return custno;
	}

	public void setCustno(int custno) {
		this.custno = custno;
	}

	public String getCustname() {
		return custname;
	}

	public void setCustname(String custname) {
		this.custname = custname;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getJoindate() {
		return joindate;
	}

	public void setJoindate(String joindate) {
		this.joindate = joindate;
	}

	public String getGrade() {
		return grade;
	}

	public void setGrade(String grade) {
		this.grade = grade;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}
}

DTO/Money.java

package DTO;

public class Money {
	
	int custno;
	String custname;
	String grade;
	int price;

	public int getCustno() {
		return custno;
	}

	public void setCustno(int custno) {
		this.custno = custno;
	}

	public String getCustname() {
		return custname;
	}

	public void setCustname(String custname) {
		this.custname = custname;
	}

	public String getGrade() {
		return grade;
	}

	public void setGrade(String grade) {
		this.grade = grade;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}
}

WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app>
<servlet-mapping>
<servlet-name>default</servlet-name>
<url-pattern>*.js</url-pattern>
<url-pattern>*.css</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>jsp</servlet-name>
<url-pattern>*.jsp</url-pattern>
<url-pattern>*.jspx</url-pattern>
</servlet-mapping>
</web-app>

 tomcat9/lib/servlet-api.jar

( 데이터베이스 👉🏻 서버 👉🏻 프론트 👉🏻 백엔드 ) [ OPEN ]

 

index.jsp (메인)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
<link rel="stylesheet" href="style.css?ver=1">
</head>
<body>
<%@ include file="topmenu.jsp" %>
<section>
<div class="title">
쇼핑몰 회원관리 프로그램
</div>
<div class="main">
쇼핑몰 회원정보와 회원매출정보 데이터베이스를 구축하고 회원관리 프로그램을 작성하는 프로그램이다.<br>
프로그램 작성 순서
<ul>
	<li>회원정보 테이블을 생성한다.</li>
	<li>회원정보, 매출정보 테이블에 제시된 문제지의 참조데이터를 추가 생성한다.</li>
	<li>회원정보 입력 하면프로그램을 작성한다.</li>
	<li>회원정보 조회 프로그램을 작성한다.</li>
	<li>회원매출정보 조회 프로그램을 작성한다.</li>
</ul>
</div>
</section>
<%@ include file="footer.jsp" %>
</body>
</html>

add.jsp (회원등록)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="DTO.Member" %>
<%
request.setCharacterEncoding("UTF-8");
int custno = 0;
custno = (Integer)request.getAttribute("custno");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
<script src="script.js"></script>
<link rel="stylesheet" href="style.css?ver=1">
</head>
<body>
<%@ include file="topmenu.jsp" %>
<section>
<div class="title">
홈쇼핑 회원 등록
</div>
<form name="frm" action="insert">
<input type="hidden" id="GUBUN" value="insert" >
<div class="wrapper">
<table>
	<tr>
		<th>회원번호(자동발생)</th>
		<td><input name="custno" value="<%=custno %>" readonly value="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>
		<td colspan="2">
		<button class="btn" type="submit" onclick="fn_submit(); return false;">등 록</button>
		<button class="btn" type="button" onclick="location='list'">조 회</button>
		</td>
	</tr>
</table>
</div>
</form>
</section>
<%@ include file="footer.jsp" %>
</body>
</html>

list.jsp (회원목록조회/수정)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import = "java.util.*" %>
<%@ page import = "DTO.Member" %>
<%
request.setCharacterEncoding("UTF-8");
ArrayList<Member>list = new ArrayList<Member>();
list = (ArrayList<Member>)request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
<link rel="stylesheet" href="style.css?ver=1">
</head>
<body>
<%@ include file="topmenu.jsp" %>
<section>
<div class="title">
쇼핑몰 회원관리 프로그램
</div>
<div class="wrapper">
<table style="width:900px">
<tr>
	<th>회원번호</th>
	<th>회원성명</th>
	<th>전화번호</th>
	<th>주소</th>
	<th>가입일자</th>
	<th>고객등급</th>
	<th>거주지역</th>
	<th>삭제</th>
</tr>
<%for(int i=0; i<list.size(); i++){ %>
<tr>
	<td><a href="modify?custno=<%=list.get(i).getCustno() %>">
	<%=list.get(i).getCustno() %></a></td>
	<td><%=list.get(i).getCustname() %></td>
	<td><%=list.get(i).getPhone() %></td>
	<td><%=list.get(i).getAddress() %></td>
	<td><%=list.get(i).getJoindate() %></td>
	<td><%=list.get(i).getGrade() %></td>
	<td><%=list.get(i).getCity() %></td>
	<td><a href="delete?custno=<%=list.get(i).getCustno() %>">
	Delete</a></td>
</tr>
<%}%>
</table>
</div>
</section>
<%@ include file="footer.jsp" %>
</body>
</html>

result.jsp (회원매출조회)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import = "java.util.*" %>
<%@ page import = "DTO.Money" %>
<%
request.setCharacterEncoding("UTF-8");
ArrayList<Money>list = new ArrayList<Money>();
list = (ArrayList<Money>)request.getAttribute("list");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
<link rel="stylesheet" href="style.css?ver=1">
</head>
<body>
<%@ include file="topmenu.jsp" %>
<section>
<div class="title">
회원매출조회
</div>
<div class="wrapper">
<table>
<tr>
	<th>회원번호</th>
	<th>회원성명</th>
	<th>고객등급</th>
	<th>매출</th>
</tr>
<%
for(int i=0; i<list.size(); i++){
%>
<tr>
	<td><%=list.get(i).getCustno() %></td>
	<td><%=list.get(i).getCustname() %></td>
	<td><%=list.get(i).getGrade() %></td>
	<td><%=list.get(i).getPrice() %></td>
</tr>
<%}%>
</table>
</div>
</section>
<%@ include file="footer.jsp" %>
</body>
</html>

modify.jsp (회원수정)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="DTO.Member" %>
<%
request.setCharacterEncoding("UTF-8");
ArrayList<Member>list = new ArrayList<Member>();
list = (ArrayList<Member>)request.getAttribute("list");		
		
int custno = list.get(0).getCustno();
String custname = list.get(0).getCustname();
String phone = list.get(0).getPhone();
String address = list.get(0).getAddress();
String joindate = list.get(0).getJoindate();
String grade = list.get(0).getGrade();
String city = list.get(0).getCity();

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main</title>
<script src="script.js"></script>
<link rel="stylesheet" href="style.css?ver=1">
</head>
<body>
<%@ include file="topmenu.jsp" %>
<section>
<div class="title">
홈쇼핑 회원 정보 수정
</div>
<form name="frm" action="update">
<input type="hidden" id="GUBUN" value="update" >
<div class="wrapper">
<table>
	<tr>
		<th>회원번호(자동발생)</th>
		<td><input name="custno" value="<%=custno %>" readonly value="1"></td>
	</tr>
	<tr>
		<th>회원성명</th>
		<td><input type="text" name="custname" value="<%=custname%>"></td>
	</tr>
	<tr>
		<th>회원전화</th>
		<td><input type="text" name="phone" value="<%=phone%>"></td>
	</tr>
	<tr>
		<th>회원주소</th>
		<td><input type="text" name="address" value="<%=address%>"></td>
	</tr>
	<tr>
		<th>가입일자</th>
		<td><input type="text" name="joindate" value="<%=joindate%>"></td>
	</tr>
	<tr>
		<th>고객등급[A:VIP,B:일반,C:직원]</th>
		<td><input type="text" name="grade" value="<%=grade%>"></td>
	</tr>
	<tr>
		<th>도시코드</th>
		<td><input type="text" name="city" value="<%=city%>"></td>
	</tr>
	<tr>
		<td colspan="2">
		<button class="btn" type="submit" onclick="fn_submit(); return false;">수 정</button>
		<button class="btn" type="button" onclick="location='list'">조 회</button>
		</td>
	</tr>
</table>
</div>
</form>
</section>
<%@ include file="footer.jsp" %>
</body>
</html>

topmenu.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="add">회원등록</a></li>
	<li><a href="list">회원목록조회/수정</a></li>
	<li><a href="result">회원매출조회</a></li>
	<li><a href="home">홈으로</a></li>
</ul>
</nav>
</body>
</html>

footer.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>
<footer>
HRDKOREA Copyright@2016 All rights reserved. Human Resources Development Service of Korea
</footer>
</body>
</html>

style.css

@charset "UTF-8";
*{
	margin: 0;
	padding: 0;
}
body{
	font-family: gulim;
}
header{
	top: 0;
	height: 150px;
	line-height: 150px;
	width: 100%;
	background: #333;
	text-align: center;
	font-size: 35px;
	font-weight: 900;
	color: #fff;
}
nav ul{
	height: 50px;
	line-height: 50px;
	width: 100%;
	background: #444;
}
nav ul li{
	list-style: none;
	float: left;
	padding: 0 10px;
}
a{
	text-decoration: none;
	color: #fff;
}
a:hover{
	cursor: pointer;
	opacity: 0.3;
}
section{
	position: absolute;
	overflow: auto;
	width: 100%;
	height: 100%;
	background: #e3e5e5;
}
.main{
	margin: 0 2rem;
}
.main ul li{
	list-style: number;
}
.title{
	font-size: 30px;
	font-weight: 900;
	text-align: center;
	margin: 3rem;
}
.wrapper{
	display: flex;
	justify-content: center;
	align-items: center;
	text-align: center;
}
table{
	width: 600px;
}
table,tr,th,td{
	border: 1px solid #333;
	border-collapse: collapse;
	padding: 0.7rem;
}
td input{
	float: left;
	padding: 0.3rem;
}
.btn{
	padding: 0.4rem 1rem;
	border-radius: 0.4rem;
	border: 0;
}
.btn:hover{
	opacity: 0.6;
	cursor: pointer;
}

footer{
	position: fixed;
	bottom: 0;
	width: 100%;
	height: 50px;
	line-height: 50px;
	text-align: center;
	background: #333;
}

script.js

function fn_submit(){
	var fn = document.frm;
	if(fn.custname.value == ""){
		alert("회원성명이 입력되지 않았습니다.");
		fn.custname.focus();
		return false;
	}
	if(fn.phone.value == ""){
		alert("회원전화가 입력되지 않았습니다.");
		fn.phone.focus();
		return false;
	}
	if(fn.address.value == ""){
		alert("회원주소가 입력되지 않았습니다.");
		fn.address.focus();
		return false;
	}
	if(fn.joindate.value == ""){
		alert("가입일자가 입력되지 않았습니다.");
		fn.joindate.focus();
		return false;
	}
	if(fn.grade.value == ""){
		alert("고객등급이 입력되지 않았습니다.");
		fn.grade.focus();
		return false;
	}
	if(fn.city.value == ""){
		alert("도시코드가 입력되지 않았습니다.");
		fn.city.focus();
		return false;
	}
	fn.submit();
}

( 데이터베이스 👉🏻 서버 👉🏻 프론트 👉🏻 백엔드 ) [ OPEN ]

DAO/MemberDAO.java

package DAO;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import DTO.Member;
import DTO.Money;

public class MemberDAO {
	
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;	 
	
	public static Connection getConnection() throws Exception{
		Class.forName("oracle.jdbc.OracleDriver");
		Connection con = DriverManager.getConnection
				("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
		return con;
	}
	
	public String nextCustno(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
			conn = getConnection();
			// 회원번호+1 가져오기
			String sql = "SELECT max(custno)+1 custno FROM member_tbl_02";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int custno = 0;
			if(rs.next()) custno = rs.getInt(1);
			
			request.setAttribute("custno", custno);
			conn.close();
			ps.close();
			rs.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return "add.jsp";
	}
	
	public String selectAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ArrayList<Member>list = new ArrayList<Member>();
		try {
			conn = getConnection();
			// 회원리스트 가져오기
			String sql = " SELECT ";
				   sql+= " custno, ";
				   sql+= " custname, ";
				   sql+= " phone, ";
				   sql+= " address, ";
				   sql+= " TO_CHAR(joindate,'YYYY-MM-DD') joindate, ";
				   sql+= " DECODE(grade,'A','VIP','B','일반','직원') grade, ";
				   sql+= " city ";
				   sql+= " FROM member_tbl_02 ";
				   sql+= " ORDER BY custno";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Member member = new Member();
				member.setCustno(rs.getInt(1));
				member.setCustname(rs.getString(2));
				member.setPhone(rs.getString(3));
				member.setAddress(rs.getString(4));
				member.setJoindate(rs.getString(5));
				member.setGrade(rs.getString(6));
				member.setCity(rs.getString(7));
				list.add(member);
			}		
			request.setAttribute("list",list);
			
			conn.close();
			ps.close();
			rs.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return "list.jsp";
	}
	public String modify(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ArrayList<Member>list = new ArrayList<Member>();
		try {
			conn = getConnection();
			int custno = Integer.parseInt(request.getParameter("custno"));
			// 수정할 회원정보 가져오기
			String sql = " SELECT ";
			       sql+= " custname, ";
			       sql+= " phone, ";
			       sql+= " address, ";
			       sql+= " TO_CHAR(joindate,'YYYY-MM-DD') joindate, ";
			       sql+= " grade, ";
			       sql+= " city ";
			       sql+= " FROM member_tbl_02 WHERE custno ="+custno;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
			Member member = new Member();
			member.setCustno(custno);
			member.setCustname(rs.getString(1));
			member.setPhone(rs.getString(2));
			member.setAddress(rs.getString(3));
			member.setJoindate(rs.getString(4));
			member.setGrade(rs.getString(5));
			member.setCity(rs.getString(6));
			list.add(member);
			}
				
			request.setAttribute("list",list);	
			request.setAttribute("custno", custno);
			
			conn.close();
			ps.close();
			rs.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return "modify.jsp";
	}
	public String selectResult(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ArrayList<Money>list = new ArrayList<Money>();
		try {
			conn = getConnection();
			// 회원번호+1 가져오기
			String sql = "SELECT ";
			       sql+= "m1.custno, ";
			       sql+= "m1.custname, ";
			       sql+= "DECODE(m1.grade,'A','VIP','B','일반','직원')grade, ";
			       sql+= "SUM(m2.price)price ";
			       sql+= "FROM member_tbl_02 m1, money_tbl_02 m2 ";
			       sql+= "WHERE m1.custno = m2.custno ";
			       sql+= "GROUP BY (m1.custno, m1.custname, grade) ";
			       sql+= "ORDER BY price DESC";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Money money = new Money();
				money.setCustno(rs.getInt(1));
				money.setCustname(rs.getString(2));
				money.setGrade(rs.getString(3));
				money.setPrice(rs.getInt(4));
				list.add(money);
			}
			request.setAttribute("list", list);
			
			conn.close();
			ps.close();
			rs.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return "result.jsp";
	}
	public String insert(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int custno = Integer.parseInt(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");
		int result = 0;
		try {
			conn = getConnection();
			// prepareStatement는 순서에 따라 값이 등록된다.
			String sql = "INSERT INTO member_tbl_02 VALUES(?,?,?,?,TO_DATE(?,'YYYY-MM-DD'),?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, custno);
			ps.setString(2, custname);
			ps.setString(3, phone);
			ps.setString(4, address);
			ps.setString(5, joindate);
			ps.setString(6, grade);
			ps.setString(7, city);
			result = ps.executeUpdate();	
			System.out.println(result);
			conn.close();
			ps.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return "add";
	}
	public int update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int custno = Integer.parseInt(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");
		int result = 0;
		try {
			conn = getConnection();
			
			String sql = "UPDATE member_tbl_02 SET";
				   sql+= " custname = ? , ";
				   sql+= " phone = ? , ";
				   sql+= " address = ? , ";
				   sql+= " joindate = TO_DATE(?,'YYYY-MM-DD'), ";
				   sql+= " grade = ? , ";
				   sql+= " city = ? ";
				   sql+= " WHERE custno = ? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, custname);
			ps.setString(2, phone);
			ps.setString(3, address);
			ps.setString(4, joindate);
			ps.setString(5, grade);
			ps.setString(6, city);
			ps.setInt(7, custno);
			result = ps.executeUpdate();	
			
			conn.close();
			ps.close();
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public int delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int result = 0;
		try {
			conn = getConnection();
			String custno = request.getParameter("custno");
			String sql = "DELETE FROM member_tbl_02 WHERE custno="+custno;
			ps = conn.prepareStatement(sql);
			result = ps.executeUpdate();
			
			conn.close();
			ps.close();			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}

 

---

 warfile 다운로드 

HRD2.war

2.77MB

war 파일 이클립스에서 import 하기 ( 오른쪽마우스 -> import -> war file -> war file 불러오기 -> finish )