Spring Data Jpa

Spring Data JPA

Database access patterns with Spring Data JPA and Hibernate.

Use this skill when

• Creating JPA entities
• Setting up repositories
• Writing custom queries (JPQL, native, QueryDSL)
• Configuring entity relationships (OneToMany, ManyToOne, etc.)
• Implementing auditing (createdAt, updatedAt)
• Need pagination or sorting
• User mentions "database", "entity", "repository", "JPA", or "Hibernate"

Do not use this skill when

• User wants NoSQL (MongoDB, Redis)
• User needs raw JDBC without JPA
• User asks about database migrations (use Flyway/Liquibase specific guidance)

Dependencies

Add to pom.xml:

<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>

Configuration

application.yml

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST:localhost}:5432/${DB_NAME:myapp}
    username: ${DB_USER:postgres}
    password: ${DB_PASSWORD:}
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

  jpa:
    hibernate:
      ddl-auto: ${JPA_DDL_AUTO:validate}
    show-sql: ${JPA_SHOW_SQL:false}
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
        jdbc:
          time_zone: UTC
    open-in-view: false  # Recommended: disable OSIV

Enable JPA Auditing

package com.example.app.config;

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

@Configuration
@EnableJpaAuditing
public class JpaConfig {
}

Entity Patterns

BaseEntity (DRY principle)

package com.example.app.model;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import java.time.LocalDateTime;
import java.util.UUID;

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@Getter
@Setter
public abstract class BaseEntity {

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

    @CreatedDate
    @Column(name = "created_at", updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;
}

Simple Entity

package com.example.app.model;

import jakarta.persistence.*;
import jakarta.validation.constraints.*;
import lombok.*;

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_users_email", columnList = "email", unique = true)
})
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class User extends BaseEntity {

    @NotBlank
    @Size(min = 2, max = 100)
    @Column(nullable = false, length = 100)
    private String name;

    @NotBlank
    @Email
    @Column(nullable = false, unique = true)
    private String email;

    @Column(name = "password_hash")
    private String passwordHash;

    @Builder.Default
    @Column(nullable = false)
    private boolean active = true;

    @Enumerated(EnumType.STRING)
    @Column(length = 20)
    @Builder.Default
    private UserRole role = UserRole.USER;

    public enum UserRole {
        ADMIN, USER, GUEST
    }
}

Entity with Relationships

package com.example.app.model;

import jakarta.persistence.*;
import lombok.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "orders")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Order extends BaseEntity {

    @Column(name = "order_number", unique = true, nullable = false)
    private String orderNumber;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id", nullable = false)
    private User customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    @Builder.Default
    private List<OrderItem> items = new ArrayList<>();

    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    @Builder.Default
    private OrderStatus status = OrderStatus.PENDING;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal total;

    // Helper methods for bidirectional relationship
    public void addItem(OrderItem item) {
        items.add(item);
        item.setOrder(this);
    }

    public void removeItem(OrderItem item) {
        items.remove(item);
        item.setOrder(null);
    }

    public enum OrderStatus {
        PENDING, CONFIRMED, SHIPPED, DELIVERED, CANCELLED
    }
}

@Entity
@Table(name = "order_items")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class OrderItem extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;

    @Column(nullable = false)
    private int quantity;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal unitPrice;

    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal subtotal;
}

Repository Patterns

Basic Repository

package com.example.app.repository;

import com.example.app.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;
import java.util.List;
import java.util.UUID;

@Repository
public interface UserRepository extends JpaRepository<User, UUID> {

    // Query derivation - Spring generates the query
    Optional<User> findByEmail(String email);
    
    List<User> findByActiveTrue();
    
    List<User> findByRole(User.UserRole role);
    
    boolean existsByEmail(String email);
    
    List<User> findByNameContainingIgnoreCase(String name);
}

Repository with Custom Queries

package com.example.app.repository;

import com.example.app.model.Order;
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.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.UUID;

@Repository
public interface OrderRepository extends JpaRepository<Order, UUID> {

    // JPQL query
    @Query("SELECT o FROM Order o WHERE o.customer.id = :customerId ORDER BY o.createdAt DESC")
    List<Order> findByCustomerId(@Param("customerId") UUID customerId);

    // JPQL with JOIN FETCH (avoid N+1)
    @Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") UUID id);

    // JPQL with pagination
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    Page<Order> findByStatus(@Param("status") Order.OrderStatus status, Pageable pageable);

    // Native SQL query
    @Query(value = """
        SELECT o.* FROM orders o
        WHERE o.created_at >= :since
          AND o.status = 'DELIVERED'
        ORDER BY o.total DESC
        LIMIT :limit
        """, nativeQuery = true)
    List<Order> findTopDeliveredOrdersSince(
        @Param("since") LocalDateTime since,
        @Param("limit") int limit
    );

    // Count query
    @Query("SELECT COUNT(o) FROM Order o WHERE o.customer.id = :customerId AND o.status = :status")
    long countByCustomerAndStatus(
        @Param("customerId") UUID customerId,
        @Param("status") Order.OrderStatus status
    );

