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:

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

yaml

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

java

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)

java

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

java

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

java

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

java

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

java

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)

java

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

java

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);
}

java

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

java

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

java

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