1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one or more
3 * contributor license agreements. See the NOTICE file distributed with
4 * this work for additional information regarding copyright ownership.
5 * The ASF licenses this file to You under the Apache License, Version 2.0
6 * (the "License"); you may not use this file except in compliance with
7 * the License. You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17
18 package org.apache.commons.resources.impl;
19
20 import java.io.FileNotFoundException;
21 import java.io.IOException;
22 import java.io.InputStream;
23 import java.net.URL;
24 import java.sql.Connection;
25 import java.sql.DriverManager;
26 import java.sql.PreparedStatement;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.util.Locale;
30 import java.util.Map;
31 import java.util.Properties;
32
33 import org.apache.commons.logging.Log;
34 import org.apache.commons.logging.LogFactory;
35
36 /**
37 * <p>Concrete implementation of
38 * {@link org.apache.commons.resources.Resources} that retrieves message
39 * key value pairs using JDBC. For this specific implementation, database
40 * connection properties and <code>Locale</code> for which the document's
41 * messages apply are specified via property files. Resources are looked up in
42 * a hierarchy of database values in a manner identical to that
43 * performed by <code>java.util.ResourceBundle.getBundle().</code>.
44 *
45 * </p>
46 *
47 * <p>The base URL passed to our constructor must contain the base name
48 * of a properties file that holds the JDBC datasource configuration.</p>
49 *
50 * <p>
51 * The expected format of the required properties file might look like this:<br>
52 * <code><pre>
53 * jdbc.connect.driver = org.gjt.mm.mysql.Driver
54 * jdbc.connect.url = jdbc:mysql://localhost/resources
55 * jdbc.connect.login = resourcesTest
56 * jdbc.connect.password = resourcesTest
57 *
58 * jdbc.sql.db = resources
59 * jdbc.sql.table = resources
60 * jdbc.sql.locale.column = locale
61 * jdbc.sql.key.column = msgKey
62 * jdbc.sql.val.column = val
63 *
64 * org.apache.commons.resource.CACHE = true
65 *
66 * </pre></code>
67 *
68 * </p>
69 *
70 * @author James Mitchell
71 * @version $Revision: 775615 $ $Date: 2009-05-17 10:45:58 +0100 (Sun, 17 May 2009) $
72 */
73 public class JDBCResources extends CollectionResourcesBase {
74
75 /**
76 * <p>The <code>Log</code> instance for this class.</p>
77 */
78 private transient Log log = LogFactory.getLog(JDBCResources.class);
79
80 // ----------------------------------------------------------- Constructors
81
82 /**
83 * <p>Create a new
84 * {@link org.apache.commons.resources.Resources} instance with the specified
85 * logical name and base resource URL.</p>
86 *
87 * @param name Logical name of the new instance
88 * @param base Base URL of the JDBC configuration properties.
89 */
90 public JDBCResources(String name, String base) {
91 super(name, base);
92 }
93
94
95 // ------------------------------------------------------ Protected Methods
96
97
98 /**
99 * <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 }