본문 바로가기
👨‍💻 2. 웹개발_Back end/2-4 JSP & Servlet

[JSP & Servlet] 4장 서블릿과 JDBC (2) - HttpServlet으로 GET 요청 다루기

by 달님🌙 2021. 10. 14.
반응형

 

1. HttpServlet으로 GET 요청 다루기

 

1. 서블릿 만들기

 

1) Servlet 만들기

2) GenericServlet 만들기 

3) HttpServlet 만들기 

 

2. 한글 입력 값이 깨지는 이유 

 

- URL 인코딩 -> UTF-8

 

 

 

 

 

3. GenericServlet 실습 ( _06_JDBCServlet 프로젝트 )

 

1) mysql workbench를 이용하여 테이블 생성

스키마 생성

 

유저 생성

 

유저 권한 설정

 

커넥션 설정

 

MEMBER 테이블 생성 코드

CREATE TABLE MEMBERS (
	MNO INTEGER NOT NULL COMMENT '회원일련번호',
    EMAIL VARCHAR(40) NOT NULL COMMENT '이메일',
    PWD VARCHAR(100) NOT NULL COMMENT '암호',
    MNAME VARCHAR(50) NOT NULL COMMENT '이름',
    CRE_DATE DATETIME NOT NULL COMMENT '가입일',
    MOD_DATE DATETIME NOT NULL COMMENT '마지막암호변경일'
)
COMMENT '회원기본정보';

ALTER TABLE MEMBERS
		ADD CONSTRAINT PK_MEMBERS
			PRIMARY KEY (
				MNO
            );
            
CREATE UNIQUE INDEX UIX_MEMBERS
	ON MEMBERS (
		EMAIL ASC
    );
    
ALTER TABLE MEMBERS
	MODIFY COLUMN MNO INTEGER NOT NULL AUTO_INCREMENT
    COMMENT '회원일련번호';
    
INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)
VALUES ('s1@test.com', '1111', '홍길동', NOW(), NOW());

INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)
VALUES ('s2@test.com', '1111', '임꺽정', NOW(), NOW());

INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)
VALUES ('s3@test.com', '1111', '일지매', NOW(), NOW());

INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)
VALUES ('s4@test.com', '1111', '이몽룡', NOW(), NOW());

INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)
VALUES ('s5@test.com', '1111', '성춘향', NOW(), NOW());

SELECT * FROM MEMBERS;

 

2) eclipse를 이용하여 Servlet 생성

 

MemberListServlet 서블릿 파일 생성

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.GenericServlet;
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;

/**
 * Servlet implementation class MemberListServlet
 */
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {
	/**
	 * @see Servlet#service(ServletRequest request, ServletResponse response)
	 */
	@Override
	public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		// DB 연결
		Connection conn = null;
		//sql문을 담을 객체
		Statement stmt = null;
		//sql문 결과값
		ResultSet rs = null;
		
		String sqlSelect = "SELECT * FROM MEMBERS ORDER BY MNO ASC";
		
		//mysql 서버 접속정보
		String mySqlUrl = "jdbc:mysql://localhost/studydb?serverTimezone=UTC";
		String id = "study";
		String pwd = "study";
		
		try {
			// 1. MySQL 제어 객체를 로딩
			DriverManager.deregisterDriver(new com.mysql.cj.jdbc.Driver()); // 어떤 드라이버를 쓸지
			// 2. MySQL 연결
			conn = DriverManager.getConnection(mySqlUrl, id, pwd);
			// 3. sql문 객체 생성
			stmt = conn.createStatement();
			// 4. sql 전송 후 결과 값 리턴받기
			rs = stmt.executeQuery(sqlSelect);
			// 5. 결과를 브라우저로 전송
			response.setContentType("text/html;charset=UTF-8");
			PrintWriter out = response.getWriter();
			out.println("<html><head><title>회원목록</title></head>");
			out.println("<body><h1>회원 목록</h1>");
			while(rs.next()) { // 결과값이 있을 동안 하나씩 받아옴
				out.println(rs.getInt(1) + ", " +
							rs.getString(2) + ", " + 
							rs.getString(3) + ", " + 
							rs.getString(4) + "<br>");
			}
			out.println("</body></html>");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(conn != null) {
					conn.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

 

* 변수 rs : return되는 결과 값을 받아줌

 

* driver.class 파일 : 

 

3) 실행 화면

 

4. HttpServlet 실습 ( _07_JDBCServlet_Add )

 

1) spms.servlets 패키지 생성

 

2) List서블릿과 Add 서블릿 생성

MemberListServlet.java

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.GenericServlet;
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebServlet;

