다연이네

[days03] DB연결 (알집부터) (DBConn, DeptDTO, EmpDTO) 본문

JSP

[days03] DB연결 (알집부터) (DBConn, DeptDTO, EmpDTO)

　다연　 2020. 12. 24. 21:21
반응형

우선, ojdbc6.jar 파일을 WEB-INF > lib 폴더 안에 넣는다.

(나는 예전에 ojdbc6.jar를 C드라이브에 저장시켜 둬서 그냥 끌어 놓았다.)

위 파일을 

 

이 위치로 끌어다 놓기

 

그리고 jstl-1.2 다운받아서 또 끌어다 놓기 (다연아 sd카드에 있다~)

ㄱ. http://archive.apache.org/dist/jakarta/taglibs/standard/binaries/ 

ㄴ. jakarta-taglibs-standard-1.1.2.zip  다운로드 
ㄷ. jstl.jar, standard.jar를 WEB-INF > lib 안에 끌어놓기 (JSTL을 사용할 수 있는 환경이 된 것)

 

 

준비완료

 

 

 

DB에서 dept정보를 가져와 list option에 담기

<%@page import="java.sql.PreparedStatement"%>
<%@page import=" java.sql.Connection"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.Iterator"%>
<%@page import="days03.DeptDTO"%>
<%@page import="com.util.DBConn"%>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<%
String sql = "SELECT deptno, dname, loc FROM DEPT";
Connection con = DBConn.getConnection(); //connection객체 얻어옴
ArrayList<DeptDTO> list = null; //캐치블록 밖에 빼주기

//CRUD
//1. 쿼리를 수행할 객체가 필요 -3가지                  : Statement    3가지 종류
//2. select 한 결과물을 가지고 있는 저장하고 있는 객체  : ResultSet 
try {
   PreparedStatement stmt = con.prepareStatement(sql);
   ResultSet rs = stmt.executeQuery(sql);
   if(rs.next()){
	   
   list = new ArrayList<DeptDTO>();
   do{
	   DeptDTO dto = new DeptDTO();
	  	 dto.setDeptno(rs.getInt(1));
	  	 dto.setDname(rs.getString(2));
	  	 dto.setLoc(rs.getString(3));
	  	 
	  	 list.add(dto); //굳이 왜 어레이리스트에 담나 ? 나중에 출력용에 담아서 넘겨줌
   }while(rs.next()) ;
}
   rs.close();    //닫는거 잊지말자!
   stmt.close();  //닫는거 잊지말자!
   
} catch (SQLException e) {
   e.printStackTrace();
}


DBConn.close();

%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="">
<style>
</style>
<script>
   $(document).ready(function (){     
   });
</script>
</head>
<body>

<form action="">
<select name="" id="">
<option value="">부서 선택...</option>
<%
if(list==null){
	%>
	<option value="">부서가 존재하지 않습니다.</option>
	<%
}else{
	Iterator<DeptDTO> ir = list.iterator();
	while(ir.hasNext()){
		DeptDTO dto = ir.next();
		//deptno,dname,loc
		%>
		<option value="<%=dto.getDeptno()%>"><%=dto.getDname() %></option>
		<%
	}//while
}//if
%>
</select>
</form>
</body>
</html>

위 코딩은 로직을 처리하는 파트 (jdbc 로직 코딩) + 화면에 뿌려지는 (html디자인 코딩)이 합쳐져 있다.

=> 나중에 유지, 보수, 확장에 어렵다.

 

(로직/디자인 두개로 나눠보자)
로직(서블릿)에 request(list) 포워딩 시킬 것이다. 
jsp페이지는 디자인 담당이니 list에 접근 가능할 것(포워딩에 의해)

 

ex01.jsp

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="">
<style>
</style>
<script>
   $(document).ready(function (){     
   });
</script>
</head>
<body>
<h3>ex01.jsp</h3>
<a href="/jspPro/days03/dept">/jspPro/days03/dept</a>
</body>
</html>

a태그를 클릭하면 .java 파일(서블릿)로 연결

 

DeptInfo.java (서블릿)

package days03;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

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

import com.util.DBConn;

// 서블릿 규약
public class DeptInfo extends HttpServlet{

