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);
}
}
데이터 조회
DAO: DepartmentDao.java
/* import부분 생략 */
public class DepartmentDao{
// DB에 접속해 부서정보를 조회할 DAO 작성
public List<Department> getAllDepartments() throws SQLException{
String sql = "select department_id, department_name "
+"from departments "
+"order by d.department_id"
List<Department> departments = new ArrayList<>();
/* ConnectionUtil에서 정의한 getConnection호출 */
Connection connection = ConnectionUtil.getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
Department department = new Department();
/* getInt&getString("컬럼") 컬럼을 지정해 값을 가져옴*/
/* setId(값)으로 db내 데이터를 department객체에 저장 */
department.setId(rs.getInt("department_id"));
department.setName(rs.getString("department_name"));
/* 가져온 값들을 List<Department> departments에 순서대로 저장 */
departments.add(department);
}
rs.close();
pstmt.close();
connection.close();
return departments;
}
}
메인: home.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>인사관리</h3>
<ul>
<li><a href="hr/dept/list.jsp">1. 부서관리</a></li>
</body>
</html>
부서관리페이지: /hr/dept/list.jsp
<%@page import="java.util.List"%>
<%@page import="com.sample.dao.DepartmentDao"%>
<%@page import="com.sample.dto.Department"%>
<%@ 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 void insertDepartment(String departmentName) throws SQLException{
String sql = "insert into departments(department_id, department_name) "
+ "values(departments_seq.nextval, ?)";
Connection connection = getConnection();
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, departmentName);
pstmt.executeUpdate();
pstmt.close();
connection.close();
}
}
메인: home.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h3>인사관리</h3>
<ul>
<li><a href="hr/dept/list.jsp">2. 새 부서 등록</a></li>
</body>
</html>
<%@page import="java.util.List"%>
<%@ 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>
<div>
/* 입력된 정보를 register.jsp로 보내 db에 등록*/
<form class="well" method="post" action="register.jsp">
<div class="form-group">
<label>부서이름</label>
<input type="text" name="deptname"/>
</div>
<div class="text-right">
<input class="btn" type="submit" value="등록"/>
</div>
</form>
</div>
</body>
</html>
부서등록 로직: /hr/dept/register.jsp
<%@page import="com.sample.dao.DepartmentDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
DepartmentDao departmentDao = new DepartmentDao();
/* form.jsp에서 입력한 값은 deptname이라는 요청객체에 저장된 상태 */
// request.getParameter 메서드로 요청된 파라미터 값을 읽어옴
String departmentName = request.getParameter("deptname");
// 부서정보를 저장하는 메서드 실행
departmentDao.insertDepartment(departmentName);
// response.sendRedirect 메서드로 리다이렉트(부서관리창)
response.sendRedirect("list.jsp");
%>
