-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDepartmentDAO.java
More file actions
140 lines (117 loc) · 3.64 KB
/
Copy pathDepartmentDAO.java
File metadata and controls
140 lines (117 loc) · 3.64 KB
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
135
136
137
138
139
140
package java0928_jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/*
* 1. ClassNotFoundException
* 해결) JRE System Library에서 ojdbc14.jar파일 확인
* Class.forName(): 경로확인
*
* 2. java.sql.SQLException:
* IO예외 상황: The Network Adapter...
* Listener refused.....
*
* 해결) 제어판->관리도구->서비스->OracleServiceXE,
* OracleXETNSListener을 다시 시작한다.
*
* java.sql.PreparedStatement
* 1 반복되는 쿼리문의 수행에 사용한다.
* 2 미리 정의된 SQL문을 수행하기 때문에 Statement에 비해 속도가 빠르다.
* 3 위치표시자(placeholder)(?)를 사용하여 쿼리문을 간략하게 작성한다.
*/
/*
* 싱글톤 패턴 : 하나의 객체만을 생성해서 사용할 수 있도록 설계한 구조이다.
* 1. 생성자의 접근 제어자는 private
* 2. 객체 자신을 생성한다.
* 3. 생성된 객체를 넘겨줄 수 있는 메소드를 정의한다. (static)
*/
// DAO(Data Access Object) : 데이터 접근 객체
public class DepartmentDAO {
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
private static DepartmentDAO dao = new DepartmentDAO();
private DepartmentDAO() {
}
public static DepartmentDAO getInstance() {
return dao;
}
private Connection init() throws ClassNotFoundException, SQLException {
// 1. 드라이버 로딩
Class.forName("oracle.jdbc.OracleDriver");
// 2. 서버 연결
String url = "jdbc:oracle:thin://@127.0.0.1:1521:xe";
String username = "hr";
String password = "a1234";
conn = DriverManager.getConnection(url, username, password);
return conn;
}
private void exit() throws SQLException {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
}
public List<DepartmentDTO> listMethod() {
List<DepartmentDTO> aList = new ArrayList<DepartmentDTO>();
try {
conn = init();
stmt = conn.createStatement();
String sql = "SELECT * FROM departments ORDER BY department_id";
rs = stmt.executeQuery(sql);
while (rs.next()) {
DepartmentDTO dto = new DepartmentDTO();
dto.setDepartment_id(rs.getInt("department_id"));
dto.setDepartment_name(rs.getString("department_name"));
dto.setManager_id(rs.getInt("manager_id"));
dto.setLocation_id(rs.getInt("location_id"));
aList.add(dto);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
return aList;
}
public List<DepartmentDTO> searchMethod(String data) {
List<DepartmentDTO> aList = new ArrayList<DepartmentDTO>();
try {
conn = init();
stmt = conn.createStatement();
String sql = "SELECT * FROM departments WHERE lower(department_name) LIKE lower('%" + data + "%')";
rs = stmt.executeQuery(sql);
while (rs.next()) {
DepartmentDTO dto = new DepartmentDTO();
dto.setDepartment_id(rs.getInt("department_id"));
dto.setDepartment_name(rs.getString("department_name"));
dto.setManager_id(rs.getInt("manager_id"));
dto.setLocation_id(rs.getInt("location_id"));
aList.add(dto);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
return aList;
}
}