How to change a TERASOLUNA 5.x blank project to support PostgreSQL

1.First of all

In the initial state of the blank project of TERASOLUNA Server Framework for Java (5.x), it is set to use in-memory h2db as the database. This time I would like to keep a personal memorandum on how to change the database to PostgreSQL.

1.1. Verification environment

Java 1.8.0_131
TERASOLUNA Server Framework for Java 5.3.0
PostgreSQL 9.3
Apache Tomcat 8.0.44

1.2. PostgreSQL compatible change work flow

The workflow for changing a blank project to support PostgreSQL is shown below. This time, we will use the connection pool prepared in the application instead of the connection pool defined in the data source of the application server (Servlet container).

Item number	work	Overview
1	Creating a blank project	Create a blank project according to TERASOLUNA guidelines.
2	Preparing the database to use	Create a database and a user in PostgreSQL on localhost.
3	Add the library of the database to be used to the dependency of the parent pom	Add a dependency to load the PostgreSQL JDBC driver.
4	Disable DB initialization process when starting application	In the initial state, h2db database initialization processing is set, so disable it.
5	(Optional) Disable JDBC log writing by log4jdbc	Disable the log4jdbc setting for debugging.
6	Change database connection destination	Set to connect to the database prepared this time.
7	Add the library of the database to be used to the dependency of the domain project	Add a dependency to load the JDBC driver when testing the domain.
8	(Optional) Modify the initdb project	Initdb also has a database connection information setting, so fix this.
9	(Optional) Build a database using the initdb project	Building the database prepared in 2 using initdb,Initialize the process.
10	Trial build	設定に不備がないことを確認するためTrial buildを行います。
11	Trial start	設定に不備がないことを確認するためTrial startを行います。

2. Create a blank project

Described in the published guideline "3.1.2. Creating a development project" Create a blank project according to the procedure described above. This time, the project settings are as follows, and a blank project is created with C: \ work as the current directory.

Item number	item	value	Remarks
1	archetypeArtifactId	terasoluna-gfw-multi-web-blank-mybatis3-archetype	Created with multi-project using MyBatis
2	archetypeVersion	5.3.0.RELEASE	Version is 5.3.0
3	groupId	com.example.demo.postgresql
4	artifactId	demo-database-postgresql

`Create a project with mvn command`


mvn archetype:generate -B^
 -DarchetypeGroupId=org.terasoluna.gfw.blank^
 -DarchetypeArtifactId=terasoluna-gfw-multi-web-blank-mybatis3-archetype^
 -DarchetypeVersion=5.3.0.RELEASE^
 -DgroupId=com.example.demo.postgresql^
 -DartifactId=demo-database-postgresql^
 -Dversion=1.0.0-SNAPSHOT

`Execution result`


...abridgement...
[INFO] Parameter: groupId, Value: com.example.demo.postgresql
[INFO] Parameter: artifactId, Value: demo-database-postgresql
[INFO] Parent element not overwritten in C:\work\demo-database-postgresql\demo-database-postgresql-env\pom.xml
[INFO] Parent element not overwritten in C:\work\demo-database-postgresql\demo-database-postgresql-domain\pom.xml
[INFO] Parent element not overwritten in C:\work\demo-database-postgresql\demo-database-postgresql-web\pom.xml
[INFO] Parent element not overwritten in C:\work\demo-database-postgresql\demo-database-postgresql-initdb\pom.xml
[INFO] Parent element not overwritten in C:\work\demo-database-postgresql\demo-database-postgresql-selenium\pom.xml
[INFO] Project created from Archetype in dir: C:\work\demo-database-postgresql
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 26.941 s
[INFO] Finished at: 2017-08-31T10:12:00+09:00
[INFO] Final Memory: 10M/48M
[INFO] ------------------------------------------------------------------------

C:\work>

If BUILD SUCCESS is displayed and the demo-database-postgresql directory specified by artifactId is created under the C: \ work directory, it is successful.

3. Preparation of database to use

Create a database and user to use, and make it ready for DDL. This time, I prepared a database in PostgreSQL prepared on localhost with the following settings.

Item number	item	value
1	port number	5432
2	Database name	demodb
3	User ID	demoUser
4	password	demoPass

4. Add the library of the database to be used to the dependency of the parent pom

In the initial state, the settings of Oracle and PostgreSQL are described in the commented out state. Uncomment PostgreSQL and enable it.

Target file: C: \ work \ demo-database-postgresql \ pom.xml

`Before correction`


            <!-- == Begin Database == -->
