본문 바로가기
Programming

Employee DB연결 예제.

by Mizix 2009. 8. 13.
반응형

View

search.jsp

<%@ 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>
</head>
<body>
<form action="empSearch.do" method="get">
검색어 이름을 입력하세요 : <input type="text" name="searchName"/><br>
<input type="submit" value="확인"/>
</form>
<form action="empSearch.do" method="get">
 이름 : <input type="text" name="name"/><br>
 이메일 : <input type="text" name="email"/>
 <input type="submit" value="확인"/>
</form>
</body>
</html>

result.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="example.emp.model.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.Date" %>
<!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>
</head>
<body>
<table border="1">
<tr>
<td>EMPLOYEE_ID</td>
<td>NAME</td>
<td>EMAIL</td>
<td>PHONE_NUMBER</td>
<td>HIRE_DATE</td>
<td>JOB_ID</td>
<td>SALARY</td>
<td>COMMISSION_PCT</td>
<td>MANAGER_ID</td>
<td>DEPARTMENT_ID</td>
</tr>

<% ArrayList<Employee> result = (ArrayList<Employee>) request.getAttribute("result1");%>
<% for(int i=0;i<result.size();i++){
int employee_id = result.get(i).getEMPLOYEE_ID();
String first_name = result.get(i).getFIRST_NAME();
String last_name = result.get(i).getLAST_NAME();
String email = result.get(i).getEMAIL();
String phone_number = result.get(i).getPHONE_NUMBER();
Date hire_date = result.get(i).getHIRE_DATE();
String job_id = result.get(i).getJOB_ID();
double salary = result.get(i).getSALARY();
double commission_pct = result.get(i).getCOMMISSION_PCT();
String manager_id = result.get(i).getMANAGER_ID();
String department_id = result.get(i).getDEPARTMENT_ID();
%>
<tr>
<td><%=employee_id %></td>
<td><%=first_name +" " + last_name %></td>
<td><%= email %></td>
<td><%=phone_number %></td>
<td><%=hire_date %></td>
<td><%=job_id %></td>
<td><%=salary%></td>
<td><%=commission_pct %></td>
<td><%=manager_id %></td>
<td><%=department_id %></td>
</tr>
<% } %>

</table>

</body>
</html>

Listener,Filter Class

EmpListener.java

package example.emp.etc;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;

/**
 * Application Lifecycle Listener implementation class EmpListener
 *
 */
public class EmpListener implements ServletContextListener {

    /**
     * Default constructor. 
     */
    public EmpListener() {
        // TODO Auto-generated constructor stub
    }

/**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */
    public void contextInitialized(ServletContextEvent arg0) {
        // TODO Auto-generated method stub
     try {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
//ServlerContext 의 Attribute로 등록
ServletContext sc = arg0.getServletContext();
sc.setAttribute("dataSource", ds);
System.out.println("데이터베이스 접속");
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
    
    }

/**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */
    public void contextDestroyed(ServletContextEvent arg0) {
        // TODO Auto-generated method stub
    }
}

koreanFilter.java

package example.emp.etc;

import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

/**
 * Servlet Filter implementation class koreanFilter
 */
public class koreanFilter implements Filter {

    /**
     * Default constructor. 
     */
    public koreanFilter() {
        // TODO Auto-generated constructor stub
    }

/**
 * @see Filter#destroy()
 */
public void destroy() {
// TODO Auto-generated method stub
}

/**
 * @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
 */
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
request.setCharacterEncoding("euc-kr");
chain.doFilter(request, response);
System.out.println("###############  Filtering   #################");
}

/**
 * @see Filter#init(FilterConfig)
 */
public void init(FilterConfig fConfig) throws ServletException {
// TODO Auto-generated method stub
}

}

Servlet class

EmployreeSearchServlet.java

package example.emp.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import example.emp.model.Employee;
import example.emp.model.EmployeeService;

/**
 * Servlet implementation class EmployreeSearchServlet
 */
