This page handles several aspects related to databases in a Spring Boot development.
For tracking and managing database changes with Spring Boot, see the Liquibase article:
Liquibase page
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.
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>
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.
Because of hibernate performance, keep "Open Session in View" to false.
For more information, see:
Spring-boot project db connection leak investigation [https://raul8804.wordpress.com/2019/03/31/spring-boot-project-db-connection-leak-investigation/]
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
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
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