Database (Spring Boot)

Index

Introduction

This page handles several aspects related to databases in a Spring Boot development.

Database tracking (Liquibase)

For tracking and managing database changes with Spring Boot, see the Liquibase article:

Liquibase page

Database connection

The strategy is using a persistent database for regular use but an in-memory one for executing tests.

The following sample uses PostgreSQL as a regular database and H2 for unit tests.


Maven pom.xml

Let's add the drivers for the 2 databases (H2 is only needed in scope 'test') in section project.dependencies.

        <dependency>

            <groupId>org.postgresql</groupId>

            <artifactId>postgresql</artifactId>

            <version>42.2.2</version>

        </dependency>

        <dependency>

            <groupId>com.h2database</groupId>

            <artifactId>h2</artifactId>

            <scope>test</scope>

        </dependency>


Spring Boot application.properties

This is the file that the framework Spring Boot reads for database configuration (among other purposes). The official documentation and sample file is available at > https://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html

Notice that we'll need 2 different configuration files: one for the regular database and another one that overrides the configuration when executing unit tests.

src/main/resources/application.properties

# DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)

spring.datasource.url=jdbc:postgresql://${POSTGRESQL_SERVICE_HOST}:${POSTGRESQL_SERVICE_PORT}/${DATABASE_NAME}

spring.datasource.username=${DATABASE_USER}

spring.datasource.password=${DATABASE_PASSWORD}

# JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)

spring.jpa.hibernate.ddl-auto=none

src/test/resources/application.properties

# DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)

spring.datasource.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL

spring.datasource.username=sa

spring.datasource.password=

# JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)

spring.jpa.hibernate.ddl-auto=none

The regular database requires connection parameters that will be, obviously, different for environments (TEST, PRE, PRO, etc.).

Since this sample was run in a Docker/Openshift container, SO environment variables were used. Sample follows:

POSTGRESQL_SERVICE_HOST=172.23.220.20

POSTGRESQL_SERVICE_PORT=5432

DATABASE_NAME=sample_test_db

DATABASE_USER=postgres

DATABASE_PASSWORD=postgres

No additional configuration needed since Spring Book uses the configured datasource and the primary database connection.


HikariCP

Because of hibernate performance, keep "Open Session in View" to false.

For more information, see:

Sample src/main/resources/application.properties with relevant settings:

spring.jpa.hibernate.ddl-auto=none

#[spring.jpa.open-in-view]=true (keep false for performance)# Register OpenEntityManagerInViewInterceptor. Binds a JPA EntityManager to the thread for the entire processing of the request.

spring.jpa.open-in-view=false

spring.jpa.properties.hibernate.generate_statistics=true


#[connection-test-query] If driver supports JDBC4 it is strongly recommended not setting this property

#spring.datasource.hikari.connection-test-query=SELECT 1;

#[connection-timeout] 30k by default, but need less than apigtw timeout (>29s in AWS)

spring.datasource.hikari.connection-timeout=29000

#[leak-detection-threshold] controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak.

spring.datasource.hikari.leak-detection-threshold=92000

#[maximum-pool-size] 10 by default. Controls the maximum size that the pool is allowed to reach, including both idle and in-use connections.

spring.datasource.hikari.maximum-pool-size=32



JPA Hibernate query execution time

The following configuration shows in log JPA query execution time (tested on Spring Boot 2.1.6.RELEASE):


a) Activate hibernate stats in file application.properties:

spring.jpa.properties.hibernate.generate_statistics=true


b) Log level for 'org.hibernate.stat' must be at least DEBUG (only for GPRA_docback < 2.3):

curl -i -X POST -H 'Content-Type: application/json' -d '{"configuredLevel": "DEBUG"}' http://localhost:8080/actuator/loggers/org.hibernate.stat


c) Search in log for entries similar to the following:

2019-07-04 10:27:27.739 DEBUG 11797 --- [nio-8080-exec-3] o.h.stat.internal.StatisticsImpl         : HHH000117: HQL: select count(*) from AppRequest x WHERE x.uuid = :uuid, time: 1ms, rows: 1


Querydsl - REST Query Language with Spring Data JPA and Querydsl

For any complex enough API – searching/filtering your resources by very simple fields is simply not enough. A query language is more flexible, and allows you to filter down to exactly the resources you need.

Querydsl > http://www.querydsl.com/

Tutorial > https://www.baeldung.com/rest-api-search-language-spring-data-querydsl