✿∘˚˳°∘°

52일차 : DB연결 본문

국비수업/Server

52일차 : DB연결

_HYE_ 2023. 2. 10. 17:10

20230210

 

0. DB연결하기

JDBCTemplate.java : jdbc수업 시 사용했던 java파일

(connection, preparedstatement, resultset, commit, rollback에 대한 항목들이 있음)

ojdbc6.jar : 자바와 연결하기 위한파일 

경로 - C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext

JDBCTemplate / ojdbc6.jar 이 들어가는 위치

 

-- 실습을 위한 SQL 테이블

CREATE TABLE MEMBER_TBL(
    MEMBER_NO       NUMBER          PRIMARY KEY,
    MEMBER_ID       VARCHAR2(20)    UNIQUE NOT NULL,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20)    NOT NULL,
    MEMBER_PHONE    CHAR(13)        NOT NULL,
    MEMBER_ADDR     VARCHAR2(300)   NOT NULL,
    MEMBER_LEVEL    NUMBER, -- 회원등급(1:관리자 2:정회원 3:준회원)
    ENROLL_DATE     CHAR(10) --YYYY-MM-DD 문자열로 집어넣고 읽어옴
);
-- MEMBER_NO / MEMBER_LEVEL / ENROLL_DATE 빼고는 사용자에게 입력을 받음
CREATE SEQUENCE MEMBER_SEQ;

INSERT INTO MEMBER_TBL 
VALUES(MEMBER_SEQ.NEXTVAL, 'user01', '1111', '김관리', '010-1111-1111', '서울시', 1, '2023-02-10'); 

INSERT INTO MEMBER_TBL 
VALUES(MEMBER_SEQ.NEXTVAL, 'user02', '2222', '이멤버', '010-2222-2222', '인천시', 2, '2023-02-10');

INSERT INTO MEMBER_TBL 
VALUES(MEMBER_SEQ.NEXTVAL, 'user03', '3333', '삼멤버', '010-3333-3333', '부산시', 3, '2023-02-10');

SELECT * FROM MEMBER_TBL;

COMMIT;

DAO : Member.java

package kr.or.iei.member.vo;

public class Member {
	//vo : Value Object 변수를 만들어서 사용
	private int memberNo;
	private String memberId;
	private String memberPw;
	private String memberName;
	private String memberPhone;
	private String memberAddr;
	private int memberLevel;
	private String enrollDate;
	public Member() {
		super();
	}

	public Member(String memberId, String memberPw, String memberName, String memberPhone, String memberAddr) {
		super();
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.memberPhone = memberPhone;
		this.memberAddr = memberAddr;
	}

	public Member(int memberNo, String memberId, String memberPw, String memberName, String memberPhone,
			String memberAddr, int memberLevel, String enrollDate) {
		super();
		this.memberNo = memberNo;
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.memberPhone = memberPhone;
		this.memberAddr = memberAddr;
		this.memberLevel = memberLevel;
		this.enrollDate = enrollDate;
	}
	
	public int getMemberNo() {
		return memberNo;
	}
	public void setMemberNo(int memberNo) {
		this.memberNo = memberNo;
	}
	public String getMemberId() {
		return memberId;
	}
	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}
	public String getMemberPw() {
		return memberPw;
	}
	public void setMemberPw(String memberPw) {
		this.memberPw = memberPw;
	}
	public String getMemberName() {
		return memberName;
	}
	public void setMemberName(String memberName) {
		this.memberName = memberName;
	}
	public String getMemberPhone() {
		return memberPhone;
	}
	public void setMemberPhone(String memberPhone) {
		this.memberPhone = memberPhone;
	}
	public String getMemberAddr() {
		return memberAddr;
	}
	public void setMemberAddr(String memberAddr) {
		this.memberAddr = memberAddr;
	}
	public int getMemberLevel() {
		return memberLevel;
	}
	public void setMemberLevel(int memberLevel) {
		this.memberLevel = memberLevel;
	}
	public String getEnrollDate() {
		return enrollDate;
	}
	public void setEnrollDate(String enrollDate) {
		this.enrollDate = enrollDate;
	}		
}

