Typical Spring’s message source (see the MessageSource Javadoc) relies on the resource bundles. However it is quite easy to create a custom MessageSource implementation that determines messages from some other external data source.

This post demonstrates how to initialize a database-driven MessageSource and presents two approaches how messages can be saved in the database.

First we create an abstract DatabaseMessageSourceBase class containing functionalities common for every possible database-driven message source:

public abstract class DatabaseMessageSourceBase extends AbstractMessageSource {

	private static final Logger LOG = LoggerFactory
			.getLogger(DatabaseMessageSourceBase.class);

	private Messages messages;

	@Inject
	private JdbcTemplate jdbcTemplate;

	@Override
	protected MessageFormat resolveCode(String code, Locale locale) {
		String msg = messages.getMessage(code, locale);
		return createMessageFormat(msg, locale);

	}

	@PostConstruct
	public void init() {

		String i18nQuery = this.getI18NSqlQuery();

		LOG.info("Initializing message source using query [{}]", i18nQuery);

		this.messages = jdbcTemplate.query(i18nQuery,
				new ResultSetExtractor<Messages>() {
					@Override
					public Messages extractData(ResultSet rs)
							throws SQLException, DataAccessException {

						return extractI18NData(rs);
					}
				});
	}

	/**
	 * Returns sql query used to fetch the messages from the database.
	 * 
	 * @return sql query string
	 */
	protected abstract String getI18NSqlQuery();

	/**
	 * Extracts messages from the given result set.
	 * 
	 * @param rs
	 *            is a result set
	 * @return initialized {@link Messages} instance
	 * @throws SQLException
	 *             if a SQLException is encountered getting column values or
	 *             navigating (that is, there's no need to catch SQLException)
	 * @throws DataAccessException
	 *             in case of custom exceptions
	 */
	protected abstract Messages extractI18NData(ResultSet rs)
			throws SQLException, DataAccessException;

	/**
	 * 
	 * Messages bundle
	 */
	protected static final class Messages {

		/* <code, <locale, message>> */
		private Map<String, Map<Locale, String>> messages;

		public void addMessage(String code, Locale locale, String msg) {
			if (messages == null)
				messages = new HashMap<String, Map<Locale, String>>();

			Map<Locale, String> data = messages.get(code);
			if (data == null) {
				data = new HashMap<Locale, String>();
				messages.put(code, data);
			}

			data.put(locale, msg);
		}

		public String getMessage(String code, Locale locale) {
			Map<Locale, String> data = messages.get(code);
			return data != null ? data.get(locale) : null;
		}
	}

}

As you may notice, this class defines two abstract methods getI18NSqlQuery() and extractI18NData(ResultSet rs) that should be implemented by the concrete implementation.

“Vertical approach”

Probably the most common and intuitive way for storing messages in the database. It defines a database table with 3 columns CODE, LOCALE and MSG, that grows vertically for each new code, locale or message.

create table T_I18N_VERTICAL(
    CODE      varchar2(100) not null,
    LOCALE    varchar2(100) not null,	
    MSG       varchar2(255) not null
); 

alter table T_I18N_VERTICAL
  add constraint PK_T_I18N_VERTICAL primary key (CODE, LOCALE);
  
alter table T_I18N_VERTICAL
  add constraint U_T_I18N_VERTICAL unique (CODE, LOCALE);
  
insert into T_I18N_VERTICAL values ('app.startup.successful', 'EN', 'Application started.');
insert into T_I18N_VERTICAL values ('app.startup.successful', 'DE', 'Applikation gestartet.');
insert into T_I18N_VERTICAL values ('app.startup.successful', 'RU', 'Приложение запущено.');

A corresponding DatabaseMessageSourceBase implementation in this case will look like this:

@Named("messageSourceVertical")
public class VerticalDatabaseMessageSource extends DatabaseMessageSourceBase {

	private static final String I18N_QUERY = "select code, locale, msg from t_i18n_vertical";

	@Override
	protected String getI18NSqlQuery() {
		return I18N_QUERY;
	}

	@Override
	protected Messages extractI18NData(ResultSet rs) throws SQLException, DataAccessException {

		Messages messages = new Messages();
		while (rs.next()) {
			Locale locale = new Locale(rs.getString("locale"));
			messages.addMessage(rs.getString("code"), locale, rs.getString("msg"));
		}
		return messages;
	}
}

“Horizontal approach”

