[Java] JDBC 부서등록 과제.
반응형
package control
class DeptAction
|
1
2
3
4
5
6
7
8 |
package control;
import java.util.Scanner;
public interface DeptAction {
public void execute(Scanner scanner);
}
|
cs |
package db
class DBUse
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
package db;
public class DBUse {
String driver = "oracle.jdbc.driver.OracleDriver";
public String url = "jdbc:oracle:thin:@192.168.35.201:1521:orcl";
public String userid = "java415";
public String passwd = "java415";
public DBUse() {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
} |
cs |
package dao
class DeptDao
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134 |
package dao;
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 db.DBUse;
import model.DeptCommand;
public class DeptDao {
public ArrayList<DeptCommand> select() {
ArrayList<DeptCommand> arrayList = new ArrayList<DeptCommand>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
DBUse dbUse = new DBUse();
try {
connection = DriverManager.getConnection(dbUse.url, dbUse.userid, dbUse.passwd);
String sql = "select deptno, dname, loc from dept";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
DeptCommand deptCommand = new DeptCommand();
deptCommand.setDeptno(resultSet.getInt("deptno"));
deptCommand.setDname(resultSet.getString("dname"));
deptCommand.setLoc(resultSet.getString("loc"));
arrayList.add(deptCommand);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return arrayList;
}
public ArrayList<DeptCommand> insert(int deptno, String dname, String loc) {
ArrayList<DeptCommand> arrayList = new ArrayList<DeptCommand>();
Connection connection = null;
PreparedStatement preparedStatement = null;
DBUse dbUse = new DBUse();
try {
connection = DriverManager.getConnection(dbUse.url, dbUse.userid, dbUse.passwd);
String sql = "insert into dept (deptno, dname, loc ) ";
sql += " values( ? , ? , ? ) ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, deptno);
preparedStatement.setString(2, dname);
preparedStatement.setString(3, loc);
preparedStatement.executeUpdate();
System.out.println("입력 하였습니다.");
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return arrayList;
}
public ArrayList<DeptCommand> update(int deptno, String dname, String loc) {
ArrayList<DeptCommand> arrayList = new ArrayList<DeptCommand>();
Connection connection = null;
PreparedStatement preparedStatement = null;
DBUse dbUse = new DBUse();
try {
connection = DriverManager.getConnection(dbUse.url, dbUse.userid, dbUse.passwd);
String sql = "update dept set dname = ?, loc = ? ";
sql += " where deptno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, dname);
preparedStatement.setString(2, loc);
preparedStatement.setInt(3, deptno);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("수정 하였습니다.");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return arrayList;
}
public ArrayList<DeptCommand> delete(int deptno) {
ArrayList<DeptCommand> arrayList = new ArrayList<DeptCommand>();
Connection connection = null;
PreparedStatement preparedStatement = null;
DBUse dbUse = new DBUse();
try {
connection = DriverManager.getConnection(dbUse.url, dbUse.userid, dbUse.passwd);
String sql = "delete from dept ";
sql += " where deptno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, deptno);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("삭제하였습니다.");
} else {
System.out.println("삭제할 번호가 없습니다.");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return arrayList;
}
} |
cs |
package service
class DeptInsert
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 |
package service;
import java.util.ArrayList;
import java.util.Scanner;
import control.DeptAction;
import dao.DeptDao;
import model.DeptCommand;
public class DeptInsert implements DeptAction {
@Override
public void execute(Scanner scanner) {
DeptDao deptDao = new DeptDao();
ArrayList<DeptCommand> arrayList = deptDao.select();
System.out.println("*****자료 등록*****");
System.out.print("부서번호를 입력하세요: " + " ");
int deptno = scanner.nextInt();
for (DeptCommand deptCommand : arrayList) {
if (deptCommand.getDeptno() == deptno) {
System.out.println("번호가 있습니다.");
return;
}
}
System.out.print("부서이름을 입력하세요: " + " ");
String dname = scanner.next();
System.out.print("지역을 입력하세요: " + " ");
String loc = scanner.next();
arrayList = deptDao.insert(deptno, dname, loc);
arrayList.clear();
}
} |
cs |
package service
class DeptDelete
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 |
package service;
import java.util.ArrayList;
import java.util.Scanner;
import control.DeptAction;
import dao.DeptDao;
import model.DeptCommand;
public class DeptDelete implements DeptAction {
@Override
public void execute(Scanner scanner) {
System.out.println("*****자료 삭제*****");
System.out.print("삭제할 부서번호를 입력하세요: " + " ");
int deptno = scanner.nextInt();
DeptDao deptDao = new DeptDao();
ArrayList<DeptCommand> arrayList = deptDao.delete(deptno);
arrayList.clear();
}
} |
cs |
package service
class DeptSearch
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 |
package service;
import java.util.ArrayList;
import java.util.Scanner;
import control.DeptAction;
import dao.DeptDao;
import model.DeptCommand;
public class DeptSearch implements DeptAction {
@Override
public void execute(Scanner scanner) {
System.out.println("*****자료 보여주기*****");
DeptDao deptDao = new DeptDao();
ArrayList<DeptCommand> arrayList = deptDao.select();
System.out.println();
for (DeptCommand command : arrayList) {
int deptno = command.getDeptno();
String dname = command.getDname();
String loc = command.getLoc();
System.out.println(deptno + " " + dname + " " + loc);
}
}
} |
cs |
package service
class DeptUpdate
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35 |
package service;
import java.util.ArrayList;
import java.util.Scanner;
import control.DeptAction;
import dao.DeptDao;
import model.DeptCommand;
public class DeptUpdate implements DeptAction {
@Override
public void execute(Scanner scanner) {
DeptDao deptDao = new DeptDao();
ArrayList<DeptCommand> arrayList = deptDao.select();
System.out.println("*****자료 수정*****");
System.out.print("수정할 부서의 번호를 입력하세요: " + " ");
int deptno = scanner.nextInt();
for (DeptCommand deptCommand : arrayList) {
if (deptCommand.getDeptno() != deptno) {
System.out.println("번호가 없습니다.");
return;
} else {
System.out.print("새로운 부서 이름을 입력하세요: " + " ");
String dname = scanner.next();
System.out.print("새로운 지역을 입력하세요: " + " ");
String loc = scanner.next();
arrayList = deptDao.update(deptno, dname, loc);
arrayList.clear();
return;
}
}
}
} |
cs |
package view
class DeptMain
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 |
package view;
import java.util.Scanner;
import service.DeptDelete;
import service.DeptInsert;
import service.DeptSearch;
import service.DeptUpdate;
public class DeptMain {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
boolean isStop = false;
do {
System.out.println();
System.out.println("다음 메뉴 중 하나를 입력하세요.");
System.out.print("1. 자료 보기" + " ");
System.out.print("2. 자료 등록" + " ");
System.out.print("3. 자료 수정" + " ");
System.out.print("4. 자료 삭제" + " ");
System.out.println("5. 종료");
System.out.println();
System.out.print("메뉴 번호 입력 : ");
String menu = scanner.next();
switch (menu) {
case "1":
DeptSearch deptSearch = new DeptSearch();
deptSearch.execute(scanner);
break;
case "2":
DeptInsert deptInsert = new DeptInsert();
deptInsert.execute(scanner);
break;
case "3":
DeptUpdate deptUpdate = new DeptUpdate();
deptUpdate.execute(scanner);
break;
case "4":
DeptDelete deptDelete = new DeptDelete();
deptDelete.execute(scanner);
break;
case "5":
isStop = true;
System.out.println("프로그램을 종료합니다.");
break;
}
} while (!isStop);
}
} |
cs |
DeptDiagram
Dept 부서 등록
Dept 부서 수정
Dept 부서 삭제 및 종료
-------------------------DeptUpdate 수정---------------------------
1개의 데이터만 변경 되는 부분 수정
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 |
package service;
import java.util.ArrayList;
import java.util.Scanner;
import control.DeptAction;
import dao.DeptDao;
import model.DeptCommand;
public class DeptUpdate implements DeptAction {
@Override
public void execute(Scanner scanner) {
DeptDao deptDao = new DeptDao();
ArrayList<DeptCommand> arrayList = deptDao.select();
System.out.println("*****자료 수정*****");
System.out.print("수정할 부서의 번호를 입력하세요: " + " ");
int deptno = scanner.nextInt();
for (DeptCommand deptCommand : arrayList) {
if (deptCommand.getDeptno() != deptno) {
continue;
} else {
System.out.print("새로운 부서 이름을 입력하세요: " + " ");
String dname = scanner.next();
System.out.print("새로운 지역을 입력하세요: " + " ");
String loc = scanner.next();
arrayList = deptDao.update(deptno, dname, loc);
return;
}
}
System.out.println("번호가 없습니다.");
}
} |
cs |
반응형
'Programming > JAVA' 카테고리의 다른 글
| [JSP] tomcat v7.0 설치 및 인코딩 메서드, get방식 test. jQuery 변경 과제. (0) | 2017.08.03 |
|---|---|
| [Java] Jakarta? (0) | 2017.08.03 |
| [JAVA] 객체지향 (Object-Oriented) (0) | 2017.06.28 |
| JAVA 상속 <Inheritance> (0) | 2017.05.30 |
| JAVA 배열(Array) (0) | 2017.05.26 |
댓글