0. index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Servlet-DB 연동 프로젝트(신혜빈)</h1>
	<hr>
	<!-- 기능을위해서 필요한 것 : 컨트롤러 쪽에 요청 -> Servlet생성 -> 쿼리문생각(select * from member_tbl)
	                       쿼리문수행 시 사용자에게 받아야할 값이 있는지 생각 -> 없는경우 Servlet요청만(a태그사용) -->
	<!-- 원하는 처리를 위한 쿼리문 수행을 위해서 필요한 데이터가 없음 : 바로 Servlet에 요청하면 된다 -->
	<h3><a href="/allMember.do">1. 전체 회원 조회</a></h3>
	
	<!-- select * from member_tbl where member_id='입력아이디'; -->
	<!-- 아이디를 입력받아야 하는 상황 -> 아이디를 입력받을 수 있는 양식이 있는 페이지로 이동 -->
	<h3><a href="/html/searchMemberFrm.html">2. 아이디로 회원조회</a></h3>
	<!-- 사용할 쿼리
	     입력받아야할 항목이 있으면 입력받는 페이지 생성해서 이동
	     회원가입 성공/실패 메세지까지 화면에 출력 -->
	<h3><a href="/html/joinFrm.html">3. 회원가입</a></h3>
	<!-- 로그인이 없으므로 수정할 회원아이디를 입력받아 수정 
	     수정은 비밀번호 / 전화번호 / 주소만 가능 -->
	<h3><a href="/html/findMemberFrm.html">4.정보수정</a></h3>
</body>
</html>

 

1. 전체회원조회

Controller - 앞으로 Servlet파일이 여기에 들어갈 것 

AllMemberServlet.java

package kr.or.iei.member.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

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 kr.or.iei.member.service.MemberService;
import kr.or.iei.member.vo.Member;

/**
 * Servlet implementation class AllMemberServlet
 */
@WebServlet(name = "AllMember", urlPatterns = { "/allMember.do" })
public class AllMemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public AllMemberServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//1. 인코딩
		request.setCharacterEncoding("utf-8");
		//2. 값추출
		// 화면에서 사용자가 보내준 값이 없으므로 생략 가능
		//3. 비즈니스로직(이 요청으로 서버가 해줘야 할 일) 
		// -> DB에서 모든 회원을 조회 -> service에서 db작업이 완료된걸 ArrayList로 받을것 
		MemberService service = new MemberService();
		ArrayList<Member> list = service.selectAllMember();//보내줄 값이 없으므로 매개변수X
		//4. 결과처리
		/*
		for(Member m : list) {
			System.out.println("memberId : "+m.getMemberId());
			System.out.println("memberPw : "+m.getMemberPw());
		}*/
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE html>");
		out.println("<html><head><title>전체회원조회</title></head>");
		out.println("<body>");
		if(list.size() == 0) {
			out.println("<h2>회원정보를 조회할 수 없습니다.</h2>");
		}else {
			out.println("<h1>전체회원정보</h1>");
			out.println("<hr>");
			out.println("<table border=1>");
			out.println("<tr>");
			out.println("<th>회원번호</th><th>아이디</th><th>비밀번호</th><th>이름</th><th>전화번호</th><th>주소</th><th>등급</th><th>가입일</th>");
			out.println("</tr>");
			String level = " ";
			for(Member m : list) {
				if(m.getMemberLevel() == 1) {
					level = "관리자";
				}else if(m.getMemberLevel() == 2){
					level = "정회원";
				}else if(m.getMemberLevel() == 3) {
					level = "준회원";
				}
				out.println("<tr>");
				out.println("<td>"+m.getMemberNo()+"</td><td><a href='/searchMember.do?memberId="+m.getMemberId()+"'>"+m.getMemberId()+"</a></td><td>"+m.getMemberPw()+"</td><td>"+m.getMemberName()+"</td>");
				out.println("<td>"+m.getMemberPhone()+"</td><td>"+m.getMemberAddr()+"</td><td>"+level+"</td><td>"+m.getEnrollDate()+"</td>");
				out.println("</tr>");
			}
			out.println("</table>");
		}
		out.println("</body></html>");	
	}

	/**
	 * @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);
	}
}

Service 

	private MemberDao dao;
	
	public MemberService() {
		super();
		dao = new MemberDao();
	}

	public ArrayList<Member> selectAllMember() {
		//MemberDao dao = new MemberDao();
		//전역변수는 딱히 의미가없어서 지역변수로 선언해도 된다.
		Connection conn = JDBCTemplate.getConnection();
		ArrayList<Member> list = dao.selectAllMember(conn);
		JDBCTemplate.close(conn);
		return list;
	}

dao

	public ArrayList<Member> selectAllMember(Connection conn) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		ArrayList<Member> list = new ArrayList<Member>();
		String query = "select * from member_tbl";
		
		try {
			pstmt = conn.prepareStatement(query);
			rset = pstmt.executeQuery();
			while(rset.next()) {
				Member m = new Member();
				m.setEnrollDate(rset.getString("enroll_Date"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberLevel(rset.getInt("member_level"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberNo(rset.getInt("member_no"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberPw(rset.getString("member_pw"));
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(rset);
		}	
		return list;
	}

2. 아이디를 입력받아 회원 조회

Controller

SearchMemberServlet.java

package kr.or.iei.member.controller;

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

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 kr.or.iei.member.service.MemberService;
import kr.or.iei.member.vo.Member;

/**
 * Servlet implementation class SearchMemberServlet
 */
