1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
|
5 |
|
|
6 |
|
|
7 |
|
|
8 |
|
|
9 |
|
|
10 |
|
|
11 |
|
|
12 |
|
|
13 |
|
|
14 |
|
|
15 |
|
|
16 |
|
|
17 |
|
package org.apache.commons.configuration; |
18 |
|
|
19 |
|
import java.sql.Connection; |
20 |
|
import java.sql.PreparedStatement; |
21 |
|
import java.sql.ResultSet; |
22 |
|
import java.sql.SQLException; |
23 |
|
import java.sql.Statement; |
24 |
|
import java.util.ArrayList; |
25 |
|
import java.util.Collection; |
26 |
|
import java.util.Iterator; |
27 |
|
import java.util.List; |
28 |
|
|
29 |
|
import javax.sql.DataSource; |
30 |
|
|
31 |
|
import org.apache.commons.logging.Log; |
32 |
|
import org.apache.commons.logging.LogFactory; |
33 |
|
|
34 |
|
|
35 |
|
|
36 |
|
|
37 |
|
|
38 |
|
|
39 |
|
|
40 |
|
|
41 |
|
|
42 |
3 |
public class DatabaseConfiguration extends AbstractConfiguration |
43 |
|
{ |
44 |
|
|
45 |
3 |
private static Log log = LogFactory.getLog(DatabaseConfiguration.class); |
46 |
|
|
47 |
|
|
48 |
|
private DataSource datasource; |
49 |
|
|
50 |
|
|
51 |
|
private String table; |
52 |
|
|
53 |
|
|
54 |
|
private String nameColumn; |
55 |
|
|
56 |
|
|
57 |
|
private String keyColumn; |
58 |
|
|
59 |
|
|
60 |
|
private String valueColumn; |
61 |
|
|
62 |
|
|
63 |
|
private String name; |
64 |
|
|
65 |
|
|
66 |
|
|
67 |
|
|
68 |
|
|
69 |
|
|
70 |
|
|
71 |
|
|
72 |
|
|
73 |
|
|
74 |
|
|
75 |
|
public DatabaseConfiguration(DataSource datasource, String table, String nameColumn, |
76 |
|
String keyColumn, String valueColumn, String name) |
77 |
57 |
{ |
78 |
57 |
this.datasource = datasource; |
79 |
57 |
this.table = table; |
80 |
57 |
this.nameColumn = nameColumn; |
81 |
57 |
this.keyColumn = keyColumn; |
82 |
57 |
this.valueColumn = valueColumn; |
83 |
57 |
this.name = name; |
84 |
57 |
} |
85 |
|
|
86 |
|
|
87 |
|
|
88 |
|
|
89 |
|
|
90 |
|
|
91 |
|
|
92 |
|
|
93 |
|
|
94 |
|
public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn) |
95 |
|
{ |
96 |
33 |
this(datasource, table, null, keyColumn, valueColumn, class="keyword">null); |
97 |
33 |
} |
98 |
|
|
99 |
|
|
100 |
|
|
101 |
|
|
102 |
|
public Object getProperty(String key) |
103 |
|
{ |
104 |
21 |
Object result = null; |
105 |
|
|
106 |
|
|
107 |
21 |
StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?"); |
108 |
21 |
if (nameColumn != null) |
109 |
|
{ |
110 |
9 |
query.append(" AND " + nameColumn + "=?"); |
111 |
|
} |
112 |
|
|
113 |
21 |
Connection conn = null; |
114 |
21 |
PreparedStatement pstmt = null; |
115 |
|
|
116 |
|
try |
117 |
|
{ |
118 |
21 |
conn = datasource.getConnection(); |
119 |
|
|
120 |
|
|
121 |
21 |
pstmt = conn.prepareStatement(query.toString()); |
122 |
21 |
pstmt.setString(1, key); |
123 |
21 |
if (nameColumn != null) |
124 |
|
{ |
125 |
9 |
pstmt.setString(2, name); |
126 |
|
} |
127 |
|
|
128 |
21 |
ResultSet rs = pstmt.executeQuery(); |
129 |
|
|
130 |
21 |
if (rs.next()) |
131 |
|
{ |
132 |
15 |
result = rs.getObject(valueColumn); |
133 |
|
} |
134 |
|
|
135 |
|
|
136 |
21 |
if (rs.next()) |
137 |
|
{ |
138 |
3 |
List results = new ArrayList(); |
139 |
3 |
results.add(result); |
140 |
3 |
results.add(rs.getObject(valueColumn)); |
141 |
9 |
while (rs.next()) |
142 |
|
{ |
143 |
3 |
results.add(rs.getObject(valueColumn)); |
144 |
|
} |
145 |
3 |
result = results; |
146 |
|
} |
147 |
21 |
} |
148 |
|
catch (SQLException e) |
149 |
|
{ |
150 |
0 |
log.error(e.getMessage(), e); |
151 |
0 |
} |
152 |
|
finally |
153 |
|
{ |
154 |
0 |
closeQuietly(conn, pstmt); |
155 |
|
} |
156 |
|
|
157 |
21 |
return result; |
158 |
|
} |
159 |
|
|
160 |
|
|
161 |
|
|
162 |
|
|
163 |
|
protected void addPropertyDirect(String key, Object obj) |
164 |
|
{ |
165 |
|
|
166 |
9 |
StringBuffer query = new StringBuffer("INSERT INTO " + table); |
167 |
9 |
if (nameColumn != null) |
168 |
|
{ |
169 |
3 |
query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)"); |
170 |
|
} |
171 |
|
else |
172 |
|
{ |
173 |
6 |
query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)"); |
174 |
|
} |
175 |
|
|
176 |
9 |
Connection conn = null; |
177 |
9 |
PreparedStatement pstmt = null; |
178 |
|
|
179 |
|
try |
180 |
|
{ |
181 |
9 |
conn = datasource.getConnection(); |
182 |
|
|
183 |
|
|
184 |
9 |
pstmt = conn.prepareStatement(query.toString()); |
185 |
9 |
int index = 1; |
186 |
9 |
if (nameColumn != null) |
187 |
|
{ |
188 |
3 |
pstmt.setString(index++, name); |
189 |
|
} |
190 |
9 |
pstmt.setString(index++, key); |
191 |
9 |
pstmt.setString(index++, String.valueOf(obj)); |
192 |
|
|
193 |
9 |
pstmt.executeUpdate(); |
194 |
9 |
} |
195 |
|
catch (SQLException e) |
196 |
|
{ |
197 |
0 |
log.error(e.getMessage(), e); |
198 |
0 |
} |
199 |
|
finally |
200 |
|
{ |
201 |
|
|
202 |
0 |
closeQuietly(conn, pstmt); |
203 |
|
} |
204 |
9 |
} |
205 |
|
|
206 |
|
|
207 |
|
|
208 |
|
|
209 |
|
public boolean isEmpty() |
210 |
|
{ |
211 |
18 |
boolean empty = true; |
212 |
|
|
213 |
|
|
214 |
18 |
StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table); |
215 |
18 |
if (nameColumn != null) |
216 |
|
{ |
217 |
9 |
query.append(" WHERE " + nameColumn + "=?"); |
218 |
|
} |
219 |
|
|
220 |
18 |
Connection conn = null; |
221 |
18 |
PreparedStatement pstmt = null; |
222 |
|
|
223 |
|
try |
224 |
|
{ |
225 |
18 |
conn = datasource.getConnection(); |
226 |
|
|
227 |
|
|
228 |
18 |
pstmt = conn.prepareStatement(query.toString()); |
229 |
18 |
if (nameColumn != null) |
230 |
|
{ |
231 |
9 |
pstmt.setString(1, name); |
232 |
|
} |
233 |
|
|
234 |
18 |
ResultSet rs = pstmt.executeQuery(); |
235 |
|
|
236 |
18 |
if (rs.next()) |
237 |
|
{ |
238 |
18 |
empty = rs.getInt(1) == 0; |
239 |
|
} |
240 |
18 |
} |
241 |
|
catch (SQLException e) |
242 |
|
{ |
243 |
0 |
log.error(e.getMessage(), e); |
244 |
0 |
} |
245 |
|
finally |
246 |
|
{ |
247 |
|
|
248 |
0 |
closeQuietly(conn, pstmt); |
249 |
|
} |
250 |
|
|
251 |
18 |
return empty; |
252 |
|
} |
253 |
|
|
254 |
|
|
255 |
|
|
256 |
|
|
257 |
|
public boolean containsKey(String key) |
258 |
|
{ |
259 |
30 |
boolean found = false; |
260 |
|
|
261 |
|
|
262 |
30 |
StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?"); |
263 |
30 |
if (nameColumn != null) |
264 |
|
{ |
265 |
12 |
query.append(" AND " + nameColumn + "=?"); |
266 |
|
} |
267 |
|
|
268 |
30 |
Connection conn = null; |
269 |
30 |
PreparedStatement pstmt = null; |
270 |
|
|
271 |
|
try |
272 |
|
{ |
273 |
30 |
conn = datasource.getConnection(); |
274 |
|
|
275 |
|
|
276 |
30 |
pstmt = conn.prepareStatement(query.toString()); |
277 |
30 |
pstmt.setString(1, key); |
278 |
30 |
if (nameColumn != null) |
279 |
|
{ |
280 |
12 |
pstmt.setString(2, name); |
281 |
|
} |
282 |
|
|
283 |
30 |
ResultSet rs = pstmt.executeQuery(); |
284 |
|
|
285 |
30 |
found = rs.next(); |
286 |
30 |
} |
287 |
|
catch (SQLException e) |
288 |
|
{ |
289 |
0 |
log.error(e.getMessage(), e); |
290 |
0 |
} |
291 |
|
finally |
292 |
|
{ |
293 |
|
|
294 |
0 |
closeQuietly(conn, pstmt); |
295 |
|
} |
296 |
|
|
297 |
30 |
return found; |
298 |
|
} |
299 |
|
|
300 |
|
|
301 |
|
|
302 |
|
|
303 |
|
public void clearProperty(String key) |
304 |
|
{ |
305 |
|
|
306 |
9 |
StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?"); |
307 |
9 |
if (nameColumn != null) |
308 |
|
{ |
309 |
3 |
query.append(" AND " + nameColumn + "=?"); |
310 |
|
} |
311 |
|
|
312 |
9 |
Connection conn = null; |
313 |
9 |
PreparedStatement pstmt = null; |
314 |
|
|
315 |
|
try |
316 |
|
{ |
317 |
9 |
conn = datasource.getConnection(); |
318 |
|
|
319 |
|
|
320 |
9 |
pstmt = conn.prepareStatement(query.toString()); |
321 |
9 |
pstmt.setString(1, key); |
322 |
9 |
if (nameColumn != null) |
323 |
|
{ |
324 |
3 |
pstmt.setString(2, name); |
325 |
|
} |
326 |
|
|
327 |
9 |
pstmt.executeUpdate(); |
328 |
9 |
} |
329 |
|
catch (SQLException e) |
330 |
|
{ |
331 |
0 |
log.error(e.getMessage(), e); |
332 |
0 |
} |
333 |
|
finally |
334 |
|
{ |
335 |
|
|
336 |
0 |
closeQuietly(conn, pstmt); |
337 |
|
} |
338 |
9 |
} |
339 |
|
|
340 |
|
|
341 |
|
|
342 |
|
|
343 |
|
public void clear() |
344 |
|
{ |
345 |
|
|
346 |
6 |
StringBuffer query = new StringBuffer("DELETE FROM " + table); |
347 |
6 |
if (nameColumn != null) |
348 |
|
{ |
349 |
3 |
query.append(" WHERE " + nameColumn + "=?"); |
350 |
|
} |
351 |
|
|
352 |
6 |
Connection conn = null; |
353 |
6 |
PreparedStatement pstmt = null; |
354 |
|
|
355 |
|
try |
356 |
|
{ |
357 |
6 |
conn = datasource.getConnection(); |
358 |
|
|
359 |
|
|
360 |
6 |
pstmt = conn.prepareStatement(query.toString()); |
361 |
6 |
if (nameColumn != null) |
362 |
|
{ |
363 |
3 |
pstmt.setString(1, name); |
364 |
|
} |
365 |
|
|
366 |
6 |
pstmt.executeUpdate(); |
367 |
6 |
} |
368 |
|
catch (SQLException e) |
369 |
|
{ |
370 |
0 |
log.error(e.getMessage(), e); |
371 |
0 |
} |
372 |
|
finally |
373 |
|
{ |
374 |
|
|
375 |
0 |
closeQuietly(conn, pstmt); |
376 |
|
} |
377 |
6 |
} |
378 |
|
|
379 |
|
|
380 |
|
|
381 |
|
|
382 |
|
public Iterator getKeys() |
383 |
|
{ |
384 |
15 |
Collection keys = new ArrayList(); |
385 |
|
|
386 |
|
|
387 |
15 |
StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table); |
388 |
15 |
if (nameColumn != null) |
389 |
|
{ |
390 |
3 |
query.append(" WHERE " + nameColumn + "=?"); |
391 |
|
} |
392 |
|
|
393 |
15 |
Connection conn = null; |
394 |
15 |
PreparedStatement pstmt = null; |
395 |
|
|
396 |
|
try |
397 |
|
{ |
398 |
15 |
conn = datasource.getConnection(); |
399 |
|
|
400 |
|
|
401 |
15 |
pstmt = conn.prepareStatement(query.toString()); |
402 |
15 |
if (nameColumn != null) |
403 |
|
{ |
404 |
3 |
pstmt.setString(1, name); |
405 |
|
} |
406 |
|
|
407 |
15 |
ResultSet rs = pstmt.executeQuery(); |
408 |
|
|
409 |
54 |
while (rs.next()) |
410 |
|
{ |
411 |
24 |
keys.add(rs.getString(1)); |
412 |
|
} |
413 |
15 |
} |
414 |
|
catch (SQLException e) |
415 |
|
{ |
416 |
0 |
log.error(e.getMessage(), e); |
417 |
0 |
} |
418 |
|
finally |
419 |
|
{ |
420 |
|
|
421 |
0 |
closeQuietly(conn, pstmt); |
422 |
|
} |
423 |
|
|
424 |
15 |
return keys.iterator(); |
425 |
|
} |
426 |
|
|
427 |
|
|
428 |
|
|
429 |
|
|
430 |
|
|
431 |
|
|
432 |
|
|
433 |
|
|
434 |
|
private void closeQuietly(Connection conn, Statement stmt) |
435 |
|
{ |
436 |
|
try |
437 |
|
{ |
438 |
108 |
if (stmt != null) |
439 |
|
{ |
440 |
108 |
stmt.close(); |
441 |
|
} |
442 |
108 |
if (conn != null) |
443 |
|
{ |
444 |
108 |
conn.close(); |
445 |
|
} |
446 |
108 |
} |
447 |
|
catch (SQLException e) |
448 |
|
{ |
449 |
0 |
log.error(e.getMessage(), e); |
450 |
|
} |
451 |
108 |
} |
452 |
|
} |