Skip to main content

TP4.1 - PostgreSQL Integration with Jakarta Persistence

Migration to a Production Database​

Objectives​

This practical work will allow you to:

  • Integrate PostgreSQL with Spring Boot
  • Use Jakarta Persistence (JPA) for data persistence
  • Configure Hibernate as ORM
  • Create and manage a relational database
  • Understand the difference between Jakarta and Javax Persistence
  • Master configuration with application.properties and application.yml

Table of Contents​

  1. Quick Reminder
  2. Adding the Database
  3. Testing with PostgreSQL
  4. Exercise 1: Javax Persistence
  5. Exercise 2: YAML Configuration
  6. Exercise 3: Lombok
  7. Exercise 4: Automatic Recreation
  8. Exercise 5: Other Database

1. Quick Reminder​

Context​

In previous practical works, we created a complete CRUD application with H2. Now, we will migrate to PostgreSQL, a professional relational database suitable for production.

Objectives of this TP​

  1. Migrate from H2 to PostgreSQL - Transition from an embedded database to a production database
  2. Use Jakarta Persistence (JPA) - Map Java objects to SQL tables
  3. Configure PostgreSQL - Install and configure a professional database

Prerequisites​

  • TP3 (CRUD Architecture) completed
  • PostgreSQL installed (version 12 or higher)
  • pgAdmin or DBeaver to manage PostgreSQL
  • Visual Studio Code with Java extensions

2. Adding the Database​

2.1 Adding Dependencies in pom.xml​

Open your pom.xml file and add the following two dependencies in the <dependencies> section:

<!-- PostgreSQL Driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- Spring Data JPA (includes Jakarta Persistence) -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Explanation:

DependencyRole
postgresqlJDBC Driver to connect to PostgreSQL
spring-boot-starter-data-jpaIncludes JPA, Hibernate, and Spring Data

Note: After adding, VS Code will prompt you to reload the Maven project. Accept or run:

mvn clean install -DskipTests

2.2 Configuration of application.properties​

Create or modify the src/main/resources/application.properties file:

# Database connection URL
spring.datasource.url=jdbc:postgresql://localhost:5432/greeting_db?useSSL=false

# Connection credentials
spring.datasource.username=postgres
spring.datasource.password=postgres

# Hibernate configuration
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Schema generation strategy
spring.jpa.hibernate.ddl-auto=create

# Show SQL queries (useful for development)
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Explanation of properties:

PropertyDescriptionPossible values
spring.datasource.urlJDBC connection URLFormat: jdbc:postgresql://host:port/database
spring.datasource.usernamePostgreSQL usernameDefault: postgres
spring.datasource.passwordPostgreSQL passwordAdapt according to your installation
spring.jpa.hibernate.ddl-autoSchema creation strategynone, validate, update, create, create-drop
spring.jpa.show-sqlShow SQL queries in the consoletrue or false

ddl-auto values:

Important: Change greeting_db to your database name if necessary.

2.3 Annotation of the Entity Greeting.java​

Modify the Greeting.java class to make it a JPA entity:

package com.example.demo;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "greeting")
public class Greeting {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "content", nullable = false)
    private String content;

    // Default constructor (required by JPA)
    public Greeting() {
    }

    // Constructor with parameters
    public Greeting(Long id, String content) {
        this.id = id;
        this.content = content;
    }

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }
}

Explanation of annotations:

AnnotationRole
@EntityMarks the class as a JPA entity (mapped to a table)
@Table(name = "greeting")Specifies the SQL table name
@IdMarks the field as a primary key
@GeneratedValue(strategy = GenerationType.IDENTITY)Auto-increment of the ID
@Column(name = "content", nullable = false)SQL column configuration

JPA Architecture:

2.4 Creation of the Repository​

Create an interface GreetingRepository.java in the com.example.demo package:

package com.example.demo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface GreetingRepository extends JpaRepository<Greeting, Long> {
    // Spring Data JPA automatically generates the implementation
    // Available methods: save(), findAll(), findById(), delete(), etc.
}

