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.