JDBC 데이터 가져오기 예제 (DataBase -> Java -> Servlet -> JSP)

_웹에서 DB사용하려면.zip

2.05MB

 

 

 

 

 

DBService

package service;

import java.sql.Connection;
import java.sql.SQLException;

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

public class DBService {
	
	// single-ton pattern : 객체 1개만 생성해서 사용하자
	static DBService single = null;
	DataSource ds = null;

	public static DBService getInstance() {
		// 없으면 생성해라
		if (single == null) {
			single = new DBService();
		}
		return single;
	}

	private DBService() {
		
		try {
			// JNDI를 이용해서 DataSource정보를 얻어온다
			// 1. InitialContext 생성(JNDI->interface 추출객체)
			InitialContext ic = new InitialContext();
			
			// 2. Context정보 얻어온다(look up 함수가 Object랑 연관되어 있어서 Casting 해주었음)
			Context context = (Context) ic.lookup("java:comp/env");
			
			// 3. naming을 이용해서 DataSource 얻어온다
			ds = (DataSource) context.lookup("jdbc/oracle_test");
			
			// 2+3 한번에
			// ds = (DataSource) ic.lookup("java:comp/env/jdbc/oracle_test");
			
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}//end:DBService()
	
	public Connection getConnection() throws SQLException {
		// DataSource를 이용해서 BasicDataSource가 관리하고 있는 Connection을 요청
		return ds.getConnection();
	}
	
}

 

 

Vo

package db.vo;

public class SawonVo {
	
	int sabun;
	String saname;
	String sasex;
	int deptno;
	String sajob;
	String sahire;
	int samgr;
	int sapay;
	
	public int getSabun() {
		return sabun;
	}
	public void setSabun(int sabun) {
		this.sabun = sabun;
	}
	public String getSaname() {
		return saname;
	}
	public void setSaname(String saname) {
		this.saname = saname;
	}
	public String getSasex() {
		return sasex;
	}
	public void setSasex(String sasex) {
		this.sasex = sasex;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getSajob() {
		return sajob;
	}
	public void setSajob(String sajob) {
		this.sajob = sajob;
	}
	public String getSahire() {
		return sahire;
	}
	public void setSahire(String sahire) {
		this.sahire = sahire;
	}
	public int getSamgr() {
		return samgr;
	}
	public void setSamgr(int samgr) {
		this.samgr = samgr;
	}
	public int getSapay() {
		return sapay;
	}
	public void setSapay(int sapay) {
		this.sapay = sapay;
	}
	
}

 

 

Dao

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import db.vo.SawonVo;
import service.DBService;

public class SawonDao {

	// single-ton pattern : 객체 1개만 생성해서 사용하자
	static SawonDao single = null;

	public static SawonDao getInstance() {
		// 없으면 생성해라
		if (single == null) {
			single = new SawonDao();
		}
		return single;
	}

	private SawonDao() {

	}
	
	public List<SawonVo> selectList() {
		List<SawonVo> list = new ArrayList<SawonVo>();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "select * from sawon";

		try {
			// 1. Connection 얻어오기
			conn = DBService.getInstance().getConnection();

			// 2. PreparedStatement
			pstmt = conn.prepareStatement(sql);

			// 3. ResultSet 얻어오기(킵해놨던 sql문 출력하기)
			rs = pstmt.executeQuery();

			while (rs.next()) {
				// 1. rs가 가리키는 레코드의 값을 읽어온다

				// 2. 저장객체 생성 -> 레코드에서 읽은 값을 넣는다.
				SawonVo vo = new SawonVo();

				// rs가 가리키는 레코드값을 VO에 넣는다.
				vo.setSabun(rs.getInt("sabun"));
				vo.setSaname(rs.getString("saname"));
				vo.setSasex(rs.getString("sasex"));
				vo.setDeptno(rs.getInt("deptno"));
				vo.setSajob(rs.getString("sajob"));
				vo.setSahire(rs.getString("sahire"));
				vo.setSamgr(rs.getInt("samgr"));
				vo.setSapay(rs.getInt("sapay"));

				// ArrayList에 추가
				list.add(vo);

			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 예외 처리에서 무조건 실행되는 부분
			// 마무리 작업(열린역순으로 닫기)
			// 한 번에 잡아서 try catch 하기 : alt + shift + s
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return list;
	}
	
}

 

 

Servlet

package action;

import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

import dao.SawonDao;
import db.vo.SawonVo;

/**
 * Servlet implementation class SawonListAction
 */
@WebServlet("/sawon/list.do")
public class SawonListAction extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		List<SawonVo> list = SawonDao.getInstance().selectList();
		
		request.setAttribute("list", list);
		
		String forward_page = "sawon_list.jsp";
		RequestDispatcher disp = request.getRequestDispatcher(forward_page);
		disp.forward(request, response);
	}
}

 

 

JSP

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
 <!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<style type="text/css">
	#box {
		width: 600px;
		margin: auto;
		margin-top: 50px;
	}
	
