1 package org.synyx.messagesource.jdbc;
2
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.util.Iterator;
7 import java.util.List;
8 import java.util.Locale;
9 import java.util.Map;
10
11 import javax.sql.DataSource;
12
13 import org.springframework.dao.DataAccessException;
14 import org.springframework.jdbc.core.BatchPreparedStatementSetter;
15 import org.springframework.jdbc.core.JdbcTemplate;
16 import org.springframework.jdbc.core.ResultSetExtractor;
17 import org.springframework.util.Assert;
18 import org.synyx.messagesource.MessageAcceptor;
19 import org.synyx.messagesource.MessageProvider;
20 import org.synyx.messagesource.Messages;
21 import org.synyx.messagesource.util.LocaleUtils;
22
23
24 /***
25 * {@link MessageProvider} implementation that reads messages out of a database. The table to be used as well as the
26 * names of the columns is configurable.
27 *
28 * @author Marc Kannegießer - kannegiesser@synyx.de
29 */
30 public class JdbcMessageProvider implements MessageProvider, MessageAcceptor {
31
32 protected static final String QUERY_INSERT_MESSAGE =
33 "INSERT INTO %s (%s, %s, %s, %s, %s, %s) VALUES (?, ?, ?, ?, ?, ?)";
34 protected static final String QUERY_DELETE_MESSAGES = "DELETE FROM %s WHERE %s = ?";
35 protected static final String QUERY_SELECT_BASENAMES = "SELECT DISTINCT %s from %s";
36 protected static final String QUERY_SELECT_MESSAGES = "SELECT %s,%s,%s,%s,%s FROM %s WHERE %s = ?";
37
38 private JdbcTemplate template;
39
40 private String languageColumn = "language";
41 private String countryColumn = "country";
42 private String variantColumn = "variant";
43 private String basenameColumn = "basename";
44 private String keyColumn = "key";
45 private String messageColumn = "message";
46 private String tableName = "Message";
47
48 private String delimiter = "`";
49
50 private final MessageExtractor extractor = new MessageExtractor();
51
52
53
54
55
56
57
58 public Messages getMessages(String basename) {
59
60 String query =
61 String.format(getSelectMessagesQuery(), addDelimiter(languageColumn), addDelimiter(countryColumn),
62 addDelimiter(variantColumn), addDelimiter(keyColumn), addDelimiter(messageColumn),
63 addDelimiter(tableName), addDelimiter(basenameColumn));
64
65 return template.query(query, new Object[] { basename }, extractor);
66 }
67
68
69
70
71
72
73
74 public void setMessages(String basename, Messages messages) {
75
76 deleteMessages(basename);
77
78 String query =
79 String.format(getInsertMessageQuery(), addDelimiter(tableName), addDelimiter(basenameColumn),
80 addDelimiter(languageColumn), addDelimiter(countryColumn), addDelimiter(variantColumn),
81 addDelimiter(keyColumn), addDelimiter(messageColumn));
82
83 for (Locale locale : messages.getLocales()) {
84
85 insert(query, basename, LocaleUtils.getLanguage(locale), LocaleUtils.getCountry(locale),
86 LocaleUtils.getVariant(locale), messages.getMessages(locale));
87
88 }
89
90 }
91
92
93 private void insert(String query, final String basename, final String language, final String country,
94 final String variant, final Map<String, String> messages) {
95
96 final Iterator<Map.Entry<String, String>> messagesIterator = messages.entrySet().iterator();
97
98 template.batchUpdate(query, new BatchPreparedStatementSetter() {
99
100 public void setValues(PreparedStatement ps, int i) throws SQLException {
101
102 Map.Entry<String, String> entry = messagesIterator.next();
103 ps.setString(1, basename);
104 ps.setString(2, language);
105 ps.setString(3, country);
106 ps.setString(4, variant);
107 ps.setString(5, entry.getKey());
108 ps.setString(6, entry.getValue());
109
110 }
111
112
113 public int getBatchSize() {
114
115 return messages.size();
116 }
117 });
118
119 }
120
121
122
123
124
125
126
127 public List<String> getAvailableBaseNames() {
128
129 List<String> basenames =
130 template.queryForList(
131 String.format(getSelectBasenamesQuery(), addDelimiter(basenameColumn), addDelimiter(tableName)),
132 String.class);
133 return basenames;
134 }
135
136
137 private void deleteMessages(final String basename) {
138
139 String query = String.format(getDeleteMessagesQuery(), addDelimiter(tableName), addDelimiter(basenameColumn));
140
141 template.update(query, basename);
142
143 }
144
145
146 /***
147 * Returns the query used to select messages of a basename
148 *
149 * @return the query
150 */
151 protected String getSelectMessagesQuery() {
152
153 return QUERY_SELECT_MESSAGES;
154 }
155
156
157 /***
158 * Returns the query used for selecting available basenames.
159 *
160 * @return the query
161 */
162 protected String getSelectBasenamesQuery() {
163
164 return QUERY_SELECT_BASENAMES;
165 }
166
167
168 /***
169 * Returns the Query-Template used to insert a Message
170 *
171 * @return the query
172 */
173 protected String getInsertMessageQuery() {
174
175 return QUERY_INSERT_MESSAGE;
176 }
177
178
179 /***
180 * Returns the query to delete Messages
181 *
182 * @return the query
183 */
184 protected String getDeleteMessagesQuery() {
185
186 return QUERY_DELETE_MESSAGES;
187 }
188
189
190 /***
191 * Method that "wraps" a field-name (or table-name) into the delimiter.
192 *
193 * @param name the name of the field/table
194 * @return the wrapped field/table
195 */
196 protected String addDelimiter(String name) {
197
198 return String.format("%s%s%s", delimiter, name, delimiter);
199 }
200
201
202 /***
203 * Returns the delimiter used within queries to delimit table- and column-names
204 *
205 * @return the delimiter
206 */
207 public String getDelimiter() {
208
209 return delimiter;
210 }
211
212
213 /***
214 * Sets the delimiter used within queries to delimit table- and column-names (defaults to `). Must not be null.
215 *
216 * @param delimiter the delimiter to use
217 */
218 public void setDelimiter(String delimiter) {
219
220 Assert.notNull(delimiter);
221 this.delimiter = delimiter;
222 }
223
224
225 /***
226 * Returns the name of the column holding the information about the basename (string-type)
227 *
228 * @return the name of the basename-column
229 */
230 public String getBasenameColumn() {
231
232 return basenameColumn;
233 }
234
235
236 /***
237 * Sets the name of the column holding the information about the basename (string-type)
238 *
239 * @param basenameColumn the name of the basename-column
240 */
241 public void setBasenameColumn(String basenameColumn) {
242
243 this.basenameColumn = basenameColumn;
244 }
245
246
247 /***
248 * Returns the name of the table containing the messages
249 *
250 * @return the name of the table containing the messages
251 */
252 public String getTableName() {
253
254 return tableName;
255 }
256
257
258 /***
259 * Sets the name of the table containing the messages
260 *
261 * @param tableName the name of the table containing the messages
262 */
263 public void setTableName(String tableName) {
264
265 Assert.notNull(tableName);
266 this.tableName = tableName;
267 }
268
269
270 /***
271 * Sets the {@link DataSource} where connections can be created to the database containing the table with messages
272 *
273 * @param dataSource the {@link DataSource} to set
274 */
275 public void setDataSource(DataSource dataSource) {
276
277 Assert.notNull(dataSource);
278 this.template = new JdbcTemplate(dataSource);
279 }
280
281
282 /***
283 * Returns the name of the column holding the information about the language (string-type)
284 *
285 * @return the name of the column holding the information about the language (string-type)
286 */
287 public String getLanguageColumn() {
288
289 return languageColumn;
290 }
291
292
293 /***
294 * Sets the name of the column holding the information about the language (string-type)
295 *
296 * @param languageColumn the name of the language-column
297 */
298 public void setLanguageColumn(String languageColumn) {
299
300 Assert.notNull(languageColumn);
301
302 this.languageColumn = languageColumn;
303 }
304
305
306 /***
307 * Returns the name of the column holding the information about the country (string-type)
308 *
309 * @return the name of the column holding the information about the country (string-type)
310 */
311 public String getCountryColumn() {
312
313 return countryColumn;
314 }
315
316
317 /***
318 * Sets the name of the column holding the information about the country (string-type)
319 *
320 * @param countryColumn the name of the country-column
321 */
322 public void setCountryColumn(String countryColumn) {
323
324 Assert.notNull(countryColumn);
325
326 this.countryColumn = countryColumn;
327 }
328
329
330 /***
331 * Returns the name of the column holding the information about the variant (string-type)
332 *
333 * @return the name of the column holding the information about the variant (string-type)
334 */
335 public String getVariantColumn() {
336
337 return variantColumn;
338 }
339
340
341 /***
342 * Sets the name of the column holding the information about the variant (string-type)
343 *
344 * @param variantColumn the name of the variant-column
345 */
346 public void setVariantColumn(String variantColumn) {
347
348 Assert.notNull(variantColumn);
349 this.variantColumn = variantColumn;
350 }
351
352
353 /***
354 * Returns the name of the column holding the information about the key (string-type)
355 *
356 * @return the name of the column holding the information about the key (string-type)
357 */
358 public String getKeyColumn() {
359
360 return keyColumn;
361 }
362
363
364 /***
365 * Sets the name of the column holding the information about the key aka the name of the message-code (string-type)
366 *
367 * @param keyColumn the name of the key-column
368 */
369 public void setKeyColumn(String keyColumn) {
370
371 Assert.notNull(keyColumn);
372
373 this.keyColumn = keyColumn;
374 }
375
376
377 /***
378 * Returns the name of the column holding the information about the message (string-type)
379 *
380 * @return the name of the column holding the information about the message (string-type)
381 */
382 public String getMessageColumn() {
383
384 return messageColumn;
385 }
386
387
388 /***
389 * Sets the name of the column holding the information about the message-value aka the message itself (string-type)
390 *
391 * @param messageColumn the name of the message-column
392 */
393 public void setMessageColumn(String messageColumn) {
394
395 Assert.notNull(messageColumn);
396 this.messageColumn = messageColumn;
397 }
398
399 /***
400 * Helper that extracts messages from a resultset
401 **/
402 class MessageExtractor implements ResultSetExtractor<Messages> {
403
404
405
406
407
408
409 public Messages extractData(ResultSet rs) throws SQLException, DataAccessException {
410
411 Messages messages = new Messages();
412
413 while (rs.next()) {
414 String language = rs.getString(languageColumn);
415 String country = rs.getString(countryColumn);
416 String variant = rs.getString(variantColumn);
417 String key = rs.getString(keyColumn);
418 String message = rs.getString(messageColumn);
419
420 Locale locale = LocaleUtils.toLocale(language, country, variant);
421 messages.addMessage(locale, key, message);
422 }
423
424 return messages;
425 }
426
427 }
428
429 }