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