Introduction
PostgreSQL is one of the most powerful and popular open-source relational databases available today, powering everything from startups to Fortune 500 companies. Its advanced features like JSONB support, full-text search, and powerful indexing make it an excellent choice for modern applications.
Whether you’re building a CRUD API, a SaaS platform, or a microservice, connecting Spring Boot to PostgreSQL is a common and essential skill for backend Java developers. Spring Boot’s auto-configuration and Spring Data JPA make this integration seamless and productive.
In this comprehensive guide, you’ll learn how to connect a Spring Boot 3 application to PostgreSQL, set up proper configuration for different environments, define robust entity models, implement service layers, and perform CRUD operations with best practices for production applications.
Prerequisites
- Java 17 or higher (Java 21 LTS recommended)
- Spring Boot 3.x
- PostgreSQL 14+ installed locally or via Docker
- IDE (IntelliJ IDEA, VSCode with Java extensions, or Eclipse)
- Maven or Gradle build tool
Step 1: Create a New Spring Boot Project
Use Spring Initializr or create a project with the following dependencies:
<!-- pom.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.2.0</version>
</parent>
<groupId>com.example</groupId>
<artifactId>spring-postgresql-demo</artifactId>
<version>1.0.0</version>
<properties>
<java.version>21</java.version>
</properties>
<dependencies>
<!-- Spring Web for REST APIs -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Data JPA for database operations -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- PostgreSQL Driver -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Validation -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<!-- Lombok for boilerplate reduction -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- DevTools for hot reload -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<!-- Testing -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- Testcontainers for integration tests -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<version>1.19.3</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>Step 2: Configure PostgreSQL
Create environment-specific configuration files:
# src/main/resources/application.yml
spring:
profiles:
active: ${SPRING_PROFILES_ACTIVE:dev}
---
# Development profile
spring:
config:
activate:
on-profile: dev
datasource:
url: jdbc:postgresql://localhost:5432/demo_db
username: postgres
password: ${DB_PASSWORD:password}
driver-class-name: org.postgresql.Driver
# HikariCP connection pool settings
hikari:
maximum-pool-size: 10
minimum-idle: 5
idle-timeout: 300000
connection-timeout: 20000
max-lifetime: 1200000
jpa:
hibernate:
ddl-auto: update # Use 'validate' in production
show-sql: true
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialect
format_sql: true
jdbc:
batch_size: 25
order_inserts: true
order_updates: true
logging:
level:
org.hibernate.SQL: DEBUG
org.hibernate.type.descriptor.sql.BasicBinder: TRACE
---
# Production profile
spring:
config:
activate:
on-profile: prod
datasource:
url: ${DATABASE_URL}
username: ${DB_USERNAME}
password: ${DB_PASSWORD}
hikari:
maximum-pool-size: 20
minimum-idle: 10
jpa:
hibernate:
ddl-auto: validate # Never use update/create in production
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialectStep 3: Create Entity Models
// src/main/java/com/example/demo/entity/Product.java
package com.example.demo.entity;
import jakarta.persistence.*;
import jakarta.validation.constraints.*;
import lombok.*;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import java.math.BigDecimal;
import java.time.LocalDateTime;
@Entity
@Table(name = "products", indexes = {
@Index(name = "idx_product_name", columnList = "name"),
@Index(name = "idx_product_category", columnList = "category_id")
})
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotBlank(message = "Product name is required")
@Size(min = 2, max = 100, message = "Name must be between 2 and 100 characters")
@Column(nullable = false, length = 100)
private String name;
@Size(max = 1000, message = "Description cannot exceed 1000 characters")
@Column(length = 1000)
private String description;
@NotNull(message = "Price is required")
@DecimalMin(value = "0.01", message = "Price must be greater than 0")
@Column(nullable = false, precision = 10, scale = 2)
private BigDecimal price;
@Min(value = 0, message = "Stock cannot be negative")
@Column(nullable = false)
@Builder.Default
private Integer stockQuantity = 0;
@NotBlank(message = "SKU is required")
@Column(nullable = false, unique = true, length = 50)
private String sku;
@Column(nullable = false)
@Builder.Default
private Boolean active = true;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "category_id")
private Category category;
@CreationTimestamp
@Column(updatable = false)
private LocalDateTime createdAt;
@UpdateTimestamp
private LocalDateTime updatedAt;
@Version
private Long version; // Optimistic locking
}
// src/main/java/com/example/demo/entity/Category.java
package com.example.demo.entity;
import jakarta.persistence.*;
import jakarta.validation.constraints.NotBlank;
import lombok.*;
import java.util.ArrayList;
import java.util.List;
@Entity
@Table(name = "categories")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Category {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotBlank(message = "Category name is required")
@Column(nullable = false, unique = true, length = 50)
private String name;
@Column(length = 500)
private String description;
@OneToMany(mappedBy = "category", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@Builder.Default
private List products = new ArrayList<>();
} Step 4: Create Repositories
// src/main/java/com/example/demo/repository/ProductRepository.java
package com.example.demo.repository;
import com.example.demo.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
@Repository
public interface ProductRepository extends JpaRepository, JpaSpecificationExecutor {
// Find by unique field
Optional findBySku(String sku);
// Check existence
boolean existsBySku(String sku);
// Find by category with pagination
Page findByCategoryId(Long categoryId, Pageable pageable);
// Find active products
List findByActiveTrue();
// Custom query: Find products by price range
@Query("SELECT p FROM Product p WHERE p.price BETWEEN :minPrice AND :maxPrice AND p.active = true")
List findByPriceRange(
@Param("minPrice") BigDecimal minPrice,
@Param("maxPrice") BigDecimal maxPrice
);
// Native query: Full-text search (PostgreSQL specific)
@Query(value = """
SELECT * FROM products
WHERE active = true
AND to_tsvector('english', name || ' ' || COALESCE(description, ''))
@@ plainto_tsquery('english', :searchTerm)
""", nativeQuery = true)
List fullTextSearch(@Param("searchTerm") String searchTerm);
// Bulk update
@Modifying
@Query("UPDATE Product p SET p.active = false WHERE p.stockQuantity = 0")
int deactivateOutOfStockProducts();
// Projection: Get only needed fields
@Query("SELECT p.id as id, p.name as name, p.price as price FROM Product p WHERE p.active = true")
List findAllProductSummaries();
interface ProductSummary {
Long getId();
String getName();
BigDecimal getPrice();
}
}
// src/main/java/com/example/demo/repository/CategoryRepository.java
package com.example.demo.repository;
import com.example.demo.entity.Category;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
@Repository
public interface CategoryRepository extends JpaRepository {
Optional findByName(String name);
@Query("SELECT c FROM Category c LEFT JOIN FETCH c.products WHERE c.id = :id")
Optional findByIdWithProducts(Long id);
// Get categories with product count
@Query("""
SELECT c.name, COUNT(p.id)
FROM Category c
LEFT JOIN c.products p
GROUP BY c.id, c.name
""")
List getCategoryProductCounts();
} Step 5: Create Service Layer
// src/main/java/com/example/demo/service/ProductService.java
package com.example.demo.service;
import com.example.demo.dto.CreateProductRequest;
import com.example.demo.dto.ProductResponse;
import com.example.demo.dto.UpdateProductRequest;
import com.example.demo.entity.Category;
import com.example.demo.entity.Product;
import com.example.demo.exception.ResourceNotFoundException;
import com.example.demo.exception.DuplicateResourceException;
import com.example.demo.repository.CategoryRepository;
import com.example.demo.repository.ProductRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@RequiredArgsConstructor
@Slf4j
@Transactional(readOnly = true)
public class ProductService {
private final ProductRepository productRepository;
private final CategoryRepository categoryRepository;
public Page getAllProducts(Pageable pageable) {
return productRepository.findAll(pageable)
.map(this::mapToResponse);
}
public ProductResponse getProductById(Long id) {
return productRepository.findById(id)
.map(this::mapToResponse)
.orElseThrow(() -> new ResourceNotFoundException("Product", "id", id));
}
public ProductResponse getProductBySku(String sku) {
return productRepository.findBySku(sku)
.map(this::mapToResponse)
.orElseThrow(() -> new ResourceNotFoundException("Product", "sku", sku));
}
@Transactional
public ProductResponse createProduct(CreateProductRequest request) {
// Check for duplicate SKU
if (productRepository.existsBySku(request.getSku())) {
throw new DuplicateResourceException("Product with SKU " + request.getSku() + " already exists");
}
Category category = null;
if (request.getCategoryId() != null) {
category = categoryRepository.findById(request.getCategoryId())
.orElseThrow(() -> new ResourceNotFoundException("Category", "id", request.getCategoryId()));
}
Product product = Product.builder()
.name(request.getName())
.description(request.getDescription())
.price(request.getPrice())
.stockQuantity(request.getStockQuantity())
.sku(request.getSku())
.active(true)
.category(category)
.build();
Product saved = productRepository.save(product);
log.info("Created product with id: {} and sku: {}", saved.getId(), saved.getSku());
return mapToResponse(saved);
}
@Transactional
public ProductResponse updateProduct(Long id, UpdateProductRequest request) {
Product product = productRepository.findById(id)
.orElseThrow(() -> new ResourceNotFoundException("Product", "id", id));
// Check SKU uniqueness if changed
if (request.getSku() != null && !request.getSku().equals(product.getSku())) {
if (productRepository.existsBySku(request.getSku())) {
throw new DuplicateResourceException("Product with SKU " + request.getSku() + " already exists");
}
product.setSku(request.getSku());
}
if (request.getName() != null) product.setName(request.getName());
if (request.getDescription() != null) product.setDescription(request.getDescription());
if (request.getPrice() != null) product.setPrice(request.getPrice());
if (request.getStockQuantity() != null) product.setStockQuantity(request.getStockQuantity());
if (request.getActive() != null) product.setActive(request.getActive());
if (request.getCategoryId() != null) {
Category category = categoryRepository.findById(request.getCategoryId())
.orElseThrow(() -> new ResourceNotFoundException("Category", "id", request.getCategoryId()));
product.setCategory(category);
}
Product updated = productRepository.save(product);
log.info("Updated product with id: {}", updated.getId());
return mapToResponse(updated);
}
@Transactional
public void deleteProduct(Long id) {
if (!productRepository.existsById(id)) {
throw new ResourceNotFoundException("Product", "id", id);
}
productRepository.deleteById(id);
log.info("Deleted product with id: {}", id);
}
public List searchProducts(String searchTerm) {
return productRepository.fullTextSearch(searchTerm)
.stream()
.map(this::mapToResponse)
.toList();
}
private ProductResponse mapToResponse(Product product) {
return ProductResponse.builder()
.id(product.getId())
.name(product.getName())
.description(product.getDescription())
.price(product.getPrice())
.stockQuantity(product.getStockQuantity())
.sku(product.getSku())
.active(product.getActive())
.categoryName(product.getCategory() != null ? product.getCategory().getName() : null)
.createdAt(product.getCreatedAt())
.updatedAt(product.getUpdatedAt())
.build();
}
} Step 6: Create REST Controller
// src/main/java/com/example/demo/controller/ProductController.java
package com.example.demo.controller;
import com.example.demo.dto.CreateProductRequest;
import com.example.demo.dto.ProductResponse;
import com.example.demo.dto.UpdateProductRequest;
import com.example.demo.service.ProductService;
import jakarta.validation.Valid;
import lombok.RequiredArgsConstructor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.web.PageableDefault;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/api/v1/products")
@RequiredArgsConstructor
public class ProductController {
private final ProductService productService;
@GetMapping
public ResponseEntity> getAllProducts(
@PageableDefault(size = 20, sort = "createdAt", direction = Sort.Direction.DESC)
Pageable pageable) {
return ResponseEntity.ok(productService.getAllProducts(pageable));
}
@GetMapping("/{id}")
public ResponseEntity getProductById(@PathVariable Long id) {
return ResponseEntity.ok(productService.getProductById(id));
}
@GetMapping("/sku/{sku}")
public ResponseEntity getProductBySku(@PathVariable String sku) {
return ResponseEntity.ok(productService.getProductBySku(sku));
}
@GetMapping("/search")
public ResponseEntity> searchProducts(
@RequestParam String q) {
return ResponseEntity.ok(productService.searchProducts(q));
}
@PostMapping
public ResponseEntity createProduct(
@Valid @RequestBody CreateProductRequest request) {
return ResponseEntity
.status(HttpStatus.CREATED)
.body(productService.createProduct(request));
}
@PutMapping("/{id}")
public ResponseEntity updateProduct(
@PathVariable Long id,
@Valid @RequestBody UpdateProductRequest request) {
return ResponseEntity.ok(productService.updateProduct(id, request));
}
@DeleteMapping("/{id}")
public ResponseEntity deleteProduct(@PathVariable Long id) {
productService.deleteProduct(id);
return ResponseEntity.noContent().build();
}
} Step 7: DTOs and Exception Handling
// src/main/java/com/example/demo/dto/CreateProductRequest.java
package com.example.demo.dto;
import jakarta.validation.constraints.*;
import lombok.Data;
import java.math.BigDecimal;
@Data
public class CreateProductRequest {
@NotBlank(message = "Name is required")
@Size(min = 2, max = 100)
private String name;
@Size(max = 1000)
private String description;
@NotNull(message = "Price is required")
@DecimalMin(value = "0.01")
private BigDecimal price;
@Min(0)
private Integer stockQuantity = 0;
@NotBlank(message = "SKU is required")
@Size(max = 50)
private String sku;
private Long categoryId;
}
// src/main/java/com/example/demo/dto/ProductResponse.java
package com.example.demo.dto;
import lombok.Builder;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;
@Data
@Builder
public class ProductResponse {
private Long id;
private String name;
private String description;
private BigDecimal price;
private Integer stockQuantity;
private String sku;
private Boolean active;
private String categoryName;
private LocalDateTime createdAt;
private LocalDateTime updatedAt;
}
// src/main/java/com/example/demo/exception/GlobalExceptionHandler.java
package com.example.demo.exception;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.FieldError;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;
@RestControllerAdvice
@Slf4j
public class GlobalExceptionHandler {
@ExceptionHandler(ResourceNotFoundException.class)
public ResponseEntity handleResourceNotFound(ResourceNotFoundException ex) {
log.error("Resource not found: {}", ex.getMessage());
return ResponseEntity.status(HttpStatus.NOT_FOUND)
.body(new ErrorResponse(HttpStatus.NOT_FOUND.value(), ex.getMessage(), LocalDateTime.now()));
}
@ExceptionHandler(DuplicateResourceException.class)
public ResponseEntity handleDuplicateResource(DuplicateResourceException ex) {
log.error("Duplicate resource: {}", ex.getMessage());
return ResponseEntity.status(HttpStatus.CONFLICT)
.body(new ErrorResponse(HttpStatus.CONFLICT.value(), ex.getMessage(), LocalDateTime.now()));
}
@ExceptionHandler(MethodArgumentNotValidException.class)
public ResponseEntity handleValidationErrors(MethodArgumentNotValidException ex) {
Map errors = new HashMap<>();
ex.getBindingResult().getAllErrors().forEach(error -> {
String fieldName = ((FieldError) error).getField();
String errorMessage = error.getDefaultMessage();
errors.put(fieldName, errorMessage);
});
return ResponseEntity.status(HttpStatus.BAD_REQUEST)
.body(new ValidationErrorResponse(HttpStatus.BAD_REQUEST.value(), "Validation failed", errors, LocalDateTime.now()));
}
public record ErrorResponse(int status, String message, LocalDateTime timestamp) {}
public record ValidationErrorResponse(int status, String message, Map errors, LocalDateTime timestamp) {}
} PostgreSQL Setup with Docker
# docker-compose.yml
version: '3.8'
services:
postgres:
image: postgres:16-alpine
container_name: demo_postgres
environment:
POSTGRES_DB: demo_db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 10s
timeout: 5s
retries: 5
volumes:
postgres_data:-- init.sql: Initialize database with extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For fuzzy search
-- Create categories table
CREATE TABLE IF NOT EXISTS categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(500)
);
-- Insert sample categories
INSERT INTO categories (name, description) VALUES
('Electronics', 'Electronic devices and accessories'),
('Clothing', 'Apparel and fashion items'),
('Books', 'Books and publications')
ON CONFLICT (name) DO NOTHING;Common Mistakes to Avoid
Using ddl-auto=update in production: Always use validate in production and manage schema changes with Flyway or Liquibase migrations.
N+1 query problems: Use JOIN FETCH or @EntityGraph when loading entities with relationships to avoid multiple queries.
Exposing entities directly: Always use DTOs for API responses to control what data is exposed and avoid serialization issues.
Missing indexes: Always add indexes for frequently queried columns. Use EXPLAIN ANALYZE to identify slow queries.
Connection pool exhaustion: Configure HikariCP properly based on your workload. Monitor connection usage in production.
Not using @Transactional properly: Mark read-only methods with @Transactional(readOnly = true) for performance optimization.
Conclusion
Spring Boot and PostgreSQL work seamlessly together—especially when paired with Spring Data JPA, which removes the boilerplate of database interaction. This setup is perfect for REST APIs, internal tools, and full-stack applications needing robust relational data with advanced PostgreSQL features.
The combination provides production-ready capabilities including connection pooling, transaction management, and powerful query capabilities. Add proper exception handling, validation, and DTOs for a professional-grade API.
For more Spring Boot patterns, check out our guide on global exception handling in Spring Boot. For deployment, see our guide on deploying Spring Boot apps to Docker and Kubernetes.
