# Spring Data Jpa

> Spring Data JPA patterns for database access including entities, repositories, queries, relationships, and auditing. Use when working with databases in Spring Boot applications.

- Skill: `gazolla/spring-data-jpa` (Agent Skill, multi-file: 14 files)
- Install (CLI): `npx skillmds@latest add gazolla/spring-data-jpa`
- Raw SKILL.md: https://api.skillmd.com/api/skills/gazolla/spring-data-jpa/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: gazolla (https://skillmd.com/u/gazolla)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/gazolla/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`:

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