PostgreSQL

Fundamentals of PostgreSQL Database in Spring Boot Applications

By Utility Zone · 2025-11-02T13:01:41.320315

Overview and Setup

PostgreSQL is a powerful, open-source relational database that integrates seamlessly with Spring Boot applications through JDBC connectivity and ORM frameworks like Hibernate. The integration leverages Spring Boot's auto-configuration capabilities to minimize boilerplate code while providing robust database interaction mechanisms.

Step 1: Adding Required Dependencies

To connect a Spring Boot application to PostgreSQL, include the PostgreSQL JDBC driver in your project's pom.xml (for Maven):¹

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

For Spring Data JPA integration, add:²

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Step 2: Database Configuration

Configure the PostgreSQL connection in your application.properties file:¹

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=postgres
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=org.postgresql.Driver

Or in application.yml format:³

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydatabase
    username: postgres
    password: yourpassword
    driver-class-name: org.postgresql.Driver
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: update
    show-sql: true

The JDBC URL follows the pattern jdbc:postgresql://[host]:[port]/[database], where the default port for PostgreSQL is 5432.⁴

Step 3: Entity Definition with JPA Annotations

Entities are Java classes that map to database tables. Define them using JPA annotations:⁵

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "username", nullable = false, length = 100)
    private String username;
    
    @Column(name = "email", nullable = false, unique = true)
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
    
    // Getters and setters
}

Key annotations include:⁶⁵

@Entity: Marks the class as a JPA entity representing a database table
@Table(name = "users"): Specifies the table name; defaults to the class name if omitted
@Id: Designates the primary key field
@GeneratedValue: Defines primary key generation strategy (IDENTITY for auto-increment)
@Column: Customizes column properties such as name, nullability, length, and uniqueness constraints

Step 4: Connection Pooling with HikariCP

Spring Boot uses HikariCP by default for connection pooling, which improves performance by reusing database connections:⁷

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000

These settings control:

maximum-pool-size: Maximum number of active connections (default: 10)
minimum-idle: Minimum idle connections maintained (default: 5)
idle-timeout: Connection idle timeout in milliseconds (default: 30 seconds)
max-lifetime: Maximum connection lifetime in milliseconds (default: 30 minutes)

Step 5: Creating Repository Interfaces

Repositories provide an abstraction layer for database operations. Use Spring Data JPA's CrudRepository or JpaRepository:⁸

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

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    User findByEmail(String email);
    List<User> findByUsernameContaining(String username);
}

The @Repository annotation is optional when extending JpaRepository, as Spring automatically detects and creates bean implementations. However, it enables exception translation, converting database-specific exceptions into Spring's DataAccessException hierarchy.⁹¹⁰

Step 6: Implementing CRUD Operations

With repositories configured, implement CRUD operations in service classes:¹¹⁸

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Optional;

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    // Create
    public User createUser(User user) {
        return userRepository.save(user);
    }
    
    // Read
    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
    
    public Optional<User> getUserById(Long id) {
        return userRepository.findById(id);
    }
    
    // Update
    public User updateUser(Long id, User userDetails) {
        User user = userRepository.findById(id)
            .orElseThrow(() -> new ResourceNotFoundException("User not found"));
        user.setUsername(userDetails.getUsername());
        user.setEmail(userDetails.getEmail());
        return userRepository.save(user);
    }
    
    // Delete
    public void deleteUser(Long id) {
        userRepository.deleteById(id);
    }
}

Step 7: Custom Query Methods

For complex queries, use Spring Data JPA's @Query annotation:¹²

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.email = ?1")
    User findByEmailAddress(String emailAddress);
    
    @Query(value = "SELECT * FROM users WHERE username LIKE ?1%", nativeQuery = true)
    List<User> findUsersStartingWith(String prefix);
}

Set nativeQuery = true to execute raw SQL queries instead of JPQL (Java Persistence Query Language).¹³

Step 8: Transaction Management

Use the @Transactional annotation to manage database transactions:¹⁴

@Service
public class UserService {
    @Transactional
    public void transferData() {
        // All database operations here are part of a single transaction
        // If any exception occurs, all changes are rolled back
    }
}