public class EmployreeSearchServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public EmployreeSearchServlet() {
        super();
        // TODO Auto-generated constructor stub
    }
/**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 */
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String searchName = request.getParameter("searchName");
EmployeeService service = new EmployeeService();
service.setDataSource(((DataSource)getServletContext().getAttribute("dataSource")));
ArrayList<Employee> result = service.searchAll();
request.setAttribute("result",result);
ArrayList<Employee> result1=service.searchByName(searchName);
request.setAttribute("result1", result1);
RequestDispatcher view = request.getRequestDispatcher("result.jsp");
view.forward(request, response);
//입력부분.
String name = request.getParameter("name");
String email = request.getParameter("email");
service.input(name, email);
}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}

}

Model class

Employee.java

package example.emp.model;

import java.sql.Date;

public class Employee {
//생성자
public Employee() {
super();
}
public Employee(int employeeId, String firstName, String lastName,
String email2, String phoneNumber, Date hireDate, String jobId,
double salary2, double commissionPct, String managerId,
String departmentId) {
super();
EMPLOYEE_ID = employeeId;
FIRST_NAME = firstName;
LAST_NAME = lastName;
EMAIL = email2;
PHONE_NUMBER = phoneNumber;
HIRE_DATE = hireDate;
JOB_ID = jobId;
SALARY = salary2;
COMMISSION_PCT = commissionPct;
MANAGER_ID = managerId;
DEPARTMENT_ID = departmentId;
}
//get,set 메소드.
public int getEMPLOYEE_ID() {
return EMPLOYEE_ID;
}
public void setEMPLOYEE_ID(int eMPLOYEEID) {
EMPLOYEE_ID = eMPLOYEEID;
}
public String getFIRST_NAME() {
return FIRST_NAME;
}
public void setFIRST_NAME(String fIRSTNAME) {
FIRST_NAME = fIRSTNAME;
}
public String getLAST_NAME() {
return LAST_NAME;
}
public void setLAST_NAME(String lASTNAME) {
LAST_NAME = lASTNAME;
}
public String getEMAIL() {
return EMAIL;
}
public void setEMAIL(String eMAIL) {
EMAIL = eMAIL;
}
public String getPHONE_NUMBER() {
return PHONE_NUMBER;
}
public void setPHONE_NUMBER(String pHONENUMBER) {
PHONE_NUMBER = pHONENUMBER;
}
public Date getHIRE_DATE() {
return HIRE_DATE;
}
public void setHIRE_DATE(Date hIREDATE) {
HIRE_DATE = hIREDATE;
}
public String getJOB_ID() {
return JOB_ID;
}
public void setJOB_ID(String jOBID) {
JOB_ID = jOBID;
}
public double getSALARY() {
return SALARY;
}
public void setSALARY(double sALARY) {
SALARY = sALARY;
}
public double getCOMMISSION_PCT() {
return COMMISSION_PCT;
}
public void setCOMMISSION_PCT(double cOMMISSIONPCT) {
COMMISSION_PCT = cOMMISSIONPCT;
}
public String getMANAGER_ID() {
return MANAGER_ID;
}
public void setMANAGER_ID(String mANAGERID) {
MANAGER_ID = mANAGERID;
}
public String getDEPARTMENT_ID() {
return DEPARTMENT_ID;
}
public void setDEPARTMENT_ID(String dEPARTMENTID) {
DEPARTMENT_ID = dEPARTMENTID;
}
private int EMPLOYEE_ID;
private String FIRST_NAME;
private String LAST_NAME;
private String EMAIL;
private String PHONE_NUMBER;
private Date HIRE_DATE;
private String JOB_ID;
private double SALARY;
private double COMMISSION_PCT;
private String MANAGER_ID;
private String DEPARTMENT_ID;
}

EmployeeDAO.java

package example.emp.model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.sql.DataSource;

