Employee.java
EmpDAO.java
empDesc.jsp
package web.jdbc.test;
public class Employee {
private int empno;
private String ename;
private int deptno;
private String hiredate;
private int sal;
private int mgr;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
}
package web.jdbc.test;
import java.sql.*;
import java.util.*;
public class EmpDAO {
// 데이터베이스 연결관련정보를 문자열로 선언
private String jdbc_driver = "oracle.jdbc.OracleDriver";
private String db_url = "jdbc:oracle:thin:@211.183.3.5:1521:ORCL";
private Connection getConn() throws Exception{
// JDBC 드라이버 로드하여 DriverManager에 등록함
Class.forName(jdbc_driver);
// 로드된 드라이버를 이용하여 DB와 연결하고 Connection 인스턴스 구함
Connection conn = DriverManager.getConnection(db_url,"user05","user05");
return conn;
}
private void closeAll(ResultSet rs, Statement stmt, Connection conn){
try {
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public ArrayList<Employee> getAllEmps(){
String sql ="select * from employee";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
Employee emp = null;
ArrayList<Employee> list = new ArrayList<Employee>();
while(rs.next()){
emp = new Employee();
emp.setEmpno(rs.getInt("EMPNO"));
emp.setEname(rs.getString("ENAME"));
emp.setDeptno(rs.getInt("DEPTNO"));
list.add(emp);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally{
closeAll(rs, stmt, conn);
}
return null;
}
// 사번을 전달받고 데이터베이스를 검색하여 해당 사원의 상세정보를 리턴한다
// @param empno 검색할 사원의 사번
// @return Employee 사원의 상세정보를 저장하고 있는 DTO
public Employee getEmp(int empno){
String sql = "select * from employee where empno="+empno;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()){
Employee emp = new Employee();
emp.setEmpno(rs.getInt("EMPNO"));
emp.setEname(rs.getString("ENAME"));
emp.setDeptno(rs.getInt("DEPTNO"));
emp.setHiredate(rs.getString("HIREDATE"));
emp.setSal(rs.getInt("SAL"));
emp.setMgr(rs.getInt("MGR"));
return emp;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
closeAll(rs, stmt, conn);
}
return null;
}
}
<%@page import="web.jdbc.test.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id="dao" class="web.jdbc.test.EmpDAO"/>
<%
int empno = Integer.valueOf(request.getParameter("empno"));
Employee emp = dao.getEmp(empno);
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<style type="text/css">
table {border: 1px solid black;}
td {text-align: center;}
th {border: 1px solid black; background-color: green; }
</style>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>사원의 상세정보 보기</title>
</head>
<body>
<table>
<tr>
<td colspan="4"><h1>사번이 <%=empno %>인 사원의 상세정보</h1></td>
</tr>
<tr>
<td>사번</td><td><%=empno %></td>
<td>이름</td><td><%=emp.getEname() %></td>
</tr>
<tr>
<td>부서</td><td><%=emp.getDeptno()%></td>
<td>입사일</td><td><%=emp.getHiredate()%></td>
</tr>
<tr>
<td>급여</td><td><%=emp.getSal()%></td>
<td>매니져</td><td><%=emp.getMgr()%></td>
</tr>
</table>
</body>
</html>
empList.jsp<%@page import="web.jdbc.test.Employee"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id="dao" class="web.jdbc.test.EmpDAO"/>
<%
ArrayList<Employee> list = dao.getAllEmps();
%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
table {border: 1px solid black;}
td {text-align: center;}
th {border: 1px solid black; background-color: green; }
</style>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>오라클 접속 테스트</title>
</head>
<body>
<table rules="rows">
<tr>
<th>Empno</th>
<th>Ename</th>
<th>Deptno</th>
</tr>
<%
for(int i=0;i<list.size();i++){
Employee emp = list.get(i);
int empno = emp.getEmpno();
%>
<tr>
<td><%=empno %></td>
<td><a href="empDesc.jsp?empno=<%=empno%>"><%=emp.getEname() %></a></td>
<td><%=emp.getDeptno() %></td>
</tr>
<%}
%>
</table>
</body>
</html>
http://dl.dropbox.com/u/92046264/303jsp/
'JSP > 2012.04강의(MySQL)' 카테고리의 다른 글
| 복습9 JDBC Test in Oracle (0) | 2012.07.16 |
|---|---|
| 복습8 (0) | 2012.07.13 |
| 복습7 회원 정보 저장 폼 (0) | 2012.07.12 |
| 복습6 Cookie,Session을 이용한 login 처리,자바빈(JavaBeans) (0) | 2012.07.11 |
| 복습5 페이지 모듈화,error page,Cookie (0) | 2012.07.10 |