<!--             <dependency> -->
<!--                 <groupId>org.postgresql</groupId> -->
<!--                 <artifactId>postgresql</artifactId> -->
<!--                 <version>${postgresql.version}</version> -->
<!--             </dependency> -->
<!--             <dependency> -->
<!--                 <groupId>com.oracle</groupId> -->
<!--                 <artifactId>ojdbc7</artifactId> -->
<!--                 <version>${ojdbc.version}</version> -->
<!--             </dependency> -->
            <!-- == End Database == -->

`Revised`


            <!-- == Begin Database == -->
             <dependency>
                 <groupId>org.postgresql</groupId>
                 <artifactId>postgresql</artifactId>
                 <version>${postgresql.version}</version>
             </dependency>
<!--             <dependency> -->
<!--                 <groupId>com.oracle</groupId> -->
<!--                 <artifactId>ojdbc7</artifactId> -->
<!--                 <version>${ojdbc.version}</version> -->
<!--             </dependency> -->
            <!-- == End Database == -->

5. Disable DB initialization process when starting application

In the initial state of the blank project, the DDL file specified by <jdbc: initialize-database> is executed when the DI container is started, that is, when the application is started. It is necessary when using an in-memory database, but I think that this function is not necessary for general Web applications. Comment out the description of <jdbc: initialize-database> to disable it.

Target file: C: \ work \ demo-database-postgresql \ demo-database-postgresql-env \ src \ main \ resources \ META-INF \ spring \ demo-database-postgresql-env.xml

`Before correction`


    <jdbc:initialize-database data-source="dataSource"
        ignore-failures="ALL">
        <jdbc:script location="classpath:/database/${database}-schema.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:/database/${database}-dataload.sql" encoding="UTF-8" />
    </jdbc:initialize-database>

`Revised`


    <!--
    <jdbc:initialize-database data-source="dataSource"
        ignore-failures="ALL">
        <jdbc:script location="classpath:/database/${database}-schema.sql" encoding="UTF-8" />
        <jdbc:script location="classpath:/database/${database}-dataload.sql" encoding="UTF-8" />
    </jdbc:initialize-database>
    -->

6. (Optional) Disable JDBC log writing by log4jdbc

If you make the transaction read-only with @Transactional (readOnly = true), the error message log will be output by log writing of log4jdbc.

`Log of output error messages`


date:2017-08-31 20:04:25	thread:http-nio-8080-exec-2	X-Track:b67e7712dcc143f398f5e51b762e3dea	level:ERROR	logger:jdbc.audit                                      	message:2. Connection.setReadOnly(true)
org.postgresql.util.PSQLException:You cannot change read-only properties during a transaction.
	at org.postgresql.jdbc2.AbstractJdbc2Connection.setReadOnly(AbstractJdbc2Connection.java:741)
	at org.apache.commons.dbcp2.DelegatingConnection.setReadOnly(DelegatingConnection.java:562)
	at org.apache.commons.dbcp2.DelegatingConnection.setReadOnly(DelegatingConnection.java:562)
	at net.sf.log4jdbc.ConnectionSpy.setReadOnly(ConnectionSpy.java:374)
...abridgement...

Not surprisingly, log4jdbc requires "read / write" transactions, even though the application has set it to "read only". Since the error message is only output to the log, it does not affect the processing of the application, but I personally care about it, so I always disable log4jdbc.

It's easy to disable it, just remove the Bean definition in Log4jdbcProxyDataSource and change the beanId dataSource to specify the dbcp2 datasource.

Target file: C: \ work \ demo-database-postgresql \ demo-database-postgresql-env \ src \ main \ resources \ META-INF \ spring \ demo-database-postgresql-env.xml

`Before correction`


    <bean id="realDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${database.driverClassName}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
        <property name="defaultAutoCommit" value="false" />
        <property name="maxTotal" value="${cp.maxActive}" />
        <property name="maxIdle" value="${cp.maxIdle}" />
        <property name="minIdle" value="${cp.minIdle}" />
        <property name="maxWaitMillis" value="${cp.maxWait}" />
    </bean>


    <bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
        <constructor-arg index="0" ref="realDataSource" />
    </bean>

`Revised`


    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="${database.driverClassName}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
        <property name="defaultAutoCommit" value="false" />
        <property name="maxTotal" value="${cp.maxActive}" />
        <property name="maxIdle" value="${cp.maxIdle}" />
        <property name="minIdle" value="${cp.minIdle}" />
        <property name="maxWaitMillis" value="${cp.maxWait}" />
    </bean>

    <!--
    <bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
        <constructor-arg index="0" ref="realDataSource" />
    </bean>
    -->

7. Change the database connection destination

