What is the N+1 query problem in JPA and how do I fix it?

N+1 happens when JPA runs 1 query to load entities, then N additional queries to load each entity's relations. Fix it with JOIN FETCH in JPQL, @EntityGraph annotations, or batch fetching via @BatchSize.

What is the difference between FetchType.LAZY and FetchType.EAGER?

LAZY loads related entities only when accessed, reducing initial query cost. EAGER loads them immediately with the parent entity. Always default to LAZY and use JOIN FETCH when you actually need the data.

How do I write custom queries in Spring Data JPA?

Use derived query methods (findByEmailAndStatus), @Query with JPQL or native SQL, Specifications for dynamic queries, or QueryDSL for type-safe complex queries. Choose based on query complexity.

How do I handle database migrations with Spring Data JPA?

Never use hibernate.ddl-auto in production. Use Flyway or Liquibase for versioned, repeatable migrations. They track which migrations have run and apply new ones automatically on startup.

Spring Data JPA: Advanced Queries and Best Practices

Introduction to Spring Data JPA

Spring Data JPA simplifies database operations by providing a repository abstraction over JPA. You get CRUD operations automatically and can define custom queries easily.

Entity Mapping

Basic Entity

@Entity
@Table(name = "products")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Product {

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

    @Column(nullable = false, length = 100)
    private String name;

    @Column(columnDefinition = "TEXT")
    private String description;

    @Column(precision = 10, scale = 2)
    private BigDecimal price;

    @Enumerated(EnumType.STRING)
    private ProductStatus status;

    @CreationTimestamp
    private LocalDateTime createdAt;

    @UpdateTimestamp
    private LocalDateTime updatedAt;

    @Version
    private Long version;
}

Relationships

@Entity
@Table(name = "orders")
public class Order {

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

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

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

    @ManyToMany
    @JoinTable(
        name = "order_tags",
        joinColumns = @JoinColumn(name = "order_id"),
        inverseJoinColumns = @JoinColumn(name = "tag_id")
    )
    private Set<Tag> tags = new HashSet<>();

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

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

Repository Methods

Query Methods

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Simple queries
    List<Product> findByName(String name);
    Optional<Product> findByNameIgnoreCase(String name);
    List<Product> findByStatus(ProductStatus status);

    // Comparison
    List<Product> findByPriceGreaterThan(BigDecimal price);
    List<Product> findByPriceBetween(BigDecimal min, BigDecimal max);

    // String matching
    List<Product> findByNameContaining(String keyword);
    List<Product> findByNameStartingWith(String prefix);
    List<Product> findByDescriptionContainingIgnoreCase(String keyword);

    // Null checks
    List<Product> findByDescriptionIsNull();
    List<Product> findByDescriptionIsNotNull();

    // Collection membership
    List<Product> findByStatusIn(Collection<ProductStatus> statuses);
    List<Product> findByIdIn(List<Long> ids);

    // Boolean
    List<Product> findByActiveTrue();

    // Ordering
    List<Product> findByStatusOrderByPriceDesc(ProductStatus status);
    List<Product> findTop10ByStatusOrderByCreatedAtDesc(ProductStatus status);

    // Limiting
    Product findFirstByOrderByPriceDesc();
    List<Product> findTop5ByStatusOrderByPriceAsc(ProductStatus status);

    // Counting
    long countByStatus(ProductStatus status);
    boolean existsByName(String name);

    // Delete
    void deleteByStatus(ProductStatus status);
    long deleteByCreatedAtBefore(LocalDateTime date);
}