	@Override
	protected void doGet(HttpServletRequest request
			, HttpServletResponse response) throws ServletException, IOException {
		String sql ="SELECT deptno, dname, loc FROM DEPT";
		Connection con = DBConn.getConnection(); 
		
		ArrayList<DeptDTO> list  = null;
		try {
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				list = new ArrayList<DeptDTO>();
				do{
					DeptDTO dto = new DeptDTO();
					dto.setDeptno(  rs.getInt(1) );
					dto.setDname( rs.getString(2) );
					dto.setLoc( rs.getString(3));
			        list.add(dto);
				}while(rs.next());
			} // if
			rs.close();         
			pstmt.close();    
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		DBConn.close();
		// ArrayList list 을 request 에 저장해서 포워딩 
		request.setAttribute("list", list);
		// 포워딩
		String path = "/days03/ex04.jsp";
		//String path = "/days03/ex04_EL_JSTL.jsp";
		RequestDispatcher dispatcher = request.getRequestDispatcher(path);
		dispatcher.forward(request, response);
	}

	@Override
	protected void doPost(HttpServletRequest request
			, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
	
	

}

ex04.jsp

<%@page import="java.util.ArrayList"%>
<%@page import="java.util.Iterator"%>
<%@page import="days03.DeptDTO"%>

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
 	ArrayList<DeptDTO> list = (ArrayList<DeptDTO>)request.getAttribute("list");
//모든 클래스의 부모니까 형변환
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="">
<style>
</style>
<script>
   $(document).ready(function (){     
   });
</script>
</head>
<body>
<!-- 
1.JSP -> Oracle : ojdbc6.jar
	WEB-INF > lib 폴더 안에 ojdbc6.jar
2. com.util 패키지
 -->

<form action="">
<select name="" id="">
<option value="">부서 선택...</option>
<%
if(list==null){
	%>
	<option value="">부서가 존재하지 않습니다.</option>
	<%
}else{
	Iterator<DeptDTO> ir = list.iterator();
	while(ir.hasNext()){
		DeptDTO dto = ir.next();
		//deptno,dname,loc
		%>
		<option value="<%=dto.getDeptno()%>"><%=dto.getDname() %></option>
		<%
	}//while
}//if
%>
</select>
</form>
</body>
</html>

위 코딩을 EL 연산자를 사용하여 수정

<form action=""> 

<select>
  <option value="">부서 선택..</option>
  <!-- empty  EL 연산자 -->
  <!-- c:if  JSP 표준 태그 라이브러리( JSTL ) -->
  <c:if test="${  empty  list }">
      <option value="">부서가 존재 X.</option>
  </c:if>
  <!--   !   not EL 연산자 -->
  <c:if test="${ not empty list }">
     <c:forEach items="${ list }" var="dto">
         <option value="${  dto.deptno }">${ dto.dname }</option>
     </c:forEach>
  </c:if> 
</select>

</form>

 

 

위 코딩을 option을 누르면 해당 부서의 사원을 출력하는 페이지로 넘어가도록 추가해보자.

<%@page import="java.util.Iterator"%>
<%@page import="days03.DeptDTO"%>
<%@page import="java.util.ArrayList"%>

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!-- 지금은 공식처럼 암기하듯이  -->
<%
 	ArrayList<DeptDTO> list = (ArrayList<DeptDTO>)request.getAttribute("list");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="">
<style>
</style>
<script>
$(document).ready(function (){	  
	   $("#deptno").change(function(event) {
	   	   if(   $(this).val()  ){
		   	   $("form")
		   	       .attr({
		   	    	  action:"/jspPro/days03/emp",
		   	    	  method:"post"
		   	       })	   	       
		   	       .submit();
	   	   }else{
	   		   alert("부서를 다시 선택해 주세요.^^");
	   	   }
	   });
});
</script>
</head>
<body>

<form action=""> 
<select name="deptno" id="deptno">
  <option value="">부서 선택..</option>
  <!-- empty  EL 연산자 -->
  <!-- c:if  JSP 표준 태그 라이브러리( JSTL ) -->
  <c:if test="${  empty  list }">
      <option value="">부서가 존재 X.</option>
  </c:if>
  <!--   !   not EL 연산자 -->
  <c:if test="${ not empty list }">
     <c:forEach items="${ list }" var="dto">
         <option value="${  dto.deptno }">${ dto.dname }</option>
     </c:forEach>
  </c:if> 
</select>
</form>

</body>
</html>

EmpInfo.java(서블릿)

package days03;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

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 com.util.DBConn;

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

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public EmpInfo() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request,
    	  HttpServletResponse response) throws ServletException, IOException {
		
		String deptno = request.getParameter("deptno");
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<EmpDTO> list = null;
		EmpDTO dto = null; 

		String sql =  " select   rank() OVER (ORDER BY sal DESC) no  , empno, ename, sal "
				+ " from emp "
				+ " where deptno = ? ";

		con = DBConn.getConnection();
		int count = 0;
		try {
			pstmt = con.prepareStatement(sql); 
			pstmt.setString(1,  deptno );
			rs = pstmt.executeQuery(); 
			
			if (rs.next()) {  
				list = new ArrayList<EmpDTO>(); // 사원이 존재할 경우에만 new 객체 생성
				do {						
					count++;
					int no = rs.getInt("no");
					int empno = rs.getInt("empno");
					String ename = rs.getString("ename");
					double sal = rs.getDouble("sal");

					dto = new EmpDTO();
					dto.setNo(no);
					dto.setEmpno(empno);
					dto.setEname(ename);
					dto.setSal(sal);
					list.add(dto);  
				} while (rs.next()); 
			} // if
		} catch (SQLException e) { 
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				DBConn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}// try

		// request 저장
		request.setAttribute("list", list);
		request.setAttribute("count", count);
		// 포워딩
		String path = "/days03/ex04_emp.jsp";
		//String path = "/days03/ex04_emp_EL_JSTL.jsp";
		RequestDispatcher dispatcher = request.getRequestDispatcher(path);
		dispatcher.forward(request, response);

	}  // doGet

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request,
      	HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

 