@WebServlet(name = "SearchMember", urlPatterns = { "/searchMember.do" })
public class SearchMemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchMemberServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//1. 인코딩
		request.setCharacterEncoding("utf-8");
		//2. 값추출
		String memberId = request.getParameter("memberId");
		//3. 비즈니스로직
		MemberService service = new MemberService();
		Member m = service.selectOneMember(memberId);
		//4. 결과처리
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE html>");
		out.println("<html><head><title>아이디로 조회</title></head>");
		out.println("<body>");
		out.println("<h1>[ "+memberId+" ] 정보 조회</h1>");
		out.println("<hr>");
		if(m == null) {
			out.println("<h3>존재하지 않는 아이디 입니다.</h3>");
		}else {
			out.println("NO : "+m.getMemberNo()+"<br>");
			out.println("ID : "+m.getMemberId()+"<br>");
			out.println("PW : "+m.getMemberPw()+"<br>");
			out.println("이름 : "+m.getMemberName()+"<br>");
			out.println("전화번호 : "+m.getMemberPhone()+"<br>");
			out.println("주소 : "+m.getMemberAddr()+"<br>");
			out.println("등급 : "+m.getMemberLevel()+"<br>");
			out.println("가입일 : "+m.getEnrollDate()+"<br>");
		}
		out.println("</body></html>");
	}

	/**
	 * @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);
	}
}

service

	public Member selectOneMember(String memberId) {
		Connection conn = JDBCTemplate.getConnection();
		Member m = dao.selectOneMember(conn, memberId);
		JDBCTemplate.close(conn);
		return m;
	}

dao

	public Member selectOneMember(Connection conn, String memberId) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		Member m = null;
		String query = "select * from member_tbl where member_id = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberId);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				m = new Member();
				m.setMemberAddr(rset.getString("member_addr"));
				m.setEnrollDate(rset.getString("enroll_Date"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberLevel(rset.getInt("member_level"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberNo(rset.getInt("member_no"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberPw(rset.getString("member_pw"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(rset);
		}		
		return m;
	}

 

3. 회원가입

3 - 1 ) 회원가입을 하기위해 회원가입form(html파일)으로 이동

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>회원가입</h1>
	<hr>
	<form action="/joinMember.do" method = "post">
		<label for="id">아이디 : </label>
		<input tpye="text" name="memberId" id="id"><br>
		<label for="pw">비밀번호 : </label>
		<input type="password" name="memberPw" id="pw"><br>
		<label for="name">이름</label>
		<input type="text" name="memberName" id="name"><br>
		<label for="phone">전화번호</label>
		<input type="text" name="memberPhone" id="phone"><br>
		<label for="addr">주소</label>
		<input tpye="text" name="memberAddr" id="addr"><br>
		<input type="submit" value="회원가입">
		<input type="reset" value="취소">
	</form>
</body>
</html>

3 - 2 ) 입력받은 값을 Servlet을 통해 처리

- Servlet

package kr.or.iei.member.controller;

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

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 kr.or.iei.member.service.MemberService;
import kr.or.iei.member.vo.Member;

/**
 * Servlet implementation class JoinMemberServlet
 */
