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.LogFactory;
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 public class DatabaseConfiguration extends AbstractConfiguration
89 {
90
91 private final DataSource datasource;
92
93
94 private final String table;
95
96
97 private final String nameColumn;
98
99
100 private final String keyColumn;
101
102
103 private final String valueColumn;
104
105
106 private final String name;
107
108
109 private final boolean doCommits;
110
111
112
113
114
115
116
117
118
119
120
121
122 public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
123 String keyColumn, String valueColumn, String name)
124 {
125 this(datasource, table, nameColumn, keyColumn, valueColumn, name, false);
126 }
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141 public DatabaseConfiguration(DataSource datasource, String table,
142 String nameColumn, String keyColumn, String valueColumn,
143 String name, boolean commits)
144 {
145 this.datasource = datasource;
146 this.table = table;
147 this.nameColumn = nameColumn;
148 this.keyColumn = keyColumn;
149 this.valueColumn = valueColumn;
150 this.name = name;
151 doCommits = commits;
152 setLogger(LogFactory.getLog(getClass()));
153 addErrorLogListener();
154 }
155
156
157
158
159
160
161
162
163
164 public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
165 {
166 this(datasource, table, null, keyColumn, valueColumn, null);
167 }
168
169
170
171
172
173
174
175
176
177
178
179
180 public DatabaseConfiguration(DataSource datasource, String table,
181 String keyColumn, String valueColumn, boolean commits)
182 {
183 this(datasource, table, null, keyColumn, valueColumn, null, commits);
184 }
185
186
187
188
189
190
191
192 public boolean isDoCommits()
193 {
194 return doCommits;
195 }
196
197
198
199
200
201
202
203
204
205
206
207 public Object getProperty(String key)
208 {
209 Object result = null;
210
211
212 StringBuilder query = new StringBuilder("SELECT * FROM ");
213 query.append(table).append(" WHERE ");
214 query.append(keyColumn).append("=?");
215 if (nameColumn != null)
216 {
217 query.append(" AND " + nameColumn + "=?");
218 }
219
220 Connection conn = null;
221 PreparedStatement pstmt = null;
222 ResultSet rs = null;
223
224 try
225 {
226 conn = getConnection();
227
228
229 pstmt = conn.prepareStatement(query.toString());
230 pstmt.setString(1, key);
231 if (nameColumn != null)
232 {
233 pstmt.setString(2, name);
234 }
235
236 rs = pstmt.executeQuery();
237
238 List<Object> results = new ArrayList<Object>();
239 while (rs.next())
240 {
241 Object value = rs.getObject(valueColumn);
242 if (isDelimiterParsingDisabled())
243 {
244 results.add(value);
245 }
246 else
247 {
248
249 Iterator<?> it = PropertyConverter.toIterator(value, getListDelimiter());
250 while (it.hasNext())
251 {
252 results.add(it.next());
253 }
254 }
255 }
256
257 if (!results.isEmpty())
258 {
259 result = (results.size() > 1) ? results : results.get(0);
260 }
261 }
262 catch (SQLException e)
263 {
264 fireError(EVENT_READ_PROPERTY, key, null, e);
265 }
266 finally
267 {
268 close(conn, pstmt, rs);
269 }
270
271 return result;
272 }
273
274
275
276
277
278
279
280
281
282
283
284 @Override
285 protected void addPropertyDirect(String key, Object obj)
286 {
287
288 StringBuilder query = new StringBuilder("INSERT INTO " + table);
289 if (nameColumn != null)
290 {
291 query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
292 }
293 else
294 {
295 query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
296 }
297
298 Connection conn = null;
299 PreparedStatement pstmt = null;
300
301 try
302 {
303 conn = getConnection();
304
305
306 pstmt = conn.prepareStatement(query.toString());
307 int index = 1;
308 if (nameColumn != null)
309 {
310 pstmt.setString(index++, name);
311 }
312 pstmt.setString(index++, key);
313 pstmt.setString(index++, String.valueOf(obj));
314
315 pstmt.executeUpdate();
316 commitIfRequired(conn);
317 }
318 catch (SQLException e)
319 {
320 fireError(EVENT_ADD_PROPERTY, key, obj, e);
321 }
322 finally
323 {
324
325 close(conn, pstmt, null);
326 }
327 }
328
329
330
331
332
333
334
335
336
337
338
339
340
341 @Override
342 public void addProperty(String key, Object value)
343 {
344 boolean parsingFlag = isDelimiterParsingDisabled();
345 try
346 {
347 if (value instanceof String)
348 {
349
350 setDelimiterParsingDisabled(true);
351 }
352 super.addProperty(key, value);
353 }
354 finally
355 {
356 setDelimiterParsingDisabled(parsingFlag);
357 }
358 }
359
360
361
362
363
364
365
366
367
368 public boolean isEmpty()
369 {
370 boolean empty = true;
371
372
373 StringBuilder query = new StringBuilder("SELECT count(*) FROM " + table);
374 if (nameColumn != null)
375 {
376 query.append(" WHERE " + nameColumn + "=?");
377 }
378
379 Connection conn = null;
380 PreparedStatement pstmt = null;
381 ResultSet rs = null;
382
383 try
384 {
385 conn = getConnection();
386
387
388 pstmt = conn.prepareStatement(query.toString());
389 if (nameColumn != null)
390 {
391 pstmt.setString(1, name);
392 }
393
394 rs = pstmt.executeQuery();
395
396 if (rs.next())
397 {
398 empty = rs.getInt(1) == 0;
399 }
400 }
401 catch (SQLException e)
402 {
403 fireError(EVENT_READ_PROPERTY, null, null, e);
404 }
405 finally
406 {
407
408 close(conn, pstmt, rs);
409 }
410
411 return empty;
412 }
413
414
415
416
417
418
419
420
421
422
423
424 public boolean containsKey(String key)
425 {
426 boolean found = false;
427
428
429 StringBuilder query = new StringBuilder("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
430 if (nameColumn != null)
431 {
432 query.append(" AND " + nameColumn + "=?");
433 }
434
435 Connection conn = null;
436 PreparedStatement pstmt = null;
437 ResultSet rs = null;
438
439 try
440 {
441 conn = getConnection();
442
443
444 pstmt = conn.prepareStatement(query.toString());
445 pstmt.setString(1, key);
446 if (nameColumn != null)
447 {
448 pstmt.setString(2, name);
449 }
450
451 rs = pstmt.executeQuery();
452
453 found = rs.next();
454 }
455 catch (SQLException e)
456 {
457 fireError(EVENT_READ_PROPERTY, key, null, e);
458 }
459 finally
460 {
461
462 close(conn, pstmt, rs);
463 }
464
465 return found;
466 }
467
468
469
470
471
472
473
474
475
476
477 @Override
478 protected void clearPropertyDirect(String key)
479 {
480
481 StringBuilder query = new StringBuilder("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
482 if (nameColumn != null)
483 {
484 query.append(" AND " + nameColumn + "=?");
485 }
486
487 Connection conn = null;
488 PreparedStatement pstmt = null;
489
490 try
491 {
492 conn = getConnection();
493
494
495 pstmt = conn.prepareStatement(query.toString());
496 pstmt.setString(1, key);
497 if (nameColumn != null)
498 {
499 pstmt.setString(2, name);
500 }
501
502 pstmt.executeUpdate();
503 commitIfRequired(conn);
504 }
505 catch (SQLException e)
506 {
507 fireError(EVENT_CLEAR_PROPERTY, key, null, e);
508 }
509 finally
510 {
511
512 close(conn, pstmt, null);
513 }
514 }
515
516
517
518
519
520
521
522
523 @Override
524 public void clear()
525 {
526 fireEvent(EVENT_CLEAR, null, null, true);
527
528 StringBuilder query = new StringBuilder("DELETE FROM " + table);
529 if (nameColumn != null)
530 {
531 query.append(" WHERE " + nameColumn + "=?");
532 }
533
534 Connection conn = null;
535 PreparedStatement pstmt = null;
536
537 try
538 {
539 conn = getConnection();
540
541
542 pstmt = conn.prepareStatement(query.toString());
543 if (nameColumn != null)
544 {
545 pstmt.setString(1, name);
546 }
547
548 pstmt.executeUpdate();
549 commitIfRequired(conn);
550 }
551 catch (SQLException e)
552 {
553 fireError(EVENT_CLEAR, null, null, e);
554 }
555 finally
556 {
557
558 close(conn, pstmt, null);
559 }
560 fireEvent(EVENT_CLEAR, null, null, false);
561 }
562
563
564
565
566
567
568
569
570
571
572
573 public Iterator<String> getKeys()
574 {
575 Collection<String> keys = new ArrayList<String>();
576
577
578 StringBuilder query = new StringBuilder("SELECT DISTINCT " + keyColumn + " FROM " + table);
579 if (nameColumn != null)
580 {
581 query.append(" WHERE " + nameColumn + "=?");
582 }
583
584 Connection conn = null;
585 PreparedStatement pstmt = null;
586 ResultSet rs = null;
587
588 try
589 {
590 conn = getConnection();
591
592
593 pstmt = conn.prepareStatement(query.toString());
594 if (nameColumn != null)
595 {
596 pstmt.setString(1, name);
597 }
598
599 rs = pstmt.executeQuery();
600
601 while (rs.next())
602 {
603 keys.add(rs.getString(1));
604 }
605 }
606 catch (SQLException e)
607 {
608 fireError(EVENT_READ_PROPERTY, null, null, e);
609 }
610 finally
611 {
612
613 close(conn, pstmt, rs);
614 }
615
616 return keys.iterator();
617 }
618
619
620
621
622
623
624
625 public DataSource getDatasource()
626 {
627 return datasource;
628 }
629
630
631
632
633
634
635
636
637
638
639
640
641 @Deprecated
642 protected Connection getConnection() throws SQLException
643 {
644 return getDatasource().getConnection();
645 }
646
647
648
649
650
651
652
653
654
655 private void close(Connection conn, Statement stmt, ResultSet rs)
656 {
657 try
658 {
659 if (rs != null)
660 {
661 rs.close();
662 }
663 }
664 catch (SQLException e)
665 {
666 getLogger().error("An error occurred on closing the result set", e);
667 }
668
669 try
670 {
671 if (stmt != null)
672 {
673 stmt.close();
674 }
675 }
676 catch (SQLException e)
677 {
678 getLogger().error("An error occured on closing the statement", e);
679 }
680
681 try
682 {
683 if (conn != null)
684 {
685 conn.close();
686 }
687 }
688 catch (SQLException e)
689 {
690 getLogger().error("An error occured on closing the connection", e);
691 }
692 }
693
694
695
696
697
698
699
700
701
702 private void commitIfRequired(Connection conn) throws SQLException
703 {
704 if (isDoCommits())
705 {
706 conn.commit();
707 }
708 }
709 }