ex04_emp.jsp

<%@page import="java.util.Iterator"%>
<%@page import="days03.EmpDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%
    ArrayList<EmpDTO> list =  (ArrayList<EmpDTO>)request.getAttribute("list");
    int count = (int)request.getAttribute("count");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>SiSt13-k≡n¡k (2020. 12. 24-오후 4:21:22)</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

<script>
   $(document).ready(function (){	  
   });
</script>
</head>
<body>
<p></p>
<p></p>
<div class="container">
	<table class="table">
		<!-- <thead class="thead-dark"> -->
		<thead class="thead-light">
		   <tr>
			<th>no</th>
			<th>empno</th>
			<th>ename</th>
			<th>pay</th>
		  </tr>
		</thead>
		<tbody>
			<!-- 
			<tr>
				<td>1</td>
				<td>7521</td>
				<td>BLAKE</td>
				<td>2850.0</td>
			</tr>
			 --> 
			 <%
			     if(list == null ){
			  %>
			      <tr><td colspan="4">사원이 존재하지 않습니다.</td></tr>
			  <%  	 
			     }else{
			    	 Iterator<EmpDTO> ir  = list.iterator();
			    	 while(ir.hasNext()){
			    		 EmpDTO dto =  ir.next(); 
			   %>
			       <tr>
			          <td><%= dto.getNo() %></td>
			          <td><%= dto.getEmpno() %></td>
			          <td><%= dto.getEname() %></td>
			          <td><%= dto.getSal() %></td>
			       </tr> 
			   <%    		 
			    	 } //  while
			     } // if
			  %>
		</tbody>
		<tfoot>
		   <tr>
		      <td colspan="4">${ param.deptno }번 부서원은 <%= count %>명입니다.</td>
		   </tr>
		</tfoot>
	</table>
</div>

</body>
</html>

위 코딩을 EL 연산자를 사용하여 수정

<c:if test="${  empty  list }">
 <tr>
	<td colspan="4">사원이 존재하지 않습니다.</td>
 </tr>
</c:if>
<c:if test="${ not empty list }">
 <c:forEach items="${ list }" var="dto">
  <tr>
	<td>${ dto.no }</td>
	<td>${ dto.empno }</td>
	<td>${ dto.ename }</td>
	<td>${ dto.sal }</td>
  </tr>
 </c:forEach>
</c:if>

 

반응형
저작자표시 비영리

'JSP' 카테고리의 다른 글

[days04] 해당 사원 정보 테이블로 출력, 버튼 클릭시 사원번호 넘기기  (0) 2020.12.28
[days04] accordion, tabs, select로 선택한 부서의 사원 정보 출력  (0) 2020.12.28
[days03] 리다이렉트 + 포워딩 + 서블릿 복습 / 히든(hidden)  (0) 2020.12.24
[days02] 리다이렉트(redirect)와 포워딩(forward)의 차이점  (0) 2020.12.23
[days02] 리다이렉트(redirect)  (0) 2020.12.23
'JSP' Related Articles more
Comments