1 package org.apache.commons.i18n;
2
3 import javax.sql.DataSource;
4 import java.util.*;
5 import java.sql.*;
6 import java.text.MessageFormat;
7
8 /**
9 * The <code>JdbcMessageProvider</code> provides messages stored in a database (or other data source)
10 * accessible via JDBC. The <code>JdbcMessageProvider</code> only has support for different languages,
11 * but if support for country or variant is required one could easily subclass it and override the
12 * <code>getLocale</code> method. If <code>getLocale</code> is overridden, the languageColumn parameter
13 * (or <code>jdbc.sql.locale.column<code> Map entry) of the constructors may be null, since it will not be used.
14 * @author Mattias Jiderhamn
15 */
16 public class JdbcMessageProvider implements MessageProvider {
17 /**
18 * This Map has locale or language as key, and a Map with the different
19 * messages as value.
20 */
21 private final Map locales = new HashMap();
22
23 private String idColumn;
24
25 private String languageColumn;
26
27 /**
28 * Create new JDBC <code>MessageProvider</code> using the provided connection.
29 * @param conn The connection to use for initialization.
30 * @param table The name of the table holding the messages
31 * @param idColumn The name of the column holding the message ID
32 * @param languageColumn The name of the column containing the ISO-639 language code.
33 * @throws SQLException If there is an error getting data from the table
34 */
35 public JdbcMessageProvider(Connection conn, String table, String idColumn, String languageColumn)
36 throws SQLException {
37 this.idColumn = idColumn;
38 this.languageColumn = languageColumn;
39 init(conn, table);
40 }
41
42 /**
43 * Create new JDBC <code>MessageProvider</code> using a connection from the provided <code>DataSource</code>. Will
44 * get a connection from the <code>DataSource</code>, initialize and then return the connection.
45 * @param ds The connection to use for initialization.
46 * @param table The name of the table holding the messages
47 * @param idColumn The name of the column holding the message ID
48 * @param languageColumn The name of the column containing the ISO-639 language code.
49 * @throws SQLException If there is an error getting data from the table
50 */
51 public JdbcMessageProvider(DataSource ds, String table, String idColumn, String languageColumn)
52 throws SQLException {
53 this.idColumn = idColumn;
54 this.languageColumn = languageColumn;
55 Connection conn = null;
56 try {
57 conn = ds.getConnection();
58 init(conn, table);
59 }
60 finally {
61 if(conn != null)
62 conn.close();
63 }
64 }
65
66 /**
67 * Create JDBC <code>MessageProvider</code> from properties in a Map, such
68 * as a <code>java.util.Properties</code> object. The following are the properties in use, which
69 * are the same as for <code>JDBCResources</code> of Apache Commons Resources
70 * jdbc.connect.driver = org.gjt.mm.mysql.Driver
71 * jdbc.connect.url = jdbc:mysql://localhost/resources
72 * jdbc.connect.login = resourcesTest
73 * jdbc.connect.password = resourcesTest
74 *
75 * jdbc.sql.table = resources
76 * jdbc.sql.locale.column = locale
77 * jdbc.sql.key.column = msgKey
78 */
79 public JdbcMessageProvider(Map properties) throws ClassNotFoundException, SQLException {
80 String driver = (String)properties.get("jdbc.connect.driver");
81 String url = (String)properties.get("jdbc.connect.url");
82 String user = (String)properties.get("jdbc.connect.login");
83 String pass = (String)properties.get("jdbc.connect.password");
84
85 String table = (String)properties.get("jdbc.sql.table");
86 this.idColumn = (String)properties.get("jdbc.sql.key.column");
87 this.languageColumn = (String)properties.get("jdbc.sql.locale.column");
88
89 Class.forName(driver);
90 Connection conn = null;
91 try {
92 conn = DriverManager.getConnection(url, user, pass);
93 init(conn, table);
94 }
95 finally {
96 if(conn != null)
97 conn.close();
98 }
99 }
100
101 ///////////////////////////////////////////////////////////////////////
102 // Methods for initialization
103 ///////////////////////////////////////////////////////////////////////
104
105 private void init(Connection conn, String table) throws SQLException {
106 Statement stmt = null;
107 ResultSet rs = null;
108 try {
109 stmt = conn.createStatement();
110 rs = stmt.executeQuery("SELECT * FROM " + table);
111 String[] valueColumns = getValueColumns(rs);
112 while(rs.next()) {
113 String id = rs.getString(idColumn);
114 Locale locale = getLocale(rs);
115 Map entries = new HashMap();
116 for(int i = 0; i < valueColumns.length; i++) {
117 String entry = rs.getString(valueColumns[i]);
118 if(entry != null)
119 entries.put(valueColumns[i], entry);
120 }
121 Map localeMap = (Map)locales.get(locale);
122 if(localeMap == null) { // If first record for this Locale
123 localeMap = new HashMap();
124 locales.put(locale, localeMap);
125 }
126 localeMap.put(id, entries);
127 }
128 }
129 finally {
130 if(stmt != null)
131 stmt.close();
132 if(rs != null)
133 rs.close();
134 }
135 }
136
137 /**
138 * Get a String of all the column names, except the ID column and the
139 * language column.
140 * @param rs A <code>ResultSet</code> ready for reading meta data.
141 * @return A String array with the text value column names.
142 * @throws SQLException If an SQL error occurs.
143 */
144 protected String[] getValueColumns(ResultSet rs) throws SQLException {
145 List output = new LinkedList();
146 ResultSetMetaData metadata = rs.getMetaData();
147 int count = metadata.getColumnCount();
148 for(int i = 0; i < count; i++) {
149 String columnName = metadata.getColumnName(i+1); // (Count from 1)
150 if(! columnName.equals(idColumn) && ! columnName.equals(languageColumn) )
151 output.add(columnName);
152 }
153 return (String[])output.toArray(new String[0]);
154 }
155
156 /**
157 * Get <code>Locale</code> for the current record in the ResultSet. May be overridden
158 * by subclasses to allow for proprietary interpretation of language data.
159 * The default implementation assumes the column with the name provided as languageColumn
160 * for the constructor contains the ISO-639 code.
161 * @return The <code>Locale</code> of the current <code>ResultSet</code> record.
162 */
163 protected Locale getLocale(ResultSet rs) throws SQLException {
164 return new Locale(rs.getString(languageColumn).toLowerCase());
165 }
166
167 ///////////////////////////////////////////////////////////////////////
168 // Methods to implement MessageProvider
169 ///////////////////////////////////////////////////////////////////////
170
171 public String getText(String id, String entry, Locale locale) {
172 // TODO: Add Logging
173 Map entries = findEntries(id, locale);
174 if(entries != null) {
175 // TODO: Consider whether we need to recurse up if entries does not contain requested entry
176 return (String)entries.get(entry);
177 }
178 else
179 return null;
180 }
181
182 public Map getEntries(String id, Locale locale) {
183 Map entries = findEntries(id,locale);
184 if(entries == null) { // If not found by using specified or default locale
185 throw new MessageNotFoundException(MessageFormat.format(
186 I18nUtils.INTERNAL_MESSAGES.getString(I18nUtils.NO_MESSAGE_ENTRIES_FOUND),
187 new String[] { id }));
188 }
189 return entries;
190 }
191
192 private Map findEntries(String id, Locale locale) {
193 Map entries = findEntriesRecursively(id,locale);
194 if(entries == null) { // If not found by using specified locale, try to use default
195 return findEntriesRecursively(id,Locale.getDefault());
196 }
197 else
198 return entries;
199 }
200
201 /**
202 * Find entries by looking at the parent locale (language, country, variant ->
203 * language, country -> language) until entry is found. If entry not found for topmost
204 * Locale (language only), null is returned.
205 */
206 private Map findEntriesRecursively(String id, Locale locale) {
207 Map localeIds = (Map)locales.get(locale);
208 if(localeIds != null) {
209 Map entries = (Map)localeIds.get(id);
210 if(entries != null)
211 return entries;
212 }
213 Locale parentLocale = I18nUtils.getParentLocale(locale);
214 if(parentLocale == null)
215 return null;
216 else
217 return findEntriesRecursively(id, parentLocale); // Recursive call
218 }
219
220 }