JDBC Servlet & Http Servlet

jinkyung·2021년 1월 19일
1

JSP

목록 보기
3/20

mysql 라이브러리 추가

: web에서는 build path 하지 않아도 lib에 파일을 그냥 넣어주면 된다.

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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


@SuppressWarnings("serial")
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {

	@Override
	public void service(ServletRequest req, ServletResponse res) throws ServletException, IOException {
		Connection conn = null;		//mySql 서버 연결 
		Statement stmt = null;		//sql문 
		ResultSet rs = null;		// select 결과 
		
		String sqlSelect = "SELECT mno, mname, email, cre_date" + "\r\n" +
							"FROM members" + "\r\n" + 
							"ORDER BY mno ASC";
		
		// mysql 5.1.x 이후에는 timezone붙여야함
		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) 결과를 브라우저에 전송
			res.setContentType("text/html);charset=UTF-8");
			PrintWriter out = res.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.getDate(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();
			}
		}
		
	}

}

출력결과


HttpServlet

: generic servlet 으로 상속받은 service()는 get/post 상관하지않고 다 받는다. 그러나 때로는 구분해서 처리하고 싶을 때가 있다. 메서드 단위로 분리해서 처리할 수 있다. 요청에 따라 분기시키는 기능이 들어있는 것이다.


신규회원 등록

  • get 요청 
package spms.servlets;

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;

@SuppressWarnings("serial")
@WebServlet("/member/add")
public class MemberAddServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.setContentType("text/html;charset=UTF-8");
		PrintWriter out = resp.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>");
		out.println("</body></html>");
	}

}

href 추가

(상대경로를 많이 쓴다)

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

@SuppressWarnings("serial")
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {

	@Override
	public void service(ServletRequest req, ServletResponse res) throws ServletException, IOException {
		Connection conn = null;		// MySQL 서버 연결
		Statement stmt = null;		// sql문
		ResultSet rs = null;		// select 결과
		
		String sqlSelect = "SELECT mno,mname,email,cre_date " + "\r\n" +
						   "FROM members " + "\r\n" +
						   "ORDER BY mno ASC";
		String mySqlUrl = "jdbc:mysql://localhost/studydb?serverTimezone=UTC"; // 5.1.x 이후
		String id = "study";
		String pwd = "study";
		try {
			// 1) MySQL 제어 객체를 로딩
			DriverManager.registerDriver(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) 결과를 브라우저에 전송
			res.setContentType("text/html;charset=UTF-8");
			PrintWriter out = res.getWriter();
			out.println("<html><head><title>회원목록</title></head>");
			out.println("<body><h1>회원 목록</h1>");
			/*
			 *      /add : /add
			 *      add : _08_JDBCServlet_Add/member/list
			 *          => _08_JDBCServlet_Add/member/add
			 * */
			/* 컨텍스트 루트(Context Root) ==> 기본 경로
			 * 1) Apache tomcat의 주소 : http://localhost:9999 
			 * 2) 웹어플리케이션 : _08_JDBCServlet_Add
			 * 3) 컨텍스트 루트 경로 : http://localhost:9999/_08_JDBCServlet_Add
			 * 
			 *   3-1) <a href='/add'>   => 절대경로 (웹서버 루트 기준)
			 *      /는 웹서버 경로에서 add를 추가
			 *      http://localhost:9999/add
			 *      
			 *   3-2) <a href='add'>	=> 상대경로 (현재 경로 기준)
			 *      현재 경로의 마지막 경로만 교체
			 *      http://localhost:9999/_08_JDBCServlet_Add/member/list
			 *         =>
			 *      http://localhost:9999/_08_JDBCServlet_Add/member/add
			 * */
			out.println("<p><a href='add'>신규 회원</p>");
			//out.println("<p><a href='/_08_JDBCServlet_Add/member/add'>신규 회원</p>");
			
			while(rs.next()) {
				out.println(rs.getInt(1) + ", " +
							rs.getString(2) + ", " + 
							rs.getString(3) + ", " + 
							rs.getDate(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();
			}
		}
	}

}

  • post 요청
package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

@SuppressWarnings("serial")
@WebServlet("/member/add")
public class MemberAddServlet extends HttpServlet {

	// /member/list => /member/add로 get으로 링크를 이동한다
	// 브라우저에 입력 폼을 제공한다
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.setContentType("text/html;charset=UTF-8");
		PrintWriter out = resp.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>");
		out.println("</body></html>");
	}

	// doGet에서 submit을 하면 action='add' method='post'으로 처리되므로
	// 결고 다시 /member/add로 post 요청이 들어온다
	// DB에 새로운 사용자를 등록한다
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		// 서블릿은 utf-8이 아니라 유니코드로 문자열을 처리한다
		// 그러므로 utf-8로 인식하겠다는 설정을 해야한다 
		req.setCharacterEncoding("UTF-8");
		
		Connection conn = null;
		PreparedStatement stmt = null;
		String sqlInsert = "INSERT INTO members(email,pwd," + "\r\n" +
						   "mname,cre_date,mod_date)" + "\r\n" +
						   "VALUES(?,?,?,NOW(),NOW())";
		String url = "jdbc:mysql://localhost/studydb?serverTimezone=UTC";
		String id = "study";
		String pwd = "study";
		try {
			DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.prepareStatement(sqlInsert);
			stmt.setString(1, req.getParameter("email"));
			stmt.setString(2, req.getParameter("password"));
			stmt.setString(3, req.getParameter("name"));
			stmt.executeUpdate();
			
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter out = resp.getWriter();
			out.println("<html><head><title>회원등록결과</title></head>");
			out.println("<body>");
			out.println("<p>등록성공입니다!</p>");
			out.println("</body></html>");
			
		}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();
			}
		}
	}

	
}

