package com.shinhan.dbtest;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.firstzone.dept.DeptDto;
//DAO(Data Access Object)
//Service ---> DB 에 간다
// <---
public class EmpDAO {
String sql_SelectByJob_id = "select * from employees where JOB_ID = ?";
String sql_SelectBy_Salary = "select * from employees where salary >= ?";
String sql_Four_where = """
select * from employees where department_id = ? and job_id =? and salary >= ? and hire_date >=?
""" ;
Connection conn = DBUtil.getConnection();
PreparedStatement st;
Statement sta;
ResultSet rs;
/**
*
* // 4.부서,직책 ,급여,입사일 조건으로 조회
// where department_id =? and where job_id =? and where salary >=? and where
// hire_date >=?
*
* @param map
* @return
*/
public List<EmpDTO> selectByFour(Map<String,Object> map) {
// 특정직원 하기 조회하기
Connection conn = DBUtil.getConnection();
List<EmpDTO> empFourList = new ArrayList<EmpDTO>();
try {
st = conn.prepareStatement(sql_Four_where);
//값4개 셋팅
//1부서 id
st.setInt(1,(Integer)map.get("department_id"));
//2직업id
st.setString(2,(String)map.get("job_id"));
//3급여
st.setDouble(3,(Double)map.get("salary"));
//4입사날짜
st.setDate(4,(Date)map.get("hire_date"));
rs = st.executeQuery();
while (rs.next()) {
EmpDTO emp = makeEmp(rs);
empFourList.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
return empFourList;
}
/*
*
* ? 이상의 급여 직원 찾기
*
*
*/
public List<EmpDTO> selectBySalId(double salary) {
Connection conn = DBUtil.getConnection();
List<EmpDTO> empSalList = new ArrayList<EmpDTO>();
try {
st = conn.prepareStatement(sql_SelectBy_Salary);
st.setDouble(1, salary);
rs = st.executeQuery();
while (rs.next()) {
EmpDTO emp = makeEmp(rs);
empSalList.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
return empSalList;
}
/*
*
*
*
* // 2.특정job_id 인 직원조회 where job_id
*
*/
public List<EmpDTO> selectByJobId(String jobid) {
// 특정직원 하기 조회하기
List<EmpDTO> jobEmpList = new ArrayList<EmpDTO>();
try {
st = conn.prepareStatement(sql_SelectByJob_id);
st.setString(1, jobid);
rs = st.executeQuery();
while (rs.next()) {
EmpDTO emp = makeEmp(rs);
jobEmpList.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
return jobEmpList;
}
/*
* 특정부서의 직원 조회 //1.특정부서의 직원 조회 where department_id List<EmpDTO>
*/
public List<EmpDTO> selectByDeptId(int deptid) {
// 특정직원 하기 조회하기
String sql = "select * from employees where department_id = " + deptid;
Connection conn = DBUtil.getConnection();
Statement st = null;
ResultSet rs = null;
List<EmpDTO> deptEmpList = new ArrayList<EmpDTO>();
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
EmpDTO emp = makeEmp(rs);
deptEmpList.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
return deptEmpList;
}
public List<EmpDTO> selectAll() {
// 모든직원 조회하기
String sql = "select * from employees";
Connection conn = DBUtil.getConnection();
Statement st = null;
ResultSet rs = null;
List<EmpDTO> empList = new ArrayList<EmpDTO>();
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
EmpDTO emp = makeEmp(rs);
empList.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
for (EmpDTO emp : empList) {
System.out.println(emp);
}
return empList;
}
private EmpDTO makeEmp2(ResultSet rs) throws SQLException {
// GET 데이타는 컬럼이기떄문에 대소문자 상관없음
// 빌더로 간단하게 표현가능
EmpDTO emp = EmpDTO.builder()
.commission_pct(rs.getDouble("Commission_pct"))
.department_id(rs.getInt("Department_id"))
.email(rs.getString("email"))
.employee_id(rs.getInt("Employee_id"))
.first_name(rs.getString("first_name"))
.last_name(rs.getString("Last_name"))
.hire_date(rs.getDate("Hire_date"))
.job_id(rs.getString("job_id"))
.manager_id(rs.getInt("Manager_id"))
.phone_number(rs.getString("Phone_number"))
.salary(rs.getDouble("salary"))
.build();
return emp;
}
private EmpDTO makeEmp(ResultSet rs) throws SQLException {
// GET 데이타는 컬럼이기떄문에 대소문자 상관없음
EmpDTO emp = new EmpDTO();
emp.setCommission_pct(rs.getDouble("Commission_pct"));
emp.setDepartment_id(rs.getInt("Department_id"));
emp.setEmail(rs.getString("Email"));
emp.setEmployee_id(rs.getInt("Employee_id"));
emp.setFirst_name(rs.getString("First_name"));
emp.setLast_name(rs.getString("Last_name"));
emp.setHire_date(rs.getDate("Hire_date"));
emp.setJob_id(rs.getString("Job_id"));
emp.setManager_id(rs.getInt("Manager_id"));
emp.setPhone_number(rs.getString("Phone_number"));
emp.setSalary(rs.getDouble("Salary"));
return emp;
}
public EmpDTO selectById(int empid) {
// 특정직원 하기 조회하기
String sql = "select * from employees where employee_id = " + empid;
Connection conn = DBUtil.getConnection();
Statement st = null;
ResultSet rs = null;
EmpDTO emp = null;
try {
st = conn.createStatement();
rs = st.executeQuery(sql);
if (rs.next()) {
emp = makeEmp(rs);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, rs);
}
return emp;
}
public int insert(EmpDTO emp) {
int result = 0;
String sql = "insert into employees values (?,?,?,?,?,?,?,?,?,?,?)";
Connection conn = DBUtil.getConnection();
try {
PreparedStatement st = conn.prepareStatement(sql);
st.setInt(1, emp.getEmployee_id());
st.setString(2, emp.getFirst_name());
st.setString(3, emp.getLast_name());
st.setString(4, emp.getEmail());
st.setString(5, emp.getPhone_number());
st.setDate(6, emp.getHire_date());
st.setString(7, emp.getJob_id());
st.setDouble(8, emp.getSalary());
st.setDouble(9, emp.getCommission_pct());
st.setInt(10, emp.getManager_id());
st.setInt(11, emp.department_id);
result = st.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
public int update(EmpDTO emp) {
int result = 0;
String sql = """
update employees set
FIRST_NAME =?,
LAST_NAME=?,
EMAIL=?,
PHONE_NUMBER=?,
HIRE_DATE=?,
JOB_ID=?,
SALARY=?,
COMMISSION_PCT=?,
MANAGER_ID=?,
DEPARTMENT_ID=?
where EMPLOYEE_ID =?
""";
Connection conn = DBUtil.getConnection();
PreparedStatement st = null;
try {
st = conn.prepareStatement(sql);
st.setString(1, emp.getFirst_name());
st.setString(2, emp.getLast_name());
st.setString(3, emp.getEmail());
st.setString(4, emp.getPhone_number());
st.setDate(5, emp.getHire_date());
st.setString(6, emp.getJob_id());
st.setDouble(7, emp.getSalary());
st.setDouble(8, emp.getCommission_pct());
st.setInt(9, emp.getManager_id());
st.setInt(10, emp.getDepartment_id());
st.setInt(11, emp.getEmployee_id());
result = st.executeUpdate();
} catch (Exception e) {
}
return result;
}
public int delete(int empid) {
int result = 0;
String sql = """
delete from employees
where EMPLOYEE_ID=?
""";
Connection conn = DBUtil.getConnection();
PreparedStatement st = null;
try {
st = conn.prepareStatement(sql);
st.setInt(1, empid);
result = st.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.dbDisconnect(conn, st, null);
}
return result;
}
}