웹 - JDBC
웹 - jdbc - 예제(dept 테이블)
우가본
2024. 6. 19. 12:17
)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 {
// 싱글톤 패턴 : 객체 1개만 생성해서 이용하자
static DBService single = null;
DataSource ds = null;
public static DBService getInstance() {
if(single == null) single = new DBService(); // 없으면 니 객체를 생성해라
return single;
}
// 외부에서 객체 생성을 못하게 하기 위해 private 접근제어자 사용
private DBService() {
try {
// JNDI을 이용해s서 DataSource정보를 얻어온다
// 1. InitialContext생성(JNDI->interface추출객체)
InitialContext ic = new InitialContext();
// 2. Context정보(context.xml) 얻어온다
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) {
e.printStackTrace();
}
}// end - Contructor
public Connection getConnection() throws SQLException {
// DataSource를 이용해서 BasicDataSource가
// 관리하고 있는 커넥션을 요청
return ds.getConnection();
}
}
)vo
package db.vo;
// 3위일체
// DB Column명 == VO 속성명 == form parameter명
public class DeptVo {
int deptno;
String dname;
String loc;
public DeptVo() {
// TODO Auto-generated constructor stub
}
public DeptVo(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
)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.DeptVo;
import service.DBService;
// DAO(Data Access Object)
// Create : insert 추가
// Read : select 조회
// Update : update 수정
// Delete : delete 삭제
public class DeptDao {
// single-ton pattern : 객체 1개만 생성해서 이용하자
static DeptDao single = null;
public static DeptDao getInstance() {
//없으면 생성해라
if (single == null)
single = new DeptDao();
return single;
}
// 외부에서 객체생성하지 말아라...
private DeptDao() {
}
// import정리 : ctrl + shitf + o
// 부서조회
public List<DeptVo> selectList() {
List<DeptVo> list = new ArrayList<DeptVo>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from dept";
try {
//1.Connection 얻어오기
conn = DBService.getInstance().getConnection();
//2.PreparedStatement
pstmt = conn.prepareStatement(sql);
//3.ResultSet 얻어온다
rs = pstmt.executeQuery();
while (rs.next()) {
//저장객체 생성->레코드에서 읽은 값을 넣는다
DeptVo vo = new DeptVo();
//rs가 가리키는 레코드값을 vo에 넣는다
vo.setDeptno(rs.getInt("deptno"));
vo.setDname(rs.getString("dname"));
vo.setLoc(rs.getString("loc"));
//ArrayList에 추가
list.add(vo);
} //end:while
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
//마무리 작업(열린역순으로 닫기)
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;
}
}
)DeptListAction - 템플릿 저장 (_servlet_forward)
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 {
// 부서데이터 가져오기
List<DeptVo> list = DeptDao.getInstance().selectList();
//request binding
request.setAttribute("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;
box-shadow: 2px 2px 2px black;
}
table {
border: 2px solid gray !important;
}
</style>
</head>
<body>
<div id="box">
<table class="table table-border table-hover">
<tr class="info">
<th>부서번호</th>
<th>부서명</th>
<th>위치</th>
</tr>
<!-- for(DeptVo vo : list) 동일함. -->
<c:forEach var="vo" items="${ requestScope.list }">
<tr>
<td>${ pageScope.vo.deptno }</td>
<td>${ vo.dname }</td>
<td>${ vo['loc'] }</td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>
완성
)부서 일부만 존재
// 부서 일부만 조회
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 얻어온다
rs = pstmt.executeQuery();
if (rs.next()) {
//저장객체 생성->레코드에서 읽은 값을 넣는다
vo = new DeptVo();
//rs가 가리키는 레코드값을 vo에 넣는다
vo.setDeptno(rs.getInt("deptno"));
vo.setDname(rs.getString("dname"));
vo.setLoc(rs.getString("loc"));
} //end:if
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
//마무리 작업(열린역순으로 닫기)
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;
}