결과

  • 회원 등록 후 1초 뒤에 자동으로 목록이 보이도록 코드 추가


  • 실행하면 '등록성공입니다!'가 아니라 바로 결과 목록을 보이도록 수정 = redirect
package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

@SuppressWarnings("serial")
@WebServlet("/member/add")
public class MemberAddServlet extends HttpServlet {

	// /member/list => /member/add로 get으로 링크를 이동한다
	// 브라우저에 입력 폼을 제공한다
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		resp.setContentType("text/html;charset=UTF-8");
		PrintWriter out = resp.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>");
		out.println("</body></html>");
	}

	// doGet에서 submit을 하면 action='add' method='post'으로 처리되므로
	// 결고 다시 /member/add로 post 요청이 들어온다
	// DB에 새로운 사용자를 등록한다
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
		// 서블릿은 utf-8이 아니라 유니코드로 문자열을 처리한다
		// 그러므로 utf-8로 인식하겠다는 설정을 해야한다 
		req.setCharacterEncoding("UTF-8");
		
		Connection conn = null;
		PreparedStatement stmt = null;
		String sqlInsert = "INSERT INTO members(email,pwd," + "\r\n" +
						   "mname,cre_date,mod_date)" + "\r\n" +
						   "VALUES(?,?,?,NOW(),NOW())";
		String url = "jdbc:mysql://localhost/studydb?serverTimezone=UTC";
		String id = "study";
		String pwd = "study";
		try {
			DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.prepareStatement(sqlInsert);
			stmt.setString(1, req.getParameter("email"));
			stmt.setString(2, req.getParameter("password"));
			stmt.setString(3, req.getParameter("name"));
			stmt.executeUpdate();
			
			// 바로 add -> list 이동 (리다이렉트)
			resp.sendRedirect("list");
	
			/*
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter out = resp.getWriter();
			
			// 1초 후에 add->list로 이동
			// 1번째 방법 
			out.println("<meta http-equiv='Refresh' content='1; url=list'>");
			out.println("<html><head><title>회원등록결과</title></head>");
			out.println("<body>");
			out.println("<p>등록성공입니다!</p>");
			out.println("</body></html>");
			
			// 2번째 방법 
			resp.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();
			}
		}
	}

	
}
  • 필수 파라미터들을 servlet에 등록 (환경설정)

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" version="3.1">
  <display-name>_12_JDBCServlet_ServletInitParam</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.cj.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>

회원 수정


  • 회원을 클릭했을때 회원정보가 뜨도록 수정 (get 요청)

MemberListServlet.java
a href링크 : get 요청 

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

@SuppressWarnings("serial")
@WebServlet("/member/list")
public class MemberListServlet extends GenericServlet {

	@Override
	public void service(ServletRequest req, ServletResponse res) throws ServletException, IOException {
		Connection conn = null;		// MySQL 서버 연결
		Statement stmt = null;		// sql문
		ResultSet rs = null;		// select 결과
		
		String sqlSelect = "SELECT mno,mname,email,cre_date " + "\r\n" +
						   "FROM members " + "\r\n" +
						   "ORDER BY mno ASC";
		String mySqlUrl = "jdbc:mysql://localhost/studydb?serverTimezone=UTC"; // 5.1.x 이후
		String id = "study";
		String pwd = "study";
		try {
			// 1) MySQL 제어 객체를 로딩
			DriverManager.registerDriver(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) 결과를 브라우저에 전송
			res.setContentType("text/html;charset=UTF-8");
			PrintWriter out = res.getWriter();
			out.println("<html><head><title>회원목록</title></head>");
			out.println("<body><h1>회원 목록</h1>");
			out.println("<p><a href='add'>신규 회원</p>");
			
			while(rs.next()) {
					out.println(rs.getInt("mno") + ", " +
			//no라는 변수에 mno값을 전달 
					"<a href='update?no=" + rs.getInt("mno") + "'>" + // ex)member/update?no=1
					rs.getString("mname") + ", " + 
					rs.getString("email") + ", " + 
					rs.getDate("cre_date") + "<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();
			}
		}
	}

}

MemberUpdateServlet.java

package spms.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

@SuppressWarnings("serial")
public class MemberUpdateServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String no = req.getParameter("no");
		String sqlSelect = "SELECT mno,email,mname,cre_date " + "\r\n" +
						   "FROM members WHERE mno=" + "\r\n" + no;
		
		String driver = this.getInitParameter("driver");
		String url = this.getInitParameter("url");
		String id = this.getInitParameter("username");
		String pwd = this.getInitParameter("password");

		try {			
			Class.forName(driver);	// mysql드라이버 로딩
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sqlSelect);
			rs.next();				// 처음에는 1행 이전을 가리키니까 1행으로 위치 이동
			
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter out = resp.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.getString("mname") + "'><br/>");
			out.println("이메일: <input type='text' name='email'" +
						" value='" + rs.getString("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>");
			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();
			}
		}
	}

}

결과
(번호는 readonly 속성으로 인해 써지지 않는다)


  • 회원 정보 수정하여 db에 등록 (post 요청 추가)
@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("UTF-8");// 한글이 안 깨짐
		
		Connection conn = null;
		PreparedStatement stmt = null;
		String sqlUpdate = "UPDATE members SET email=?," +"\r\n" +
							"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);	// mysql객체를 로딩
			conn = DriverManager.getConnection(url, id, pwd);
			stmt = conn.prepareStatement(sqlUpdate);
			stmt.setString(1, req.getParameter("email"));
			stmt.setString(2, req.getParameter("name"));
			stmt.setInt(3, Integer.parseInt(req.getParameter("no")));
			stmt.executeUpdate();
			
			resp.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();
			}
		}
	}

결과
'아이유' -> '이지은' 으로 수정


profile
jinkyung
이전 포스트

Generic Servlet

다음 포스트

공유 Servlet

0개의 댓글