This time, we will use the dbcp2 connection pool in the application. Therefore, database connection information (URL, port number, user ID, password, etc.) must be managed within the application. By default in TERASOLUNA 5.x, it is defined in *** infra.properties. In addition, when using the data source of the application server with JNDI, I will explain it at another time.

Target file: C: \ work \ demo-database-postgresql \ demo-database-postgresql-env \ src \ main \ resources \ META-INF \ spring \ demo-database-postgresql-infra.properties

`Before correction`


database=H2
database.url=jdbc:h2:mem:demo-database-postgresql;DB_CLOSE_DELAY=-1
database.username=sa
database.password=
database.driverClassName=org.h2.Driver
# connection pool
cp.maxActive=96
cp.maxIdle=16
cp.minIdle=0
cp.maxWait=60000

`Revised`


### default setting is H2
# database=H2
# database.url=jdbc:h2:mem:demo-database-postgresql;DB_CLOSE_DELAY=-1
# database.username=sa
# database.password=
# database.driverClassName=org.h2.Driver

### setting for postgreSQL
database=POSTGRESQL
database.url=jdbc:postgresql://127.0.0.1:5432/demodb
database.username=demoUser
database.password=demoPass
database.driverClassName=org.postgresql.Driver

# connection pool
cp.maxActive=96
cp.maxIdle=16
cp.minIdle=0
cp.maxWait=60000

8. Add the library of the database to be used to the dependency of the domain project.

In the initial state, the settings of Oracle and PostgreSQL are described in the commented out state. Uncomment PostgreSQL and enable it.

Target file: C: \ work \ demo-database-postgresql \ demo-database-postgresql-domain \ pom.xml

`Before correction`


        <!-- == Begin Database == -->
<!--         <dependency> -->
<!--             <groupId>org.postgresql</groupId> -->
<!--             <artifactId>postgresql</artifactId> -->
<!--             <scope>test</scope> -->
<!--         </dependency> -->
<!--         <dependency> -->
<!--             <groupId>com.oracle</groupId> -->
<!--             <artifactId>ojdbc7</artifactId> -->
<!--             <scope>test</scope> -->
<!--         </dependency> -->
        <!-- == End Database == -->

`Revised`


        <!-- == Begin Database == -->
         <dependency>
             <groupId>org.postgresql</groupId>
             <artifactId>postgresql</artifactId>
             <scope>test</scope>
         </dependency>
<!--         <dependency> -->
<!--             <groupId>com.oracle</groupId> -->
<!--             <artifactId>ojdbc7</artifactId> -->
<!--             <scope>test</scope> -->
<!--         </dependency> -->
        <!-- == End Database == -->

9. (Optional) Modify the initdb project

The initdb project also holds database connection information. However, since the initdb project itself is for the initial construction of the database, there is no need to modify it if you do not use initdb. The parts to be modified are <db.url>, <db.username>, and <db.password>, which set the database connection information.

Target file: C: \ work \ demo-database-postgresql \ demo-database-postgresql-initdb \ pom.xml

`Before correction`


        <profile>
            <id>local-postgres</id>
            <activation>
                <activeByDefault>true</activeByDefault>
            </activation>
            <properties>
                <db.encoding>UTF8</db.encoding>
                <db.basedir>${project.basedir}/src/main/sqls/postgres</db.basedir>
                <db.url>jdbc:postgresql://127.0.0.1:5432/projectName</db.url>
                <db.username>postgres</db.username>
                <db.password>P0stgres</db.password>
                <db.driver>org.postgresql.Driver</db.driver>
                <db.groupId>org.postgresql</db.groupId>
                <db.artifactId>postgresql</db.artifactId>
                <db.version>${postgresql.version}</db.version>
                <db.delimiterType>row</db.delimiterType>
            </properties>
        </profile>

`Revised`


        <profile>
            <id>local-postgres</id>
            <activation>
                <activeByDefault>true</activeByDefault>
            </activation>
            <properties>
                <db.encoding>UTF8</db.encoding>
                <db.basedir>${project.basedir}/src/main/sqls/postgres</db.basedir>
                <db.url>jdbc:postgresql://127.0.0.1:5432/demodb</db.url>
                <db.username>demoUser</db.username>
                <db.password>demoPass</db.password>
                <db.driver>org.postgresql.Driver</db.driver>
                <db.groupId>org.postgresql</db.groupId>
                <db.artifactId>postgresql</db.artifactId>
                <db.version>${postgresql.version}</db.version>
                <db.delimiterType>row</db.delimiterType>
            </properties>
        </profile>

10. (Optional) Build a database using the initdb project

10.1. Creating DDL for database construction

The DDL used for database construction is stored in the directory specified by <db.basedir> above. In this case it would be C: \ work \ demo-database-postgresql \ demo-database-postgresql-initdb \ src \ main \ sqls \ postgres. Create this directory because it does not exist by default.

