0829_Java : JDBC : 급여관리 문제[21.09.01추가]

호쏘2 2021. 8. 29. 22:37

2021. 8. 29. 22:37

테이블

DROP TABLE salary PURGE;
DROP TABLE employee PURGE;

-- 임플로이 테이블
CREATE  TABLE  employee(
    sabeon  VARCHAR2(30) PRIMARY KEY
    ,name   VARCHAR2(30) NOT NULL
    ,birth    DATE NOT NULL
    ,tel       VARCHAR2(30) 
);

-- 셀러리 테이블
CREATE  TABLE  salary(
    salaryNum        NUMBER  PRIMARY KEY
    ,sabeon            VARCHAR2(30) NOT NULL
    ,payDate           VARCHAR2(30) NOT NULL
    ,paymentDate   DATE NOT NULL
    ,pay                  NUMBER(8)
    ,sudang             NUMBER(8)
    ,tax                   NUMBER(8)
    ,memo            VARCHAR2(1000)
    ,CONSTRAINT fk_salary_sabeon FOREIGN KEY(sabeon)
                          REFERENCES  employee(sabeon)
);

SELECT * FROM tab;

-- DROP SEQUENCE salary_seq;

CREATE SEQUENCE salary_seq
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    NOCACHE;

SELECT * FROM seq;

App

package db.employee;

import java.util.Scanner;

import db_util.DBConn;

public class App {
	public static void main(String[] args) {
		Scanner sc=new Scanner(System.in);
		
		Employee emp=new Employee();
		Salary sal=new Salary(emp);
		
		System.out.println("인사관리 프로그램");
		
		int ch;
		while(true) {
			System.out.println("\n[Main]");
			do {
				System.out.print("1.사원관리 2.급여관리 3.종료 => ");
				ch = sc.nextInt();
			}while(ch<1||ch>3);
			
			if(ch==3) break;
			
			switch(ch) {
			case 1:emp.employeeManage();break;
			case 2:sal.salaryManage();break;
			}
		}

		sc.close();
		DBConn.close(); 
	}
}

Employee

package db.employee;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

public class Employee {
	private BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
	private EmployeeDAO dao=new EmployeeDAO();
	
	public void employeeManage() {
		int ch = 0;
		while(true) {
			System.out.println("\n[사원관리]");
			
			do {
				System.out.print("1.사원등록 2.정보수정 3.사번검색 4.이름검색 5.리스트 6.메인 => ");
				try {
					ch = Integer.parseInt(br.readLine());
				} catch (Exception e) {
				}
			}while(ch<1||ch>6);
			
			if(ch==6) return;
			
			switch(ch) {
			case 1: insert(); break;
			case 2: update(); break;
			case 3: searchSabeon(); break;
			case 4: searchName(); break;
			case 5: list(); break;
			}
		}
	}
	
