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
018package org.apache.commons.configuration;
019
020import java.sql.Connection;
021import java.sql.PreparedStatement;
022import java.sql.ResultSet;
023import java.sql.SQLException;
024import java.sql.Statement;
025import java.util.ArrayList;
026import java.util.Collection;
027import java.util.Iterator;
028import java.util.List;
029
030import javax.sql.DataSource;
031
032import org.apache.commons.logging.LogFactory;
033
034/**
035 * Configuration stored in a database. The properties are retrieved from a
036 * table containing at least one column for the keys, and one column for the
037 * values. It's possible to store several configurations in the same table by
038 * adding a column containing the name of the configuration. The name of the
039 * table and the columns is specified in the constructor.
040 *
041 * <h4>Example 1 - One configuration per table</h4>
042 *
043 * <pre>
044 * CREATE TABLE myconfig (
045 *     `key`   VARCHAR NOT NULL PRIMARY KEY,
046 *     `value` VARCHAR
047 * );
048 *
049 * INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
050 *
051 *
052 * Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
053 * String value = config.getString("foo");
054 * </pre>
055 *
056 * <h4>Example 2 - Multiple configurations per table</h4>
057 *
058 * <pre>
059 * CREATE TABLE myconfigs (
060 *     `name`  VARCHAR NOT NULL,
061 *     `key`   VARCHAR NOT NULL,
062 *     `value` VARCHAR,
063 *     CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
064 * );
065 *
066 * INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
067 * INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
068 *
069 *
070 * Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
071 * String value1 = conf.getString("key1");
072 *
073 * Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
074 * String value2 = conf.getString("key2");
075 * </pre>
076 * The configuration can be instructed to perform commits after database updates.
077 * This is achieved by setting the {@code commits} parameter of the
078 * constructors to <b>true</b>. If commits should not be performed (which is the
079 * default behavior), it should be ensured that the connections returned by the
080 * {@code DataSource} are in auto-commit mode.
081 *
082 * <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
083 * @since 1.0
084 *
085 * @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
086 * @version $Id: DatabaseConfiguration.java 1344442 2012-05-30 20:17:35Z oheger $
087 */
088public class DatabaseConfiguration extends AbstractConfiguration
089{
090    /** The datasource to connect to the database. */
091    private final DataSource datasource;
092
093    /** The name of the table containing the configurations. */
094    private final String table;
095
096    /** The column containing the name of the configuration. */
097    private final String nameColumn;
098
099    /** The column containing the keys. */
100    private final String keyColumn;
101
102    /** The column containing the values. */
103    private final String valueColumn;
104
105    /** The name of the configuration. */
106    private final String name;
107
108    /** A flag whether commits should be performed by this configuration. */
109    private final boolean doCommits;
110
111    /**
112     * Build a configuration from a table containing multiple configurations.
113     * No commits are performed by the new configuration instance.
114     *
115     * @param datasource    the datasource to connect to the database
116     * @param table         the name of the table containing the configurations
117     * @param nameColumn    the column containing the name of the configuration
118     * @param keyColumn     the column containing the keys of the configuration
119     * @param valueColumn   the column containing the values of the configuration
120     * @param name          the name of the configuration
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     * Creates a new instance of {@code DatabaseConfiguration} that operates on
130     * a database table containing multiple configurations.
131     *
132     * @param datasource the {@code DataSource} to connect to the database
133     * @param table the name of the table containing the configurations
134     * @param nameColumn the column containing the name of the configuration
135     * @param keyColumn the column containing the keys of the configuration
136     * @param valueColumn the column containing the values of the configuration
137     * @param name the name of the configuration
138     * @param commits a flag whether the configuration should perform a commit
139     *        after a database update
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();  // log errors per default
154    }
155
156    /**
157     * Build a configuration from a table.
158     *
159     * @param datasource    the datasource to connect to the database
160     * @param table         the name of the table containing the configurations
161     * @param keyColumn     the column containing the keys of the configuration
162     * @param valueColumn   the column containing the values of the configuration
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     * Creates a new instance of {@code DatabaseConfiguration} that
171     * operates on a database table containing a single configuration only.
172     *
173     * @param datasource the {@code DataSource} to connect to the database
174     * @param table the name of the table containing the configurations
175     * @param keyColumn the column containing the keys of the configuration
176     * @param valueColumn the column containing the values of the configuration
177     * @param commits a flag whether the configuration should perform a commit
178     *        after a database update
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     * Returns a flag whether this configuration performs commits after database
188     * updates.
189     *
190     * @return a flag whether commits are performed
191     */
192    public boolean isDoCommits()
193    {
194        return doCommits;
195    }
196
197    /**
198     * Returns the value of the specified property. If this causes a database
199     * error, an error event will be generated of type
200     * {@code EVENT_READ_PROPERTY} with the causing exception. The
201     * event's {@code propertyName} is set to the passed in property key,
202     * the {@code propertyValue} is undefined.
203     *
204     * @param key the key of the desired property
205     * @return the value of this property
206     */
207    public Object getProperty(String key)
208    {
209        Object result = null;
210
211        // build the query
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            // bind the parameters
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                    // Split value if it contains the list delimiter
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     * Adds a property to this configuration. If this causes a database error,
276     * an error event will be generated of type {@code EVENT_ADD_PROPERTY}
277     * with the causing exception. The event's {@code propertyName} is
278     * set to the passed in property key, the {@code propertyValue}
279     * points to the passed in value.
280     *
281     * @param key the property key
282     * @param obj the value of the property to add
283     */
284    @Override
285    protected void addPropertyDirect(String key, Object obj)
286    {
287        // build the query
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            // bind the parameters
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            // clean up
325            close(conn, pstmt, null);
326        }
327    }
328
329    /**
330     * Adds a property to this configuration. This implementation will
331     * temporarily disable list delimiter parsing, so that even if the value
332     * contains the list delimiter, only a single record will be written into
333     * the managed table. The implementation of {@code getProperty()}
334     * will take care about delimiters. So list delimiters are fully supported
335     * by {@code DatabaseConfiguration}, but internally treated a bit
336     * differently.
337     *
338     * @param key the key of the new property
339     * @param value the value to be added
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                // temporarily disable delimiter parsing
350                setDelimiterParsingDisabled(true);
351            }
352            super.addProperty(key, value);
353        }
354        finally
355        {
356            setDelimiterParsingDisabled(parsingFlag);
357        }
358    }
359
360    /**
361     * Checks if this configuration is empty. If this causes a database error,
362     * an error event will be generated of type {@code EVENT_READ_PROPERTY}
363     * with the causing exception. Both the event's {@code propertyName}
364     * and {@code propertyValue} will be undefined.
365     *
366     * @return a flag whether this configuration is empty.
367     */
368    public boolean isEmpty()
369    {
370        boolean empty = true;
371
372        // build the query
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            // bind the parameters
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            // clean up
408            close(conn, pstmt, rs);
409        }
410
411        return empty;
412    }
413
414    /**
415     * Checks whether this configuration contains the specified key. If this
416     * causes a database error, an error event will be generated of type
417     * {@code EVENT_READ_PROPERTY} with the causing exception. The
418     * event's {@code propertyName} will be set to the passed in key, the
419     * {@code propertyValue} will be undefined.
420     *
421     * @param key the key to be checked
422     * @return a flag whether this key is defined
423     */
424    public boolean containsKey(String key)
425    {
426        boolean found = false;
427
428        // build the query
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            // bind the parameters
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            // clean up
462            close(conn, pstmt, rs);
463        }
464
465        return found;
466    }
467
468    /**
469     * Removes the specified value from this configuration. If this causes a
470     * database error, an error event will be generated of type
471     * {@code EVENT_CLEAR_PROPERTY} with the causing exception. The
472     * event's {@code propertyName} will be set to the passed in key, the
473     * {@code propertyValue} will be undefined.
474     *
475     * @param key the key of the property to be removed
476     */
477    @Override
478    protected void clearPropertyDirect(String key)
479    {
480        // build the query
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            // bind the parameters
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            // clean up
512            close(conn, pstmt, null);
513        }
514    }
515
516    /**
517     * Removes all entries from this configuration. If this causes a database
518     * error, an error event will be generated of type
519     * {@code EVENT_CLEAR} with the causing exception. Both the
520     * event's {@code propertyName} and the {@code propertyValue}
521     * will be undefined.
522     */
523    @Override
524    public void clear()
525    {
526        fireEvent(EVENT_CLEAR, null, null, true);
527        // build the query
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            // bind the parameters
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            // clean up
558            close(conn, pstmt, null);
559        }
560        fireEvent(EVENT_CLEAR, null, null, false);
561    }
562
563    /**
564     * Returns an iterator with the names of all properties contained in this
565     * configuration. If this causes a database
566     * error, an error event will be generated of type
567     * {@code EVENT_READ_PROPERTY} with the causing exception. Both the
568     * event's {@code propertyName} and the {@code propertyValue}
569     * will be undefined.
570     * @return an iterator with the contained keys (an empty iterator in case
571     * of an error)
572     */
573    public Iterator<String> getKeys()
574    {
575        Collection<String> keys = new ArrayList<String>();
576
577        // build the query
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            // bind the parameters
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            // clean up
613            close(conn, pstmt, rs);
614        }
615
616        return keys.iterator();
617    }
618
619    /**
620     * Returns the used {@code DataSource} object.
621     *
622     * @return the data source
623     * @since 1.4
624     */
625    public DataSource getDatasource()
626    {
627        return datasource;
628    }
629
630    /**
631     * Returns a {@code Connection} object. This method is called when
632     * ever the database is to be accessed. This implementation returns a
633     * connection from the current {@code DataSource}.
634     *
635     * @return the {@code Connection} object to be used
636     * @throws SQLException if an error occurs
637     * @since 1.4
638     * @deprecated Use a custom data source to change the connection used by the
639     * class. To be removed in Commons Configuration 2.0
640     */
641    @Deprecated
642    protected Connection getConnection() throws SQLException
643    {
644        return getDatasource().getConnection();
645    }
646
647    /**
648     * Close the specified database objects.
649     * Avoid closing if null and hide any SQLExceptions that occur.
650     *
651     * @param conn The database connection to close
652     * @param stmt The statement to close
653     * @param rs the result set to close
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     * Performs a commit if needed. This method is called after updates of the
696     * managed database table. If the configuration should perform commits, it
697     * does so now.
698     *
699     * @param conn the active connection
700     * @throws SQLException if an error occurs
701     */
702    private void commitIfRequired(Connection conn) throws SQLException
703    {
704        if (isDoCommits())
705        {
706            conn.commit();
707        }
708    }
709}