Database App with JDBC : 학생 리스트 DB에서 가져오기 (Read)

Spring 2021. 6. 18. 22:33

먼저 mysql workbench에서

sql script(다운받은 파일중에) 차례대로 2개를 실행시켜준다.

이후에 다운받은 폴터의 lib안에서의 mysql-connector-java-8.0.19jar파일을 우리가 만든

web-student-tracker 다이나믹 프로젝트안의 lib 폴더로 복사해준다.

추가로 META-INF 폴더안의 context.xml파일도 같은 위치로 복사해준다.

<Context>

  <Resource name="jdbc/web_student_tracker" 
  			auth="Container" type="javax.sql.DataSource"
               maxActive="20" maxIdle="5" maxWait="10000"
               username="webstudent" password="webstudent" 
               driverClassName="com.mysql.cj.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/web_student_tracker?useSSL=false&amp;serverTimezone=UTC"/>

</Context>

이제 com.yunhalee.web.jdbc 패키지를 만들고 그안에 TestServlet을 만드는데, 다음과 같이 조건을 변경해서 생성해준다.

package com.yunhalee.web.jdbc;

import java.io.IOException;

import javax.annotation.Resource;
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.sql.DataSource;

/**
 * Servlet implementation class TestServlet
 */
@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	
	//Define datasource/connection pool for Resource Injection
	@Resource(name="jdbc/web_student_tracker")
	private DataSource dataSource;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

}

여기서 데이터 베이스 이름은 우리가 xml에서 설정한 이름과 동일해야 한다.

다음과 같이 서블렛에서 데이터베이스와 연결해주고 쿼리를 실행해서 정보를 불러온다.

package com.yunhalee.web.jdbc;

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

import javax.annotation.Resource;
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.sql.DataSource;

/**
 * Servlet implementation class TestServlet
 */
@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	
	//Define datasource/connection pool for Resource Injection
	@Resource(name="jdbc/web_student_tracker")
	private DataSource dataSource;

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		
		//Step 1: Set up the printwriter
		PrintWriter out = response.getWriter();
		response.setContentType("text/plain");
		
		//Step 2: Get a connection to the database
		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;
		
		try {
			myConn = dataSource.getConnection();
		//Step 3: Create a SQL statements
			String sql = "select * from student";
			myStmt = myConn.createStatement();
			
		//Step 4: Execute SQl query
			myRs = myStmt.executeQuery(sql);
		
		//Step 5: Process the result set
			while(myRs.next()) {
				String email = myRs.getString("email");
				out.println(email);
			}
			
		}
		catch(Exception exc) {
			exc.printStackTrace();
		}
	}

}

실행해보면 다음과 같이 이메일을 가져오느 것을 알 수 있다.

이제 MVC sample Architecture를 잡아보자.

먼저 student 모델을 담는 클래스를 만들어준다.

com.yunhalee.web.jdbc>Student.java

여기서 constructor 두개 만드는데 하나는 id있는 것(super 없음)

하나는 id 없는 것(super 있음)

package com.yunhalee.web.jdbc;

public class Student {
	
	private int id;
	private String firstName;
	private String lastName;
	private String email;
	
	public Student(String firstName, String lastName, String email) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.email = email;
	}
	
	public Student(int id, String firstName, String lastName, String email) {
		this.id = id;
		this.firstName = firstName;
		this.lastName = lastName;
		this.email = email;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
    
    @Override
	public String toString() {
		return "Student [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + "]";
	}
	
	
}

StudentList를 보내주는 작업을먼저 진행한다.

이제 util파일을 만든다.

package com.yunhalee.web.jdbc;

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

import javax.sql.DataSource;

public class StudentDbUtil {

	private DataSource dataSource;
	
	public StudentDbUtil(DataSource theDataSource) {
		dataSource = theDataSource;
	}
	
	public List<Student> getStudent() throws Exception{
		List<Student> students = new ArrayList<>();
		
		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;
		
		
		try {
		// get a connection
		myConn = dataSource.getConnection();
		
		//create sql statement
		String sql = "select * from student order by last_name";
		myStmt = myConn.createStatement();
		
		//execute query
		myRs = myStmt.executeQuery(sql);
		
		//process result set
		while(myRs.next()) {
			//retrieve data from result set row
			int id = myRs.getInt("id");
			String firstName = myRs.getString("first_name");
			String lastName = myRs.getString("last_name");
			String email = myRs.getString("email");
			
			//create new student object
			Student tempStudent = new Student(id, firstName, lastName, email);
			
			//add it to the list of students
			students.add(tempStudent);
		}
		
		
			return students;

			
		}finally {
			//close JDBC objects
			close(myConn, myStmt, myRs);
			
		}
	}

	private void close(Connection myConn, Statement myStmt, ResultSet myRs) {
		// TODO Auto-generated method stub
		try {
			if(myRs != null) {
				myRs.close();
			}
			if(myStmt !=null) {
				myStmt.close();
			}
			if(myConn !=null) {
				myConn.close(); //doesn't really close it ... just puts back in connection pool
			}
		}catch(Exception exc) {
			exc.printStackTrace();
		}
	}
	
	
}

이제 Servlet 파일을 만들어준다.

datasource conn pool을 해주고,

init을 override해서 studentDbUtil을 datasource로 하는 작업을 실행해준다.

그다음 doget을 작성해준다.

여기서 listStudents라는 함수를 따로 생성해주는데, 얘는 throws Exception을 해주고,

studentDbUtil을 이용해서 학생리스트를 받아오고,

request에 받아온 학생리스트를 추가하고 나서

dispatcher로 jsp(view)로 보내준다.

package com.yunhalee.web.jdbc;

import java.io.IOException;
import java.util.List;