@WebServlet(name = "JoinMember", urlPatterns = { "/joinMember.do" })
public class JoinMemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public JoinMemberServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//1. 인코딩
		request.setCharacterEncoding("utf-8");
		//2. 값추출
		String memberId = request.getParameter("memberId");
		String memberPw = request.getParameter("memberPw");
		String memberName = request.getParameter("memberName");
		String memberPhone = request.getParameter("memberPhone");
		String memberAddr = request.getParameter("memberAddr");
		Member m = new Member(memberId, memberPw, memberName, memberPhone, memberAddr);
		//3. 비즈니스로직
		MemberService service = new MemberService();
		int result = service.joinMember(m);
		//4. 결과처리
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE html>");
		out.println("<html><head><title>회원가입</title></head>");
		out.println("<body>");
		out.println("<h1>회원가입</h1>");
		out.println("<hr>");
		if(result == 0) {
			out.println("<h3>회원가입 실패!</h3>");
		}else {
			out.println("<h3>회원가입 성공!</h3>");
		}
		out.println("<br>");
		out.println("<a href='/'>메인으로 돌아가기</a>");
		out.println("</body></html>");
	}

	/**
	 * @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);
	}
}

- Service

	public int joinMember(Member m) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.joinMember(conn, m);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

- Dao

	public int joinMember(Connection conn, Member m) {
		PreparedStatement pstmt = null;
		
		int result = 0;
		String query = "insert into member_tbl values(member_seq.nextval, ?, ?, ?, ?, ?, 3, to_char(sysdate, 'yyyy-mm-dd'))";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, m.getMemberId());
			pstmt.setString(2, m.getMemberPw());
			pstmt.setString(3, m.getMemberName());
			pstmt.setString(4, m.getMemberPhone());
			pstmt.setString(5, m.getMemberAddr());
			result = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

4. 정보수정

- 현재 login기능이 없으므로 아이디를 입력받아서 비밀번호/전화번호/주소만 변경가능하도록 구상

( 단, 바꿀수 없는 정보포함 모든정보를 확인 할 수 있다)

4 - 1 ) 정보를 수정할 아이디를 입력하는 폼으로 이동

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="/findMember.do" method="get">
	<label for="id">수정할 아이디 입력 : </label>
	<input type="text" name="memberId" id="pw">
	<input type="submit" value="확인">
	</form>
</body>
</html>

4 - 2 ) 입력받은 아이디의 정보를 불러옴

- Servlet

package kr.or.iei.member.controller;

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

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 kr.or.iei.member.service.MemberService;
import kr.or.iei.member.vo.Member;

/**
 * Servlet implementation class FindMemberServlet
 */