    // Projection query
    @Query("SELECT o.orderNumber, o.total, o.status FROM Order o WHERE o.customer.id = :customerId")
    List<Object[]> findOrderSummaryByCustomer(@Param("customerId") UUID customerId);
}

Projections (DTOs from queries)

// Interface-based projection
public interface OrderSummary {
    String getOrderNumber();
    BigDecimal getTotal();
    Order.OrderStatus getStatus();
    LocalDateTime getCreatedAt();
}

// In repository
@Query("SELECT o FROM Order o WHERE o.customer.id = :customerId")
List<OrderSummary> findSummaryByCustomer(@Param("customerId") UUID customerId);

// Record-based projection (class-based)
public record OrderDTO(String orderNumber, BigDecimal total, String status) {}

@Query("SELECT new com.example.app.dto.OrderDTO(o.orderNumber, o.total, CAST(o.status AS string)) FROM Order o")
List<OrderDTO> findAllAsDTO();

Service Layer Pattern

package com.example.app.service;

import com.example.app.dto.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import java.util.UUID;

public interface UserService {
    UserResponse create(CreateUserRequest request);
    UserResponse findById(UUID id);
    Page<UserResponse> findAll(Pageable pageable);
    UserResponse update(UUID id, UpdateUserRequest request);
    void delete(UUID id);
}

package com.example.app.service.impl;

import com.example.app.dto.*;
import com.example.app.exception.*;
import com.example.app.model.User;
import com.example.app.repository.UserRepository;
import com.example.app.service.UserService;
import lombok.RequiredArgsConstructor;
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
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class UserServiceImpl implements UserService {

    private final UserRepository repository;

    @Override
    @Transactional
    public UserResponse create(CreateUserRequest request) {
        if (repository.existsByEmail(request.email())) {
            throw new DuplicateEntityException("Email already exists: " + request.email());
        }
        
        User user = User.builder()
            .name(request.name())
            .email(request.email().toLowerCase())
            .build();
        
        User saved = repository.save(user);
        return toResponse(saved);
    }

    @Override
    public UserResponse findById(UUID id) {
        return repository.findById(id)
            .map(this::toResponse)
            .orElseThrow(() -> new EntityNotFoundException("User", id));
    }

    @Override
    public Page<UserResponse> findAll(Pageable pageable) {
        return repository.findAll(pageable)
            .map(this::toResponse);
    }

    @Override
    @Transactional
    public UserResponse update(UUID id, UpdateUserRequest request) {
        User user = repository.findById(id)
            .orElseThrow(() -> new EntityNotFoundException("User", id));
        
        if (request.name() != null) {
            user.setName(request.name());
        }
        if (request.email() != null) {
            user.setEmail(request.email().toLowerCase());
        }
        
        return toResponse(repository.save(user));
    }

    @Override
    @Transactional
    public void delete(UUID id) {
        if (!repository.existsById(id)) {
            throw new EntityNotFoundException("User", id);
        }
        repository.deleteById(id);
    }

    private UserResponse toResponse(User user) {
        return new UserResponse(
            user.getId(),
            user.getName(),
            user.getEmail(),
            user.getCreatedAt()
        );
    }
}

Pagination and Sorting

// Controller
@GetMapping
public Page<UserResponse> findAll(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "createdAt,desc") String[] sort) {
    
    Sort sorting = Sort.by(
        Arrays.stream(sort)
            .map(s -> {
                String[] parts = s.split(",");
                return parts.length > 1 && parts[1].equalsIgnoreCase("desc")
                    ? Sort.Order.desc(parts[0])
                    : Sort.Order.asc(parts[0]);
            })
            .toList()
    );
    
    Pageable pageable = PageRequest.of(page, size, sorting);
    return userService.findAll(pageable);
}

// Or use Spring's automatic binding
@GetMapping
public Page<UserResponse> findAll(Pageable pageable) {
    return userService.findAll(pageable);
}
// Requests: /users?page=0&size=20&sort=name,asc&sort=createdAt,desc

Avoiding N+1 Problem

// BAD: N+1 queries
List<Order> orders = orderRepository.findAll();
for (Order order : orders) {
    // Each access triggers a query!
    System.out.println(order.getCustomer().getName());
}

// GOOD: JOIN FETCH
@Query("SELECT o FROM Order o JOIN FETCH o.customer")
List<Order> findAllWithCustomer();

// GOOD: EntityGraph
@EntityGraph(attributePaths = {"customer", "items"})
List<Order> findAll();

// GOOD: Batch fetching in application.yml
spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 20

Code Quality Checklist

• All entities extend BaseEntity
• Relationships use FetchType.LAZY by default
• JOIN FETCH used to avoid N+1
• Service methods are @Transactional
• Read-only operations use @Transactional(readOnly = true)
• DTOs used instead of exposing entities
• Indexes defined for frequently queried columns
• open-in-view disabled in config

References

• See references/relationship-patterns.md for relationship mappings
• See examples/ for complete code examples