Automatically generated methods:

MethodDescription
save(Greeting g)Save or update
findAll()Retrieve all entries
findById(Long id)Retrieve by ID
deleteById(Long id)Delete by ID
count()Count entries

2.5 Modification of the Controller​

Modify GreetingController.java to use the repository:

package com.example.demo;

import java.util.concurrent.atomic.AtomicLong;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class GreetingController {

    private static final String template = "Hello, %s!";
    private final AtomicLong counter = new AtomicLong();
    
    private final GreetingRepository greetingRepository;
    
    public GreetingController(GreetingRepository greetingRepository) {
        this.greetingRepository = greetingRepository;
    }
    
    @GetMapping("/greeting")
    public Greeting greeting(@RequestParam(value="name") String name) {
        Greeting greeting = new Greeting(
            counter.incrementAndGet(),
            String.format(template, name)
        );
        
        // Save to the database
        return greetingRepository.save(greeting);
    }
}

Data flow:

2.6 Creation of the PostgreSQL Database​

Option A: With pgAdmin​

  1. Open pgAdmin

  2. Create a user (optional):

    • Right-click on "Login/Group Roles"
    • "Create" → "Login/Group Role"
    • "General" tab: Name = postgres
    • "Definition" tab: Password = postgres
    • "Privileges" tab: Check "Can login?" and "Create databases?"
    • Save

  3. Create the database:

    • Right-click on "Databases"
    • "Create" → "Database"
    • Database name: greeting_db
    • Owner: postgres
    • Save

Option B: With SQL (command line)​

-- Create the database
CREATE DATABASE greeting_db;

-- Create the user (if necessary)
CREATE USER postgres WITH PASSWORD 'postgres';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE greeting_db TO postgres;

To execute these commands:

# Windows
psql -U postgres

# macOS/Linux
sudo -u postgres psql

3. Testing with PostgreSQL​

3.1 Configuration Verification​

Before launching the application, check:

Checklist:

  • PostgreSQL is started
  • The greeting_db database exists
  • The postgres user has the correct privileges
  • The application.properties file is correct
  • The Greeting class has the @Entity annotation
  • The GreetingRepository interface is created
  • The GreetingController uses the repository

3.2 Launching the Application​

In VS Code:

Method 1: Via the Terminal​

# Clean and compile
mvn clean install

# Launch the application
mvn spring-boot:run

Method 2: Via the Interface​

  1. Open GreetingApplication.java
  2. Click "Run" above the main method
  3. Or press F5

Expected logs:

Hibernate: drop table if exists greeting cascade
Hibernate: create table greeting (id bigserial not null, content varchar(255) not null, primary key (id))
Started GreetingApplication in X.XXX seconds

3.3 Testing the Endpoints​

Test 1: Create a greeting

http://localhost:8080/greeting?name=John

Expected response:

{
  "id": 1,
  "content": "Hello, John!"
}

Test 2: Create multiple greetings

http://localhost:8080/greeting?name=Alice
http://localhost:8080/greeting?name=Bob
http://localhost:8080/greeting?name=Charlie

3.4 Verification in PostgreSQL​

With pgAdmin:​

  1. Open pgAdmin
  2. Navigate: Servers → PostgreSQL → Databases → greeting_db → Schemas → public → Tables
  3. Right-click on "greeting" → "View/Edit Data" → "All Rows"

With SQL:​

-- Connect to the database
\c greeting_db

-- Display all entries
SELECT * FROM greeting;

-- Expected result:
-- id | content
-- ---+----------------
--  1 | Hello, John!
--  2 | Hello, Alice!
--  3 | Hello, Bob!
--  4 | Hello, Charlie!

3.5 SQL Logs in the Console​

Thanks to spring.jpa.show-sql=true, you will see SQL queries in the console:

Hibernate: insert into greeting (content) values (?)

4. Exercise 1: Redo with Javax Persistence​

Statement​

Redo the project using Javax Persistence instead of Jakarta Persistence (old version of JPA).

Objective​

