DTO
package mystudy1;
import java.util.Date;
public class EMPDTO {
private int empno ;
private String ename ;
private String job ;
private int mgr ;
private Date hiredate;
private int sal ;
private int comm ;
private int deptno ;
public EMPDTO(int empno, String ename, String job, int mgr, Date hiredate, int sal, int comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public String getEname() {
return ename;
}
public String getJob() {
return job;
}
public int getMgr() {
return mgr;
}
public Date getHiredate() {
return hiredate;
}
public int getSal() {
return sal;
}
public int getComm() {
return comm;
}
public int getDeptno() {
return deptno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setJob(String job) {
this.job = job;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public void setSal(int sal) {
this.sal = sal;
}
public void setComm(int comm) {
this.comm = comm;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}DAO
package mystudy1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import db.DeptDTO;
public class EMPDAO {
Connection conn = null;
Statement stmt = null;
ResultSet rst = null;
PreparedStatement stmt2 = null;
List<EMPDTO> empdto = null;
//입력
public void insertEMP(
Connection conn,
String ename,
String job,
int mgr,
java.sql.Date hiredate,
int sal,
int comm,
int deptno) throws SQLException {
String sql = "insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) "
+ "values(seq_dno,NEXTVAL,?,?,?,?,?,?,?)";
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, ename);
stmt2.setString(2, job);
stmt2.setInt(3, mgr);
stmt2.setDate(4, hiredate);
stmt2.setInt(5, sal);
stmt2.setInt(6, comm);
stmt2.setInt(7, deptno);
int cnt = stmt2.executeUpdate();
if(cnt==1) {System.out.println("입력 성공!");}
else {System.out.println("입력된게 없음!");}
}
//수정
public void updateEMP(
Connection conn,
int empno,
String ename,
String job,
int mgr,
java.sql.Date hiredate,
int sal,
int comm,
int deptno) throws SQLException {
String sql = "UPDATE DMP "
+ "SET ENAME =?, JOB=?, MGR=?, HIREDATE=?, SAL=?, COMM=?, DEPTNO=? "
+ "WHERE EMPNO = ?";
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, ename);
stmt2.setString(2, job);
stmt2.setInt(3, mgr);
stmt2.setDate(4, hiredate);
stmt2.setInt(5, sal);
stmt2.setInt(6, comm);
stmt2.setInt(7, deptno);
stmt2.setInt(8, empno);
int cnt = stmt2.executeUpdate();
if(cnt==1) {System.out.println("수정 성공!");}
else {System.out.println("수정된게 없음!");}
}
//삭제
public void deleteEMPByEmpno(Connection conn, int empno) throws SQLException {
String sql = "DELETE EMP "
+ "WHERE EMPNO=?";
stmt2 = conn.prepareStatement(sql);
stmt2.setInt(1, empno);
int cnt = stmt2.executeUpdate();
if(cnt!=0) {System.out.println(cnt+"만큼 쿼리로 삭제!");}
else {System.out.println("쿼리가 아무것도 안했다!");}
}
//조회
//번호로 상세조회
public EMPDTO getDetailEMPByEmpno(Connection conn, int empno) throws SQLException {
EMPDTO empdto = null;
String sql = "SELECT * "
+ "FROM EMP "
+ "WHERE EMPNO = ?";
stmt2 = conn.prepareStatement(sql);
stmt2.setInt(1, empno);
rst = stmt2.executeQuery();
if(rst.next()==false) {System.out.println("정보를 불러오지 못함!");}
rst.next();
int empno1 = rst.getInt("EMPNO");
String ename =rst.getString("ENAME");
String job = rst.getString("JOB");
int mgr = rst.getInt("MGR");
java.sql.Date hiredate = rst.getDate("HIREDATE");
int sal = rst.getInt("SAL");
int comm = rst.getInt("COMM");
int deptno = rst.getInt("DEPTNO");
empdto = new EMPDTO(empno1, ename, job, mgr, hiredate, sal, comm, deptno);
return empdto;
}
//목록조회
public List<EMPDTO> getEveryEMP(Connection conn) throws SQLException{
List<EMPDTO> empdto= null;
String sql = "SELECT * "
+ "FROM EMP";
stmt = conn.createStatement();
rst = stmt.executeQuery(sql);
while(rst.next()) {
int empno1 = rst.getInt("EMPNO");
String ename =rst.getString("ENAME");
String job = rst.getString("JOB");
int mgr = rst.getInt("MGR");
java.sql.Date hiredate = rst.getDate("HIREDATE");
int sal = rst.getInt("SAL");
int comm = rst.getInt("COMM");
int deptno = rst.getInt("DEPTNO");
empdto.add(new EMPDTO(empno1, ename, job, mgr, hiredate, sal, comm, deptno));
}
return empdto;
}
//직종조회
public List<EMPDTO> getDetailEMPByJob(Connection conn, String job) throws SQLException {
List<EMPDTO> empdto = null;
String sql = "SELECT * "
+ "FROM EMP "
+ "WHERE EMPNO = ?";
stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, job);
rst = stmt2.executeQuery();
if(rst.next()==false) {System.out.println("정보를 불러오지 못함!");}
while(rst.next()) {
int empno1 = rst.getInt("EMPNO");
String ename =rst.getString("ENAME");
int mgr = rst.getInt("MGR");
java.sql.Date hiredate = rst.getDate("HIREDATE");
int sal = rst.getInt("SAL");
int comm = rst.getInt("COMM");
int deptno = rst.getInt("DEPTNO");
empdto.add(new EMPDTO(empno1, ename, job, mgr, hiredate, sal, comm, deptno));
}
return empdto;
}
}
SERVICE
package mystudy1;
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.List;
public class EMPService {
EMPDAO empdao = new EMPDAO();
Statement stmt = null;
ResultSet rst = null;
PreparedStatement stmt2 = null;
EMPJDBCUtil obj = new EMPJDBCUtil();
public void insertEMP(String ename, String job,int mgr,Date hiredate, int sal, int comm, int deptno) throws ClassNotFoundException, SQLException {
Connection conn = obj.getDBConn();
empdao.insertEMP(conn, ename, job, mgr, hiredate, sal, comm, deptno);
}
public void updateEMP(int empno, String ename, String job, int mgr, Date hiredate, int sal, int comm, int deptno) throws ClassNotFoundException, SQLException {
Connection conn = obj.getDBConn();
empdao.updateEMP(conn, empno, ename, job, mgr, hiredate, sal, comm, deptno);
}
public void deleteEMPByEmpno(int empno) throws ClassNotFoundException, SQLException {
Connection conn = obj.getDBConn();
empdao.deleteEMPByEmpno(conn, empno);
}
public EMPDTO getDetailEMPByEmpno(int empno) throws ClassNotFoundException, SQLException {
Connection conn = obj.getDBConn();
EMPDTO empdto = empdao.getDetailEMPByEmpno(conn, empno);
return empdto;
}
public List<EMPDTO> getEveryEMP() throws ClassNotFoundException, SQLException{
Connection conn = obj.getDBConn();
List<EMPDTO> empdto = empdao.getEveryEMP(conn);
return empdto;
}
public List<EMPDTO> getDetailEMPByJob(String job) throws ClassNotFoundException, SQLException {
Connection conn = obj.getDBConn();
List<EMPDTO> empdto = empdao.getDetailEMPByJob(conn, job);
return empdto;
}
}
MAIN
package mystudy1;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
public class EMPMain {
//생성자
public static void main(String[] args) throws ClassNotFoundException, SQLException, ParseException {
//필드
Scanner scan = new Scanner(System.in);
int menuNum = 0;
boolean nowrun = true;
EMPService empservice = new EMPService();
SimpleDateFormat Formatter = new SimpleDateFormat("yyyy-mm-dd");
//구현부
System.out.println("===============중앙 EMP 확인메뉴===============");
System.out.println("1.신규사원 입력");
System.out.println("2.정보 수정");
System.out.println("3.정보 삭제");
System.out.println("4.사원번호로 조회");
System.out.println("5.전체 불러오기");
System.out.println("6.직종으로 불러오기");
System.out.println("0.프로그램 종료");
System.out.println("실행하실 메뉴번호를 입력하세요 > ");
menuNum = scan.nextInt();
while(nowrun) {
switch(menuNum) {
case 1://신규사원 입력
System.out.println("사원명을 입력하세요 > ");
scan.hasNextLine();
String ename = scan.nextLine();
System.out.println("직종을 입력하세요 > ");
String job = scan.nextLine();
System.out.println("mgr을 입력하세요 > ");
int mgr = scan.nextInt();
System.out.println("고용일을 입력하세요 YYYY-MM-DD를 지킬것> ");
scan.nextLine();
java.sql.Date hiredate = (java.sql.Date) Formatter.parse(scan.nextLine());
System.out.println("연봉을 입력하세요 > ");
int sal = scan.nextInt();
System.out.println("부수입을 입력하세요 > ");
scan.nextLine();
int comm = scan.nextInt();
System.out.println("부서번호를 입력하세요 > ");
scan.nextLine();
int deptno = scan.nextInt();
empservice.insertEMP(ename, job, mgr, hiredate, sal, comm, deptno);
break;
case 2://정보수정
System.out.println("수정할 사원번호를 입력하세요 >");
scan.nextLine();
int empno1 = scan.nextInt();
System.out.println("사원명을 입력하세요 > ");
scan.hasNextLine();
String ename1 = scan.nextLine();
System.out.println("직종을 입력하세요 > ");
String job1 = scan.nextLine();
System.out.println("mgr을 입력하세요 > ");
int mgr1 = scan.nextInt();
System.out.println("고용일을 입력하세요 YYYY-MM-DD를 지킬것> ");
scan.nextLine();
java.sql.Date hiredate1 = (java.sql.Date) Formatter.parse(scan.nextLine());
System.out.println("연봉을 입력하세요 > ");
int sal1 = scan.nextInt();
System.out.println("부수입을 입력하세요 > ");
scan.nextLine();
int comm1 = scan.nextInt();
System.out.println("부서번호를 입력하세요 > ");
scan.nextLine();
int deptno1 = scan.nextInt();
empservice.updateEMP(empno1, ename1, job1, mgr1, hiredate1, sal1, comm1, deptno1);
break;
case 3://정보삭제
System.out.println("수정할 사원번호를 입력하세요 > ");
scan.nextLine();
int empno2 = scan.nextInt();
empservice.deleteEMPByEmpno(empno2);
break;
case 4://사원번호로 조회
System.out.println("조회할 사원번호를 입력하세요 > ");
scan.nextLine();
int empno3 = scan.nextInt();
empservice.getDetailEMPByEmpno(empno3);
break;
case 5://전체 불러오기
System.out.println("모든 사원목록을 출력합니다.");
List<EMPDTO> empdto = empservice.getEveryEMP();
for(EMPDTO a: empdto) {
int empno5 = a.getEmpno();
String ename5 = a.getEname();
String job5 = a.getJob();
int mgr5 = a.getMgr();
Date hiredate5 = a.getHiredate();
int sal5 = a.getSal();
int comm5 = a.getComm();
int deptno5 = a.getDeptno();
System.out.println("사원번호 : "+empno5+" 직원명 : "+ename5+" 직종명 : "+job5+" mgr명 : "+ mgr5+" 고용일 : "+ hiredate5 + " 연봉 : " + sal5 + "부수입 : " +comm5+ " 부서번호 : "+ deptno5 );
}
break;
case 6://직종으로 불러오기
System.out.println("조회할 직종을 입력해주세요 > ");
scan.nextLine();
String job6 = scan.nextLine();
List<EMPDTO> empdto2 = empservice.getDetailEMPByJob(job6);
for(EMPDTO a: empdto2) {
int empno5 = a.getEmpno();
String ename5 = a.getEname();
String job5 = a.getJob();
int mgr5 = a.getMgr();
Date hiredate5 = a.getHiredate();
int sal5 = a.getSal();
int comm5 = a.getComm();
int deptno5 = a.getDeptno();
System.out.println("사원번호 : "+empno5+" 직원명 : "+ename5+" 직종명 : "+job5+" mgr명 : "+ mgr5+" 고용일 : "+ hiredate5 + " 연봉 : " + sal5 + "부수입 : " +comm5+ " 부서번호 : "+ deptno5 );
}
break;
case 0://프로그램종료
System.out.println("프로그램을 종료합니다.");
nowrun = false;
break;
default:
break;
}//switch문 끝
}//while문끝
}//main 끝
}//Class끝
전체적으로 입력을 받은것을 통하여 정보를 가져올 수 있도록 구현했다. Scanner클래스가 들어가면 조금 더 복잡해지지만, 사용성이 높아지는 것 같아서 프로그래밍이라는 느낌이 조금이라도 든다.
'OracleDB > OracleDB 국비지원 수업 정리' 카테고리의 다른 글
| MERGE (제대로 본 기억이 없어서 적는 개념) (0) | 2023.06.09 |
|---|---|
| OracleDB 7일차 수업내용 (2) | 2023.06.09 |
| OracleDB 5~6일차 수업내용 (※ DTO / DAO / Service / Main 분업화) (0) | 2023.06.08 |
| 학원 유제문제 풀이 (1) | 2023.06.05 |
| OracleDB 4일차 수업내용 (0) | 2023.06.05 |