The @Transactional annotation:

Automatically begins a transaction when the method is called
Commits if the method completes successfully
Rolls back if a RuntimeException occurs¹⁴
Is automatically configured when using Spring Boot's data-jpa starter

Step 9: Schema Initialization

Control automatic schema creation with spring.jpa.hibernate.ddl-auto:¹⁵¹⁶

spring.jpa.hibernate.ddl-auto=update

Supported values:¹⁵

validate: Validates the schema without modifications
update: Updates the schema if necessary (recommended for development)
create: Creates a new schema, destroying existing data
create-drop: Creates schema on startup and drops it on shutdown (useful for testing)
none: Takes no action (recommended for production)

For custom SQL initialization, create a schema.sql or import.sql file in the src/main/resources folder. Hibernate automatically executes these files during startup.¹⁵

Step 10: Data Type Mapping

PostgreSQL data types map to Java types through Hibernate:¹⁷

PostgreSQL Type	Java Type	JDBC Type
INTEGER	int, Integer	NUMERIC
VARCHAR	String	VARCHAR
BOOLEAN	boolean	BOOLEAN
TIMESTAMP	LocalDateTime	TIMESTAMP
BIGINT	long, Long	NUMERIC
DECIMAL	BigDecimal	NUMERIC
TEXT	String	LONGVARCHAR
DATE	LocalDate	DATE

Step 11: Lazy vs. Eager Loading

Control how related entities are fetched using @OneToMany, @ManyToOne, and @OneToOne annotations:¹⁸¹⁹

@Entity
@Table(name = "departments")
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    private List<Employee> employees;
}

@Entity
@Table(name = "employees")
public class Employee {
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "department_id")
    private Department department;
}

FetchType.LAZY (default for @OneToMany and @ManyToMany): Related data loads only when accessed, reducing memory consumption
FetchType.EAGER (default for @ManyToOne and @OneToOne): Related data loads immediately with the parent entity

Lazy loading is generally recommended for better performance with large datasets.¹⁸

Step 12: Exception Handling

Spring translates database exceptions into DataAccessException, a hierarchy of unchecked exceptions:²⁰

import org.springframework.dao.DataAccessException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

@RestControllerAdvice
public class GlobalExceptionHandler {
    @ExceptionHandler(DataAccessException.class)
    public ResponseEntity<?> handleDataAccessException(DataAccessException e) {
        return ResponseEntity.status(HttpStatus.BAD_REQUEST)
            .body("Database error: " + e.getMessage());
    }
}

Common subclasses include DataIntegrityViolationException (constraint violations) and BadSqlGrammarException (SQL syntax errors).²¹

Step 13: Advanced Features - JSONB Support

PostgreSQL's JSONB data type stores JSON data efficiently. Enable support by adding the Hibernate Types dependency:²²

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.3.4</version>
</dependency>

Then define JSONB columns:²³

@Entity
@Table(name = "products")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> attributes;
}

Best Practices

Implement these practices for robust PostgreSQL integration:

Always use prepared statements to prevent SQL injection when executing native queries
Define proper indexes on frequently queried columns for performance optimization
Use connection pooling (HikariCP) to manage database connections efficiently
Apply @Transactional at the service layer to maintain data consistency
Set appropriate DDL auto values - use validate or none in production environments
Implement proper exception handling using Spring's DataAccessException hierarchy
Use lazy loading for large collections to minimize memory and query overhead
Validate input data before persisting to ensure data integrity
Configure logging with spring.jpa.show-sql=true and logging.level.org.hibernate.SQL=DEBUG during development for debugging SQL statements
Use versioning tools like Flyway or Liquibase for production database migrations instead of relying on Hibernate's DDL auto generation

This comprehensive foundation enables you to build scalable, maintainable Spring Boot applications with PostgreSQL databases using modern Java practices and proven enterprise patterns. <span style="display:none">²⁴²⁵²⁶²⁷²⁸²⁹³⁰³¹³²³³³⁴³⁵³⁶³⁷³⁸³⁹⁴⁰⁴¹⁴²⁴³⁴⁴⁴⁵⁴⁶⁴⁷⁴⁸⁴⁹</span>