JDBC Adapter

This Adapter implementation uses JDBC to access a datebase containing a table with the translations.

Add to your project

The easiest way to include NILS - JDBC Adapter is as dependency, if you use a build tool like Maven or Gradle.

You can find the current versions in the Maven central repository.

Maven:

<dependency>
  <groupId>com.codepulsar.nils</groupId>
  <artifactId>nils-jdbc-adapter</artifactId>
  <version>3.0.0</version>
</dependency>

Gradle:

implementation group: 'com.codepulsar.nils', name: 'nils-jdbc-adapter', version: '3.0.0'

Using

After you have included the jar into your project you must create a NilsFactory with a JdbcAdapterConfig to get access to the NLS support.

public class SimpleJdbcExample extends BaseUI {

  private NilsFactory nlsFactory;

  @Override
  protected void initNilsHook() {
    try {
      var dbUtil = new DbTestUtil();
      var username = "sa";
      var password = "";
      var url = dbUtil.initDb("nls/jdbcExample.sql");
      var adapterConfig = JdbcAdapterConfig.init(url, username, password); (1)
      nlsFactory = NilsFactory.init(adapterConfig);
    } catch (SQLException ex) {
      throw new RuntimeException(ex);
    }
  }

  @Override
  protected void innerTranslate(
      JLabel lblCustomerName, JLabel lblStreet, JLabel lblPageOf, JButton btnClose) {
    NLS nls = nlsFactory.nls(); (2)

    lblCustomerName.setText(nls.get("customer.name")); (3)
    lblStreet.setText(nls.get(Address.class, "street")); (4)
    lblPageOf.setText(nls.get("page_counter", 3, 5)); (5)
    btnClose.setText(nls.get("btn.close"));
  }

  // ...
1 The example creates a NilsFactory using the JdbcAdapter.
2 Get a new NLS object for the default Locale of the running JVM.
3 Get the translation for the key "customer.name" with no further arguments.
4 Get the translation by a Class and its attribute "street".
5 Get the translation for the key "page_counter" with two arguments.

Per default the JDBC adapter looking for a table NILS_TRANSLATION with the following columns:

Column name Type Description

NLS_LOCALE

VARCHAR, TEXT

The Locale value for the translation. Use an empty string for the default locale.

NLS_KEY

VARCHAR, TEXT

The NLS key of a translation

NLS_VALUE

VARCHAR, TEXT

The transalation with optional argument placeholder ({0}, %s depending on the configuration)

In the example the entry could look like:

NILS_TRANSLATION
---------------------------------------------
ID | NLS_LOCALE | NLS_KEY       | NLS_VALUE
---------------------------------------------
1  |            | customer.name | Name
2  | de_DE      | customer.name | Kundenname

See also Advanced topics and Configuration for more information how to configure the NILS JDBC Adapter.

Include Translations

Including translations via the "include" mechanism is the same as for resource bundles.

See Include translations for more details.

Configuration

Despite of the existing global configuration (see Advanced topics) the JDBC Adapter configuration has the following additional configurations:

Disable fallback for translation resources

Per default if a translation key is not found in the current translation resource for a locale, the next translation resource will be used to resolve the requested key.

For example if your application has translation resources for "de_DE", "de" and one default, the fallback checks these resources in the given order.

So its possible to get a transalation in a different language as expected.

You can disable that fallback in the configuration:

      var adapterConfig =
          JdbcAdapterConfig.init(url, username, password) (1)
              .fallbackActive(false); (2)
      nlsFactory = NilsFactory.init(adapterConfig);

Specifiy JDBC driver class

Normaly the JDBC driver class is resolved automatically by the JDBC DriverManager. But sometimes the driver class must be loaded explicit. Therefore you can specific the JDBC driver class in the configuration.

In the example below the fullqualified class name for the H2 database driver is set.