	public void insert() {
		System.out.println("\n사원 등록...");
		EmployeeDTO dto = new EmployeeDTO();
		int result = 0;
		try {
			System.out.println("사원등록을 시작합니다.");
			
			System.out.print("사번을 입력해주세요 > ");
			dto.setSabeon(br.readLine());
			System.out.print("이름을 입력해주세요 > ");
			dto.setName(br.readLine());
			System.out.print("생년월일을 입력해주세요 > ");
			dto.setBirth(br.readLine());
			System.out.print("전화번호를 입력해주세요(선택) > ");
			dto.setTel(br.readLine());
			
			result = dao.insertEmployee(dto);
			if (result > 0) {
				System.out.println("사원 등록을 완료했습니다.");
				System.out.println();				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("사원 등록에 실패했습니다.");
		}
		
	}
	
	public void update() {
		System.out.println("\n사원 정보 수정...");
		int result = 0;
		String sabeon;
		
		try {
			System.out.print("수정할 사번을 입력해주세요 > ");
			sabeon = br.readLine();
			
			EmployeeDTO dto = dao.readEmployee(sabeon);
			if( dto == null) {
				System.out.println("등록되어있는 사번이 없습니다.");
				return;
			}
			
			System.out.print("수정할 이름을 입력하세요 > ");
			dto.setName(br.readLine());
			System.out.print("수정할 생년월일을 입력하세요 > ");
			dto.setBirth(br.readLine());
			System.out.print("수정할 전화번호를 입력하세요(선택) > ");
			dto.setTel(br.readLine());
			
			result = dao.updateEmployee(dto);
			if ( result > 0) {
				System.out.println("정보를 수정했습니다.");
			}
		} catch (Exception e) {
			System.out.println("정보 수정에 실패했습니다.");
			e.printStackTrace();
		}
		System.out.println();
	}
	
	public void searchSabeon() {
		System.out.println("\n사번 검색...");
		
		String sabeon;
		try {
			System.out.println("검색할 사번을 입력하세요 > ");
			sabeon = br.readLine();
			EmployeeDTO dto = dao.readEmployee(sabeon);
			System.out.println("사번\t이름\t생년월일\t\t전화번호");
			System.out.println("-------------------------------------------------");
			System.out.print(dto.getSabeon()+"\t");
			System.out.print(dto.getName()+"\t");
			System.out.print(dto.getBirth()+"\t");
			System.out.println(dto.getTel());
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println();
	}

	public void searchName() {
		System.out.println("\n이름 검색...");
		
		String name;
		List<EmployeeDTO> list = new ArrayList<EmployeeDTO>();
		
		
		try {
			System.out.print("검색할 이름을 입력하세요 > ");
			name = br.readLine();
			list = dao.listEmployee(name);
			
			System.out.println("사번\t이름\t생년월일\t\t전화번호");
			System.out.println("-------------------------------------------------");
			for(EmployeeDTO dto : list) {
				System.out.print(dto.getSabeon()+"\t");
				System.out.print(dto.getName()+"\t");
				System.out.print(dto.getBirth()+"\t");
				System.out.println(dto.getTel());
			}
			System.out.println();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public void list() {
		System.out.println("\n사원 리스트...");
		
		List<EmployeeDTO> list = new ArrayList<EmployeeDTO>();
		list = dao.listEmployee();
		System.out.println("사번\t이름\t생년월일\t\t전화번호");
		System.out.println("-------------------------------------------------");
		for(EmployeeDTO dto : list) {
			System.out.print(dto.getSabeon()+"\t");
			System.out.print(dto.getName()+"\t");
			System.out.print(dto.getBirth()+"\t");
			System.out.println(dto.getTel());
		}
		System.out.println();
	}
	
}

EmployeeDAO

package db.employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;

import db_util.DBConn;

public class EmployeeDAO {
	private Connection conn=DBConn.getConnection();
	
	public int insertEmployee(EmployeeDTO dto) throws SQLException {
		int result=0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = "INSERT INTO employee(sabeon, name, birth, tel) VALUES (?,?,?,?) ";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getSabeon());
			pstmt.setString(2, dto.getName());
			pstmt.setString(3, dto.getBirth());
			pstmt.setString(4, dto.getTel());
			
			result = pstmt.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			if(e.getErrorCode()==1) {
				System.out.println("사번 중복 입니다.");
			} else if(e.getErrorCode()==1400) { // NOT NULL 위반
				System.out.println("필수 사항을 입력 하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLDataException e) {
			if(e.getErrorCode()==1861 || e.getErrorCode()==1840) { 
				System.out.println("날짜 형식이 잘못 되었습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return result;
	}
	
	public int updateEmployee(EmployeeDTO dto) throws SQLException {
		int result=0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = " UPDATE employee SET name=?, birth=?, tel=? WHERE sabeon=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getBirth());
			pstmt.setString(3, dto.getTel());
			pstmt.setString(4, dto.getSabeon());
			
			result = pstmt.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			if(e.getErrorCode()==1400) {
				System.out.println("필수 입력 사항을 입력하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLDataException e) {
			if(e.getErrorCode()==1840||e.getErrorCode()==1861) {
				System.out.println("날짜 형식이 올바르지 않습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if ( pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return result;
	}
	
	public EmployeeDTO readEmployee(String sabeon) {
		EmployeeDTO dto= new EmployeeDTO();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = " SELECT sabeon, name, birth, NVL(tel, '입력안함') 번호 "
					+" FROM employee "
					+" WHERE sabeon = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, sabeon);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("birth").toString());
				dto.setTel(rs.getString("번호"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dto;
	}
	
	public List<EmployeeDTO> listEmployee() {
		List<EmployeeDTO> list=new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = " SELECT sabeon, name, birth, NVL(tel, '입력안함') 번호 "
					+" FROM employee";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				EmployeeDTO dto = new EmployeeDTO();
				
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("birth").toString());
				dto.setTel(rs.getString("번호"));
				
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return list;
	}
	
	public List<EmployeeDTO> listEmployee(String name) {
		List<EmployeeDTO> list=new ArrayList<>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = " SELECT sabeon, name, birth, NVL(tel, '입력안함') 번호 "
					+" FROM employee"
					+" WHERE INSTR(name, ?) > 0";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				EmployeeDTO dto = new EmployeeDTO();
				
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setBirth(rs.getDate("birth").toString());
				dto.setTel(rs.getString("번호"));
				
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
		}
		return list;
	}
}

EmployeeDTO

package db.employee;

public class EmployeeDTO {
	private String sabeon;
	private String name;
	private String birth;
	private String tel;
	
	public String getSabeon() {
		return sabeon;
	}
	public void setSabeon(String sabeon) {
		this.sabeon = sabeon;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getBirth() {
		return birth;
	}
	public void setBirth(String birth) {
		this.birth = birth;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
}

Salary

package db.employee;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.HashMap;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Map;

public class Salary {
	private BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
	private SalaryDAO dao=new SalaryDAO();
	private Employee emp=null;
	
	public Salary(Employee emp) {
		this.emp = emp;
	}
	
	public void salaryManage() {
		int ch = 0;
		while(true) {
			System.out.println("\n[급여관리]");
			do {
				System.out.print("1.지급 2.수정 3.삭제 4.월별리스트 5.사번검색 6.리스트 7.사원리스트 8.메인 => ");
				try {
					ch = Integer.parseInt(br.readLine());
				} catch (Exception e) {
				}
			}while(ch<1||ch>8);
			
			if(ch==8) return;
			
			switch(ch) {
			case 1:payment(); break;
			case 2:update(); break;
			case 3:delete(); break;
			case 4:monthList(); break;
			case 5:searchSabeon(); break;
			case 6:list(); break;
			case 7:emp.list(); break;
			}
		}
	}
	
	public void payment() {
		System.out.println("\n급여 지급...");
		SalaryDTO dto = new SalaryDTO();
		int result = 0;
		try {
			System.out.print("급여 지급대상의 사원번호를 입력하세요 > ");
			dto.setSabeon(br.readLine());
			System.out.print("급여년월(yyyymm)을 입력해주세요 > ");
			dto.setPayDate(br.readLine());
			System.out.print("급여지급일자(yyyymmdd)를 입력해주세요 > ");
			dto.setPaymentDate(br.readLine());
			System.out.print("기본급을 입력해주세요.(선택) > ");
			dto.setPay(Integer.parseInt(br.readLine()));
			System.out.print("수당을 입력해주세요.(선택) > ");
			dto.setSudang(Integer.parseInt(br.readLine()));
			System.out.print("기타 메모 > ");
			dto.setMemo(br.readLine());
			
			result = dao.insertSalary(dto);
			
			if ( result > 0) {
				System.out.println("급여지급을 완료했습니다.");
			}
			System.out.println();
		} catch (Exception e) {
			System.out.println("급여 지급을 실패했습니다.");
			e.printStackTrace();
			
		}
		
	}
	
	public void update() {
		System.out.println("\n급여 수정...");
		int salaryNum, result;
		
		try {
			System.out.println("수정할 급여번호를 입력해주세요 > ");
			salaryNum = Integer.parseInt(br.readLine());
			
			System.out.println("현재 해당 급여번호의 자료");
			System.out.println("------------------------------------------------------------------------------------");
			System.out.println("급여번호\t사번\t급여년월\t지급일자\t\t기본급\t수당\t총급여\t세금\t세후지급액\t메모");
			SalaryDTO dto = dao.readSalary(salaryNum);
			System.out.print(dto.getSalaryNum()+"\t");
			System.out.print(dto.getSabeon()+"\t");
			System.out.print(dto.getPayDate()+"\t");
			System.out.print(dto.getPaymentDate()+"\t");
			System.out.print(dto.getPay()+"\t");
			System.out.print(dto.getSudang()+"\t");
			System.out.print(dto.getTot()+"\t");
			System.out.print(dto.getTax()+"\t");
			System.out.print(dto.getAfterPay()+"\t");
			System.out.println(dto.getMemo());
			System.out.println();
			
			System.out.print("수정할 급여년월 > ");
			dto.setPayDate(br.readLine());
			System.out.print("수정할 지급일자 > ");
			dto.setPaymentDate(br.readLine());
			System.out.print("수정할 기본급 > ");
			dto.setPay(Integer.parseInt(br.readLine()));
			System.out.print("수정할 수당 > ");
			dto.setSudang(Integer.parseInt(br.readLine()));
			System.out.print("수정할 메모 > ");
			dto.setMemo(br.readLine());
			
			result = dao.updateSalary(dto);
			if (result > 0) {
				System.out.println("수정이 완료되었습니다.");
			}
			
		} catch (Exception e) {
			System.out.println("수정에 실패했습니다.");
			e.printStackTrace();
		}
		
	}

	public void delete() {
		System.out.println("\n급여 삭제...");
		int salaryNum, result;
		
		try {
			System.out.println("삭제할 급여번호를 입력해주세요 > ");
			salaryNum = Integer.parseInt(br.readLine());
			result = dao.deleteSalary(salaryNum);
			
			if (result > 0) {
				System.out.println("삭제되었습니다.");
			}
			
		} catch (InputMismatchException e) {
			System.out.println("급여번호는 숫자만 가능합니다.");
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void searchSabeon() {
		System.out.println("\n사번 검색...");
		String sabeon, date;
		Map<String, Object> map = new HashMap<>(); 
		int ch = 0;
		boolean b = true;
		
		try {
			while(b) {
				do {
					SalaryDTO dto = new SalaryDTO();
					System.out.print("급여년월(yyyymm)을 입력해주세요 > ");
					date = br.readLine();
					System.out.print("검색할 사번을 입력해주세요 > ");
					sabeon = br.readLine();
					dto.setSabeon(sabeon);
					
					map.put(date, dto);
					
					System.out.println("더 검색 할 자료가 없으면 1, 있으면 2를 누르세요 > ");
					ch = Integer.parseInt(br.readLine());
				} while (ch >= 2);
				if ( ch == 1) {
					b = false;					
				}
			} 
			System.out.println("검색한 사번의 급여년월 출력...");
			List<SalaryDTO> list = dao.listSalary(map);
			
			System.out.println("사번\t이름\t급여년월\t지급일자");
			System.out.println("-----------------------------------");
			for(SalaryDTO dto : list) {
				System.out.print(dto.getSabeon()+"\t");
				System.out.print(dto.getName()+"\t");
				System.out.print(dto.getPayDate()+"\t");
				System.out.println(dto.getPaymentDate());
			}
			System.out.println();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("검색에 실패했습니다.");
		}
		
	}

	public void monthList() {
		System.out.println("\n월별 리스트...");
		String paydate;
		try {
			System.out.println("검색할 급여년월(yyyymm)을 입력해주세요 > ");
			paydate = br.readLine();
			List<SalaryDTO> list = dao.listSalary(paydate);
			
			System.out.println("사번\t이름\t급여년월\t지급일자");
			System.out.println("-----------------------------------");
			for(SalaryDTO dto : list) {
				System.out.print(dto.getSabeon()+"\t");
				System.out.print(dto.getName()+"\t");
				System.out.print(dto.getPayDate()+"\t");
				System.out.println(dto.getPaymentDate());
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("검색에 실패했습니다.");
		}
		
	}
	
	public void list() {
		System.out.println("\n급여 리스트...");
		List<SalaryDTO> list = dao.listSalary();
		
		System.out.println("급여번호\t사번\t이름\t급여년월\t지급일자\t\t기본급\t\t수당\t총급여\t\t세금\t세후지급금\t\t메모");
		System.out.println("---------------------------------------------------------------------");
		for(SalaryDTO dto : list) {
			System.out.print(dto.getSalaryNum()+"\t");
			System.out.print(dto.getSabeon()+"\t");
			System.out.print(dto.getName()+"\t");
			System.out.print(dto.getPayDate()+"\t");
			System.out.print(dto.getPaymentDate()+"\t");
			System.out.printf("%,d", dto.getPay());
			System.out.print("\t");
			System.out.printf("%,d", dto.getSudang());
			System.out.print("\t");
			System.out.printf("%,d", dto.getTot());
			System.out.print("\t");
			System.out.printf("%,d", dto.getTax());
			System.out.print("\t");
			System.out.printf("%,d", dto.getAfterPay());
			System.out.print("\t");
			System.out.println(dto.getMemo());
		}
		System.out.println();
	}
}

위에서 searchSabeon() 은 이렇게 짜야했다.

Map에 대한 개념을 잊어버리지 말라고 넣으신 듯.

public void searchSabeon() {
		System.out.println("\n사번 검색...");
		
		String payDate, sabeon;
		
		try {
			System.out.print("검색할 급여년월[yyyymm] ?");
			payDate=br.readLine();
			System.out.print("검색할 사번 ?");
			sabeon=br.readLine();
			Map<String, Object> map=new HashMap<>();
			map.put("payDate", payDate);
			map.put("sabeon", sabeon);
			
			List<SalaryDTO> list=dao.listSalary(map);
			
			for(SalaryDTO dto : list) {
				System.out.print(dto.getSalaryNum()+"\t");
				System.out.print(dto.getSabeon()+"\t");
				System.out.print(dto.getName()+"\t");
				System.out.print(dto.getPayDate()+"\t");
				System.out.print(dto.getPaymentDate()+"\t");
				System.out.print(dto.getPay()+"\t");
				System.out.print(dto.getSudang()+"\t");
				System.out.print(dto.getTot()+"\t");
				System.out.print(dto.getTax()+"\t");
				System.out.println(dto.getAfterPay());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println();
		
	}

SalaryDAO

package db.employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import db_util.DBConn;

public class SalaryDAO {
	private Connection conn=DBConn.getConnection();
	
	public int insertSalary(SalaryDTO dto) throws SQLException {
		int result=0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = "INSERT INTO salary(salaryNum, sabeon, payDate, paymentDate, pay, sudang, tax, memo)"
					+ "VALUES (salary_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getSabeon());
			pstmt.setString(2, dto.getPayDate());
			pstmt.setString(3, dto.getPaymentDate());
			pstmt.setInt(4, dto.getPay());
			pstmt.setInt(5, dto.getSudang());
			pstmt.setInt(6, dto.getTax());
			pstmt.setString(7, dto.getMemo());
			
			result = pstmt.executeUpdate();
			
		} catch (SQLIntegrityConstraintViolationException e) {
			if(e.getErrorCode()==1) {
				System.out.println("사번 중복 입니다.");
			} else if(e.getErrorCode()==1400) { // NOT NULL 위반
				System.out.println("필수 사항을 입력 하지 않았습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLDataException e) {
			if(e.getErrorCode()==1861 || e.getErrorCode()==1840) { 
				System.out.println("날짜 형식이 잘못 되었습니다.");
			} else {
				System.out.println(e.toString());
			}
			throw e;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if( pstmt != null){
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return result;
	}
	
	public int updateSalary(SalaryDTO dto) throws SQLException {
		int result=0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = "UPDATE salary SET paydate = ?, paymentdate = ?, pay = ?, sudang = ?, memo = ?"
					+" WHERE salaryNum = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getPayDate());
			pstmt.setString(2, dto.getPaymentDate());
			pstmt.setInt(3, dto.getPay());
			pstmt.setInt(4, dto.getSudang());
			pstmt.setString(5, dto.getMemo());
			pstmt.setInt(6, dto.getSalaryNum());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}

	public int deleteSalary(int salaryNum) throws SQLException {
		int result=0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = " DELETE FROM salary WHERE salaryNum = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, salaryNum);
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(pstmt !=null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}

		return result;
	}
	
	public SalaryDTO readSalary(int salaryNum) {
		SalaryDTO dto= new SalaryDTO();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = " SELECT salaryNum, sabeon, paydate, paymentdate, pay, sudang, tax, NVL(memo, '입력안함') 메모 "
					+" FROM salary "
					+" WHERE salaryNum = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, salaryNum);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				dto.setSalaryNum(rs.getInt("salaryNum"));
				dto.setSabeon(rs.getString("sabeon"));
				dto.setPayDate(rs.getString("paydate"));
				dto.setPaymentDate(rs.getDate("paymentdate").toString());
				dto.setPay(rs.getInt("pay"));
				dto.setSudang(rs.getInt("sudang"));
				dto.setMemo(rs.getString("메모"));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return dto;
	}
	
	public List<SalaryDTO> listSalary(String payDate) {
		List<SalaryDTO> list=new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			sql = "SELECT e.sabeon, name, paydate, paymentdate "
					+" FROM employee e "
					+" JOIN salary s ON e.sabeon = s.sabeon"
					+" WHERE paydate = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, payDate);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				SalaryDTO dto = new SalaryDTO();
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setPayDate(rs.getString("paydate"));
				dto.setPaymentDate(rs.getDate("paymentdate").toString());
				
				list.add(dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
		}
		return list;
	}
	
	public List<SalaryDTO> listSalary(Map<String, Object> map) {
		List<SalaryDTO> list=new ArrayList<>();
		List<SalaryDTO> list1 = listSalary(); // 전체 급여리스트
		Set<String> keySet = map.keySet(); // 검색할 사번이 있는 곳
		List<String> list2 = new ArrayList<String>(keySet); // 검색할 사번 리스트
		
		for(int i = 0; i<list2.size();i++) {
			for(int j = 0; j< list1.size(); j++) {
				if(list2.get(i) == list1.get(j).getSabeon()) {				
				SalaryDTO dto;
				dto = list1.get(j);
				list.add(dto);
				}
			}
		}
		return list;
	}

	public List<SalaryDTO> listSalary() {
		List<SalaryDTO> list=new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		try {
			sql = "SELECT salarynum, e.sabeon, name, paydate, paymentdate, pay, sudang, memo "
					+" FROM employee e "
					+" JOIN salary s ON e.sabeon = s.sabeon";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				SalaryDTO dto = new SalaryDTO();
				dto.setSalaryNum(rs.getInt("salarynum"));
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setPayDate(rs.getString("paydate"));
				dto.setPaymentDate(rs.getDate("paymentdate").toString());
				dto.setPay(rs.getInt("pay"));
				dto.setSudang(rs.getInt("sudang"));
				dto.setMemo(rs.getString("memo"));
				
				list.add(dto);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
		}
		return list;
	}

}

public List<SalaryDTO> listSalary(Map<String, Object> map) 메소드의 경우 아래와 같이 짜야했다.

public List<SalaryDTO> listSalary(Map<String, Object> map) {
		List<SalaryDTO> list=new ArrayList<>();
		
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		
		String sabeon=(String)map.get("sabeon");
		String payDate=(String)map.get("payDate");
		
		try {
			sb.append(" SELECT salaryNum, s.sabeon, name, payDate, ");
			sb.append("   TO_CHAR(paymentDate, 'YYYY-MM-DD') paymentDate, ");
			sb.append("   pay, sudang, pay+sudang tot, tax, (pay+sudang)-tax afterPay, memo ");
			sb.append(" FROM salary s ");
			sb.append(" JOIN employee e ON s.sabeon = e.sabeon ");
			sb.append(" WHERE s.sabeon = ? AND payDate = ?");
			
			pstmt=conn.prepareStatement(sb.toString());
			pstmt.setString(1, sabeon);
			pstmt.setString(2, payDate);
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				SalaryDTO dto = new SalaryDTO();
				dto.setSalaryNum(rs.getInt("salaryNum"));
				dto.setSabeon(rs.getString("sabeon"));
				dto.setName(rs.getString("name"));
				dto.setPayDate(rs.getString("payDate"));
				dto.setPaymentDate(rs.getString("paymentDate"));
				dto.setPay(rs.getInt("pay"));
				dto.setSudang(rs.getInt("sudang"));
				dto.setMemo(rs.getString("memo"));
				
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs!=null) {
				try {
					rs.close();
				} catch (Exception e2) {
				}
			}
			if(pstmt!=null) {
				try {
					pstmt.close();
				} catch (Exception e2) {
				}
			}
		}
		
		return list;
	}

SalaryDTO

package db.employee;

public class SalaryDTO {
	private int salaryNum;
	private String sabeon;
	private String name;
	private String payDate; // 지급년월
	private String paymentDate; // 지급일자
	private int pay;
	private int sudang;
	private int tot;
	private int tax;
	private int afterPay;
	private String memo;

	public int getSalaryNum() {
		return salaryNum;
	}

	public void setSalaryNum(int salaryNum) {
		this.salaryNum = salaryNum;
	}

	public String getSabeon() {
		return sabeon;
	}

	public void setSabeon(String sabeon) {
		this.sabeon = sabeon;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPayDate() {
		return payDate;
	}

	public void setPayDate(String payDate) {
		this.payDate = payDate;
	}

	public String getPaymentDate() {
		return paymentDate;
	}

	public void setPaymentDate(String paymentDate) {
		this.paymentDate = paymentDate;
	}

	public int getPay() {
		return pay;
	}

	public void setPay(int pay) {
		this.pay = pay;
	}

	public int getSudang() {
		return sudang;
	}

	public void setSudang(int sudang) {
		this.sudang = sudang;
	}

	public int getTot() {
		tot = pay + sudang;
		return tot;
	}

	public int getTax() {
		if (sudang + pay >= 3000000) {
			tax = (int) ((sudang + pay) * 0.03);
		} else if (sudang + pay >= 2000000) {
			tax = (int) ((sudang + pay) * 0.02);
		} else {
			tax = 0;
		}
		return tax;
	}

	public int getAfterPay() {
		afterPay = tot - tax;
		return afterPay;
	}


	public String getMemo() {
		return memo;
	}

	public void setMemo(String memo) {
		this.memo = memo;
	}
}

SalaryDTO 클래스의 public List<SalaryDTO> listSalary(Map<String, Object> map) 메소드는 결국 못짬. 그 맵으로 받아서 뭘 하라는 건지 잘 모르겠다..ㅠㅠ

사원번호와 급여년월을 받아서 뭐 하라는건지...ㅍㅍㅠ

근데 나는 DTO 에서 아예 afterpay 나 tax부분은 넣을 때 알아서 계산하게끔 했는데... 쌤이 짜신 tax은 입력받은 곳에서 계산해서 DTO에 넣어준다.

저작자표시 동일조건

'쌍용강북교육센터 > 8월' 카테고리의 다른 글

0827_Java : JDBC : 회원관리 문제 (0)	2021.08.29
0826_Java : JDBC : Statement, ResultSet 인터페이스 (0)	2021.08.26
0826_Java : JDBC : DriverManager 클래스 Connection 인터페이스 (0)	2021.08.26
0826_Java : JDBC (Java Database Connectivity) (0)	2021.08.26
0825_Java : Serializable 직렬화 (0)	2021.08.26

개발새발, 써놓기

0829_Java : JDBC : 급여관리 문제[21.09.01추가]

'쌍용강북교육센터 > 8월' 카테고리의 다른 글

+ Recent posts

티스토리툴바