JPQL Queries

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o WHERE o.customer.id = :customerId")
    List<Order> findByCustomerId(@Param("customerId") Long customerId);

    @Query("SELECT o FROM Order o WHERE o.totalAmount > :amount AND o.status = :status")
    List<Order> findExpensiveOrdersByStatus(
        @Param("amount") BigDecimal amount,
        @Param("status") OrderStatus status
    );

    @Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") Long id);

    @Query("SELECT o FROM Order o LEFT JOIN FETCH o.customer WHERE o.id IN :ids")
    List<Order> findAllByIdWithCustomers(@Param("ids") List<Long> ids);

    @Query("SELECT DISTINCT o FROM Order o " +
           "JOIN FETCH o.items i " +
           "WHERE o.customer.id = :customerId")
    List<Order> findAllByCustomerWithItems(@Param("customerId") Long customerId);

    // Aggregate queries
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.customer.id = :customerId")
    BigDecimal getTotalSpentByCustomer(@Param("customerId") Long customerId);

    @Query("SELECT COUNT(o) FROM Order o WHERE o.createdAt >= :date")
    long countOrdersSince(@Param("date") LocalDateTime date);

    @Query("SELECT o.status, COUNT(o) FROM Order o GROUP BY o.status")
    List<Object[]> countByStatus();
}

Native Queries

@Query(value = "SELECT * FROM orders WHERE YEAR(created_at) = :year",
       nativeQuery = true)
List<Order> findByYear(@Param("year") int year);

@Query(value = "SELECT * FROM products WHERE MATCH(name, description) AGAINST(:term)",
       nativeQuery = true)
List<Product> fullTextSearch(@Param("term") String term);

@Modifying
@Query(value = "UPDATE products SET view_count = view_count + 1 WHERE id = :id",
       nativeQuery = true)
void incrementViewCount(@Param("id") Long id);

Projections

Interface Projection

public interface ProductSummary {
    Long getId();
    String getName();
    BigDecimal getPrice();
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    List<ProductSummary> findByStatus(ProductStatus status);

    @Query("SELECT p.id as id, p.name as name, p.price as price " +
           "FROM Product p WHERE p.category.id = :categoryId")
    List<ProductSummary> findSummaryByCategory(@Param("categoryId") Long categoryId);
}

Class Projection (DTO)

@Data
@AllArgsConstructor
public class ProductDTO {
    private Long id;
    private String name;
    private BigDecimal price;
    private String categoryName;
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT new com.example.dto.ProductDTO(p.id, p.name, p.price, c.name) " +
           "FROM Product p JOIN p.category c")
    List<ProductDTO> findAllProductDTOs();
}

Dynamic Projections

public interface ProductRepository extends JpaRepository<Product, Long> {

    <T> List<T> findByStatus(ProductStatus status, Class<T> type);

    <T> Optional<T> findById(Long id, Class<T> type);
}

// Usage
List<ProductSummary> summaries = productRepository.findByStatus(ACTIVE, ProductSummary.class);
List<Product> full = productRepository.findByStatus(ACTIVE, Product.class);

Specifications (Dynamic Queries)

public class ProductSpecifications {

    public static Specification<Product> hasName(String name) {
        return (root, query, cb) -> {
            if (name == null || name.isEmpty()) {
                return cb.conjunction();
            }
            return cb.like(cb.lower(root.get("name")), "%" + name.toLowerCase() + "%");
        };
    }

    public static Specification<Product> hasStatus(ProductStatus status) {
        return (root, query, cb) -> {
            if (status == null) {
                return cb.conjunction();
            }
            return cb.equal(root.get("status"), status);
        };
    }

    public static Specification<Product> priceBetween(BigDecimal min, BigDecimal max) {
        return (root, query, cb) -> {
            if (min == null && max == null) {
                return cb.conjunction();
            }
            if (min == null) {
                return cb.lessThanOrEqualTo(root.get("price"), max);
            }
            if (max == null) {
                return cb.greaterThanOrEqualTo(root.get("price"), min);
            }
            return cb.between(root.get("price"), min, max);
        };
    }

    public static Specification<Product> inCategory(Long categoryId) {
        return (root, query, cb) -> {
            if (categoryId == null) {
                return cb.conjunction();
            }
            return cb.equal(root.get("category").get("id"), categoryId);
        };
    }
}