Understand the difference between Jakarta and Javax Persistence.

Historical Context​

Instructions​

Step 1: Modify pom.xml

Add the Javax Persistence dependency:

<!-- Add Javax Persistence -->
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>javax.persistence-api</artifactId>
    <version>2.2</version>
</dependency>

Step 2: Modify imports in Greeting.java

Replace:

import jakarta.persistence.*;

With:

import javax.persistence.*;

Step 3: Complete Class with Javax

package com.example.demo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "greeting")
public class Greeting {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "content", nullable = false)
    private String content;

    public Greeting() {}

    public Greeting(Long id, String content) {
        this.id = id;
        this.content = content;
    }

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }
}

Step 4: Test

  1. Recompile: mvn clean install -DskipTests
  2. Relaunch the application
  3. Verify everything works

Reflection Questions​

  1. What is the major difference between Javax and Jakarta Persistence?
  2. Why is Jakarta recommended for new projects?
  3. Can you mix both in the same project?

5. Exercise 2: Configuration with application.yml​

Statement​

Convert your application.properties configuration to YAML format.

Objective​

Master the YAML format for Spring Boot configuration.

Instructions​

Step 1: Delete application.properties

Delete or rename the application.properties file.

Step 2: Create application.yml

Create src/main/resources/application.yml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/greeting_db?useSSL=false
    username: postgres
    password: postgres
  
  jpa:
    hibernate:
      ddl-auto: create
    show-sql: true
    properties:
      hibernate:
        jdbc:
          lob:
            non_contextual_creation: true
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true

Step 3: Test

Relaunch the application and verify everything works.

Comparison​

application.properties:

spring.datasource.url=jdbc:postgresql://localhost:5432/greeting_db
spring.datasource.username=postgres
spring.jpa.hibernate.ddl-auto=create

application.yml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/greeting_db
    username: postgres
  jpa:
    hibernate:
      ddl-auto: create

Advantages of YAML:

  • More readable for hierarchical structures
  • Less repetition
  • Modern format

6. Exercise 3: Simplification with Lombok​

Statement​

Remove manual getters, setters, and constructors using Lombok.

Objective​

Reduce boilerplate code with Lombok.

Prerequisites​

Step 1: Add Lombok dependency in pom.xml

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

Step 2: Install Lombok extension in VS Code

  1. Extensions → Search "Lombok"
  2. Install "Lombok Annotations Support for VS Code"

Instructions​

Modify Greeting.java:

package com.example.demo;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "greeting")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Greeting {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "content", nullable = false)
    private String content;
    
    // Lombok automatically generates:
    // - Getters
    // - Setters
    // - toString()
    // - equals() and hashCode()
    // - No-args constructor
    // - All-args constructor
}

Lombok Annotations:

AnnotationGenerates
@DataGetters, Setters, toString, equals, hashCode
@NoArgsConstructorNo-args constructor
@AllArgsConstructorAll-args constructor
@GetterOnly getters
@SetterOnly setters

Test​

  1. Recompile
  2. Verify the application works
  3. Methods are generated at compile time

7. Exercise 4: Automatic Recreation of the Database​

Statement​

Configure the application to automatically recreate the database at each startup.

Objective​

Understand the different schema management strategies.

Instructions​

In application.properties (or application.yml):

spring.jpa.hibernate.ddl-auto=create-drop

or

spring:
  jpa:
    hibernate:
      ddl-auto: create-drop

Comparison of Strategies​

StrategyBehaviorUsage
noneNo actionProduction
validateValidate schema without modifyingProduction
updateUpdate schemaDevelopment
createCreate schema on startupTests
create-dropCreate on startup, drop on shutdownUnit tests

Warning​

create-drop deletes all data on application shutdown!

Recommended usage:

# Development
spring:
  jpa:
    hibernate:
      ddl-auto: update

# Production
spring:
  jpa:
    hibernate:
      ddl-auto: validate

8. Exercise 5: Migration to MySQL, SQL Server, or Oracle​

Statement​

Redo the complete integration with another database.

Database Choice​

