[JSP/Servlet] 서블릿 데이터베이스 연동

hidihyeonee·2024년 11월 19일
eclipsejspservletsql developer데이터베이스연동
0
post-thumbnail

2024.11.19 작성

OS : Window
개발환경 : Eclipse, SQL Developer
개발언어 : Java, SQL

서블릿으로 회원 정보 테이블의 회원 정보 조회

  • 자바 웹을 다루는 기술(실무에서 알아야 할 기술은 따로 있다!)_이병승 지음

1. SQL Developer에서 회원 테이블과 회원 정보를 입력하기

새 테이블을 생성하기 위해 테이블 > 우클릭 > 새테이블.

그리고 아래와 같이 테이블 생성.

데이터 입력을 위해 insert문을 작성하고 실행.

커밋이 완료됐다는 메세지가 나타나고 select문으로 조회 시 회원 정보가 표시 됨.

2. 이클립스에서 만든 프로젝트에서 회원 정보 조회 하기

오라클 데이터베이스와 연동하는 데 필요한 드라이브인 ojdbc6.jar를 lib 폴더에 복사.

+) lombok 사용할 시 추가 필요.

MemberVO 클래스 작성

package sec01.ex01;

import java.sql.Date;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class MemberVO {
	private String id;
	private String pwd;
	private String name;
	private String email;
	private Date joinDate;
}

MemberDAO 클래스 작성