Item number	file name	Remarks
1	00000_drop_all_tables.sql	A file that describes the DDL that deletes the table.
2	00100_create_all_tables.sql	A file that describes the DDL that creates the required tables.
3	00200_insert_employee.sql	This is a file that describes the SQL that registers the initial data of a specific table (employee).

Since the files are sorted by file name and executed sequentially, the file names are given in consideration of the execution order. I haven't created it this time, but if necessary, prepare DDL to create sequences and indexes.

`00000_drop_all_tables.sql`



DROP TABLE IF EXISTS employee;

`00100_create_all_tables.sql`



--Employee table--
CREATE TABLE employee (
    --Employee ID
    employee_id    varchar(10)    NOT NULL,
    --Full name
    name           varchar(30)    NOT NULL,
    --Hire date
    register_date  date           NOT NULL,
    --point
    point          numeric(3,0)   NOT NULL,
    --Primary key constraint
    CONSTRAINT EMPLOYEE_PK PRIMARY KEY(employee_id)
);

`00200_insert_employee.sql`



INSERT INTO employee (employee_id, name, register_date, point) VALUES ('0000000777', 'Exam Taro', to_date('2016/04/01', 'yyyy/MM/dd'), 10);
COMMIT;

10.2. Build database with mvn sql: execute command

When the mvn sql: execute command is executed in the initdb project, the DDL prepared above is executed, and the database is built and initialized.

`Execution result`


C:\work\demo-database-postgresql\demo-database-postgresql-initdb>mvn sql:execute
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building demo-database-postgresql-initdb 1.0.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- sql-maven-plugin:1.5:execute (default-cli) @ demo-database-postgresql-initdb ---
[INFO] Executing file: C:\work\demo-database-postgresql\demo-database-postgresql-initdb\src\main\sqls\postgres\00000_dro
p_all_tables.sql
[INFO] Executing file: C:\work\demo-database-postgresql\demo-database-postgresql-initdb\src\main\sqls\postgres\00100_cre
ate_all_tables.sql
[INFO] Executing file: C:\work\demo-database-postgresql\demo-database-postgresql-initdb\src\main\sqls\postgres\00200_ins
ert_employee.sql
[INFO] 3 of 3 SQL statements executed successfully
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.217 s
[INFO] Finished at: 2017-08-31T10:56:24+09:00
[INFO] Final Memory: 8M/20M
[INFO] ------------------------------------------------------------------------

C:\work\demo-database-postgresql\demo-database-postgresql-initdb>

Check 3 of 3 SQL statements executed successfully and BUILD SUCCESS to make sure it completes successfully.

11. Trial build

Many configuration files have been modified to support PostgreSQL. Perform a trial build to check if there are any deficiencies.

`Execution result`


C:\work\demo-database-postgresql>mvn package
[INFO] Scanning for projects...

...abridgement...

[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO]
[INFO] TERASOLUNA Server Framework for Java (5.x) Web Blank Multi Project (MyBatis3) SUCCESS [  0.015 s]
[INFO] demo-database-postgresql-env ....................... SUCCESS [  1.248 s]
[INFO] demo-database-postgresql-domain .................... SUCCESS [  0.359 s]
[INFO] demo-database-postgresql-web ....................... SUCCESS [  4.883 s]
[INFO] demo-database-postgresql-initdb .................... SUCCESS [  0.031 s]
[INFO] demo-database-postgresql-selenium .................. SUCCESS [  2.090 s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 9.048 s
[INFO] Finished at: 2017-08-31T10:57:46+09:00
[INFO] Final Memory: 35M/84M
[INFO] ------------------------------------------------------------------------

C:\work\demo-database-postgresql>

If BUILD SUCCESS is displayed, it was confirmed that there are no defects in the parts that are found at build time.

12. Trial start

If the trial build is completed normally, C: \ work \ demo-database-postgresql \ demo-database-postgresql-web \ target \ demo-database-postgresql-web.war exists. Copy and deploy demo-database-postgresql-web.war to the Tomcat installation directory / webapps.

Execute Tomcat installation directory /bin/startup.bat to start Tomcat. Access http: // localhost: 8080 / demo-database-postgresql-web / with a web browser, and if the default screen "Hello world!" Is displayed, it is successful.

13. Finally

This time, I explained how to change the blank project of TERASOLUNA 5.x to support PostgreSQL. Normally, the database to be used is set only at the first time of project creation, so I think that there are few opportunities to do it. However, if the initial setting of the project is not completed, we cannot proceed to the development of the application (business process), so we want to respond quickly.