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

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.

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.

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.

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.

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.

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.

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.

Recommended Posts

How to change a TERASOLUNA 5.x blank project to support PostgreSQL
How to remove Tiles from TERASOLUNA 5.x blank project
How to make a Maven project
Change from SQLite3 to PostgreSQL in a new Ruby on Rails project
[1st] How to create a Spring-MVC framework project
How to create a Spring Boot project in IntelliJ
[Spring Boot] How to create a project (for beginners)
How to change a string in an array to a number in Ruby
How to leave a comment
Change from SQLite3 to PostgreSQL
How to insert a video
How to create a method
How to make a hinadan for a Spring Boot project using SPRING INITIALIZR
How to realize huge file upload with TERASOLUNA 5.x (= Spring MVC)
How to create a new Gradle + Java + Jar project in Intellij 2016.03
[chown] How to change the owner of a file or directory
How to add columns to a table
How to make a Java container
How to sign a Minecraft MOD
How to set Spring Boot + PostgreSQL
How to make a JDBC driver
How to change kube-proxy to ipvs mode.
[Java] How to create a folder
How to write a ternary operator
[Swift] How to send a notification
How to make a splash screen
How to make a Jenkins plugin
How to make a Java array
How to change from HTML to Haml
You cannot change the project facet version from a dynamic web module to x.x. How to deal with
How to build a Ruby on Rails development environment with Docker (Rails 6.x)
How to change the value of a variable at a breakpoint in intelliJ
[Rails] How to create a table, add a column, and change the column type
How to build a Ruby on Rails development environment with Docker (Rails 5.x)
[Xcode] How to add a README.md file
How to execute a contract using web3j
How to sort a List using Comparator
How to make a Java calendar Summary
A memorandum on how to use Eclipse
How to redo a deployment on Heroku
How to change app name in rails
[Basic] How to write a Dockerfile Self-learning ②
How to insert a video in Rails
How to add a new hash / array
[Introduction to Java] How to write a Java program
How to create a Maven repository for 2020
How to make a Discord bot (Java)
How to change the timezone on Ubuntu
How to build SquashTM and how to support Japanese
Ransack sort_link How to change the color!
How to print a Java Word document
[Swift5] How to create a splash screen
[rails] How to create a partial template
[Rails 5.x] How to introduce free fonts
How to publish a library in jCenter
[SpringBoot] How to write a controller test