001    /*
002     * Licensed to the Apache Software Foundation (ASF) under one or more
003     * contributor license agreements.  See the NOTICE file distributed with
004     * this work for additional information regarding copyright ownership.
005     * The ASF licenses this file to You under the Apache License, Version 2.0
006     * (the "License"); you may not use this file except in compliance with
007     * the License.  You may obtain a copy of the License at
008     *
009     *     http://www.apache.org/licenses/LICENSE-2.0
010     *
011     * Unless required by applicable law or agreed to in writing, software
012     * distributed under the License is distributed on an "AS IS" BASIS,
013     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014     * See the License for the specific language governing permissions and
015     * limitations under the License.
016     */
017    
018    package org.apache.commons.configuration;
019    
020    import java.sql.Connection;
021    import java.sql.PreparedStatement;
022    import java.sql.ResultSet;
023    import java.sql.SQLException;
024    import java.sql.Statement;
025    import java.util.ArrayList;
026    import java.util.Collection;
027    import java.util.Iterator;
028    import java.util.List;
029    
030    import javax.sql.DataSource;
031    
032    import org.apache.commons.collections.CollectionUtils;
033    import org.apache.commons.logging.LogFactory;
034    
035    /**
036     * Configuration stored in a database. The properties are retrieved from a
037     * table containing at least one column for the keys, and one column for the
038     * values. It's possible to store several configurations in the same table by
039     * adding a column containing the name of the configuration. The name of the
040     * table and the columns is specified in the constructor.
041     *
042     * <h4>Example 1 - One configuration per table</h4>
043     *
044     * <pre>
045     * CREATE TABLE myconfig (
046     *     `key`   VARCHAR NOT NULL PRIMARY KEY,
047     *     `value` VARCHAR
048     * );
049     *
050     * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
051     *
052     *
053     * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
054     * String value = config.getString("foo");
055     * </pre>
056     *
057     * <h4>Example 2 - Multiple configurations per table</h4>
058     *
059     * <pre>
060     * CREATE TABLE myconfigs (
061     *     `name`  VARCHAR NOT NULL,
062     *     `key`   VARCHAR NOT NULL,
063     *     `value` VARCHAR,
064     *     CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
065     * );
066     *
067     * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
068     * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
069     *
070     *
071     * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
072     * String value1 = conf.getString("key1");
073     *
074     * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
075     * String value2 = conf.getString("key2");
076     * </pre>
077     * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
078     * @since 1.0
079     *
080     * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
081     * @version $Revision: 613503 $, $Date: 2008-01-20 05:39:08 +0100 (So, 20 Jan 2008) $
082     */
083    public class DatabaseConfiguration extends AbstractConfiguration
084    {
085        /** The datasource to connect to the database. */
086        private DataSource datasource;
087    
088        /** The name of the table containing the configurations. */
089        private String table;
090    
091        /** The column containing the name of the configuration. */
092        private String nameColumn;
093    
094        /** The column containing the keys. */
095        private String keyColumn;
096    
097        /** The column containing the values. */
098        private String valueColumn;
099    
100        /** The name of the configuration. */
101        private String name;
102    
103        /**
104         * Build a configuration from a table containing multiple configurations.
105         *
106         * @param datasource    the datasource to connect to the database
107         * @param table         the name of the table containing the configurations
108         * @param nameColumn    the column containing the name of the configuration
109         * @param keyColumn     the column containing the keys of the configuration
110         * @param valueColumn   the column containing the values of the configuration
111         * @param name          the name of the configuration
112         */
113        public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
114                String keyColumn, String valueColumn, String name)
115        {
116            this.datasource = datasource;
117            this.table = table;
118            this.nameColumn = nameColumn;
119            this.keyColumn = keyColumn;
120            this.valueColumn = valueColumn;
121            this.name = name;
122            setLogger(LogFactory.getLog(getClass()));
123            addErrorLogListener();  // log errors per default
124        }
125    
126        /**
127         * Build a configuration from a table.-
128         *
129         * @param datasource    the datasource to connect to the database
130         * @param table         the name of the table containing the configurations
131         * @param keyColumn     the column containing the keys of the configuration
132         * @param valueColumn   the column containing the values of the configuration
133         */
134        public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
135        {
136            this(datasource, table, null, keyColumn, valueColumn, null);
137        }
138    
139        /**
140         * Returns the value of the specified property. If this causes a database
141         * error, an error event will be generated of type
142         * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
143         * event's <code>propertyName</code> is set to the passed in property key,
144         * the <code>propertyValue</code> is undefined.
145         *
146         * @param key the key of the desired property
147         * @return the value of this property
148         */
149        public Object getProperty(String key)
150        {
151            Object result = null;
152    
153            // build the query
154            StringBuffer query = new StringBuffer("SELECT * FROM ");
155            query.append(table).append(" WHERE ");
156            query.append(keyColumn).append("=?");
157            if (nameColumn != null)
158            {
159                query.append(" AND " + nameColumn + "=?");
160            }
161    
162            Connection conn = null;
163            PreparedStatement pstmt = null;
164    
165            try
166            {
167                conn = getConnection();
168    
169                // bind the parameters
170                pstmt = conn.prepareStatement(query.toString());
171                pstmt.setString(1, key);
172                if (nameColumn != null)
173                {
174                    pstmt.setString(2, name);
175                }
176    
177                ResultSet rs = pstmt.executeQuery();
178    
179                List results = new ArrayList();
180                while (rs.next())
181                {
182                    Object value = rs.getObject(valueColumn);
183                    if (isDelimiterParsingDisabled())
184                    {
185                        results.add(value);
186                    }
187                    else
188                    {
189                        // Split value if it containts the list delimiter
190                        CollectionUtils.addAll(results, PropertyConverter.toIterator(value, getListDelimiter()));
191                    }
192                }
193    
194                if (!results.isEmpty())
195                {
196                    result = (results.size() > 1) ? results : results.get(0);
197                }
198            }
199            catch (SQLException e)
200            {
201                fireError(EVENT_READ_PROPERTY, key, null, e);
202            }
203            finally
204            {
205                close(conn, pstmt);
206            }
207    
208            return result;
209        }
210    
211        /**
212         * Adds a property to this configuration. If this causes a database error,
213         * an error event will be generated of type <code>EVENT_ADD_PROPERTY</code>
214         * with the causing exception. The event's <code>propertyName</code> is
215         * set to the passed in property key, the <code>propertyValue</code>
216         * points to the passed in value.
217         *
218         * @param key the property key
219         * @param obj the value of the property to add
220         */
221        protected void addPropertyDirect(String key, Object obj)
222        {
223            // build the query
224            StringBuffer query = new StringBuffer("INSERT INTO " + table);
225            if (nameColumn != null)
226            {
227                query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
228            }
229            else
230            {
231                query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
232            }
233    
234            Connection conn = null;
235            PreparedStatement pstmt = null;
236    
237            try
238            {
239                conn = getConnection();
240    
241                // bind the parameters
242                pstmt = conn.prepareStatement(query.toString());
243                int index = 1;
244                if (nameColumn != null)
245                {
246                    pstmt.setString(index++, name);
247                }
248                pstmt.setString(index++, key);
249                pstmt.setString(index++, String.valueOf(obj));
250    
251                pstmt.executeUpdate();
252            }
253            catch (SQLException e)
254            {
255                fireError(EVENT_ADD_PROPERTY, key, obj, e);
256            }
257            finally
258            {
259                // clean up
260                close(conn, pstmt);
261            }
262        }
263    
264        /**
265         * Adds a property to this configuration. This implementation will
266         * temporarily disable list delimiter parsing, so that even if the value
267         * contains the list delimiter, only a single record will be written into
268         * the managed table. The implementation of <code>getProperty()</code>
269         * will take care about delimiters. So list delimiters are fully supported
270         * by <code>DatabaseConfiguration</code>, but internally treated a bit
271         * differently.
272         *
273         * @param key the key of the new property
274         * @param value the value to be added
275         */
276        public void addProperty(String key, Object value)
277        {
278            boolean parsingFlag = isDelimiterParsingDisabled();
279            try
280            {
281                if (value instanceof String)
282                {
283                    // temporarily disable delimiter parsing
284                    setDelimiterParsingDisabled(true);
285                }
286                super.addProperty(key, value);
287            }
288            finally
289            {
290                setDelimiterParsingDisabled(parsingFlag);
291            }
292        }
293    
294        /**
295         * Checks if this configuration is empty. If this causes a database error,
296         * an error event will be generated of type <code>EVENT_READ_PROPERTY</code>
297         * with the causing exception. Both the event's <code>propertyName</code>
298         * and <code>propertyValue</code> will be undefined.
299         *
300         * @return a flag whether this configuration is empty.
301         */
302        public boolean isEmpty()
303        {
304            boolean empty = true;
305    
306            // build the query
307            StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
308            if (nameColumn != null)
309            {
310                query.append(" WHERE " + nameColumn + "=?");
311            }
312    
313            Connection conn = null;
314            PreparedStatement pstmt = null;
315    
316            try
317            {
318                conn = getConnection();
319    
320                // bind the parameters
321                pstmt = conn.prepareStatement(query.toString());
322                if (nameColumn != null)
323                {
324                    pstmt.setString(1, name);
325                }
326    
327                ResultSet rs = pstmt.executeQuery();
328    
329                if (rs.next())
330                {
331                    empty = rs.getInt(1) == 0;
332                }
333            }
334            catch (SQLException e)
335            {
336                fireError(EVENT_READ_PROPERTY, null, null, e);
337            }
338            finally
339            {
340                // clean up
341                close(conn, pstmt);
342            }
343    
344            return empty;
345        }
346    
347        /**
348         * Checks whether this configuration contains the specified key. If this
349         * causes a database error, an error event will be generated of type
350         * <code>EVENT_READ_PROPERTY</code> with the causing exception. The
351         * event's <code>propertyName</code> will be set to the passed in key, the
352         * <code>propertyValue</code> will be undefined.
353         *
354         * @param key the key to be checked
355         * @return a flag whether this key is defined
356         */
357        public boolean containsKey(String key)
358        {
359            boolean found = false;
360    
361            // build the query
362            StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
363            if (nameColumn != null)
364            {
365                query.append(" AND " + nameColumn + "=?");
366            }
367    
368            Connection conn = null;
369            PreparedStatement pstmt = null;
370    
371            try
372            {
373                conn = getConnection();
374    
375                // bind the parameters
376                pstmt = conn.prepareStatement(query.toString());
377                pstmt.setString(1, key);
378                if (nameColumn != null)
379                {
380                    pstmt.setString(2, name);
381                }
382    
383                ResultSet rs = pstmt.executeQuery();
384    
385                found = rs.next();
386            }
387            catch (SQLException e)
388            {
389                fireError(EVENT_READ_PROPERTY, key, null, e);
390            }
391            finally
392            {
393                // clean up
394                close(conn, pstmt);
395            }
396    
397            return found;
398        }
399    
400        /**
401         * Removes the specified value from this configuration. If this causes a
402         * database error, an error event will be generated of type
403         * <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The
404         * event's <code>propertyName</code> will be set to the passed in key, the
405         * <code>propertyValue</code> will be undefined.
406         *
407         * @param key the key of the property to be removed
408         */
409        public void clearProperty(String key)
410        {
411            // build the query
412            StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
413            if (nameColumn != null)
414            {
415                query.append(" AND " + nameColumn + "=?");
416            }
417    
418            Connection conn = null;
419            PreparedStatement pstmt = null;
420    
421            try
422            {
423                conn = getConnection();
424    
425                // bind the parameters
426                pstmt = conn.prepareStatement(query.toString());
427                pstmt.setString(1, key);
428                if (nameColumn != null)
429                {
430                    pstmt.setString(2, name);
431                }
432    
433                pstmt.executeUpdate();
434            }
435            catch (SQLException e)
436            {
437                fireError(EVENT_CLEAR_PROPERTY, key, null, e);
438            }
439            finally
440            {
441                // clean up
442                close(conn, pstmt);
443            }
444        }
445    
446        /**
447         * Removes all entries from this configuration. If this causes a database
448         * error, an error event will be generated of type
449         * <code>EVENT_CLEAR</code> with the causing exception. Both the
450         * event's <code>propertyName</code> and the <code>propertyValue</code>
451         * will be undefined.
452         */
453        public void clear()
454        {
455            // build the query
456            StringBuffer query = new StringBuffer("DELETE FROM " + table);
457            if (nameColumn != null)
458            {
459                query.append(" WHERE " + nameColumn + "=?");
460            }
461    
462            Connection conn = null;
463            PreparedStatement pstmt = null;
464    
465            try
466            {
467                conn = getConnection();
468    
469                // bind the parameters
470                pstmt = conn.prepareStatement(query.toString());
471                if (nameColumn != null)
472                {
473                    pstmt.setString(1, name);
474                }
475    
476                pstmt.executeUpdate();
477            }
478            catch (SQLException e)
479            {
480                fireError(EVENT_CLEAR, null, null, e);
481            }
482            finally
483            {
484                // clean up
485                close(conn, pstmt);
486            }
487        }
488    
489        /**
490         * Returns an iterator with the names of all properties contained in this
491         * configuration. If this causes a database
492         * error, an error event will be generated of type
493         * <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the
494         * event's <code>propertyName</code> and the <code>propertyValue</code>
495         * will be undefined.
496         * @return an iterator with the contained keys (an empty iterator in case
497         * of an error)
498         */
499        public Iterator getKeys()
500        {
501            Collection keys = new ArrayList();
502    
503            // build the query
504            StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
505            if (nameColumn != null)
506            {
507                query.append(" WHERE " + nameColumn + "=?");
508            }
509    
510            Connection conn = null;
511            PreparedStatement pstmt = null;
512    
513            try
514            {
515                conn = getConnection();
516    
517                // bind the parameters
518                pstmt = conn.prepareStatement(query.toString());
519                if (nameColumn != null)
520                {
521                    pstmt.setString(1, name);
522                }
523    
524                ResultSet rs = pstmt.executeQuery();
525    
526                while (rs.next())
527                {
528                    keys.add(rs.getString(1));
529                }
530            }
531            catch (SQLException e)
532            {
533                fireError(EVENT_READ_PROPERTY, null, null, e);
534            }
535            finally
536            {
537                // clean up
538                close(conn, pstmt);
539            }
540    
541            return keys.iterator();
542        }
543    
544        /**
545         * Returns the used <code>DataSource</code> object.
546         *
547         * @return the data source
548         * @since 1.4
549         */
550        public DataSource getDatasource()
551        {
552            return datasource;
553        }
554    
555        /**
556         * Returns a <code>Connection</code> object. This method is called when
557         * ever the database is to be accessed. This implementation returns a
558         * connection from the current <code>DataSource</code>.
559         *
560         * @return the <code>Connection</code> object to be used
561         * @throws SQLException if an error occurs
562         * @since 1.4
563         * @deprecated Use a custom data source to change the connection used by the
564         * class. To be removed in Commons Configuration 2.0
565         */
566        protected Connection getConnection() throws SQLException
567        {
568            return getDatasource().getConnection();
569        }
570    
571        /**
572         * Close a <code>Connection</code> and, <code>Statement</code>.
573         * Avoid closing if null and hide any SQLExceptions that occur.
574         *
575         * @param conn The database connection to close
576         * @param stmt The statement to close
577         */
578        private void close(Connection conn, Statement stmt)
579        {
580            try
581            {
582                if (stmt != null)
583                {
584                    stmt.close();
585                }
586            }
587            catch (SQLException e)
588            {
589                getLogger().error("An error occured on closing the statement", e);
590            }
591    
592            try
593            {
594                if (conn != null)
595                {
596                    conn.close();
597                }
598            }
599            catch (SQLException e)
600            {
601                getLogger().error("An error occured on closing the connection", e);
602            }
603        }
604    }