emp테이블의 ename 출력하고 클릭하면 상세정보 나옴(dept테이블 조인)

DataAccessException.java

==========================================================

package kr.co.seoulit.common.dao;

@SuppressWarnings("serial")
public class DataAccessException extends RuntimeException {
 public DataAccessException(){
 super();
 }
 public DataAccessException(String message){
 super(message);
 }
 public DataAccessException(Throwable cause){
 super(cause);
 }
 public DataAccessException(String message, Throwable cause){
 super(message, cause);
 }
}

 

 

ServiceLocator.java

==============================================

package kr.co.seoulit.common.sl;

import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

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

public class ServiceLocator {
 private Map<String,DataSource> cache;
 private Context envCtx;
 private static ServiceLocator instance;

 static {
 try {
 instance = new ServiceLocator();
 } catch (ServiceLocatorException e) {
 e.printStackTrace();
 }
 }

 private ServiceLocator() {
 try {
 envCtx = new InitialContext();
 cache = Collections.synchronizedMap(new HashMap<String,DataSource>());
 } catch (Exception e) {
 e.printStackTrace();
 throw new ServiceLocatorException(e.getMessage());
 }
 }

 public static ServiceLocator getInstance() {
 return instance;
 }

 public DataSource getDataSource(String jndiName) throws ServiceLocatorException {
 DataSource dataSource;
 try {
 if (cache.containsKey(jndiName)) {
 dataSource=cache.get(jndiName);
 } else {
 dataSource = (DataSource)envCtx.lookup("java:comp/env/"+jndiName);
 cache.put(jndiName, dataSource);
 }
 } catch (NamingException e) {
 throw new ServiceLocatorException(e.getMessage());
 }
 return dataSource;
 }
}

ServiceLocatorException.java

===========================================================
package kr.co.seoulit.common.sl;

public class ServiceLocatorException extends RuntimeException {

 private static final long serialVersionUID = 1L;

 public ServiceLocatorException(String message) {
 // TODO Auto-generated constructor stub
 super(message);
 }

}

 

 

EmpDAO.java

=====================================================

package kr.co.seoulit.emp.dao;

import java.util.List;

import kr.co.seoulit.common.dao.DataAccessException;
import kr.co.seoulit.emp.to.EmpBean;

public interface EmpDAO {
 public List<EmpBean> selectEmpList() throws DataAccessException;
}

 

EmpDAOImpl.java

===============================================================

package kr.co.seoulit.emp.dao;

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

import javax.sql.DataSource;

import kr.co.seoulit.common.dao.DataAccessException;
import kr.co.seoulit.common.sl.ServiceLocator;
import kr.co.seoulit.emp.to.EmpBean;

public class EmpDAOImpl implements EmpDAO {

 private static EmpDAOImpl instance = new EmpDAOImpl();

 private EmpDAOImpl() {
 }

 public static EmpDAOImpl getInstance() {
  return instance;
 }

 public List<EmpBean> selectEmpList() throws DataAccessException {
  List<EmpBean> v = new ArrayList<EmpBean>();
  Connection con = null;
  PreparedStatement pstmt = null;
  try {
   StringBuffer query = new StringBuffer();
   query.append("select * from emp");
   DataSource dataSource = ServiceLocator.getInstance().getDataSource(
     "jdbc/myoracle");
   con = dataSource.getConnection();
   pstmt = con.prepareStatement(query.toString());
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
    EmpBean member = new EmpBean();
    member.setEname(rs.getString("ename"));
    member.setEmpno(rs.getString("empno"));
    member.setJob(rs.getString("job"));
    member.setMgr(rs.getString("mgr"));
    member.setHiredate(rs.getString("hiredate"));
    v.add(member);
   }
   return v;
  } catch (Exception sqle) {
   throw new DataAccessException(sqle.getMessage());
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
     pstmt = null;
    }
    if (con != null) {
     con.close();
     con = null;
    }
   } catch (Exception e) {
    throw new RuntimeException(e.getMessage());
   }
  }
 }
 
 public EmpBean selectEmp(String id) throws DataAccessException {
  Connection con = null;
  PreparedStatement pstmt = null;
  try {
   StringBuffer query = new StringBuffer();
   query.append("select * from emp e, dept d where ename=? and e.DEPTNO=d.DEPTNO");
   DataSource dataSource = ServiceLocator.getInstance().getDataSource(
     "jdbc/myoracle");
   con = dataSource.getConnection();
   pstmt = con.prepareStatement(query.toString());
   pstmt.setString(1, id);
   ResultSet rs = pstmt.executeQuery();
   EmpBean emp=null;
   if (rs.next()) {
    emp = new EmpBean();
    emp.setEmpno(rs.getString("empno"));
    emp.setJob(rs.getString("job"));
    emp.setMgr(rs.getString("mgr"));
    emp.setHiredate(rs.getString("hiredate"));
    emp.setLoc(rs.getString("loc"));
   }
   return emp;
  } catch (Exception sqle) {
   throw new DataAccessException(sqle.getMessage());
  } finally {
   try {
    if (pstmt != null) {
     pstmt.close();
     pstmt = null;
    }
    if (con != null) {
     con.close();
     con = null;
    }
   } catch (Exception e) {
    throw new RuntimeException(e.getMessage());
   }
  }
 }
}

 

 

 

EmpBean.java

========================================================

package kr.co.seoulit.emp.to;

public class EmpBean {
 private String ename, empno, job, mgr, hiredate, loc;

 public String getLoc() {
  return loc;
 }

 public void setLoc(String loc) {
  this.loc = loc;
 }

 public String getEname() {
  return ename;
 }

 public void setEname(String ename) {
  this.ename = ename;
 }

 public String getEmpno() {
  return empno;
 }

 public void setEmpno(String empno) {
  this.empno = empno;
 }

 public String getJob() {
  return job;
 }

 public void setJob(String job) {
  this.job = job;
 }

 public String getMgr() {
  return mgr;
 }

 public void setMgr(String mgr) {
  this.mgr = mgr;
 }

 public String getHiredate() {
  return hiredate;
 }

 public void setHiredate(String hiredate) {
  this.hiredate = hiredate;
 }

}

 

 

db1.jsp

==============================================================

<%@page import="kr.co.seoulit.emp.to.EmpBean"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.seoulit.emp.dao.EmpDAOImpl"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
 pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
<style type="text/css">
 td {border:3px solid navy; }
</style>
</head>
<body><table>
<%
 List<EmpBean> list=EmpDAOImpl.getInstance().selectEmpList();
 for(EmpBean bean:list){
 out.println("<tr>");
 out.println("<td>"+"<a href=db2.jsp?id="+bean.getEname()+">"+bean.getEname()+"</td>");
 out.println("</tr>");
 }
%>
</table></body>
</html>

 

 

 

db2.jsp

================================================================

<%@page import="kr.co.seoulit.emp.to.EmpBean"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.seoulit.emp.dao.EmpDAOImpl"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
 pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
<style type="text/css">
 td {border:3px solid navy; }
</style>
</head>
<body><table>
<%
 EmpBean bean=EmpDAOImpl.getInstance().selectEmp(request.getParameter("id"));

if(bean!=null){
 out.println("<tr>");
out.println("<td>"+bean.getEmpno()+"</td>");
 out.println("<td>"+bean.getJob()+"</td>");
 out.println("<td>"+bean.getMgr()+"</td>");
 out.println("<td>"+bean.getHiredate()+"</td>");
 out.println("<td>"+bean.getLoc()+"</td>");
 out.println("</tr>");
 }
%>
</table></body>
</html>