내용

  1. 자료실
  2. 내용
글번호 573
작성자 heojk
작성일 2017-02-21 15:53:17
제목 [실습] Employees 테이블 데이터 관리하기 예제 1(목록조회, 상세조회)
내용 1. 의존성 설정(pom.xml) - Oracle JDBC Driver Repository를 사용하기 위한 Repository 추가( 태그 위에 추가) 

    
        oracle
        ORACLE JDBC Repository
        http://maven.jahia.org/maven2
2. Spring-JDBC, DBCP, Oracle JDBC Driver의 Dependency 추가 
    
        commons-dbcp
        commons-dbcp
        1.4
    
    
        org.springframework
        spring-jdbc
        ${spring-framework.version}
    
    
        com.oracle
        ojdbc6
        11.1.0.7.0
* oracle jdbc driver dependency 에서 에러나면 첨부파일을 C:\Users\Administrator\.m2\repository\com\oracle\ojdbc6\11.1.0.7.0에 저장 3. 데이터베이스 설정 - 데이터베이스 연결정보 설정(database/jdbc.properties) 
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.username=hr
jdbc.password=hr
- 데이터소스와 JdbcTemplate 빈 설정(application-config.xml)
4. VO 클래스 작성(사원정보를 저장하기 위해) EmpVO.java 
package kr.co.javaspecialist.hr.model;

import java.sql.Date;

public class EmpVO {
    int employeeId;	//EMPLOYEE_ID
    String firstName;	//FIRST_NAME
    String lastName;	//LAST_NAME
    String email;		//EMAIL
    String phoneNumber;	//PHONE_NUMBER
    Date hireDate;		//HIRE_DATE
    String jobId;		//JOB_ID
    double salary;		//SALARY
    double commissionPct;	//COMMISSION_PCT
    int managerId;		//MANAGER_ID
    int departmentId;	//DEPARTMENT_ID
	public int getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(int employeeId) {
		this.employeeId = employeeId;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhoneNumber() {
		return phoneNumber;
	}
	public void setPhoneNumber(String phoneNumber) {
		this.phoneNumber = phoneNumber;
	}
	public Date getHireDate() {
		return hireDate;
	}
	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}
	public String getJobId() {
		return jobId;
	}
	public void setJobId(String jobId) {
		this.jobId = jobId;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public double getCommissionPct() {
		return commissionPct;
	}
	public void setCommissionPct(double commissionPct) {
		this.commissionPct = commissionPct;
	}
	public int getManagerId() {
		return managerId;
	}
	public void setManagerId(int managerId) {
		this.managerId = managerId;
	}
	public int getDepartmentId() {
		return departmentId;
	}
	public void setDepartmentId(int departmentId) {
		this.departmentId = departmentId;
	}
	@Override
	public String toString() {
		return "EmpVO [employeeId=" + employeeId + ", firstName=" + firstName + ", lastName=" 
			+ lastName + ", email=" + email + ", phoneNumber=" + phoneNumber + ", hireDate=" 
			+ hireDate + ", jobId=" + jobId + ", salary=" + salary + ", commissionPct=" + commissionPct 
			+ ", managerId=" + managerId + ", departmentId=" + departmentId + "]";
	}

}
5. DAO 클래스 작성(인터페이스 만들고 클래스 구현) - 인터페이스 
package kr.co.javaspecialist.hr.model;

import java.util.List;

public interface IEmpRepository {
    List getEmpList();
    EmpVO getEmpDetails(int employeeId);
}
- 클래스(JdbcTemplate 의존성 설정, RowMapper를 내부클래스 
package kr.co.javaspecialist.hr.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class EmpRepository implements IEmpRepository {
 
    @Autowired
    JdbcTemplate jdbcTemplate;
 
    private class EmpRowMapper implements RowMapper {
        public EmpVO mapRow(ResultSet rs, int count) throws SQLException {
            EmpVO emp = new EmpVO();
            emp.setEmployeeId(rs.getInt("employee_id"));
            emp.setFirstName(rs.getString("first_name"));
            emp.setLastName(rs.getString("last_name"));
            emp.setEmail(rs.getString("email"));
            emp.setPhoneNumber(rs.getString("phone_number"));
            emp.setHireDate(rs.getDate("hire_date"));
            emp.setJobId(rs.getString("job_id"));
            emp.setSalary(rs.getDouble("salary"));
            emp.setCommissionPct(rs.getDouble("commission_pct"));
            emp.setManagerId(rs.getInt("manager_id"));
            emp.setDepartmentId(rs.getInt("department_id"));
            return emp;
        }
    }
    
    public List getEmpList() {
        String sql = "select * from employees";
        return jdbcTemplate.query(sql, new EmpRowMapper());
    }    
    
    public EmpVO getEmpDetails(int employeeId){
        String sql = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=?";
        return jdbcTemplate.queryForObject(sql, new EmpRowMapper(), employeeId);
    }
}
6. Repository 빈 설정(application-config.xml)
7. Controller 생성 - 서비스 생성은 스킵 - JdbcTemplate 빈 의존성 설정 - /emp/list로 요청시 전체 사원정보를 조회하는 컨트롤러 메서드 작성 - /emp/{사원아이디} 로 요청시 해당 사원의 정보를 조회하는 컨트롤러 메서드 작성 
package kr.co.javaspecialist.hr.web;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import kr.co.javaspecialist.hr.model.EmpVO;
import kr.co.javaspecialist.hr.model.IEmpRepository;

@Controller
@RequestMapping("/emp")
public class EmpController {

    @Autowired
    IEmpRepository empRepository;

    @RequestMapping("/list")
    public String emplist(Model model) {
        List empList = empRepository.getEmpList();
        model.addAttribute("emplist", empList);
        return "emp/list";
    }

    @RequestMapping("/{employeeId}")
    public String empDetails(@PathVariable int employeeId, Model model) {
        EmpVO emp = empRepository.getEmpDetails(employeeId);
        model.addAttribute("emp", emp);
        return "emp/details";
    }
}
8. Controller 빈 설정 추가(mvc-config.xml)
9. emp/list 뷰 작성(WEB-INF/view/emp/list.jsp) 
<%@ page contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>




사원리스트








${message}

    



    ${emp.employeeId}
    ${emp.firstName} ${emp.lastName}
    ${emp.email}
    ${emp.phoneNumber}
    ${emp.hireDate}
    ${emp.jobId}
    ${emp.salary}
    ${emp.commissionPct}
    ${emp.managerId}
    ${emp.departmentId}
    삭제
10. 리스트를 조회하기 위한 링크 추가(index.jsp에) 
    
    Employees List
11. emp/details 뷰(상세 조회 JSP 파일) 작성(WEB-INF/view/emp/details.jsp) 
<%@ page contentType="text/html; charset=UTF-8" trimDirectiveWhitespaces="true"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>




사원정보
첨부파일 SpringWebMVC.zip (23,993byte)