-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMemDAO.java
More file actions
161 lines (133 loc) · 3.6 KB
/
Copy pathMemDAO.java
File metadata and controls
161 lines (133 loc) · 3.6 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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package java0929_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.HashMap;
import java.util.List;
/*
* java.sql.PreparedStatement
* 1. 반복되는 쿼리문의 수행에 사용한다.
* 2. 미리 정의된 SQL문을 수행하기 때문에 Statement에 비해 속도가 빠르다.
* 3. 위치표시자(Placeholder)(?)를 사용하여 쿼리문을 간략하게 작성한다.
*/
public class MemDAO {
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
private static MemDAO dao = new MemDAO();
private MemDAO() {
}
public static MemDAO getInstance() {
return dao;
}
private Connection init() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.OracleDriver");
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<MemDTO> listMethod() {
List<MemDTO> aList = new ArrayList<MemDTO>();
try {
conn = init();
stmt = conn.createStatement();
String sql = "SELECT * FROM Mem ORDER BY num";
rs = stmt.executeQuery(sql);
while (rs.next()) {
MemDTO dto = new MemDTO();
dto.setNum(rs.getInt("NUM"));
dto.setName(rs.getString("NAME"));
dto.setAge(rs.getInt("AGE"));
dto.setLoc(rs.getString("LOC"));
aList.add(dto);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
return aList;
}
public void insertMethod(MemDTO dto) {
try {
conn = init();
/*
* stmt = conn.createStatement(); String sql =
* "INSERT INTO Mem VALUES (mem_num_seq.nextval, '" + dto.getName() + "', " +
* dto.getAge() + ", '" + dto.getLoc() + "')"; stmt.executeUpdate(sql);
*/
String sql = "INSERT INTO mem VALUES (mem_num_seq.nextval, ?, ?, ?)"; // ? 와일드카드는 순서대로 인덱스가 매겨진다. 1부터 시작
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getName());
pstmt.setInt(2, dto.getAge());
pstmt.setString(3, dto.getLoc());
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void updateMethod(HashMap<String, Object> map) {
String name = map.get("name").toString();
Integer num = (Integer) map.get("num");
try {
conn = init();
String sql = "UPDATE mem SET name = ? WHERE num = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, num);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void deleteMethod(int num) {
try {
conn = init();
String sql = "DELETE FROM mem WHERE num = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
exit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}