DTO, Util
DTO: Department.java
public class Department{
// db의 null값까지 포함하기 위해 int가 아닌 Integer를 데이터 타입으로 지정
private Integer id;
private String name;
public Department(){}
public Integer getId(){
return id;
}
public void setId(Integer id){
this.id=id;
}
public String getName(){
return name;
}
public void setName(String name){
this.name=name;
}
}
ConnectionUtil
/* import부분 생략 */
public class ConnectionUtil {
private static final String url = "jdbc:oracle:thin:@localhost:1521:xe";
// url값은 db설정 값에 따라 달라질 수 있으니 주의
private static final String id = "";
// db에 설정한 id값 입력
private static final String pw = "";
// db에 설정한 pw값 입력
/* JDBC드라이버 로딩 */
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException ex) {
ex.getStackTrace();
}
}
/* JDBC연결메서드 정의 */
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,id,pw);
}
}
부서관리페이지: /hr/dept/list.jsp
/* import부분 생략 */
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>부서정보 관리::부서목록</title>
</head>
<body>
<h1>부서 관리</h1>
<table>
<thead>
<tr>
<th>아이디</th>
<th>부서명</th>
</tr>
</thead>
<tbody>
<%
DepartmentDao departmentDao = new DepartmentDao();
List<Department> departmentList = departmentDao.getAllDepartments();
for(Department dept : departmentList){
%>
<tr>
<td><%=dept.getId() %></td>
<td><%=dept.getName() %></a></td>
</tr>
<%
}
%>
</tbody>
</table>
</body>
</html>
상세정보 조회
DAO: DepartmentDao.java
/* import부분 생략 */
public class DepartmentDao{
/* 다른 기능 생략 */
public Department getDepartmentById(int departmentId) throws SQLException{
String sql="select d.department_id, d.department_name "
+ "from departments "
+ "where d.department_id=?";
Department department = null;
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, departmentId);
ResultSet rs = pstmt.executeQuery();
if(rs.next()) {
department = new Department();
department.setId(rs.getInt("department_id"));
department.setName(rs.getString("department_name"));
}
rs.close();
pstmt.close();
connection.close();
return department;
}
}
부서상세정보: /hr/dept/detail.jsp
/* import부분 생략 */
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" type="text/css" href="../../resources/css/style.css">
<title>부서정보 관리::부서 상세정보</title>
</head>
<body>
<h1>부서정보</h1>
<%
int departmentId = Integer.parseInt(request.getParameter("deptId"));
DepartmentDao departmentDao = new DepartmentDao();
EmployeeDao employeeDao = new EmployeeDao();
// 부서 아이디로 부서 상세정보 조회
Department department = departmentDao.getDepartmentById(departmentId);
%>
<h3>부서 상세정보</h3>
<table>
<thead>
<tr>
<th>아이디</th>
<th>부서명</th>
</tr>
</thead>
<tbody>
<tr>
<td><%=departmentId %></td>
<td><%=department.getName() %></td>
</tr>
</tbody>
</table>
<div class="text-right">
<a href="modifyform.jsp?deptId=<%=departmentId %>">수정</a>
<a href="delete.jsp?deptId=<%=departmentId %>">삭제</a>
</div>
// 부서 목록으로 돌아가는 링크
<div class="text-right">
<a href="list.jsp">부서 목록</a>
</div>
</body>
</html>
데이터 수정
DAO: DepartmentDao.java
/* import부분 생략 */
public class DepartmentDao{
/* 다른 기능 생략 */
public void updateDepartment(Department department) throws SQLException{
String sql = "update departments "
+ "set"
+ " department_name=?, "
+ "where department_id = ? ";
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, department.getName());
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" type="text/css" href="../../resources/css/style.css">
<title>부서정보 관리::수정폼</title>
</head>
<body>
<h1>부서정보 수정폼</h1>
<%
// request.getParameter 메서드로 요청된 파라미터 값을 읽어옴
int departmentId = Integer.parseInt(request.getParameter("deptId"));
DepartmentDao departmentDao = new DepartmentDao();
// 부서아이디에 해당하는 부서상세정보 조회
Department department = departmentDao.getDepartmentById(departmentId);
%>
<div>
// post로 입력 내용을 객체에 포함하여 전달함으로써, url창에 전송정보 노출하지 않음
<form class="well" method="post" action="update.jsp">
<div class="form-group">
<label>부서아이디</label>
// readonly속성으로 수정 불가
<input type="text" name="id" value="<%=department.getId()%>" readonly = "readonly"/>
</div>
<div class="form-group">
<label>부서명</label>
<input type="text" name="name" value="<%=department.getName()%>"/>
</div>
<div class="text-right">
<button class="btn">수정</button>
</div>
</form>
</div>
</body>
</html>
- form과 함께 쓰이면 button은 자연적으로 submit기능을 갖게 됨
<button type="button">으로 정의하면 submit기능 방지 가능
데이터 수정 로직: /hr/dept/update.jsp
/* import생략 */
<%
int departmentId = Integer.parseInt(request.getParameter("id"));
String departmentName = request.getParameter("name");
DepartmentDao departmentDao = new DepartmentDao();
// 테이블 값을 수정할 때는 항상 테이블에 저장된 현재값을 먼저 조회해야 함
Department department = departmentDao.getDepartmentById(departmentId);
//입력폼에서 제출한 값을 대입
department.setName(departmentName); // 수정폼에서 입력한 부서명
// 부서정보를 수정하는 메서드 실행
departmentDao.updateDepartment(department);
// response.sendRedirect 메서드로 리다이렉트
response.sendRedirect("detail.jsp?deptId="+departmentId);
%>
데이터 삭제
DAO: DepartmentDao.java
/* import부분 생략 */
public class DepartmentDao{
/* 다른 기능 생략 */
public void deleteDepartment(int departmentId) throws SQLException{
String sql = "delete from departments "
+ "where department_id = ? ";
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, departmentId);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
}
데이터삭제 로직: /hr/dept/delete.jsp
<%@page import="com.sample.dao.DepartmentDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
// dept값을 integer로 형변환
int departmentId = Integer.parseInt(request.getParameter("deptId"));
DepartmentDao departmentDao = new DepartmentDao();
// 부서정보 삭제하기
departmentDao.deleteDepartment(departmentId);
// response.sendRedirect 메서드로 리다이렉트
response.sendRedirect("list.jsp");
%>
