/*

  * $Id: JDBCResources.java 354539 2005-12-06 20:40:16Z niallp $

  * $Revision: 354539 $

  * $Date: 2005-12-06 20:40:16 +0000 (Tue, 06 Dec 2005) $

  *

  * ====================================================================

  *

  *  Copyright 2003-2005 The Apache Software Foundation

  *

  *  Licensed under the Apache License, Version 2.0 (the "License");

  *  you may not use this file except in compliance with the License.

  *  You may obtain a copy of the License at

  *

  *      http://www.apache.org/licenses/LICENSE-2.0

  *

  *  Unless required by applicable law or agreed to in writing, software

  *  distributed under the License is distributed on an "AS IS" BASIS,

  *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  *  See the License for the specific language governing permissions and

  *  limitations under the License.

  *

  */

 package org.apache.commons.resources.impl;

 import java.io.FileNotFoundException;

 import java.io.IOException;

 import java.io.InputStream;

 import java.net.URL;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.util.Locale;

 import java.util.Map;

 import java.util.Properties;

 import org.apache.commons.logging.Log;

 import org.apache.commons.logging.LogFactory;

 /**

  * <p>Concrete implementation of 

  * {@link org.apache.commons.resources.Resources} that retrieves message

  * key value pairs using JDBC. For this specific implementation, database

  * connection properties and <code>Locale</code> for which the document's

  * messages apply are specified via property files. Resources are looked up in

  * a hierarchy of database values in a manner identical to that 

  * performed by <code>java.util.ResourceBundle.getBundle().</code>.

  * 

  * </p>

  *

  * <p>The base URL passed to our constructor must contain the base name

  * of a properties file that holds the JDBC datasource configuration.</p>

  * 

  * <p>

  * The expected format of the required properties file might look like this:<br>

  * <code><pre>

  * jdbc.connect.driver               = org.gjt.mm.mysql.Driver

  * jdbc.connect.url                  = jdbc:mysql://localhost/resources

  * jdbc.connect.login                = resourcesTest

  * jdbc.connect.password             = resourcesTest

  * 

  * jdbc.sql.db                       = resources

  * jdbc.sql.table                    = resources

  * jdbc.sql.locale.column            = locale

  * jdbc.sql.key.column               = msgKey

  * jdbc.sql.val.column               = val

  * 

  * org.apache.commons.resource.CACHE = true

  * 

  * </pre></code>

  * 

  * </p>

  *

  * @author James Mitchell

  * @version $Revision: 354539 $ $Date: 2005-12-06 20:40:16 +0000 (Tue, 06 Dec 2005) $

  */

 public class JDBCResources extends CollectionResourcesBase {

     /**

      * <p>The <code>Log</code> instance for this class.</p>

      */

     private transient Log log = LogFactory.getLog(JDBCResources.class);

     // ----------------------------------------------------------- Constructors

     /**

      * <p>Create a new 

      * {@link org.apache.commons.resources.Resources} instance with the specified

      * logical name and base resource URL.</p>

      *

      * @param name Logical name of the new instance

      * @param base Base URL of the JDBC configuration properties.

      */

     public JDBCResources(String name, String base) {

         super(name, base);

     }

     // ------------------------------------------------------ Protected Methods

     /**

      * <p>Return a <code>Map</code> containing the name-value mappings for

      * the specified base URL and requested <code>Locale</code>, if there

      * are any.  If there are no defined mappings for the specified

      * <code>Locale</code>, return an empty <code>Map</code> instead.</p>

      *

      * <p>Concrete subclasses must override this method to perform the

      * appropriate lookup.  A typical implementation will construct an

      * absolute URL based on the specified base URL and <code>Locale</code>,

      * retrieve the specified resource file (if any), and parse it into

      * a <code>Map</code> structure.</p>

      *

      * <p>Caching of previously retrieved <code>Map</code>s (if any) should

      * be performed by callers of this method.  Therefore, this method should

      * always attempt to retrieve the specified resource and load it

      * appropriately.</p>

      *

      * @param baseUrl Base URL of the resource files for this 

      * {@link org.apache.commons.resources.Resources} instance

      * @param locale <code>Locale</code> for which name-value mappings

      *  are requested

      * @return A name-value Map for the specified URL and locale.

      */

     protected Map getLocaleMap(String baseUrl, Locale locale) {

         if (getLog().isDebugEnabled()) {

             getLog().debug("Loading database configuration'" + locale + "' resources from base '" +

                     baseUrl + "'");

         }

         Properties props = new Properties();

         //getLocaleSuffix(locale) + 

         String name = baseUrl + ".properties";

         InputStream stream = null;

         try {

             // Open an input stream to the URL for this locale (if any)

             if (getLog().isTraceEnabled()) {

                 getLog().trace("Absolute URL is '" + name + "'");

             }

             URL url = new URL(name);

             stream = url.openStream();

             // Parse the input stream and populate the name-value mappings map

             if (getLog().isTraceEnabled()) {

                 getLog().trace("Parsing input resource");

             }

             props.load(stream);

         } catch (FileNotFoundException e) {

             // Log and swallow this exception

             if (getLog().isDebugEnabled()) {

                 getLog().debug("No resources for locale '" + locale +

                           "' from base '" + baseUrl + "'");

             }

             props.clear();

         } catch (IOException e) {

             getLog().warn("IOException loading locale '" + locale +

                      "' from base '" + baseUrl + "'", e);

             props.clear();

         } finally {

             // Close the input stream that was opened earlier

             if (stream != null) {

                 try {

                     stream.close();

                 } catch (IOException e) {

                     getLog().error("Error closing stream.", e);

                 }

                 stream = null;

             }

         }

         // Return the populated (or empty) properties

         Properties properties = new Properties();

         try {

             properties = loadData(locale, props);

         } catch (InstantiationException e) {

             getLog().warn("InstantiationException: locale= '" + locale +

                      "' base= '" + baseUrl + "'", e);

         } catch (IllegalAccessException e) {

             getLog().warn("IllegalAccessException: locale= '" + locale +

                     "' base= '" + baseUrl + "'", e);

         } catch (ClassNotFoundException e) {

             getLog().warn("Specified Driver not found, make sure it is on " +

                     "the classpath: locale= '" + locale +

                     "' base= '" + baseUrl + "'", e);

         } catch (SQLException e) {

             getLog().warn("SQLException: locale= '" + locale +

                     "' base= '" + baseUrl + "'", e);

         }

         return properties;

     }

     /**

      * @param locale <code>Locale</code> for which name-value mappings

      *  are requested

      * @param connectionProps The connection properties used to instantiate

      * a JDBC connection.

      */

     private Properties loadData(Locale locale, Properties connectionProps) 

         throws InstantiationException, IllegalAccessException, 

         ClassNotFoundException, SQLException {

         String driver = connectionProps.getProperty("jdbc.connect.driver");

         String url    = connectionProps.getProperty("jdbc.connect.url");

         String user = connectionProps.getProperty("jdbc.connect.login");

         String pass = connectionProps.getProperty("jdbc.connect.password");

         String table = connectionProps.getProperty("jdbc.sql.table");

         String localeColumn = connectionProps.getProperty("jdbc.sql.locale.column");

         String keyColumn    = connectionProps.getProperty("jdbc.sql.key.column");

         String valColumn    = connectionProps.getProperty("jdbc.sql.val.column");

         Properties pairs = new Properties();

         Connection con = null;

         PreparedStatement stmt = null;

         ResultSet rs = null;

         try {

             Class.forName(driver).newInstance();

             con = DriverManager.getConnection(url, user, pass);

             String query = "SELECT " + keyColumn + ", " + valColumn + 

                     " FROM " + table + " where " + localeColumn + "= '" + locale + "'";

             stmt = con.prepareStatement(query);

             rs = stmt.executeQuery();

             while (rs.next()) {

                 pairs.put(rs.getString(keyColumn), rs.getString(valColumn));

             }

         } finally {

             try {

                 if (rs != null) {

                     rs.close();

                 }

             } catch(Exception e) {

                 getLog().warn("Error closing ResultSet: " + e);

             }

             try {

                 if (stmt != null) {

                     stmt.close();

                 }

             } catch(Exception e) {

                 getLog().warn("Error closing Statement: " + e);

             }

             try {

                 if (con != null) {

                     con.close();

                 }

             } catch(Exception e) {

                 getLog().warn("Error closing Connection: " + e);

             }

         }

         return pairs;

     }

     /**

      * Accessor method for Log instance.

      *

      * The Log instance variable is transient and

      * accessing it through this method ensures it

      * is re-initialized when this instance is

      * de-serialized.

      *

      * @return The Log instance.

      */

     private Log getLog() {

         if (log == null) {

             log =  LogFactory.getLog(JDBCResources.class);

         }

         return log;

     }

 }