8.6.1. Procedure for Changing the RDBMS used

Projects created using the Nablarch archetype are configured to use the H2 Database Engine (hereinafter H2) by default.

The procedure to change the configuration to use a different RDBMS is described.

8.6.1.1. Prerequisites

The following is assumed.

  • The project immediately after generation from each archetype is the target.
  • The user and schema used for connecting have been created in the RDBMS. Users of the RDBMS must have been granted appropriate permissions.

8.6.1.2. File registration to the Maven repository

8.6.1.2.1. Register the JDBC driver

One of the following must be satisfied for the JDBC driver to be used.

  • The driver should be registered in the central repository of Maven.
  • The driver should be registered in the Maven repository of the project.
  • The driver should be registered in the local Maven repository.

This section describes how to register a JDBC driver that is not published in the Maven central repository to the local Maven repository. Replace the driver version in the procedure as appropriate.

Important

  • It is highly recommended that the JDBC driver be registered in the Maven repository of the project before building the CI environment.
  • How to obtain the JDBC driver is also explained below.Confirm the license before obtaining the JDBC driver.

8.6.1.2.1.1. H2

In the case of H2, registration is not required because the JDBC driver is published in the Maven central repository.

8.6.1.2.1.2. Oracle

In the case of Oracle, registration is not required because the JDBC driver is published in the Maven central repository.

8.6.1.2.1.3. PostgreSQL

In the case of PostgreSQL, registration is not required because the JDBC driver is published in the Maven central repository.

8.6.1.2.1.4. DB2

Since DB2 JDBC driver is not published in the Maven central repository, it is necessary to register it in the local Maven repository.

The JDBC driver can be downloaded from the following site on the Internet.

Distribution site name URL
IBM DB2 JDBC Driver Versions
and Downloads - Japan		 https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads (External site)

An example of the command to register the downloaded JDBC driver to the local Maven repository is shown below.

mvn install:install-file -DgroupId=com.ibm -DartifactId=db2jcc4 -Dversion=10.5.0.7 -Dpackaging=jar -Dfile=db2jcc4.jar

8.6.1.2.1.5. SQLServer

In the case of SQLServer, you don’t need to register the JDBC driver because it is available in Maven’s central repository

8.6.1.3. File modification

8.6.1.3.1. Modification of the properties file

Modify the following location in env.properties.

Property name Description Project/Module used
nablarch.connectionFactory.
jndiResourceName		 Resource name when acquiring the DataSource with JNDI
  • Projects generated from each archetype (configure in the properties file (described below) of environment that acquires connections from JNDI)
nablarch.db.jdbcDriver JDBC driver class name
  • Projects generated from each archetype (configure in the properties file (described below) of environments that create local connection pools)
nablarch.db.url Database connection URL
  • Projects generated from each archetype (configure in the properties file (described below) of environments that create local connection pools)
nablarch.db.user Database access user name
  • Projects generated from each archetype (configure in the properties file (described below) of environments that create local connection pools)
nablarch.db.password Database access user password
  • Projects generated from each archetype (configure in the properties file (described below) of environments that create local connection pools)
nablarch.db.schema Connection schema name
  • Nablarch testing framework

Immediately after a project is generated from an archetype, the following applies to the “properties file of the environment that acquires connections from JNDI”.

Project type Config file of the environment that acquires connections from JNDI
  • Web
  • RESTful web service
  • Production environment properties(src/env/prod/resources/env.properties)
  • JSR352-compliant batch
  • Nablarch batch
  • Web for container
  • RESTful web service for container
  • Nablarch batch for container
 No

Immediately after creating a project from an archetype, the following applies to the “properties file of environments that create local connection pools”.

Project type properties file of environments that create local connection pools
  • Web
  • RESTful web service
  • Unit test environment (manual test) properties (src/env/dev/resources/env.properties)
  • JSR352-compliant batch
  • Nablarch batch
  • Unit test environment (manual test) properties (src/env/dev/resources/env.properties)
  • Production environment properties(src/env/prod/resources/env.properties)
  • Web for container
  • RESTful web service for container
  • Nablarch batch for container

The configuration example for a properties file of the environment that creates local connection pools is shown below.

8.6.1.3.1.1. H2 configuration example (default)

nablarch.db.jdbcDriver=org.h2.Driver
nablarch.db.url=jdbc:h2:./h2/db/SAMPLE
nablarch.db.user=SAMPLE
nablarch.db.password=SAMPLE
nablarch.db.schema=PUBLIC

