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.resources.impl;
019
020 import java.io.FileNotFoundException;
021 import java.io.IOException;
022 import java.io.InputStream;
023 import java.net.URL;
024 import java.sql.Connection;
025 import java.sql.DriverManager;
026 import java.sql.PreparedStatement;
027 import java.sql.ResultSet;
028 import java.sql.SQLException;
029 import java.util.Locale;
030 import java.util.Map;
031 import java.util.Properties;
032
033 import org.apache.commons.logging.Log;
034 import org.apache.commons.logging.LogFactory;
035
036 /**
037 * <p>Concrete implementation of
038 * {@link org.apache.commons.resources.Resources} that retrieves message
039 * key value pairs using JDBC. For this specific implementation, database
040 * connection properties and <code>Locale</code> for which the document's
041 * messages apply are specified via property files. Resources are looked up in
042 * a hierarchy of database values in a manner identical to that
043 * performed by <code>java.util.ResourceBundle.getBundle().</code>.
044 *
045 * </p>
046 *
047 * <p>The base URL passed to our constructor must contain the base name
048 * of a properties file that holds the JDBC datasource configuration.</p>
049 *
050 * <p>
051 * The expected format of the required properties file might look like this:<br>
052 * <code><pre>
053 * jdbc.connect.driver = org.gjt.mm.mysql.Driver
054 * jdbc.connect.url = jdbc:mysql://localhost/resources
055 * jdbc.connect.login = resourcesTest
056 * jdbc.connect.password = resourcesTest
057 *
058 * jdbc.sql.db = resources
059 * jdbc.sql.table = resources
060 * jdbc.sql.locale.column = locale
061 * jdbc.sql.key.column = msgKey
062 * jdbc.sql.val.column = val
063 *
064 * org.apache.commons.resource.CACHE = true
065 *
066 * </pre></code>
067 *
068 * </p>
069 *
070 * @author James Mitchell
071 * @version $Revision: 775615 $ $Date: 2009-05-17 10:45:58 +0100 (Sun, 17 May 2009) $
072 */
073 public class JDBCResources extends CollectionResourcesBase {
074
075 /**
076 * <p>The <code>Log</code> instance for this class.</p>
077 */
078 private transient Log log = LogFactory.getLog(JDBCResources.class);
079
080 // ----------------------------------------------------------- Constructors
081
082 /**
083 * <p>Create a new
084 * {@link org.apache.commons.resources.Resources} instance with the specified
085 * logical name and base resource URL.</p>
086 *
087 * @param name Logical name of the new instance
088 * @param base Base URL of the JDBC configuration properties.
089 */
090 public JDBCResources(String name, String base) {
091 super(name, base);
092 }
093
094
095 // ------------------------------------------------------ Protected Methods
096
097
098 /**
099 * <p>Return a <code>Map</code> containing the name-value mappings for
100 * the specified base URL and requested <code>Locale</code>, if there
101 * are any. If there are no defined mappings for the specified
102 * <code>Locale</code>, return an empty <code>Map</code> instead.</p>
103 *
104 * <p>Concrete subclasses must override this method to perform the
105 * appropriate lookup. A typical implementation will construct an
106 * absolute URL based on the specified base URL and <code>Locale</code>,
107 * retrieve the specified resource file (if any), and parse it into
108 * a <code>Map</code> structure.</p>
109 *
110 * <p>Caching of previously retrieved <code>Map</code>s (if any) should
111 * be performed by callers of this method. Therefore, this method should
112 * always attempt to retrieve the specified resource and load it
113 * appropriately.</p>
114 *
115 * @param baseUrl Base URL of the resource files for this
116 * {@link org.apache.commons.resources.Resources} instance
117 * @param locale <code>Locale</code> for which name-value mappings
118 * are requested
119 * @return A name-value Map for the specified URL and locale.
120 */
121 protected Map getLocaleMap(String baseUrl, Locale locale) {
122
123 if (getLog().isDebugEnabled()) {
124 getLog().debug("Loading database configuration'" + locale + "' resources from base '" +
125 baseUrl + "'");
126 }
127
128 Properties props = new Properties();
129 //getLocaleSuffix(locale) +
130 String name = baseUrl + ".properties";
131 InputStream stream = null;
132
133 try {
134
135 // Open an input stream to the URL for this locale (if any)
136 if (getLog().isTraceEnabled()) {
137 getLog().trace("Absolute URL is '" + name + "'");
138 }
139 URL url = new URL(name);
140 stream = url.openStream();
141
142 // Parse the input stream and populate the name-value mappings map
143 if (getLog().isTraceEnabled()) {
144 getLog().trace("Parsing input resource");
145 }
146 props.load(stream);
147
148 } catch (FileNotFoundException e) {
149
150 // Log and swallow this exception
151 if (getLog().isDebugEnabled()) {
152 getLog().debug("No resources for locale '" + locale +
153 "' from base '" + baseUrl + "'");
154 }
155 props.clear();
156
157 } catch (IOException e) {
158
159 getLog().warn("IOException loading locale '" + locale +
160 "' from base '" + baseUrl + "'", e);
161 props.clear();
162
163 } finally {
164
165 // Close the input stream that was opened earlier
166 if (stream != null) {
167 try {
168 stream.close();
169 } catch (IOException e) {
170 getLog().error("Error closing stream.", e);
171 }
172 stream = null;
173 }
174
175 }
176
177 // Return the populated (or empty) properties
178 Properties properties = new Properties();
179 try {
180 properties = loadData(locale, props);
181
182 } catch (InstantiationException e) {
183 getLog().warn("InstantiationException: locale= '" + locale +
184 "' base= '" + baseUrl + "'", e);
185 } catch (IllegalAccessException e) {
186 getLog().warn("IllegalAccessException: locale= '" + locale +
187 "' base= '" + baseUrl + "'", e);
188 } catch (ClassNotFoundException e) {
189 getLog().warn("Specified Driver not found, make sure it is on " +
190 "the classpath: locale= '" + locale +
191 "' base= '" + baseUrl + "'", e);
192 } catch (SQLException e) {
193 getLog().warn("SQLException: locale= '" + locale +
194 "' base= '" + baseUrl + "'", e);
195 }
196 return properties;
197
198
199
200 }
201
202 /**
203 * @param locale <code>Locale</code> for which name-value mappings
204 * are requested
205 * @param connectionProps The connection properties used to instantiate
206 * a JDBC connection.
207 */
208 private Properties loadData(Locale locale, Properties connectionProps)
209 throws InstantiationException, IllegalAccessException,
210 ClassNotFoundException, SQLException {
211
212 String driver = connectionProps.getProperty("jdbc.connect.driver");
213 String url = connectionProps.getProperty("jdbc.connect.url");
214 String user = connectionProps.getProperty("jdbc.connect.login");
215 String pass = connectionProps.getProperty("jdbc.connect.password");
216
217 String table = connectionProps.getProperty("jdbc.sql.table");
218 String localeColumn = connectionProps.getProperty("jdbc.sql.locale.column");
219 String keyColumn = connectionProps.getProperty("jdbc.sql.key.column");
220 String valColumn = connectionProps.getProperty("jdbc.sql.val.column");
221 Properties pairs = new Properties();
222
223 Connection con = null;
224 PreparedStatement stmt = null;
225 ResultSet rs = null;
226 try {
227 Class.forName(driver).newInstance();
228 con = DriverManager.getConnection(url, user, pass);
229
230 String query = "SELECT " + keyColumn + ", " + valColumn +
231 " FROM " + table + " where " + localeColumn + "= '" + locale + "'";
232 stmt = con.prepareStatement(query);
233 rs = stmt.executeQuery();
234
235 while (rs.next()) {
236 pairs.put(rs.getString(keyColumn), rs.getString(valColumn));
237 }
238 } finally {
239 try {
240 if (rs != null) {
241 rs.close();
242 }
243 } catch(Exception e) {
244 getLog().warn("Error closing ResultSet: " + e);
245 }
246 try {
247 if (stmt != null) {
248 stmt.close();
249 }
250 } catch(Exception e) {
251 getLog().warn("Error closing Statement: " + e);
252 }
253 try {
254 if (con != null) {
255 con.close();
256 }
257 } catch(Exception e) {
258 getLog().warn("Error closing Connection: " + e);
259 }
260 }
261
262 return pairs;
263 }
264
265 /**
266 * Accessor method for Log instance.
267 *
268 * The Log instance variable is transient and
269 * accessing it through this method ensures it
270 * is re-initialized when this instance is
271 * de-serialized.
272 *
273 * @return The Log instance.
274 */
275 private Log getLog() {
276 if (log == null) {
277 log = LogFactory.getLog(JDBCResources.class);
278 }
279 return log;
280 }
281 }