๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
IT์ž๊ฒฉ์ฆ ๊ณต๋ถ€/์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ์‹ค๊ธฐ

[์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ๊ณผ์ •ํ‰๊ฐ€ํ˜• ์‹ค๊ธฐ] ํ™ˆ์‡ผํ•‘ ํšŒ์›๊ด€๋ฆฌ - ์ „์ฒด์†Œ์Šค

 by yunamom 2022. 5. 18.
๋ฐ˜์‘ํ˜•

( ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๐Ÿ‘‰๐Ÿป ์„œ๋ฒ„ ๐Ÿ‘‰๐Ÿป ํ”„๋ก ํŠธ ๐Ÿ‘‰๐Ÿป ๋ฐฑ์—”๋“œ ) [ OPEN ]

( ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๐Ÿ‘‰๐Ÿป ์„œ๋ฒ„ ๐Ÿ‘‰๐Ÿป ํ”„๋ก ํŠธ ๐Ÿ‘‰๐Ÿป ๋ฐฑ์—”๋“œ ) [ 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 )

 

300x250
์ €์ž‘์žํ‘œ์‹œ

'IT์ž๊ฒฉ์ฆ ๊ณต๋ถ€ > ์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ์‹ค๊ธฐ' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

[์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ๊ณผ์ •ํ‰๊ฐ€ํ˜• ์‹ค๊ธฐ] ๊ณจํ”„์žฅ ํšŒ์›๊ด€๋ฆฌ ํ”„๋กœ๊ทธ๋žจ - JSP  (10) 2022.07.01
[๊ณผ์ •ํ‰๊ฐ€ํ˜• ์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ์‹ค๊ธฐ] ๊ณจํ”„์—ฐ์Šต์žฅ ํšŒ์›๊ด€๋ฆฌ - ์—ฐ์Šต๋ฌธ์ œ  (2) 2022.06.13
[๊ณผ์ •ํ‰๊ฐ€ํ˜•] ์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ์‹ค๊ธฐ - ํ›„๊ธฐ  (9) 2022.05.10
[์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ๊ณผ์ •ํ‰๊ฐ€ํ˜• ์‹ค๊ธฐ] ์ง€์—ญ๊ตฌ์˜์›ํˆฌํ‘œ - ๋งˆ๋ฌด๋ฆฌ  (0) 2022.04.29
[์ •๋ณด์ฒ˜๋ฆฌ์‚ฐ์—…๊ธฐ์‚ฌ ๊ณผ์ •ํ‰๊ฐ€ํ˜• ์‹ค๊ธฐ] ์ง€์—ญ๊ตฌ์˜์›ํˆฌํ‘œ - ๋ฐ์ดํ„ฐ ์ž…/์ถœ๋ ฅ  (0) 2022.04.28

๊ด€๋ จ๊ธ€

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”

์ฝ”๋“œ