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());
}
}
}
|