/**
 * Servlet implementation class MemberListServlet
 */
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {
	/**
	 * @see Servlet#service(ServletRequest request, ServletResponse response)
	 */
	@Override
	public void service(ServletRequest request, ServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		// DB 연결
		Connection conn = null;
		//sql문을 담을 객체
		Statement stmt = null;
		//sql문 결과값
		ResultSet rs = null;
		
		String sqlSelect = "SELECT * FROM MEMBERS ORDER BY MNO ASC";
		
		//mysql 서버 접속정보
		String mySqlUrl = "jdbc:mysql://localhost/studydb?serverTimezone=UTC";
		String id = "study";
		String pwd = "study";
		
		try {
			// 1. MySQL 제어 객체를 로딩
			DriverManager.deregisterDriver(new com.mysql.cj.jdbc.Driver()); // 어떤 드라이버를 쓸지
			// 2. MySQL 연결
			conn = DriverManager.getConnection(mySqlUrl, id, pwd);
			// 3. sql문 객체 생성
			stmt = conn.createStatement();
			// 4. sql 전송 후 결과 값 리턴받기
			rs = stmt.executeQuery(sqlSelect);
			// 5. 결과를 브라우저로 전송
			response.setContentType("text/html;charset=UTF-8");
			PrintWriter out = response.getWriter();
			out.println("<html><head><title>회원목록</title></head>");
			out.println("<body><h1>회원 목록</h1>");
			/*
			 *	/add : http://localhost:9999/add
			 * 	add : http://localhost:9999/member/add (마지막 페이지 경로로부터 이동)
			 * 
			 * */
			out.println("<p><a href='add'>신규 회원</a></p>");
			out.println("<p><a href='delete'>회원 삭제</a></p>");
			while(rs.next()) { // 결과값이 있을 동안 하나씩 받아옴
				//이름을 눌렀을 때 업데이트 페이지로 이동
				out.println(rs.getInt(1) + ", " + 
							"<a href='update?no=" + rs.getInt("MNO") + "'>" +
							rs.getString(2) + "</a>, " + 
							rs.getString(3) + ", " + 
							rs.getString(4) + "<br>");
			}
			out.println("</body></html>");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(conn != null) {
					conn.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

 

MemberAddServlet.java

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class MemberAddServlet
 */
@WebServlet("/member/add")
//HttpServlet은 GenericServlet을 상속받은 클래스
//service() 메소드를 호출방식에 따라 doGet(), doPost(), doPut(), doDelete()로 분리해놓음
public class MemberAddServlet extends HttpServlet {
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	//url에 주소 직접 입력 후 접속, 링크로 걸려있는 주소로 접속 -> get방식
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		
		out.println("<html><head><title>회원등록</title></head>");
		out.println("<body><h1>회원등록</h1>");
		out.println("<form action='add' method='post'>");
		out.println("이름 : <input type='text' name='name'><br>");
		out.println("이메일 : <input type='text' name='email'><br>");
		out.println("암호 : <input type='password' name='password'><br>");
		out.println("<input type='submit' value='추가'>");
		out.println("<input type='reset' value='취소'>");
		out.println("</form></body></html>");
	}
	
	//입력폼에 입력된 정보를 submit할때 method 방식이 post이므로
	//doPost에서 데이터 입력 처리
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		Connection conn = null;
		PreparedStatement stmt = null;
		String sqlInsert = "INSERT INTO MEMBERS(EMAIL, PWD, MNAME, CRE_DATE, MOD_DATE)" + 
						   "VALUES(?, ?, ?, NOW(), NOW())";
		String url = "jdbc:mysql://localhost/studydb?serverTimezone=UTC";
		String id = "study";
		String pw = "study";
		
		try {
			DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
			conn = DriverManager.getConnection(url, id, pw);
			stmt = conn.prepareStatement(sqlInsert);
			stmt.setString(1, request.getParameter("email"));
			stmt.setString(2, request.getParameter("password"));
			stmt.setString(3, request.getParameter("name"));
			stmt.executeUpdate();
			
			//sendRedirect 밑의 결과 값 출력 안하고 바로 /member/list로 이동
			response.sendRedirect("list");
			
			response.setContentType("text/html;charset=UTF-8");
			PrintWriter out = response.getWriter();
			out.println("<html><head><title>회원등록결과</title>");
			out.println("<meta http-equiv='Refresh' content='1; url=list'></head>");
			out.println("<body><p>등록성공입니다!</p></body></html>");
			
			//응답헤더에 리프레쉬 정보를 추가
			//1초 후에 url=list로 보내라
			//response.addHeader("Refresh", "1;url=list");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(conn != null) {
					conn.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

 

3) 실행 화면

 

4) Update 서블릿 생성

MemberUpdateServlet.java

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

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

/**
 * Servlet implementation class MemberUpdateServlet
 */
//@WebServlet("/MemberUpdateServlet")
public class MemberUpdateServlet extends HttpServlet {
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String no = request.getParameter("no");
		String sqlSelect = "SELECT * FROM MEMBERS WHERE MNO = " + no;
		
		//서블릿 초기화 매개변수 꺼내 쓰기 -> getIninParameter메소드로 접근
		String driver = this.getInitParameter("driver");
		String url = this.getInitParameter("url");
		String id = this.getInitParameter("username");
		String pwd = this.getInitParameter("password");
		
		try {
			//mysql 제어 객체 로딩
			Class.forName(driver);
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sqlSelect);
			rs.next();
			
			response.setContentType("text/html;charset=UTF-8");
			PrintWriter out = response.getWriter();
			out.println("<html><head><title>회원정보</title></head>");
			out.println("<body><h1>회원정보</h1>");
			out.println("<form action='update' method='post'>");
			out.println("번호 : <input type='text' name='no' value='" + no + "' readonly> <br>");
			out.println("이름 : <input type='text' name='name' value='" + rs.getNString("mname") + "'> <br>");
			out.println("이메일 : <input type='text' name='email' value='" + rs.getNString("email") + "'> <br>");
			out.println("가입일 : " + rs.getDate("cre_date") + "<br>");
			out.println("<input type='submit' value='저장'>");
			out.println("<input type='button' value='취소' onclick='location.href=\"list\"'>");
			out.println("</form></body></html>");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) {
					rs.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(conn != null) {
					conn.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("UTF-8");
		
		Connection conn = null;
		PreparedStatement stmt = null;
		String sqlUpdate = "UPDATE MEMBERS SET EMAIL=?, MNAME=?, MOD_DATE=NOW() WHERE MNO=?";
		
		String driver = this.getInitParameter("driver");
		String url = this.getInitParameter("url");
		String id = this.getInitParameter("username");
		String pwd = this.getInitParameter("password");
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.prepareStatement(sqlUpdate);
			stmt.setString(1, request.getParameter("email"));
			stmt.setString(2, request.getParameter("name"));
			stmt.setInt(3, Integer.parseInt(request.getParameter("no")));
			stmt.executeUpdate();
			
			response.sendRedirect("list");
		} catch(Exception e) {
			e.printStackTrace();
		} finally {			
			try {
				if(stmt != null) {
					stmt.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
			
			try {
				if(conn != null) {
					conn.close();
				}
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
	}

}

 

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>_06_JDBCServlet</display-name>
  
  <!-- 서블릿 선언 -->
  <servlet>
  	<servlet-name>MemberUpdateServlet</servlet-name>
  	<servlet-class>spms.servlets.MemberUpdateServlet</servlet-class>
  	<!-- 서블릿 초기화 매개변수 -->
  	<init-param>
  		<param-name>driver</param-name>
  		<param-value>com.mysql.jdbc.Driver</param-value>
  	</init-param>
  	<init-param>
  		<param-name>url</param-name>
  		<param-value>jdbc:mysql://localhost/studydb?serverTimezone=UTC</param-value>
  	</init-param>
  	<init-param>
  		<param-name>username</param-name>
  		<param-value>study</param-value>
  	</init-param>
  	<init-param>
  		<param-name>password</param-name>
  		<param-value>study</param-value>
  	</init-param>
  </servlet>
  
  <!-- 서블릿 url 매핑 -->
  <servlet-mapping>
  	<servlet-name>MemberUpdateServlet</servlet-name>
  	<url-pattern>/member/update</url-pattern>
  </servlet-mapping>
  
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
</web-app>

 

 

5) 실행 결과

 

 

반응형
저작자표시 비영리 변경금지

'👨‍💻 2. 웹개발_Back end > 2-4 JSP & Servlet' 카테고리의 다른 글

[JSP & Servlet] 4장 서블릿과 JDBC (4) - 필터 사용하기  (0) 2021.10.14
[JSP & Servlet] 4장 서블릿과 JDBC (3) - 리프래시와 서블릿 초기화 매개변수  (0) 2021.10.14
[JSP & Servlet] 4장 서블릿과 JDBC(1) - JDBC와 ODBC 구조  (0) 2021.10.13
[JSP & Servlet] 3장 서블릿 프로그래밍  (0) 2021.10.12
[JSP & Servlet] 2장 웹 프로그래밍의 기초 다지기  (0) 2021.10.12

관련글

댓글

티스토리툴바