View Javadoc

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 }