This approach defines a database table that grows vertically only in case of new message codes. Furthemore it grows horizontally by adding a new column if someday someone decides to add a completely new language to the application. The disadvantage of this method is that you have to modify a table (by adding a new column) every time you want to add a new language. But the big advantage of this approach is that every message in the table is identified by exactly one row (pretty nice for referencing message codes from other tables by the foreign key) and “not null” constraint on every language column ensures that the message is always “complete”.

create table T_I18N_HORIZONTAL(
    CODE varchar2(100) not null,
    EN   varchar2(255) not null,
    DE   varchar2(255) not null,
    RU   varchar2(255) not null
);

alter table T_I18N_HORIZONTAL
  add constraint PK_T_I18N_HORIZONTAL primary key (CODE);
  
alter table T_I18N_HORIZONTAL
  add constraint U_T_I18N_HORIZONTAL unique (CODE);

insert into T_I18N_HORIZONTAL values (
    'app.startup.successful', 'Application started.', 'Applikation gestartet.', 'Приложение запущено.'
);

The HorizontalDatabaseMessageSource implementation looks like this:

@Named("messageSourceHorizontal")
public class HorizontalDatabaseMessageSource extends DatabaseMessageSourceBase {

	private static final String I18N_QUERY = "select * from t_i18n_horizontal";

	@Override
	protected String getI18NSqlQuery() {
		return I18N_QUERY;
	}

	@Override
	protected Messages extractI18NData(ResultSet rs) throws SQLException,
			DataAccessException {
		Messages messages = new Messages();
		ResultSetMetaData metaData = rs.getMetaData();
		int noOfColumns = metaData.getColumnCount();
		while (rs.next()) {
			String key = rs.getString("code");
			for (int i = 1; i <= noOfColumns; i++) {
				String columnName = metaData.getColumnName(i);
				if (!"code".equalsIgnoreCase(columnName)) {
					Locale locale = new Locale(columnName);
					String msg = rs.getString(columnName);
					messages.addMessage(key, locale, msg);
				}
			}
		}
		return messages;
	}
}

Usage

First a brief example for the Spring’s application context definition:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:jee="http://www.springframework.org/schema/jee"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsdhttp://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
	">
	<context:annotation-config />
	<context:component-scan base-package="net.javaforge.blog.db.i18n" />
	<jee:jndi-lookup id="dataSource" jndi-name="${myapp.ds}" />
	<bean id="tx.manager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<tx:annotation-driven transaction-manager="tx.manager"
		proxy-target-class="false" />
</beans>

Note! Xml snippet above doesn’t define our MessageSource implementation(s) explicitly, rather it makes usage of Spring’s “annotation configuration” facilities (see <annotation-config> / <component-scan> tags and a @Named annotation applied to the MessageSource implementations)

And finally a simple JSP page demonstrating that both message sources were  correctly initialized and are ready to use:

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8" isELIgnored="false"%>
<%@page import="java.util.Locale"%>
<%@page import="org.springframework.context.MessageSource"%>
<%@page import="org.springframework.web.context.WebApplicationContext"%>
<%@ page
	import="org.springframework.web.context.support.WebApplicationContextUtils"%>
<%
	WebApplicationContext ctx = WebApplicationContextUtils
			.getRequiredWebApplicationContext(request.getSession()
					.getServletContext());
	MessageSource messageSourceVertical = (MessageSource) ctx
			.getBean("messageSourceVertical");
	MessageSource messageSourceHorizontal = (MessageSource) ctx
			.getBean("messageSourceHorizontal");	
%>
<!DOCTYPE unspecified PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>i18n example page</title>
<style type="text/css">
body {
	font-family: verdana, sans-serif;
	font-size: 10px;
}
</style>
</head>
<body>
	<h3>Messages from "horizontal" message source</h3>
	<table>
		<tr>
			<td><strong>ENGLISH</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					Locale.ENGLISH)%></td>
		<tr>
		<tr>
			<td><strong>GERMAN</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					Locale.GERMAN)%></td>
		<tr>
		<tr>
			<td><strong>RUSSIAN</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					new Locale("ru"))%></td>
		<tr>
	</table>
	<br/>
	<h3>Messages from "vertical" message source</h3>
	<table>
		<tr>
			<td><strong>ENGLISH</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					Locale.ENGLISH)%></td>
		<tr>
		<tr>
			<td><strong>GERMAN</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					Locale.GERMAN)%></td>
		<tr>
		<tr>
			<td><strong>RUSSIAN</strong></td>
			<td><%=messageSourceVertical.getMessage("app.startup.successful", null,
					new Locale("ru"))%></td>
		<tr>
	</table>	
</body>
</html>