8.6.1.3.1.2. Oracle configuration example

nablarch.db.jdbcDriver=oracle.jdbc.driver.OracleDriver
# jdbc:oracle:thin: @Host name:port number:database SID
nablarch.db.url=jdbc:oracle:thin:@localhost:1521/xe
nablarch.db.user=sample
nablarch.db.password=sample
nablarch.db.schema=sample

8.6.1.3.1.3. PostgreSQL configuration example

nablarch.db.jdbcDriver=org.postgresql.Driver
# jdbc:postgresql://Host name:Port number/database name
nablarch.db.url=jdbc:postgresql://localhost:5432/postgres
nablarch.db.user=sample
nablarch.db.password=sample
nablarch.db.schema=sample

8.6.1.3.1.4. DB2 configuration example

nablarch.db.jdbcDriver=com.ibm.db2.jcc.DB2Driver
# jdbc:db2://Host name:Port number/database name
nablarch.db.url=jdbc:db2://localhost:50000/SAMPLE
nablarch.db.user=sample
nablarch.db.password=sample
nablarch.db.schema=sample

8.6.1.3.1.5. SQL Server configuration example

nablarch.db.jdbcDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
# jdbc:sqlserver://Host name:Port number;instanceName=Instance name
nablarch.db.url=jdbc:sqlserver://localhost:1433;instanceName=SQLEXPRESS
nablarch.db.user=SAMPLE
nablarch.db.password=SAMPLE
nablarch.db.schema=SAMPLE

Important

Depending on the DB, user names, passwords and schemas are case-sensitive.

Should be configured even in the properties file as configured in the DB.

8.6.1.3.1.6. Production configuration of containers

In projects for containers, do not switch the preferences by profile. Instead, it uses the OS environment variable of the environment in which the application will run to override the configuration values declared in env.properties.

Thus, the configuration in src/main/resources/env.properties is used in the environment where OS environment variables are not set. When running in a production container environment, the OS environment variables must be used to properly override environment-dependent values such as nablarch.db.url.

See Overwrite environment dependent values using system properties for how to overwrite the configuration in OS environment variables.

See The Twelve-Factor App’s III. Configuration (external site) for the reason why it switches settings by OS environment variables instead of profiles.

8.6.1.3.2. Modification of the pom.xml file

8.6.1.3.2.1. In the profiles element (for projects that acquire a connection from JNDI in the production environment)

Modify the location where the dependency of the JDBC driver is described in the profiles element.

Tip

In the case of a project that acquires a connection from JNDI in a production environment, since the connection has to be explicitly included in the dependency only when creating a local connection pool, it is described in the profiles element.

(When acquiring a connection from JNDI, it should be possible to acquire the JDBC driver from the class loader of AP server.)

Hereinafter, configuration examples will be described for each type of database.

8.6.1.3.2.1.1. H2 configuration example (default)
<profiles>
  <!-- Omitted -->
  <profile>
    <!-- Omitted -->
    <dependencies>
      <!-- Omitted -->
      <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.1.214</version>
        <scope>runtime</scope>
      </dependency>
      <!-- Omitted -->
    </dependencies>
  </profile>
8.6.1.3.2.1.2. Oracle configuration example
<profiles>
  <!-- Omitted -->
  <profile>
    <!-- Omitted -->
    <dependencies>
      <!-- Omitted -->
      <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.4</version>
        <scope>runtime</scope>
      </dependency>
      <!-- Omitted -->
    </dependencies>
  </profile>
8.6.1.3.2.1.3. PostgreSQL configuration example
<profiles>
  <!-- Omitted -->
  <profile>
    <!-- Omitted -->
    <dependencies>
      <!-- Omitted -->
      <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4.1207</version>
        <scope>runtime</scope>
      </dependency>
      <!-- Omitted -->
    </dependencies>
  </profile>
8.6.1.3.2.1.4. DB2 configuration example
<profiles>
  <!-- Omitted -->
  <profile>
    <!-- Omitted -->
    <dependencies>
      <!-- Omitted -->
      <dependency>
        <groupId>com.ibm</groupId>
        <artifactId>db2jcc4</artifactId>
        <version>10.5.0.7</version>
        <scope>runtime</scope>
      </dependency>
      <!-- Omitted -->
    </dependencies>
  </profile>