package sec01.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {
	private Statement stmt;
	private Connection con;

	private String driver = "oracle.jdbc.OracleDriver";
	private String url = "jdbc:oracle:thin:@localhost:1521/xe";
	private String user = "testuser";
	private String pwd = "test1234";

	public List<MemberVO> listMembers() {
		List<MemberVO> list = new ArrayList<>();

		try {
			connDB();
			String query = "select * from t_member";
			System.out.println(query);
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()) {
				String id = rs.getString("id");
				String pwd = rs.getString("pwd");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date joinDate = rs.getDate("joinDate");
				MemberVO vo = new MemberVO();
				vo.setId(id);
				vo.setPwd(pwd);
				vo.setName(name);
				vo.setEmail(email);
				vo.setJoinDate(joinDate);
				list.add(vo);
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private void connDB() {

		try {
			Class.forName(driver);
			System.out.println("Oracle 드라이버 로딩 성공");
			con = DriverManager.getConnection(url, user, pwd);
			System.out.println("Connection 생성 성공");
			stmt = con.createStatement();
			System.out.println("Statement 생성 성공");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

MemberServlet 클래스 작성

package sec01.ex01;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;

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 MemberServlet
 */
@WebServlet("/member")
public class MemberServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.setContentType("text/html;charset=utf-8"); // MIME-TYPE 설정
		PrintWriter out = response.getWriter(); // PrintWriter 객체 생성 }

		MemberDAO dao = new MemberDAO();

		List<MemberVO> list = dao.listMembers();

		out.print("<html><body>");
		out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
		out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td></tr>");

		for (int i = 0; i < list.size(); i++) {
			MemberVO memberVO = list.get(i);

			String id = memberVO.getId();
			String pwd = memberVO.getPwd();
			String name = memberVO.getName();
			String email = memberVO.getEmail();
			Date joinDate = memberVO.getJoinDate();

			out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>"
					+ joinDate + "</td></tr>");
		}
		out.print("</table></body></html>");
	}

}

3. 실행하기

Tomcat 서버 실행.

서버 로딩 성공.

http://localhost:8090/pro07/member

로 요청하여 실행 결과를 확인하기.

결과

4. PreparedStatement를 이용한 회원 정보 실습

PreparedStatement를 사용하면 SQL문을 미리 컴파일해서 재사용하므로 Statement 보다 훨씬 빠르게 데이터베이스 작업을 수행할 수 있다.

MemberDAO 클래스 수정

package sec01.ex02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {
	private PreparedStatement pstmt;
	private Connection con;

	private String driver = "oracle.jdbc.OracleDriver";
	private String url = "jdbc:oracle:thin:@localhost:1521/xe"; // @localhost:포트번호/서비스아이디
	private String user = "testuser";
	private String pwd = "test1234";

	public List<MemberVO> listMembers() {
		List<MemberVO> list = new ArrayList<>();

		try {
			connDB();
			String query = "select * from t_member";
			System.out.println("preparedStatement: " + query);
			pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery(query);

			while (rs.next()) {
				String id = rs.getString("id");
				String pwd = rs.getString("pwd");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date joinDate = rs.getDate("joinDate");
				MemberVO vo = new MemberVO();
				vo.setId(id);
				vo.setPwd(pwd);
				vo.setName(name);
				vo.setEmail(email);
				vo.setJoinDate(joinDate);
				list.add(vo);
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	private void connDB() {

		try {
			Class.forName(driver);
			System.out.println("Oracle 드라이버 로딩 성공");
			con = DriverManager.getConnection(url, user, pwd);
			System.out.println("Connection 생성 성공");
//			stmt = con.createStatement();
			System.out.println("Statement 생성 성공");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

차이점 : 데이터베이스와 연동할 경우 속도가 좀 더 빠르다.

DataSource 이용해 데이터베이스 연동하기

  • 자바 웹을 다루는 기술(실무에서 알아야 할 기술은 따로 있다!)_이병승 지음

이전 방법은 데이터베이스 연결에 시간이 많이 걸린다는 단점이 있다.

이 단점을 해결하기 위해 미리 데이터베이스와 연결시킨 상태를 유지하는 기술 커넥션풀을 사용한다.

Tomcat v9.0 Server at localhost-config > context.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor 
	license agreements. See the NOTICE file distributed with this work for additional 
	information regarding copyright ownership. The ASF licenses this file to 
	You under the Apache License, Version 2.0 (the "License"); you may not use 
	this file except in compliance with the License. You may obtain a copy of 
	the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required 
	by applicable law or agreed to in writing, software distributed under the 
	License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS 
	OF ANY KIND, either express or implied. See the License for the specific 
	language governing permissions and limitations under the License. --><!-- The contents of this file will be loaded for each web application -->
<Context>

	<!-- Default set of monitored resources. If one of these changes, the -->
	<!-- web application will be reloaded. -->
	<WatchedResource>WEB-INF/web.xml</WatchedResource>
	<WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
	<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
	<Resource name="jdbc/oracle" auth="Container"
		type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:xe"
		username="testuser" password="test1234" maxActive="50" maxWait="-1" />

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
	<!-- <Manager pathname="" /> -->
</Context>

MemberRegistServlet

package sec02.ex01;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;

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 sec02.ex01.MemberDAO;
import sec02.ex01.MemberVO;

/**
 * Servlet implementation class MemberRegistServlet
 */
@WebServlet("/member3")
public class MemberRegistServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doHandle(request, response);
	}

	private void doHandle(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8"); // MIME-TYPE 설정
		PrintWriter out = response.getWriter(); // PrintWriter 객체 생성 }

		MemberDAO dao = new MemberDAO();

		String command = request.getParameter("command");

		if ("addMember".equals(command)) {
			String _id = request.getParameter("id");
			String _pwd = request.getParameter("pwd");
			String _name = request.getParameter("name");
			String _email = request.getParameter("email");

			MemberVO vo = new MemberVO();
			vo.setId(_id);
			vo.setPwd(_pwd);
			vo.setName(_name);
			vo.setEmail(_email);

			dao.addMember(vo);
		} else if ("delMember".equals(command)) {
			String id = request.getParameter("id");
			dao.delMember(id);
		}

		List<MemberVO> list = dao.listMembers();

		out.print("<html><body>");
		out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
		out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td></tr>");

		for (int i = 0; i < list.size(); i++) {
			MemberVO memberVO = list.get(i);

			String id = memberVO.getId();
			String pwd = memberVO.getPwd();
			String name = memberVO.getName();
			String email = memberVO.getEmail();
			Date joinDate = memberVO.getJoinDate();

			out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>"
					+ joinDate + "</td><td>" + "<a href='/pro07/member3?command=delMember&id=" + id
					+ "'>삭제</a></td></tr>");
		}
		out.print("</table>\n");
		out.print("<a href='/pro07/memberForm.html'>새 회원 등록하기 </a>\n</body>\n</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);
	}

}

memberForm.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script>
	function fn_sendMember() {
		var frmMember = document.frmMember;
		var id = frmMember.id.value;
		var pwd = frmMember.pwd.value;
		var name = frmMember.name.value;
		var email = frmMember.email.value;

		// if (id.length == 0 || id == '') {
		if (id.trim() == '') {
			alert('아이디는 필수입니다.');
			document.querySelector("input[name='id']").focus();
			return;
		} else if (name.length == 0 || name == '') {
			alert('이름은 필수입니다.');
			return;

		} else if (pwd.length == 0 || pwd == '') {
			alert('비밀번호는 필수입니다.');
			return;

		} else if (email.length == 0 || email == '') {
			alert('이메일은 필수입니다.');
			return;

		} else {
			frmMember.method = "post";
			frmMember.action = "member3";
			frmMember.submit();
		}
	}
</script>
</head>
<body>
	<form name="frmMember">
		<table>
			<th>회원 가입창</th>
			<tr>
				<td>아이디</td>
				<td><input type="text" name="id"></td>
			</tr>
			<tr>
				<td>비밀번호</td>
				<td><input type="password" name="pwd"></td>
			</tr>
			<tr>
				<td>이름</td>
				<td><input type="text" name="name"></td>
			</tr>
			<tr>
				<td>이메일</td>
				<td><input type="text" name="email"></td>
			</tr>
		</table>
		<input type="button" value="가입하기" onclick="fn_sendMember()"> <input
			type="reset" value="다시 입력"> <input type="hidden"
			name="command" value="addMember">
	</form>
</body>
</html>

MemberDAO

package sec02.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class MemberDAO {
	private PreparedStatement pstmt;
	private Connection con;

//	private String driver = "oracle.jdbc.OracleDriver";
//	private String url = "jdbc:oracle:thin:@localhost:1521/xe"; // @localhost:포트번호/서비스아이디
//	private String user = "testuser";
//	private String pwd = "test1234";

	private DataSource dataFactory;

	public MemberDAO() {
		try {
			Context ctx = new InitialContext();
			Context encContext = (Context) ctx.lookup("java:/comp/env");
			dataFactory = (DataSource) encContext.lookup("jdbc/oracle");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<MemberVO> listMembers() {
		List<MemberVO> list = new ArrayList<>();

		try {
//			connDB();
			con = dataFactory.getConnection();
			String query = "select * from t_member";
			System.out.println("preparedStatement: " + query);
			pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery(query);

			while (rs.next()) {
				String id = rs.getString("id");
				String pwd = rs.getString("pwd");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date joinDate = rs.getDate("joinDate");
				MemberVO vo = new MemberVO();
				vo.setId(id);
				vo.setPwd(pwd);
				vo.setName(name);
				vo.setEmail(email);
				vo.setJoinDate(joinDate);
				list.add(vo);
			}
			rs.close();
			pstmt.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void addMember(MemberVO vo) {
		try {
			con = dataFactory.getConnection();

			String id = vo.getId();
			String pwd = vo.getPwd();
			String name = vo.getName();
			String email = vo.getEmail();

			String query = "INSERT INTO t_member (id, pwd, name, email) VALUES(?,?,?,?)";

			pstmt = con.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.setString(2, pwd);
			pstmt.setString(3, name);
			pstmt.setString(4, email);

			pstmt.executeUpdate();
			pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delMember(String id) {
		try {
			con = dataFactory.getConnection();

			String query = "delete from t_member" + " where id=?";
			System.out.println("prepareStatement: " + query);

			pstmt = con.prepareStatement(query);
			pstmt.setString(1, id);
			pstmt.executeUpdate();
			pstmt.close();

		} catch (Exception e) {
			e.printStackTrace();
		}		
	}

//	private void connDB() {
//
//		try {
//			Class.forName(driver);
//			System.out.println("Oracle 드라이버 로딩 성공");
//			con = DriverManager.getConnection(url, user, pwd);
//			System.out.println("Connection 생성 성공");
////			stmt = con.createStatement();
//			System.out.println("Statement 생성 성공");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//	}
}

결과


profile
hidihyeonee
벨로그 쫌 재밌네?
이전 포스트

[VisualStudioCode] 커밋 메시지 수정하기

다음 포스트

[DevOps] 데브옵스 - 1

0개의 댓글