package dept;
public class Dept {
// VO : 데이터를 저장하는 기능만 가지는 클래스, read Only
// DTO : 데이터틀 저장하는 기능을 가지는 클래스, Write 가능
public Dept(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
private int deptno;
private String dname;
private String loc;
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
package dept;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionProvider {
public static Connection getConnection() throws SQLException {
Connection conn = null;
// 2. DB 연결 localhost == 127.0.0.1
String jdbcUrl = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
String user = "hyo";
String password = "admin";
conn = DriverManager.getConnection(jdbcUrl, user, password);
return conn;
}
}
package dept;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
// DAO : Data Access Object -> 데이터베이스 처리하는 기능만 가지는 클래스
public class DeptDao {
private static DeptDao dao = new DeptDao();
private DeptDao() {
}
public static DeptDao getInstance() {
return dao;
}
// 부서 입력
int insertDept(Dept dept, Connection conn) {
int resultCnt = 0;
// DB 연결 : Connection
try {
// Statement
// SQL : Insert into
String sql = "insert into dept values(?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, dept.getDeptno());
pstmt.setString(2, dept.getDname());
pstmt.setString(3, dept.getLoc());
// sql 실행
resultCnt = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 결과
return resultCnt;
}
// 부서 정보를 수정 : Dept
int updateDept(Dept dept, Connection conn) {
int resultCnt = 0;
// DB 연결 : Connection
try {
// Statement
// SQL : Update
String sql = "update dept set dname=?, loc=? where deptno=? ";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(3, dept.getDeptno());
pstmt.setString(1, dept.getDname());
pstmt.setString(2, dept.getLoc());
// sql 실행
resultCnt = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 결과
return resultCnt;
}
// 부서정보 삭제
int deleteDept(int deptno, Connection conn) {
int resultCnt = 0;
// DB 연결 : Connection
try {
// Statement
// SQL : delete
String sql = "delete from dept where deptno=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, deptno);
// sql 실행
resultCnt = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 결과
return resultCnt;
}
// 부서의 전체 리스트
List<Dept> listDept(Connection conn) {
List<Dept> list = new ArrayList<Dept>();
Statement stmt = null;
try {
stmt = conn.createStatement();
String sql = "select * from dept order by deptno";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
// Dept d = new Dept(rs.getInt(1), rs.getString(2), rs.getString(3));
// list.add(d);
list.add(new Dept(rs.getInt(1), rs.getString(2), rs.getString(3)));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
// 부서 하나 검색
Dept searchDept(int deptno, Connection conn) {
Dept dept = null;
Statement stmt = null;
try {
stmt = conn.createStatement();
String sql = "select * from dept where deptno="+deptno;
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()) {
dept = new Dept(rs.getInt(1), rs.getString(2), rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dept;
}
}
package dept;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;
public class DeptManager {
// 사용자 입력
Scanner sc = new Scanner(System.in);
DeptDao dao = DeptDao.getInstance();
// 부서입력 메소드 : 사용자에게 입력 받은 데이터를 DAO를 이용해서 DB 입력
void insertDept() {
// 부서정보를 받아서 인스턴스를 생성하고 dao.insertDept()
System.out.println("부서정보를 입력합니다.");
System.out.println("부서번호를 입력해주세요.");
String deptno = sc.nextLine();
System.out.println("부서이름을 입력해주세요.");
String dname = sc.nextLine();
System.out.println("지역 이름을 입력해주세요.");
String loc = sc.nextLine();
Dept dept = new Dept(Integer.parseInt(deptno), dname, loc);
Connection conn = null;
int resultCnt = 0;
try {
conn = ConnectionProvider.getConnection();
resultCnt = dao.insertDept(dept, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(resultCnt>0) {
System.out.println("입력되었습니다.");
} else {
System.out.println("입력이 실패했습니다.");
}
}
// 부서 정보 수정 메소드 : 사용자에게 입력 받고 데이터를 DAO를 이용해서 수정
void editDept() {
System.out.println("수정을 원하시는 부서번호를 입력해주세요.");
String deptno = sc.nextLine();
System.out.println("새로운 부서 이름을 입력해주세요. ");
String dname = sc.nextLine();
System.out.println("새로운 지역 이름을 입력해주세요.");
String loc = sc.nextLine();
Dept dept = new Dept(Integer.parseInt(deptno), dname, loc);
Connection conn = null;
int resultCnt = 0;
try {
conn = ConnectionProvider.getConnection();
resultCnt = dao.updateDept(dept, conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(resultCnt>0) {
System.out.println("수정되었습니다.");
} else {
System.out.println("수정이 실패했습니다.");
}
}
// 부서 정보를 삭제
void delDept() {
System.out.println("삭제를 원하시는 부서번호를 입력해주세요.");
String deptno = sc.nextLine();
Connection conn = null;
int resultCnt = 0;
try {
conn = ConnectionProvider.getConnection();
resultCnt = dao.deleteDept(Integer.parseInt(deptno), conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(resultCnt>0) {
System.out.println("삭제되었습니다.");
} else {
System.out.println("지우려는 정보가 존재하지 않습니다.");
}
}
void listDept() {
Connection conn = null;
List<Dept> list = null;
try {
conn = ConnectionProvider.getConnection();
list = dao.listDept(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(list.isEmpty()) {
System.out.println("저장된 정보가 없습니다.");
} else {
for(Dept d : list) {
//System.out.println(d);
System.out.printf("%5s", d.getDeptno()+"\t" );
System.out.printf("%12s", d.getDname()+"\t" );
System.out.printf("%12s", d.getLoc()+"\n" );
}
}
}
void searchDept() {
System.out.println("부서를 검색합니다.");
System.out.println("찾으시는 부서의 번호를 입력해주세요.");
String deptno = sc.nextLine();
Connection conn = null;
Dept dept = null;
try {
conn = ConnectionProvider.getConnection();
dept = dao.searchDept(Integer.parseInt(deptno), conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(dept!=null) {
System.out.println(dept);
} else {
System.out.println("찾으시는 정보가 존재하지 않습니다.");
}
}
}
package dept;
import java.util.Scanner;
public class DeptMain {
public static void main(String[] args) {
DeptManager manager = new DeptManager();
Scanner sc = new Scanner(System.in);
// 데이터 베이스 드라이버 로드
try {
Class.forName("com.mysql.cj.jdbc.Driver");
while(true) {
System.out.println("부서관리 메뉴를 입력해주세요.");
System.out.println("1. 입력, 2. 수정, 3.삭제, 4. 전체리스트, 5. 검색, 6. 종료");
System.out.println("__________________________________________________");
String select = sc.nextLine();
switch (select.charAt(0)) {
case '1':
manager.insertDept();
break;
case '2':
manager.editDept();
break;
case '3':
manager.delDept();
break;
case '4':
manager.listDept();
break;
case '5':
manager.searchDept();
break;
case '6':
System.out.println("프로그램을 종료합니다.");
return;
}
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}