Option A: MySQL​

Dependency in pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/greeting_db?useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update

Database creation in MySQL:

CREATE DATABASE greeting_db;
CREATE USER 'root'@'localhost' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON greeting_db.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Option B: SQL Server​

Dependency in pom.xml:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration application.properties:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=greeting_db
spring.datasource.username=sa
spring.datasource.password=YourStrong@Password
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
spring.jpa.hibernate.ddl-auto=update

Database creation in SQL Server:

CREATE DATABASE greeting_db;
GO

ALTER LOGIN sa WITH PASSWORD = 'YourStrong@Password';
GO

USE greeting_db;
GO

Option C: Oracle​

Dependency in pom.xml:

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <scope>runtime</scope>
</dependency>

Configuration application.properties:

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=system
spring.datasource.password=oracle
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.jpa.hibernate.ddl-auto=update

Database creation in Oracle:

CREATE USER greeting_user IDENTIFIED BY oracle;
GRANT ALL PRIVILEGES TO greeting_user;

Comparison Table​

DatabaseDefault PortHibernate DialectDifficulty
PostgreSQL5432PostgreSQLDialectEasy
MySQL3306MySQLDialectEasy
SQL Server1433SQLServerDialectMedium
Oracle1521Oracle12cDialectDifficult

General Instructions​

  1. Install the chosen database
  2. Modify pom.xml with the correct driver
  3. Modify application.properties with the correct configuration
  4. Create the database and user
  5. Recompile and test

Troubleshooting​

Problem 1: Connection Refused​

Symptom:

org.postgresql.util.PSQLException: Connection to localhost:5432 refused

Solutions:

  1. Verify that PostgreSQL is started:

Windows:

# In Services, search for PostgreSQL
services.msc

Linux:

sudo systemctl status postgresql
sudo systemctl start postgresql
  1. Verify the port in postgresql.conf

Problem 2: Authentication Failed​

Symptom:

PSQLException: FATAL: password authentication failed for user "postgres"

Solutions:

  1. Verify the password in application.properties
  2. Reset the password:
ALTER USER postgres WITH PASSWORD 'new_password';

Problem 3: Database Does Not Exist​

Symptom:

PSQLException: FATAL: database "greeting_db" does not exist

Solution:

Create the database:

CREATE DATABASE greeting_db;

Problem 4: Table Already Exists​

Symptom:

Table "greeting" already exists

Solution:

Change the DDL strategy:

spring.jpa.hibernate.ddl-auto=update

Problem 5: Lombok Not Working​

Symptom:

error: cannot find symbol
  method getId()

Solutions:

  1. Install the Lombok extension in VS Code
  2. Restart VS Code
  3. Verify the dependency is in pom.xml
  4. Recompile: mvn clean install -DskipTests spring-boot:run

Review Questions​

  1. What is the difference between Jakarta and Javax Persistence?

  2. What does the @Entity annotation do?

  3. What is the difference between create, update, and validate for ddl-auto?

  4. How does Spring Data JPA generate the repository implementation?

  5. What does the @GeneratedValue(strategy = GenerationType.IDENTITY) annotation do?

  6. Why must a JPA entity have a no-args constructor?

  7. What does the @Data annotation from Lombok generate?

  8. What configuration should be used in production for ddl-auto?

Deliverables​

To submit:

  1. Complete source code with functional PostgreSQL
  2. Screenshots showing:
    • The application running
    • Data in pgAdmin
    • SQL queries in the console
  3. At least 2 completed exercises
  4. Document with answers to the review questions

Evaluation Criteria​

CriterionPoints
Functional PostgreSQL integration25%
Greeting entity correctly annotated20%
Repository created and used15%
Data persisted in PostgreSQL20%
Completed exercises10%
Answered review questions10%
Total100%

Next Steps​

In the next TP, we will see:

  • TP3: Creating a complete CRUD API
  • Operations: Create, Read, Update, Delete
  • Error management
  • Data validation

Additional Resources​

Documentation​

Tutorials​

Back to Module 2