@WebServlet(name = "FindMember", urlPatterns = { "/findMember.do" })
public class FindMemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public FindMemberServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//1. 인코딩
		request.setCharacterEncoding("utf-8");
		//2. 값추출
		String memberId = request.getParameter("memberId");
		//3. 비즈니스로직
		MemberService service = new MemberService();
		//3-1 멤버찾기
		Member m = service.selectOneMember(memberId);
		//4. 결과처리
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE html>");
		out.println("<html><head><title>회원정보조회</title></head>");
		out.println("<body>");
		if(m == null) {
			out.println("<h2>회원정보를 조회할 수 없습니다.</h2>");
		}else {
			out.println("<form action='/updateMember.do' method='post'>");
			out.println("<table>");
			
			out.println("<tr>");
			out.println("<th>회원번호</th>");
			out.println("<td><input type='text' value = '"+m.getMemberNo()+"' readonly></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>아이디</th>");
			out.println("<td><input type='text' value = '"+m.getMemberId()+"' name='memberId' readonly></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>비밀번호</th>");
			out.println("<td><input type='password' value = '"+m.getMemberPw()+"' name='memberPw'></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>이름</th>");
			out.println("<td><input type='text' value = '"+m.getMemberName()+"' readonly></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>전화번호</th>");
			out.println("<td><input type='text' value = '"+m.getMemberPhone()+"' name='memberPhone'></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>주소</th>");
			out.println("<td><input type='text' value = '"+m.getMemberAddr()+"' name='memberAddr'></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>회원등급</th>");
			out.println("<td><input type='text' value = '"+m.getMemberLevel()+"' readonly></td>");
			out.println("</tr>");
			
			out.println("<tr>");
			out.println("<th>가입일</th>");
			out.println("<td><input type='text' value = '"+m.getEnrollDate()+"' readonly></td>");
			out.println("</tr>");			
			out.println("</table>");
			out.println("<input type='submit' value='정보수정'>");
			out.println("</form>");
		}
		out.println("</body></html>");
	}

	/**
	 * @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);
	}
}

- Service

	public Member selectOneMember(String memberId) {
		Connection conn = JDBCTemplate.getConnection();
		Member m = dao.selectOneMember(conn, memberId);
		JDBCTemplate.close(conn);
		return m;
	}

- Dao

	public Member selectOneMember(Connection conn, String memberId) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		Member m = null;
		String query = "select * from member_tbl where member_id = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberId);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				m = new Member();
				m.setMemberAddr(rset.getString("member_addr"));
				m.setEnrollDate(rset.getString("enroll_Date"));
				m.setMemberAddr(rset.getString("member_addr"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberLevel(rset.getInt("member_level"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberNo(rset.getInt("member_no"));
				m.setMemberPhone(rset.getString("member_phone"));
				m.setMemberPw(rset.getString("member_pw"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(rset);
		}		
		return m;
	}

4 - 3 ) 위의 Servlet에서 작성한 html폼에 입력한대로 정보 업데이트

- Servlet

package kr.or.iei.member.controller;

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

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 kr.or.iei.member.service.MemberService;
import kr.or.iei.member.vo.Member;

/**
 * Servlet implementation class UpdateMemberServlet
 */
@WebServlet(name = "UpdateMember", urlPatterns = { "/updateMember.do" })
public class UpdateMemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UpdateMemberServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//1. 인코딩
		request.setCharacterEncoding("utf-8");
		//2. 값추출
		String memberId = request.getParameter("memberId");
		String memberPw = request.getParameter("memberPw");
		String memberPhone = request.getParameter("memberPhone");
		String memberAddr = request.getParameter("memberAddr");
		
		//3. 비즈니스로직
		MemberService service = new MemberService();
		int result = service.upadateMember(memberId, memberPw, memberPhone, memberAddr);
		//4. 결과처리
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		
		out.println("<!DOCTYPE html>");
		out.println("<html><head><title>화원정보변경</title></head>");
		out.println("<body>");
		if(result > 0) {
			out.println("<h2>정보변경성공</h2>");
		}else {
			out.println("<h2>정보변경실패</h2>");
		}
		out.println("<a href='/'>메인으로 돌아가기</a>");
		
	}

	/**
	 * @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);
	}
}

- Service

	public int upadateMember(String memberId, String memberPw, String memberPhone, String memberAddr) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.updateMember(conn, memberId, memberPw, memberPhone, memberAddr);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

- Dao

	public int updateMember(Connection conn, String memberId, String memberPw, String memberPhone, String memberAddr) {
		PreparedStatement pstmt = null;
		int result = 0;
		String query = "update member_tbl set member_pw = ?, member_phone = ?, member_addr = ? where member_id = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberPw);
			pstmt.setString(2, memberPhone);
			pstmt.setString(3, memberAddr);
			pstmt.setString(4, memberId);
			
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

저작자표시

'국비수업 > Server' 카테고리의 다른 글

56일차 : mvc2WebProject - 3  (0) 2023.02.16
55일차 : mvc2WebProject - 2  (0) 2023.02.16
54일차 : mvc2WebProject - 1  (0) 2023.02.16
53일차 : JSP, MVC1, MVC2  (0) 2023.02.16
51일차 2 : web, servlet 기초  (0) 2023.02.09
'국비수업/Server' Related Articles more
Comments