import javax.annotation.Resource;
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.sql.DataSource;

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

	
	private StudentDbUtil studentDbUtil;
	
	@Resource(name = "jdbc/web_student_tracker")
	private DataSource dataSource;
	
	
	
	@Override
	public void init() throws ServletException {
		super.init();
		
		//create our student db util ... and pass in the conn pool / datasource
		
		try {
			studentDbUtil = new StudentDbUtil(dataSource);
		}catch(Exception exc){
			throw new ServletException(exc);
		}
	}


	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
		//list the students ... in MVC fashion
		listStudents(request, response);
		}catch(Exception exc) {
			throw new ServletException(exc);
		}
	}


	private void listStudents(HttpServletRequest request, HttpServletResponse response) throws Exception{
		
		//get students from db util
		List<Student> students = studentDbUtil.getStudent();
		
		//add students to the request
		request.setAttribute("STUDENT_LIST", students);
		
		//send to JSP page (view)
		RequestDispatcher dispatcher = request.getRequestDispatcher("/list-students.jsp");
		dispatcher.forward(request, response);
		
	}

}

이제 jsp 페이지를 만들자.

<%@ page import = "java.util.*, com.yunhalee.web.jdbc.*" %>

<!DOCTYPE html>
<html>
<head>
<title>Student Tracker App</title>
</head>
<%
	//get the students from the request object (sent by servlet)
	List<Student> theStudents = (List<Student>) request.getAttribute("STUDENT_LIST");
%>
<body>

<%= theStudents %>
</body>
</html>

서블렛 run해보면 다음과 같이 확인 가능하다.

테이블을 만들어보자.

<%@ page import = "java.util.*, com.yunhalee.web.jdbc.*" %>

<!DOCTYPE html>
<html>
<head>
<title>Student Tracker App</title>
</head>
<%
	//get the students from the request object (sent by servlet)
	List<Student> theStudents = (List<Student>) request.getAttribute("STUDENT_LIST");
%>
<body>

	<div id = "wrapper">
		<div id = "header">
			<h2>FooBar University</h2>
		</div>
	</div>
	
	<div id = "container">
		<div id = "content">
			<table>
				
				<tr>
					<th>First Name</th>
					<th>Last Name</th>
					<th>Email</th>
					
				</tr>
				
				<% for (Student tempStudent : theStudents){  %>
					<tr>
						<td> <%= tempStudent.getFirstName() %> </td>
						<td> <%= tempStudent.getLastName() %> </td>
						<td> <%= tempStudent.getEmail() %> </td>
					</tr>
					
				<% } %>
			</table>
		</div>
	</div>

</body>
</html>

css를 적용시키자

다운받은 style.css를 우리 프로젝트에 css 폴더를 만들어서 복사해준다.

jsp에 적용해준다.

<head>
	<title>Student Tracker App</title>
	<link type="text/css" rel="stylesheet" href = "css/style.css">
</head>

이제 jstl을 적용시킬 것이다.

먼저 jar파일을 복사해서 붙여준다.

<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix="c" %>


<!DOCTYPE html>
<html>
<head>
	<title>Student Tracker App</title>
	<link type="text/css" rel="stylesheet" href = "css/style.css">
</head>

<body>

	<div id = "wrapper">
		<div id = "header">
			<h2>FooBar University</h2>
		</div>
	</div>
	
	<div id = "container">
		<div id = "content">
			<table>
				
				<tr>
					<th>First Name</th>
					<th>Last Name</th>
					<th>Email</th>
					
				</tr>
				
				<c:forEach var = "tempStudent" items = "${STUDENT_LIST}">
					<tr>
						<td> ${tempStudent.firstName} </td>
						<td> ${tempStudent.lastName} </td>
						<td> ${tempStudent.email} </td>
					</tr>
					
				</c:forEach>
			</table>
		</div>
	</div>

</body>
</html>

같은 결과를 나타내지만 코드가 훨씬 간편해진다.

이제 추가로 deployment descriptor file을 만들자.

(페이지를 공유할때 보여주는 페이지)

지금은 web-student-tracker/StudentControllerServlet을 해야지만 우리가 원하는 페이지를 볼 수 있는데,

web-student-tracker만 검색해도 우리가 원하는 페이지가 나오도록 설정할 것이다.

만약 다이나믹 웹 프로젝트를 생성할 때 web.xml파일을 만들었다면, webContent에 index.html파일만 만들어주면,

web-student-tracker를 입력하면 다음과 같이 될 것이다.

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_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>web-student-tracker</display-name>
  <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>

index.html

<html>


<body>
<h1>Hello Brave New World!</h1>
</body>
</html>

servlet을 우리의 welcome파일로 지정해보자.

서블렛의 이름을 그대로 welcomefile로 지정해주면된다.

여기서 welcomefile은 위에서 아래로 순서대로 적용되는 것을 명심하자.

<?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_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>web-student-tracker</display-name>
  <welcome-file-list>
  	<welcome-file>StudentControllerServlet</welcome-file>
    <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>

잘작동된다.

'Spring' 카테고리의 다른 글

Database App with JDBC : 학생 정보를 업데이트하고 삭제하는 기능 만들기 (Update, Delete) (0)	2021.06.19
Database App with JDBC : 학생을 추가하는 기능 만들기 (Create) (0)	2021.06.19
MVC : MVC의 기본작용방법 (0)	2021.06.18
Servlets : 기본 사용 방법 (0)	2021.06.18
JSP : Standard JSP Tag Library (JSTL) - I18N Tags (Multi-Lingual App) (0)	2021.06.18

ABOUT ME

합니다, 개발공부📝 합니다, 개발공부📝

'Spring' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'Spring' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바