Working with PostgreSQL - up1/course-springboot-2024 GitHub Wiki

Working with PostgreSQL

  • PostgreSQL for production
  • H2 for testing

1. Edit file pom.xml 

<!-- For production-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- For Testing-->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

2. Configuration database in application

For production :: PostgreSQL

  • File src/main/resources/application.properties 
spring.datasource.url=${POSTGRES_URL:jdbc:postgresql://localhost:5432/demo}
spring.datasource.username=${POSTGRES_USER:postgres}
spring.datasource.password=${POSTGRES_PASS:password}

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

spring.jpa.show-sql=true

Start server for testing configuration

$mvnw spring-boot:run

For testing :: H2 database

  • File src/test/resources/application.properties 
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.username=sa
spring.datasource.password=

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto=update

Run testing and see result

$mvnw clean test

Tuning Datasource configuration (application.properties)


spring.datasource.hikari.minimumIdle=3
spring.datasource.hikari.maximumPoolSize=10
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.connectionTimeout=10000
spring.datasource.hikari.idleTimeout=100
spring.datasource.hikari.maxLifetime=120000
⚠️ **GitHub.com Fallback** ⚠️