	.mycolor {
		background: #333333;
		color: white;
	}
</style>
</head>
<body>

	<div id ="box">
		<table class="table-hover">
			<tr class="mycolor">
				<th>사원번호</th>
				<th>사원이름</th>
				<th>성별</th>
				<th>번호</th>
				<th>직업</th>
				<th>입사날짜</th>
				<th>상사</th>
				<th>연봉</th>
			</tr>
			<c:forEach var="vo" items="${ requestScope.list }">
			<tr>
				<td>${ pageScope.vo.sabun }</td>
				<td>${ pageScope.vo.saname }</td>
				<td>${ pageScope.vo.sasex }</td>
				<td>${ vo.deptno }</td>
				<td>${ vo.sajob }</td>
				<td>${ fn:substring(vo.sahire,0,10) }</td>
				<td>${ vo.samgr }</td>
				<td><fmt:formatNumber type="currency" value="${ vo.sapay*10000 }"/> </td>
			</tr>
			</c:forEach>
		</table>
	</div>
</body>
</html>

 

 

 

DeptVo에서 한 가지 데이터만 출력할 때(selectOne)

 

 

// 하나의 정보만 가져올 때 (where 사용해서 ex)deptno=10)
	public DeptVo selectOne(int deptno) {
		DeptVo vo = null;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "select * from dept where deptno=?";

		try {
			// 1. Connection 얻어오기
			conn = DBService.getInstance().getConnection();

			// 2. PreparedStatement
			pstmt = conn.prepareStatement(sql);
			
			// 3. pstmt parameter index 채우기
			pstmt.setInt(1, deptno);

			// 4. ResultSet 얻어오기(킵해놨던 sql문 출력하기)
			rs = pstmt.executeQuery();
			
			// while문을 사용해도 되지만, 공회전이 1회더 발생한다.
			if (rs.next()) {
				// 1. rs가 가리키는 레코드의 값을 읽어온다

				// 2. 저장객체 생성 -> 레코드에서 읽은 값을 넣는다.
				vo = new DeptVo();

				// rs가 가리키는 레코드값을 VO에 넣는다.
				vo.setDeptno(rs.getInt("deptno"));
				vo.setDname(rs.getString("dname"));
				vo.setLoc(rs.getString("loc"));
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			// 예외 처리에서 무조건 실행되는 부분
			// 마무리 작업(열린역순으로 닫기)
			// 한 번에 잡아서 try catch 하기 : alt + shift + s
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return vo;
	}

 

package action;

import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

import dao.DeptDao;
import db.vo.DeptVo;

/**
 * Servlet implementation class DeptListAction
 */
@WebServlet("/dept/list.do")
public class DeptListAction extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 부서데이터 가져오기(DeptDao -> DBService)
		//List<DeptVo> list = DeptDao.getInstance().selectList();
		DeptVo vo = DeptDao.getInstance().selectOne(10);
		
		// request binding(list에 가져온 데이터 request에 저장)
		request.setAttribute("vo", vo);
		// 이로써, 현재 이 class와 dept_list와 list가 공유되어 있다.

		// Dispatcher형식으로 호출
		//                    webapp/dept/폴더내에 dept_list.jsp 생성
		String forward_page = "dept_list.jsp";
		RequestDispatcher disp = request.getRequestDispatcher(forward_page);
		disp.forward(request, response);

	}

}

 

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

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<style type="text/css">
	#box {
		width: 600px;
		margin: auto;
		margin-top: 50px;
	}
	
	table {
	/* !important : 충돌시 우선적용하겠다 */
		border: 2px solid blue !important;
	}
	
	.mybtn{
	   background: blue;
	}
</style>
</head>
<body>

	<button type="button" class="btn btn-outline-secondary mybtn">Action</button>


	<div id="box">
		<table class="table table-bordered table-hover">
			<tr class="success">
				<th>부서번호</th>
				<th>부서명</th>
				<th>위치</th>
			</tr>
			<!-- DeptListAction와 request의 데이터 창고가 공유되어 있다. -->
			<!-- for(DeptVo vo : list) 동일함. -->
			<tr>
				<td>${ vo.deptno }</td>
				<td>${ vo.dname }</td>
				<td>${ vo['loc'] }</td>
			</tr>
			
		</table>
	</div>

</body>
</html>