public class EmployeeDAO {

public ArrayList<Employee> findAll() {
ArrayList<Employee> result = new ArrayList<Employee>();
try {
// dataSource로 부터 conn을 가져오고,
Connection conn = dataSource.getConnection();
// conn에 Statement를 생성시킨다. 쿼리문을 사용할수 있는 stmt를 생성시키는 것.
Statement stmt = conn.createStatement();
// ResultSet로 결과를 담을 수 있는 rs객체를 생성해서 쿼리 결과를 담아준다.
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES");
// rs객체는 rs-rs-rs-rs-rs 와 같이 결과를 이어서 보내주기때문에 다음 값이 있는지 확인하는 매소드인
// next()로 결과를 넘겨서 하나씩 받는다.
while (rs.next()) {

int employee_id = rs.getInt("employee_id");// rs.getInt("칼럼의 이름");
String first_name = rs.getString("first_name");
String last_name = rs.getString("last_name");
String email = rs.getString("email");
String phone_number = rs.getString("phone_number");
Date hire_date = rs.getDate("hire_date");
String job_id = rs.getString("job_id");
double salary = rs.getDouble("salary");
double commission_pct = rs.getDouble("commission_pct");
String manager_id = rs.getString("manager_id");
String department_id = rs.getString("department_id");

Employee emp = new Employee(employee_id, first_name, last_name,
email, phone_number, hire_date, job_id, salary,
commission_pct, manager_id, department_id);

result.add(emp);

}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}

public ArrayList<Employee> findByName(String name) {
 ArrayList<Employee> result  =new ArrayList<Employee>();
try {
Connection conn = dataSource.getConnection();
// conn에 Statement를 생성시킨다. 쿼리문을 사용할수 있는 stmt를 생성시키는 것.
Statement stmt = conn.createStatement();
// ResultSet로 결과를 담을 수 있는 rs객체를 생성해서 쿼리 결과를 담아준다.
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES WHERE FIRST_NAME='"+name+"'");
while (rs.next()) {
int employee_id = rs.getInt("employee_id");// rs.getInt("칼럼의 이름");
String first_name = rs.getString("first_name");
String last_name = rs.getString("last_name");
String email = rs.getString("email");
String phone_number = rs.getString("phone_number");
Date hire_date = rs.getDate("hire_date");
String job_id = rs.getString("job_id");
double salary = rs.getDouble("salary");
double commission_pct = rs.getDouble("commission_pct");
String manager_id = rs.getString("manager_id");
String department_id = rs.getString("department_id");

Employee emp = new Employee(employee_id, first_name, last_name,
email, phone_number, hire_date, job_id, salary,
commission_pct, manager_id, department_id);

result.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
public void input(String name,String email){
Connection conn;
try {
conn = dataSource.getConnection();
// conn에 Statement를 생성시킨다. 쿼리문을 사용할수 있는 stmt를 생성시키는 것.
Statement stmt = conn.createStatement();
// ResultSet로 결과를 담을 수 있는 rs객체를 생성해서 쿼리 결과를 담아준다.
ResultSet rs = stmt.executeQuery("insert into employees(first_name,email) values('"+name+"','"+email+"')");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

public static void setDataSource(DataSource ds) {
EmployeeDAO.dataSource = ds;
}

private static DataSource dataSource;

}

EmployeeService.java

package example.emp.model;

import java.util.ArrayList;

import javax.sql.DataSource;

public class EmployeeService {
EmployeeDAO empDAO = new EmployeeDAO();
public ArrayList<Employee> searchAll(){
return empDAO.findAll();
}
public void setDataSource(DataSource ds){
EmployeeDAO.setDataSource(ds);
}
public ArrayList<Employee> searchByName(String name){
return empDAO.findByName(name);
}
public void input(String name, String email){

empDAO.input(name,email);
}

}
반응형
저작자표시 비영리 변경금지 (새창열림)

'Programming' 카테고리의 다른 글

자바 강좌 사이트 모음  (0) 2009.08.17
JSTL 설치 방법.  (0) 2009.08.14
JSP에서 DB 접속 절차.  (0) 2009.08.13
프로퍼티가 String,기본형이 아닌것 출력하는 법.  (0) 2009.08.12

관련글

티스토리툴바