내용

글번호 823
작성자 heojk
작성일 2018-03-08 17:25:12
제목 Employees 테이블 CRUD DAO 클래스
내용 JDBC Programming EmpVO.java
import java.sql.Date;

public class EmpVO {
	private int employeeId;
	private String firstName;
	private String lastName;
	private String email;
	private String phoneNumber;
	private Date hireDate;
	private String jobId;
	private double salary;
	private double commissionPct;
	private int managerId;
	private int departmentId;
	
	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 + "]";
	}
	
	
}
IEmpDAO.java
import java.util.List;

public interface IEmpDAO {
	int insertEmp(EmpVO emp);
	int updateEmp(EmpVO emp);
	int deleteEmp(int employeeId);
	EmpVO selectEmp(int employeeId);
	List<EmpVO> selectEmpByDeptno(int departmentId);
	List<EmpVO> selectAllEmp();
}
EmpDAO.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO implements IEmpDAO {

	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("드라이버클래스가 로드되었습니다.");
		} catch (ClassNotFoundException e) {
			System.out.println("클래스 파일을 찾을 수 없습니다.");
		}
	}
	
	public static final String URL = "jdbc:oracle:thin:@localhost:1521/xe";
	public static final String ID = "hr";
	public static final String PW = "hr";
	
	@Override
	public int insertEmp(EmpVO emp) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "insert into employees ( "
					+ "employee_id, first_name, last_name, email, phone_number,"
					+ "hire_date, job_id, salary, commission_pct, manager_id, department_id) "
					+ "values (?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, emp.getEmployeeId());
			stmt.setString(2, emp.getFirstName());
			stmt.setString(3, emp.getLastName());
			stmt.setString(4, emp.getEmail());
			stmt.setString(5, emp.getPhoneNumber());
			stmt.setDate(6, emp.getHireDate());
			stmt.setString(7, emp.getJobId());
			stmt.setDouble(8, emp.getSalary());
			stmt.setDouble(9, emp.getCommissionPct());
			stmt.setInt(10, emp.getManagerId());
			stmt.setInt(11, emp.getDepartmentId());
			
			int rowCount = stmt.executeUpdate();
			return rowCount;
//			return stmt.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return 0;
	}

	@Override
	public int updateEmp(EmpVO emp) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "update employees set email=?, salary=?, "
					+ "commission_pct=? where employee_id=?";
			
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, emp.getEmail());
			stmt.setDouble(2, emp.getSalary());
			stmt.setDouble(3, emp.getCommissionPct());
			stmt.setInt(4, emp.getEmployeeId());
			
			return stmt.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return 0;
	}

	@Override
	public int deleteEmp(int employeeId) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "delete from employees where employee_id=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, employeeId);
			return stmt.executeUpdate();
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return 0;
	}

	@Override
	public EmpVO selectEmp(int employeeId) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "select * from employees where employee_id=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, employeeId);
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				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;
			}else {
				throw new RuntimeException("No row selected");
			}
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return null;
	}

	@Override
	public List<EmpVO> selectEmpByDeptno(int departmentId) {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "select * from employees where department_id=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, departmentId);
			List<EmpVO> empList = new ArrayList<>();
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				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"));
				empList.add(emp);
			}
			return empList;
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return null;
	}

	@Override
	public List<EmpVO> selectAllEmp() {
		Connection con = null;
		try {
			con = DriverManager.getConnection(URL, ID, PW);
			String sql = "select * from employees";
			PreparedStatement stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			List<EmpVO> empList = new ArrayList<>();
			while(rs.next()) {
				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"));
				empList.add(emp);
			}
			return empList;
		} catch(SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(con != null) {
				try { con.close(); } catch(Exception e) { }
			}
		}
//		return null;
	}

}
EmpManager.java
import java.util.List;

public class EmpManager {
	
	public static void main(String[] args) {
		IEmpDAO dao = new EmpDAO();
		EmpVO emp = new EmpVO();
		emp.setEmployeeId(400);
		emp.setFirstName("JinKyoung");
		emp.setLastName("Heo");
		emp.setEmail("asdfgh");
		emp.setPhoneNumber("010-3402-7902");
		emp.setHireDate(new java.sql.Date(new java.util.Date().getTime()));
		emp.setJobId("IT_PROG");
		emp.setSalary(9000);
		emp.setCommissionPct(0.8);
		emp.setManagerId(100);
		emp.setDepartmentId(60);
		try {
//			dao.insertEmp(emp);
//			System.out.println("입력 성공!!!");
//			dao.updateEmp(emp);
//			System.out.println("수정 성공");
//			dao.deleteEmp(400);
//			System.out.println("삭제 성공");
//			EmpVO emp1 = dao.selectEmp(103);
//			System.out.println(emp1);
			//List<EmpVO> empList = dao.selectEmpByDeptno(60);
			List<EmpVO> empList = dao.selectAllEmp();
			for(EmpVO vo : empList) {
				System.out.println(vo);
			}
		}catch(RuntimeException e) {
			System.out.println(e.getMessage());
		}
	}
}