      var adapterConfig =
          JdbcAdapterConfig.init(url, username, password) (1)
              .driverClass("org.h2.Driver"); (2)
      nlsFactory = NilsFactory.init(adapterConfig);
1 Create a JdbcAdapter instance.
2 Set the JDBC driver name.

Configure the translation database table

Depending on your database or your company requirements the default table is not working for you (see definition in section Using). As long a table exists that the given data contains you can configure the values for the translation. You can override these values in the JdbcAdapterConfig:

Configuration Description

schema

The name of the schema. Optional, depending on your database system.

tableName

The name of the table containing the translations. Default is NILS_TRANSLATION

localeField

The name of the column containing locale information. Data type VARCHAR, TEXT. Default value is NLS_LOCALE. Use an empty string for the default locale or set the value JdbcAdapterConfig.rootLocale().

keyField

The name of the column containing the translation key. Data type VARCHAR, TEXT. Default value is NLS_KEY.

valueField

The name of the column containing the translation. Data type VARCHAR, TEXT. Default value is NLS_VALUE.

 
      var adapterConfig =
          JdbcAdapterConfig.init(url, username, password) (1)
              .schema("DIFFERENT") (2)
              .tableName("TRANSLATION") (3)
              .localeField("TLOCALE") (4)
              .keyField("TKEY") (5)
              .valueField("TVALUE"); (6)

      nlsFactory = NilsFactory.init(adapterConfig);
1 Create a JdbcAdapter instance.
2 Set a schema name.
3 Set the table containing the translations.
4 Set the field name for the locale.
5 Set the field name for the translation key.
6 Set the filed name for the translation value.

Configure the root locale

As default the root locale for not-existing translation is an empty locale ('').

If you have a locale with should be used as root locale for non-existing translations you can set the config property rootLocale in the JdbcAdapterConfig.

      var adapterConfig =
          JdbcAdapterConfig.init(url, username, password) (1)
              .rootLocale(Locale.ENGLISH); (2)
      nlsFactory = NilsFactory.init(adapterConfig);
1 Create a JdbcAdapter instance.
2 Define the root locale.

Configure the caching of translations

Accessing the database is normaly expensive. NILS does not hold an open database connection to the translation table. Each call of a key results in a database access at the first call. After getting the result these result is stored in a cache. The cache is activated by default.

But you can configure the period of time, when the cache is invalidated and the translation are read again from the database.

You can use the config property cacheTimeout for that. The value is a time period in seconds. If you set the value to '0' no caching is done, and the database is called everytime. If you set the value to -1 the cache is enabled as long as the Java process is running.

      var adapterConfig =
          JdbcAdapterConfig.init(url, username, password) (1)
              .cacheTimeout(3600); (2)
      nlsFactory = NilsFactory.init(adapterConfig);
1 Create a JdbcAdapter instance.
2 Set the cache timeout value.

Example create table statements

Following some examples of create table statements for different databases. Use these as template for your system.

H2 
CREATE TABLE NILS_TRANSLATION (
  ID INT PRIMARY KEY,
  NLS_LOCALE VARCHAR(10) NOT NULL,
  NLS_KEY VARCHAR(255) NOT NULL,
  NLS_VALUE VARCHAR(255) NOT NULL
);
MariaDB 
CREATE TABLE NILS_TRANSLATION (
  ID INT AUTO_INCREMENT,
  NLS_LOCALE VARCHAR(10) NOT NULL,
  NLS_KEY VARCHAR(255) NOT NULL,
  NLS_VALUE VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
);
Oracle 
CREATE TABLE NILS_TRANSLATION (
  ID NUMBER(10) NOT NULL,
  NLS_LOCALE VARCHAR2(10) NOT NULL,
  NLS_KEY VARCHAR2(255) NOT NULL,
  NLS_VALUE VARCHAR2(255) NOT NULL,
  CONSTRAINT ID_PK PRIMARY KEY (id)
);
PostgreSQL 
CREATE TABLE NILS_TRANSLATION (
  ID SERIAL PRIMARY KEY,
  NLS_LOCALE VARCHAR(10) NOT NULL,
  NLS_KEY VARCHAR(255) NOT NULL,
  NLS_VALUE VARCHAR(255) NOT NULL
);