이번 프로젝트는 로그인, 회원가입, 정보수정, 탈퇴, 로그아웃을 만들어 본다.
기능은 거의 전과 비슷하다.
폴더 구조는 다음과 같다.
전에 사용했던 기술들은 모두 사용한다.
사진에는 안나와 있지만 WEB-INF 아래 lib 폴더에 ojdbc6.jar 파일이 있다.
시작페이지(form)
로그인 폼이 있다.
------------------------------------------------------------------------------------------------------------------------------------
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>
<style type="text/css">
html{
height: 100%;
}
body {
background: skyblue;
height: 100%;
}
#main{
min-height: 90%;
}
h1{
color: navy;
}
#title{
color: orangered;
}
input[type='submit'] {
height: 50px;
background: orangered;
color: white;
border-radius: 10px;
}
</style>
</head>
<body>
<div id="main">
<h1 align="center">Welcome to <label id="title">둘리's World</label></h1>
<div>
<form action="<%=request.getContextPath() %>/login" method="post">
<table align="center">
<tr>
<td><label>ID</label></td>
<td colspan="2"><input type="text" name="userId"></td>
<td rowspan="2"><input type="submit" value="LOGIN"></td>
</tr>
<tr>
<td><label>PWD</label></td>
<td colspan="2"><input type="password" name="password"></td>
</tr>
<tr>
<td colspan="4"><a href="views/member/memberJoinForm.jsp">회원가입하기</a></td>
</tr>
<tr>
<td colspan="4"><a href="#">아이디 / 비밀번호 찾기</a></td>
</tr>
</table>
</form>
</div>
</div>
<%@ include file="views/common/footer.jsp" %>
</body>
</html>
------------------------------------------------------------------------------------------------------------------------------------
회원가입(form, servlet)
------------------------------------------------------------------------------------------------------------------------------------
memberJoinForm.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>
<h1 align="center">회원가입</h1>
<form action="<%= request.getContextPath() %>/insertMember" method="post">
<table align="center">
<tr>
<td><label>아이디</label></td>
<td><input type="text" name="userId"></td>
</tr>
<tr>
<td><label>비밀번호</label></td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td><label>비밀번호 확인</label></td>
<td><input type="password" name="password2"></td>
</tr>
<tr>
<td><label>이름</label></td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td><label>나이</label></td>
<td><input type="number" name="age"></td>
</tr>
<tr>
<td>성별</td>
<td>
<input type="radio" name="gender" value="M" id="male">
<label for="male">남</label>
<input type="radio" name="gender" value="F" id="female">
<label for="female">여</label>
</td>
</tr>
<tr>
<td><label>이메일</label></td>
<td><input type="email" name="email"></td>
</tr>
<tr>
<td><label>전화번호</label></td>
<td><input type="tel" name="phone"></td>
</tr>
<tr>
<td><label>주소</label></td>
<td><input type="text" name="address"></td>
</tr>
<tr>
<td><label>취미</label></td>
<td>
<input type="checkbox" name="hobby" value="운동" id="sports">
<label for="sports">운동</label>
<input type="checkbox" name="hobby" value="낚시" id="fishing">
<label for="sports">낚시</label>
<input type="checkbox" name="hobby" value="등산" id="climbing">
<label for="sports">등산</label>
<input type="checkbox" name="hobby" value="요리" id="cooking">
<label for="sports">요리</label>
</td>
</tr>
<tr align="center">
<td colspan="2">
<input type="reset" value="리셋">
<input type="submit" value="가입하기">
</td>
</tr>
</table>
</form>
</body>
</html>
InsertMemberServlet.java
package com.kh.w7.member.controller;
import java.io.IOException;
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 com.kh.w7.member.model.service.MemberService;
import com.kh.w7.member.model.vo.Member;
@WebServlet("/insertMember")
public class InsertMemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public InsertMemberServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 1. 인코딩
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
// 2. request에서 파라미터 값 꺼내기
String userId = request.getParameter("userId");
String password = request.getParameter("password");
String userName = request.getParameter("userName");
String gender = request.getParameter("gender");
int age = Integer.parseInt(request.getParameter("age"));
String email = request.getParameter("email");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String[] hobby = request.getParameterValues("hobby");
String hobbies = "";
for (int i = 0; i < hobby.length; i++) {
if (i == 0) {
hobbies += hobby[i];
}else {
hobbies += ", " + hobby[i];
}
}
// 3. Member 객체 생성
Member reqMember = new Member();
reqMember.setUserId(userId);
reqMember.setPassword(password);
reqMember.setUserName(userName);
reqMember.setGender(gender);
reqMember.setAge(age);
reqMember.setEmail(email);
reqMember.setPhone(phone);
reqMember.setAddress(address);
reqMember.setHobby(hobbies);
// 4. Service 로직으로 전달
int result = new MemberService().insertMember(reqMember);
if(result > 0) {
String page = "views/common/successPage.jsp";
request.getSession().setAttribute("msg", "회원 가입에 성공하셨습니다.");
response.sendRedirect(page);
}else {
String page = "views/common/errorPage.jsp";
request.setAttribute("msg", "회원 가입에 실패하셨습니다!");
RequestDispatcher view = request.getRequestDispatcher(page);
view.forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
------------------------------------------------------------------------------------------------------------------------------------
로그인(servlet)
------------------------------------------------------------------------------------------------------------------------------------
LoginServlet.java
package com.kh.w7.member.controller;
import java.io.IOException;
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 javax.servlet.http.HttpSession;
import com.kh.w7.member.model.service.MemberService;
import com.kh.w7.member.model.vo.Member;
@WebServlet("/login")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public LoginServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 1. 전송값에 한글이 있는 경우 인코딩 처리 해야 함
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
// 2. 전송값 꺼내서 변수에 기록하기
String userId = request.getParameter("userId");
String password = request.getParameter("password");
System.out.println("ID : " + userId);
System.out.println("PW : " + password);
Member reqMember = new Member();
reqMember.setUserId(userId);
reqMember.setPassword(password);
// 3. 서비스 로직 호출
Member loginUser = new MemberService().loginCheck(reqMember);
// 4. 받은 결과에 따라 뷰 페이지 내보내기
String page = "";
if (loginUser != null) { // 성공
HttpSession session = request.getSession();
session.setAttribute("loginUser", loginUser);
page = "views/main/main.jsp";
}else { // 실패
request.setAttribute("msg", "로그인 실패!");
page = "views/common/errorPage.jsp";
}
RequestDispatcher view = request.getRequestDispatcher(page);
view.forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
------------------------------------------------------------------------------------------------------------------------------------
메인 페이지, 성공, 에러 페이지(form), 로그아웃(servlet)
------------------------------------------------------------------------------------------------------------------------------------
main.jsp
<%@page import="com.kh.w7.member.model.vo.Member"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%-- <% Member loginUser = (Member)session.getAttribute("loginUser"); %> --%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%@ include file="../common/menubar.jsp" %>
<%-- <h1><% out.println(loginUser.getUserName()); %></h1> --%>
</body>
</html>
menubar.jsp(실질적인 메인)
<%@page import="com.kh.w7.member.model.vo.Member"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
Member loginUser = (Member)session.getAttribute("loginUser");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
div > button {
float: right;
}
</style>
</head>
<body>
<h1 align="center">메인화면</h1>
<h3 align="right"><%= loginUser.getUserName() %>님 환영합니다.</h3>
<div>
<button onclick="logout()">로그아웃</button>
<% if(!loginUser.getUserId().equals("admin")){ %>
<button onclick="location.href='/w7/views/member/memberUpdateForm.jsp'">내 정보 수정</button>
<% }else {%>
<button>관리자 메뉴로</button>
<% } %>
</div>
<script type="text/javascript">
function logout() {
var check = window.confirm("로그아웃 하시겠습니까?");
if(check == true){
location.href="<%= request.getContextPath() %>/logout";
}
}
</script>
</body>
</html>
errorPage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% String msg = (String)request.getAttribute("msg"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>에러 발생!</h1>
<h3><%= msg %></h3>
</body>
</html>
successPage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String msg = (String)session.getAttribute("msg");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1><%= msg %></h1>
<a href="/w7/index.jsp">메인으로 이동</a>
</body>
</html>
LogoutServlet.java
package com.kh.w7.member.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/logout")
public class LogoutServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public LogoutServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.getSession().invalidate();
response.sendRedirect("index.jsp");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
------------------------------------------------------------------------------------------------------------------------------------
정보수정, 탈퇴(form, servlet)
계정삭제는 서블릿을 따로 만들지 않고 UpdateForm 스크립트에 했다.
------------------------------------------------------------------------------------------------------------------------------------
memberUpdateForm.jsp
<%@page import="com.kh.w7.member.model.service.MemberService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="com.kh.w7.member.model.vo.Member"%>
<% Member loginUser = (Member)session.getAttribute("loginUser"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<body>
<h1 align="center"><%= loginUser.getUserName() %>님의 회원 정보 수정<button onclick="deleteMember()">탈퇴하기</button></h1>
<script type="text/javascript">
function deleteMember() {
<%
int result = 0;
result = new MemberService().deleteMember(loginUser);
if(result > 0){
session.invalidate();
%>
alert("계정이 삭제되었습니다.");
window.location.href = '../../index.jsp';
<%
}else{
%>
alert("계정 삭제에 실패했습니다.");
<%
}
%>
}
</script>
<form action="<%= request.getContextPath() %>/updateMember" method="post">
<table align="center">
<tr>
<td><label>아이디</label></td>
<td><input type="text" name="userId" value="<%= loginUser.getUserId() %>" readonly></td>
</tr>
<tr>
<td><label>비밀번호</label></td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td><label>비밀번호 확인</label></td>
<td><input type="password" name="password2"></td>
</tr>
<tr>
<td><label>이름</label></td>
<td><input type="text" name="userName" value="<%= loginUser.getUserName() %>"></td>
</tr>
<tr>
<td><label>나이</label></td>
<td><input type="number" name="age" value="<%= loginUser.getAge() %>"></td>
</tr>
<tr>
<td>성별</td>
<td>
<% if(loginUser.getGender().equals("M")){%>
<input type="radio" name="gender" value="M" id="male" checked>
<label for="male">남</label>
<input type="radio" name="gender" value="F" id="female">
<label for="female">여</label>
<% }else { %>
<input type="radio" name="gender" value="M" id="male">
<label for="male">남</label>
<input type="radio" name="gender" value="F" id="female" checked>
<label for="female">여</label>
<% } %>
</td>
</tr>
<tr>
<td><label>이메일</label></td>
<td><input type="email" name="email" value="<%= loginUser.getEmail() %>"></td>
</tr>
<tr>
<td><label>전화번호</label></td>
<td><input type="tel" name="phone" value="<%= loginUser.getPhone() %>"></td>
</tr>
<tr>
<td><label>주소</label></td>
<td><input type="text" name="address" value="<%= loginUser.getAddress() %>"></td>
</tr>
<tr>
<td><label>취미</label></td>
<td>
<input type="checkbox" name="hobby" value="운동" id="sports">
<label for="sports">운동</label>
<input type="checkbox" name="hobby" value="낚시" id="fishing">
<label for="sports">낚시</label>
<input type="checkbox" name="hobby" value="등산" id="climbing">
<label for="sports">등산</label>
<input type="checkbox" name="hobby" value="요리" id="cooking">
<label for="sports">요리</label>
</td>
</tr>
<tr align="center">
<td colspan="2">
<input type="reset" value="리셋">
<input type="submit" value="변경하기">
</td>
</tr>
</table>
<script type="text/javascript">
$(function() {
var arr = '<%= loginUser.getHobby() %>'.split(", ");
$("input[name=hobby]").each(function() {
for (var i = 0; i < arr.length; i++) {
if ($(this).val() === arr[i]) {
$(this).attr("checked", true);
}else{
}
}
});
});
</script>
</form>
</body>
</html>
UpdateMemberServlet.java
package com.kh.w7.member.controller;
import java.io.IOException;
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 javax.servlet.http.HttpSession;
import com.kh.w7.member.model.service.MemberService;
import com.kh.w7.member.model.vo.Member;
@WebServlet("/updateMember")
public class UpdateMemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public UpdateMemberServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//인코딩
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
//request객체에서 parameter꺼내기
String userId = request.getParameter("userId");
String password = request.getParameter("password");
String userName = request.getParameter("userName");
String gender = request.getParameter("gender");
int age = Integer.parseInt(request.getParameter("age"));
String email = request.getParameter("email");
String phone = request.getParameter("phone");
String address = request.getParameter("address");
String[] hobby = request.getParameterValues("hobby");
String hobbies = "";
for (int i = 0; i < hobby.length; i++) {
if (i == 0) {
hobbies += hobby[i];
}else {
hobbies += ", " + hobby[i];
}
}
//reqMember 객체 생성
Member reqMember = new Member(userId, password, userName, gender, age, email, phone, address, hobbies);
//service 로직으로 전달
int result = new MemberService().updateMember(reqMember);
//처리 결과에 따른 뷰 페이지 결정
if (result > 0) {
HttpSession session = request.getSession();
session.setAttribute("loginUser", reqMember);
response.sendRedirect("views/member/memberUpdateForm.jsp");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
------------------------------------------------------------------------------------------------------------------------------------
모델(vo, service, dao)
------------------------------------------------------------------------------------------------------------------------------------
Member.java
package com.kh.w7.member.model.vo;
import java.sql.Date;
public class Member implements java.io.Serializable{
private String userId;
private String password;
private String userName;
private String gender;
private int age;
private String email;
private String phone;
private String address;
private String hobby;
private Date enrollDate;
public Member() {}
public Member(String userId, String password, String userName, String gender, int age, String email, String phone,
String address, String hobby, Date enrollDate) {
super();
this.userId = userId;
this.password = password;
this.userName = userName;
this.gender = gender;
this.age = age;
this.email = email;
this.phone = phone;
this.address = address;
this.hobby = hobby;
this.enrollDate = enrollDate;
}
public Member(String userId, String password, String userName, String gender, int age, String email, String phone,
String address, String hobby) {
super();
this.userId = userId;
this.password = password;
this.userName = userName;
this.gender = gender;
this.age = age;
this.email = email;
this.phone = phone;
this.address = address;
this.hobby = hobby;
}
@Override
public String toString() {
return "Member [userId=" + userId + ", password=" + password + ", userName=" + userName + ", gender=" + gender
+ ", age=" + age + ", email=" + email + ", phone=" + phone + ", address=" + address + ", hobby=" + hobby
+ ", enrollDate=" + enrollDate + "]";
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
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 getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public Date getEnrollDate() {
return enrollDate;
}
public void setEnrollDate(Date enrollDate) {
this.enrollDate = enrollDate;
}
}
MemberService.java
package com.kh.w7.member.model.service;
import java.sql.Connection;
import com.kh.w7.member.model.dao.MemberDao;
import com.kh.w7.member.model.vo.Member;
import static com.kh.w7.common.JDBCTemplate.*;
public class MemberService {
// 로그인용 메소드
public Member loginCheck(Member reqMember) {
Connection con = getConnection();
System.out.println(con);
Member loginUser = new MemberDao().loginCheck(con, reqMember);
close(con);
return loginUser;
}
public int insertMember(Member reqMember) {
Connection con = getConnection();
System.out.println(con);
int result = new MemberDao().insertMember(con, reqMember);
if (result > 0) {
commit(con);
}else {
rollback(con);
}
close(con);
return result;
}
public int updateMember(Member reqMember) {
Connection con = getConnection();
System.out.println(con);
int result = new MemberDao().updateMember(con, reqMember);
if (result > 0) {
commit(con);
}else {
rollback(con);
}
close(con);
return result;
}
public int deleteMember(Member reqMember) {
Connection con = getConnection();
System.out.println(con);
int result = new MemberDao().deleteMember(con, reqMember.getUserId());
if (result > 0) {
commit(con);
}else {
rollback(con);
}
close(con);
return result;
}
}
MemberDao.java
package com.kh.w7.member.model.dao;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.kh.w7.member.model.vo.Member;
import static com.kh.w7.common.JDBCTemplate.*;
public class MemberDao {
private Properties prop = new Properties();
public MemberDao() {
String fileName = MemberDao.class.getResource("/sql/member/member-query.properties").getPath();
try {
prop.load(new FileReader(fileName));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public Member loginCheck(Connection con, Member reqMember) {
PreparedStatement pstmt = null;
ResultSet rset = null;
Member loginUser = null;
String query = prop.getProperty("loginCheck");
try {
pstmt = con.prepareStatement(query);
pstmt.setString(1, reqMember.getUserId());
pstmt.setString(2, reqMember.getPassword());
rset = pstmt.executeQuery();
if (rset.next()) {
loginUser = new Member();
loginUser.setUserId(rset.getString("USER_ID"));
loginUser.setPassword(rset.getString("PASSWORD"));
loginUser.setUserName(rset.getString("USER_NAME"));
loginUser.setGender(rset.getString("GENDER"));
loginUser.setAge(rset.getInt("AGE"));
loginUser.setEmail(rset.getString("EMAIL"));
loginUser.setPhone(rset.getString("PHONE"));
loginUser.setAddress(rset.getString("ADDRESS"));
loginUser.setHobby(rset.getString("HOBBY"));
loginUser.setEnrollDate(rset.getDate("ENROLL_DATE"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(rset);
close(pstmt);
}
return loginUser;
}
public int insertMember(Connection con, Member reqMember) {
PreparedStatement pstmt = null;
int result = 0;
String query = prop.getProperty("insertMember");
try {
pstmt = con.prepareStatement(query);
pstmt.setString(1, reqMember.getUserId());
pstmt.setString(2, reqMember.getPassword());
pstmt.setString(3, reqMember.getUserName());
pstmt.setString(4, reqMember.getGender());
pstmt.setInt(5, reqMember.getAge());
pstmt.setString(6, reqMember.getEmail());
pstmt.setString(7, reqMember.getPhone());
pstmt.setString(8, reqMember.getAddress());
pstmt.setString(9, reqMember.getHobby());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(pstmt);
}
return result;
}
public int updateMember(Connection con, Member reqMember) {
PreparedStatement pstmt = null;
int result = 0;
String query = prop.getProperty("updateMember");
try {
pstmt = con.prepareStatement(query);
pstmt.setString(1, reqMember.getPassword());
pstmt.setString(2, reqMember.getUserName());
pstmt.setString(3, reqMember.getGender());
pstmt.setInt(4, reqMember.getAge());
pstmt.setString(5, reqMember.getEmail());
pstmt.setString(6, reqMember.getPhone());
pstmt.setString(7, reqMember.getAddress());
pstmt.setString(8, reqMember.getHobby());
pstmt.setString(9, reqMember.getUserId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(pstmt);
}
return result;
}
public int deleteMember(Connection con, String userId) {
PreparedStatement pstmt = null;
int result = 0;
String query = prop.getProperty("deleteMember");
try {
pstmt = con.prepareStatement(query);
pstmt.setString(1, userId);
result = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(pstmt);
}
return result;
}
}
------------------------------------------------------------------------------------------------------------------------------------
그 외 (Properties, Template, footer)
------------------------------------------------------------------------------------------------------------------------------------
JDBCTemplate.java
package com.kh.w7.common;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTemplate {
public static Connection getConnection() {
Connection conn = null;
Properties prop = new Properties();
String fileName = JDBCTemplate.class.getResource("/sql/driver.properties").getPath();
System.out.println(fileName);
try {
prop.load(new FileReader(fileName));
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String password = prop.getProperty("password");
// 아래 클래스 구문은 신버전에서는 자동으로 추가되어서 생략해도 되지만
// 구버전 호환을 위해 명시해두자.
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
System.out.println(conn);
} catch (Exception e) {
e.printStackTrace();
}
// prop 파일을 수정할 때 classes 폴더 아래의 파일을 수정하면 서버 껏따 킬때 재생성 되기 때문에 사라진다.
// 그리고 바로바로 수정사항이 적용이 안될 때도 있는데 메뉴바 상단의 Project - clean을 실행하면 강제 재컴파일 시켜준다.
return conn;
}
public static void close(Connection con) {
try {
if(con != null && !con.isClosed()) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement stmt) {
try {
if(stmt != null && !stmt.isClosed()) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rset) {
try {
if(rset != null && !rset.isClosed()) {
rset.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void commit(Connection con) {
try {
if(con != null && !con.isClosed()) {
con.commit();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback(Connection con) {
try {
if(con != null && !con.isClosed()) {
con.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
driver.properties
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
user=jsp
password:jsp
member-query.properties
loginCheck=SELECT * FROM member WHERE user_id=? AND password=?
insertMember=INSERT INTO member VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)
updateMember=UPDATE member SET password = ?, user_name = ?, gender = ?, age = ?, email = ?, phone = ?, address = ?, hobby = ? WHERE user_id = ?
deleteMember=DELETE FROM member WHERE user_id = ?
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>
<style type="text/css">
hr {
color: gray;
}
p {
text-align: center;
}
</style>
</head>
<body>
<hr>
<p>Copyright, All right reserved</p>
</body>
</html>
------------------------------------------------------------------------------------------------------------------------------------
특별한 기능은 없다.
전부 전에 설명했던 기능들을 짜집기하여 만든 예시이다.
로그아웃 시 Session을 invalidate 하여 삭제한다.
images.zip