public interface ProductRepository extends JpaRepository<Product, Long>,
                                          JpaSpecificationExecutor<Product> {
}

// Usage
@Service
@RequiredArgsConstructor
public class ProductService {

    private final ProductRepository productRepository;

    public Page<Product> searchProducts(ProductSearchCriteria criteria, Pageable pageable) {
        Specification<Product> spec = Specification
            .where(ProductSpecifications.hasName(criteria.getName()))
            .and(ProductSpecifications.hasStatus(criteria.getStatus()))
            .and(ProductSpecifications.priceBetween(criteria.getMinPrice(), criteria.getMaxPrice()))
            .and(ProductSpecifications.inCategory(criteria.getCategoryId()));

        return productRepository.findAll(spec, pageable);
    }
}

Pagination and Sorting

// Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    Page<Product> findByStatus(ProductStatus status, Pageable pageable);

    @Query("SELECT p FROM Product p WHERE p.category.id = :categoryId")
    Slice<Product> findByCategorySliced(@Param("categoryId") Long categoryId, Pageable pageable);
}

// Service
@Service
public class ProductService {

    public Page<Product> getProducts(int page, int size, String sortBy, String direction) {
        Sort sort = direction.equalsIgnoreCase("desc")
            ? Sort.by(sortBy).descending()
            : Sort.by(sortBy).ascending();

        Pageable pageable = PageRequest.of(page, size, sort);

        return productRepository.findAll(pageable);
    }

    public Page<Product> getProductsMultiSort(int page, int size) {
        Sort sort = Sort.by(
            Sort.Order.desc("featured"),
            Sort.Order.asc("name")
        );

        return productRepository.findAll(PageRequest.of(page, size, sort));
    }
}

Auditing

@Configuration
@EnableJpaAuditing
public class JpaConfig {

    @Bean
    public AuditorAware<String> auditorProvider() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext())
            .map(SecurityContext::getAuthentication)
            .filter(Authentication::isAuthenticated)
            .map(Authentication::getName);
    }
}

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

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    private LocalDateTime updatedAt;

    @CreatedBy
    @Column(updatable = false)
    private String createdBy;

    @LastModifiedBy
    private String updatedBy;
}

@Entity
public class Product extends BaseEntity {
    // fields...
}

Performance Optimization

N+1 Problem Solution

// Problem: N+1 queries
List<Order> orders = orderRepository.findAll();
orders.forEach(o -> System.out.println(o.getCustomer().getName())); // N additional queries!

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

// Solution 2: Entity Graph
@EntityGraph(attributePaths = {"customer", "items"})
List<Order> findByStatus(OrderStatus status);

// Solution 3: Batch fetching (in entity)
@OneToMany(mappedBy = "order")
@BatchSize(size = 20)
private List<OrderItem> items;

Batch Operations

@Modifying
@Query("UPDATE Product p SET p.price = p.price * :multiplier WHERE p.category.id = :categoryId")
int updatePricesByCategory(@Param("categoryId") Long categoryId,
                          @Param("multiplier") BigDecimal multiplier);

@Modifying
@Query("DELETE FROM Product p WHERE p.status = :status AND p.createdAt < :date")
int deleteOldInactiveProducts(@Param("status") ProductStatus status,
                              @Param("date") LocalDateTime date);

Read-Only Transactions

@Service
@Transactional(readOnly = true)
public class ProductQueryService {

    public List<Product> getProducts() {
        return productRepository.findAll();
    }

    @Transactional // Override for write operations
    public Product save(Product product) {
        return productRepository.save(product);
    }
}

Summary

Feature	Use Case
Query methods	Simple queries
@Query JPQL	Complex queries
Native queries	Database-specific
Specifications	Dynamic queries
Projections	Partial data
EntityGraph	Eager loading

Spring Data JPA simplifies database operations while providing powerful customization options.