Spring Data JPA with PostgreSQL — Repository Pattern Done Right

The starter post gave you a running API. The REST API design post added validation and error handling. Now it’s time to persist data properly — with Spring Data JPA, PostgreSQL, and Flyway migrations.

Dependencies

Add JPA, PostgreSQL, and Flyway to your build.gradle.kts:

dependencies {
  implementation("org.springframework.boot:spring-boot-starter-data-jpa")
  implementation("org.springframework.boot:spring-boot-starter-web")
  implementation("org.flywaydb:flyway-core")
  implementation("org.flywaydb:flyway-database-postgresql")
  runtimeOnly("org.postgresql:postgresql")

  testImplementation("org.springframework.boot:spring-boot-starter-test")
  testImplementation("org.springframework.boot:spring-boot-testcontainers")
  testImplementation("org.testcontainers:postgresql")
}

Database configuration

application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: myapp
    password: secret
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        format_sql: true
  flyway:
    locations: classpath:db/migration

Set ddl-auto: validate — Hibernate checks that entities match the schema but never modifies it. Flyway handles schema changes.

Set open-in-view: false — the default true keeps a database connection open for the entire HTTP request, which is a performance trap.

Profiles for local development

# application-local.yml
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp_dev
    username: dev
    password: dev
  jpa:
    show-sql: true

Defining entities

Base entity with audit fields

package com.example.demo.domain

import jakarta.persistence.Column
import jakarta.persistence.EntityListeners
import jakarta.persistence.GeneratedValue
import jakarta.persistence.GenerationType
import jakarta.persistence.Id
import jakarta.persistence.MappedSuperclass
import org.springframework.data.annotation.CreatedDate
import org.springframework.data.annotation.LastModifiedDate
import org.springframework.data.jpa.domain.support.AuditingEntityListener
import java.time.Instant
import java.util.UUID

@MappedSuperclass
@EntityListeners(AuditingEntityListener::class)
abstract class BaseEntity(
  @Id
  @GeneratedValue(strategy = GenerationType.UUID)
  val id: UUID? = null,

  @CreatedDate
  @Column(nullable = false, updatable = false)
  var createdAt: Instant = Instant.now(),

  @LastModifiedDate
  @Column(nullable = false)
  var updatedAt: Instant = Instant.now()
)

Enable auditing in your configuration:

package com.example.demo.config

import org.springframework.context.annotation.Configuration
import org.springframework.data.jpa.repository.config.EnableJpaAuditing

@Configuration
@EnableJpaAuditing
class JpaConfig

Product entity

package com.example.demo.domain

import jakarta.persistence.Column
import jakarta.persistence.Entity
import jakarta.persistence.EnumType
import jakarta.persistence.Enumerated
import jakarta.persistence.Table
import java.math.BigDecimal

@Entity
@Table(name = "products")
class Product(
  @Column(nullable = false)
  var name: String,

  @Column(length = 1000)
  var description: String? = null,

  @Column(nullable = false, precision = 10, scale = 2)
  var price: BigDecimal,

  @Enumerated(EnumType.STRING)
  @Column(nullable = false)
  var status: ProductStatus = ProductStatus.ACTIVE,

  @Column(nullable = false)
  var stockQuantity: Int = 0
) : BaseEntity()

enum class ProductStatus {
  ACTIVE, INACTIVE, DISCONTINUED
}

Use class instead of data class for JPA entities. Data classes generate equals(), hashCode(), and toString() based on all properties, which conflicts with JPA’s proxy-based lazy loading.

Flyway migrations

Create migration files in src/main/resources/db/migration:

V1__create_products_table.sql

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description VARCHAR(1000),
  price NUMERIC(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_name ON products(name);

V2__add_category_to_products.sql

ALTER TABLE products ADD COLUMN category VARCHAR(100);
CREATE INDEX idx_products_category ON products(category);

Flyway runs migrations in order. Never edit a migration that’s already been applied — create a new one.

Repository pattern

Basic repository

package com.example.demo.repository

import com.example.demo.domain.Product
import com.example.demo.domain.ProductStatus
import org.springframework.data.jpa.repository.JpaRepository
import java.util.UUID

interface ProductRepository : JpaRepository<Product, UUID> {

  fun findByStatus(status: ProductStatus): List<Product>

  fun findByNameContainingIgnoreCase(name: String): List<Product>

  fun countByStatus(status: ProductStatus): Long
}

Spring Data generates the implementation from method names. No boilerplate.

Custom queries with @Query

When method names get unwieldy, use JPQL or native SQL:

package com.example.demo.repository

import com.example.demo.domain.Product
import com.example.demo.domain.ProductStatus
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param
import java.math.BigDecimal
import java.util.UUID

interface ProductRepository : JpaRepository<Product, UUID> {

  fun findByStatus(status: ProductStatus): List<Product>

  @Query("SELECT p FROM Product p WHERE p.price BETWEEN :min AND :max AND p.status = :status")
  fun findByPriceRangeAndStatus(
    @Param("min") min: BigDecimal,
    @Param("max") max: BigDecimal,
    @Param("status") status: ProductStatus
  ): List<Product>

  @Query(
    value = "SELECT * FROM products WHERE category = :category ORDER BY price DESC LIMIT :limit",
    nativeQuery = true
  )
  fun findTopByCategory(
    @Param("category") category: String,
    @Param("limit") limit: Int
  ): List<Product>

  @Modifying
  @Query("UPDATE Product p SET p.status = :status WHERE p.id IN :ids")
  fun updateStatusByIds(
    @Param("ids") ids: List<UUID>,
    @Param("status") status: ProductStatus
  ): Int
}

Use JPQL for portability. Use native queries when you need database-specific features.

Pagination and sorting

Paginated endpoint

package com.example.demo.controller

import com.example.demo.domain.ProductStatus
import com.example.demo.dto.ProductResponse
import com.example.demo.service.ProductService
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.web.PageableDefault
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.RestController

@RestController
@RequestMapping("/api/v1/products")
class ProductController(
  private val productService: ProductService
) {

  @GetMapping
  fun list(
    @RequestParam(required = false) status: ProductStatus?,
    @PageableDefault(size = 20, sort = ["createdAt"]) pageable: Pageable
  ): ResponseEntity<Page<ProductResponse>> {
    val page = productService.findAll(status, pageable)
    return ResponseEntity.ok(page)
  }
}

Repository with pagination

interface ProductRepository : JpaRepository<Product, UUID> {

  fun findByStatus(status: ProductStatus, pageable: Pageable): Page<Product>
}

Calling it

GET /api/v1/products?status=ACTIVE&page=0&size=10&sort=price,desc

Spring auto-resolves Pageable from query parameters. page is zero-indexed.

Service layer

package com.example.demo.service

import com.example.demo.domain.Product
import com.example.demo.domain.ProductStatus
import com.example.demo.dto.CreateProductRequest
import com.example.demo.dto.ProductResponse
import com.example.demo.repository.ProductRepository
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.UUID

@Service
@Transactional(readOnly = true)
class ProductService(
  private val productRepository: ProductRepository
) {

  fun findAll(status: ProductStatus?, pageable: Pageable): Page<ProductResponse> {
    val page = if (status != null) {
      productRepository.findByStatus(status, pageable)
    } else {
      productRepository.findAll(pageable)
    }
    return page.map { it.toResponse() }
  }

  fun findById(id: UUID): ProductResponse {
    val product = productRepository.findById(id)
      .orElseThrow { ProductNotFoundException(id) }
    return product.toResponse()
  }

  @Transactional
  fun create(request: CreateProductRequest): ProductResponse {
    val product = Product(
      name = request.name,
      description = request.description,
      price = request.price,
      stockQuantity = request.stockQuantity
    )
    return productRepository.save(product).toResponse()
  }

  @Transactional
  fun delete(id: UUID) {
    if (!productRepository.existsById(id)) {
      throw ProductNotFoundException(id)
    }
    productRepository.deleteById(id)
  }
}

class ProductNotFoundException(id: UUID) :
  RuntimeException("Product not found: $id")

Mark the class @Transactional(readOnly = true) and override with @Transactional on write methods. Read-only transactions skip dirty checking, which improves performance.

Projections — fetching only what you need

When you don’t need the full entity:

interface ProductSummary {
  val id: UUID
  val name: String
  val price: java.math.BigDecimal
  val status: ProductStatus
}

interface ProductRepository : JpaRepository<Product, UUID> {

  fun findByStatus(status: ProductStatus): List<ProductSummary>
}

Spring generates a proxy that only loads the projected fields. Useful for list views where you don’t need all columns.

Mapping entities to DTOs

package com.example.demo.dto

import com.example.demo.domain.ProductStatus
import java.math.BigDecimal
import java.time.Instant
import java.util.UUID

data class ProductResponse(
  val id: UUID,
  val name: String,
  val description: String?,
  val price: BigDecimal,
  val status: ProductStatus,
  val stockQuantity: Int,
  val createdAt: Instant,
  val updatedAt: Instant
)

fun com.example.demo.domain.Product.toResponse() = ProductResponse(
  id = id!!,
  name = name,
  description = description,
  price = price,
  status = status,
  stockQuantity = stockQuantity,
  createdAt = createdAt,
  updatedAt = updatedAt
)

Extension functions keep mapping logic out of the entity. No mapping framework needed.

Common mistakes

Using data class for entities — breaks proxy-based lazy loading. Use regular class.

Not setting open-in-view: false — holds database connections for the entire request lifecycle. Set it to false and handle lazy loading explicitly.

Editing applied Flyway migrations — causes checksum mismatches. Always create new migrations.

Missing indexes — add indexes for columns you filter or sort by. Check query plans with EXPLAIN ANALYZE.

N+1 queries — use @EntityGraph or fetch joins when loading associations:

@Query("SELECT p FROM Product p JOIN FETCH p.categories WHERE p.id = :id")
fun findByIdWithCategories(@Param("id") id: UUID): Product?

What’s next

You’ve got JPA, PostgreSQL, and Flyway working. Next: secure it with JWT authentication.