8.6.1.3.2.1.5. SQLServer configuration example
<profiles>
  <!-- Omitted -->
  <profile>
    <!-- Omitted -->
    <dependencies>
      <!-- Omitted -->
      <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>7.4.1.jre8</version>
        <scope>runtime</scope>
      </dependency>
      <!-- Omitted -->
    </dependencies>
  </profile>

8.6.1.3.2.2. In the dependencies element (for projects that create a local connection pool in the production environment)

Modify the location where the dependency of the JDBC driver is described in the dependencies element.

An example of the dependency element described by default is shown.

<dependencies>
  <!-- TODO: プロジェクトで使用するDB製品にあわせたJDBCドライバに修正してください。(Modify the JDBC driver according to the DB product used in the project.) -->
  <!-- Omitted -->
  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
    <scope>runtime</scope>
  </dependency>
  <!-- Omitted -->
</dependencies>

For each element in the dependency element, enter the same description as In the profiles element (for projects that acquire a connection from JNDI in the production environment).

8.6.1.3.3. (for projects that acquire a connection from JNDI in the production environment) component configuration file (src/main/resources/)

In the case of a project that fetches a connection from JNDI in a production environment, the dialect class of the database that is used by the project, is defined in the component configuration file located in src/main/resources. The component configuration file name of each project is as follows.

Project type Component configuration file name
Web web-component-configuration.xml
RESTful web service rest-component-configuration.xml

Change the following configuration in the above file.

<!-- ダイアレクト設定(Dialect configuration.) -->
<!-- TODO:使用するDBに合わせてダイアレクトを設定すること(Configure dialect according to the DB to be used.) -->
<component name="dialect" class="nablarch.core.db.dialect.H2Dialect" />

The following dialect classes are available in Nablarch. Modify the dialect class corresponding to the database to be used.

Database Dialect class
Oracle nablarch.core.db.dialect.OracleDialect
PostgreSQL nablarch.core.db.dialect.PostgreSQLDialect
DB2 nablarch.core.db.dialect.DB2Dialect
SQL Server nablarch.core.db.dialect.SqlServerDialect

8.6.1.3.4. (for projects that create a local connection pool in the production environment) data-source.xml (src/main/resources/)

In the case of a project that creates a local connection pool in the production environment, the dialect class of the database used by the project is described in data-source.xml.

Modify this dialect class to the one that corresponds to the database to be used.

The Dialect class to be used is the same as (for projects that acquire a connection from JNDI in the production environment) component configuration file (src/main/resources/).

8.6.1.3.5. unit-test.xml (src/test/resources)

Describes the database configuration used by the testing framework.

The default is a general-purpose DB configuration as shown below.

When using Oracle, modify the description.

<!-- TODO: 使用するDBに合せて設定してください。(configure it for project DB.) -->
<!-- Oracle用の設定(Configuration for Oracle) -->
<!--
  <import file="nablarch/test/test-db-info-oracle.xml"/>
-->
<!-- General purpose DB configuration -->
<component name="dbInfo" class="nablarch.test.core.db.GenericJdbcDbInfo">
  <property name="dataSource" ref="dataSource"/>
  <property name="schema" value="${nablarch.db.schema}"/>
</component>

8.6.1.4. Create tables and populate data used by Nablarch

8.6.1.4.1. Create table

DDL is prepared for each RDBMS in the following directory of each project. By executing this DDL, tables used by Nablarch can be created.

  • db/ddl/

Tip

In the case of DB2, since the connected database and schema to be used are described at the top of create.sql, edit this information before executing DDL.

To execute DDL, execute the following in the “DB2 Command Window”.

db2 -tvf "C:\develop\myapp-web\db\ddl\db2\create.sql"

Tip

When using gsp-dba-maven-plugin [1], create a table by executing gsp-dba-maven-plugin with the following command.

mvn -P gsp clean generate-resources
[1]

Separate configuration is required to use gsp-dba-maven-plugin.

See Initial Configuration Method of gsp-dba-maven-plugin (DBA Work Support Tool) for configuration.

8.6.1.4.2. Data input

Insert statements of data are available in the following directory of each project. By executing the insert statement, data that is used by Nablarch can be inserted.

  • db/data/

Tip

In the case of DB2, describe the connected database and schema used at the top of data.sql and then execute the SQL.

A description example of the connected database and schema used is shown below.

CONNECT TO SAMPLE2;
SET SCHEMA sample;

To execute DDL, execute the following in the “DB2 Command Window”.

db2 -tvf "C:\develop\myapp-web\db\data\data.sql"