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