SQL Jpa

SQL & JPA Queries Skill

Write correct, efficient JPA mappings, JPQL queries, and Spring Data patterns for Spring Boot applications.

Entity Relationship Mapping

One-to-Many (most common)

// Parent (One side)
@Entity
public class Order {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

    // Helper methods to maintain bidirectional consistency
    public void addItem(OrderItem item) {
        items.add(item);
        item.setOrder(this);
    }
    public void removeItem(OrderItem item) {
        items.remove(item);
        item.setOrder(null);
    }
}

// Child (Many side)
@Entity
public class OrderItem {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

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

Many-to-Many

@Entity
public class Student {
    @ManyToMany
    @JoinTable(
        name = "student_course",
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id")
    )
    private Set<Course> courses = new HashSet<>();
}

Fetch Strategy Rules

• @ManyToOne → always FetchType.LAZY (EAGER causes performance issues)
• @OneToMany → default is LAZY, keep it
• @ManyToMany → always LAZY
• Load eagerly only via JOIN FETCH in specific queries where needed

Spring Data Repository Patterns

Derived Query Methods

// Simple conditions
Optional<User> findByEmail(String email);
List<User> findByStatus(UserStatus status);
boolean existsByEmail(String email);
long countByStatus(UserStatus status);

// Combined conditions
List<User> findByStatusAndCreatedAtAfter(UserStatus status, LocalDateTime date);
List<User> findByNameContainingIgnoreCase(String name);

// Ordering
List<Product> findByCategory_IdOrderByPriceAsc(Long categoryId);

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

JPQL Queries

// Fetch join to avoid N+1
@Query("SELECT o FROM Order o LEFT JOIN FETCH o.items WHERE o.customer.id = :customerId")
List<Order> findByCustomerWithItems(@Param("customerId") Long customerId);

// Projection (DTO constructor)
@Query("SELECT new com.example.dto.OrderSummary(o.id, o.total, o.status, c.name) " +
       "FROM Order o JOIN o.customer c WHERE o.status = :status")
List<OrderSummary> findOrderSummaries(@Param("status") OrderStatus status);

// Count with condition
@Query("SELECT COUNT(o) FROM Order o WHERE o.customer.id = :customerId AND o.status = 'PENDING'")
long countPendingOrdersByCustomer(@Param("customerId") Long customerId);

// Exists check (more efficient than findBy)
@Query("SELECT CASE WHEN COUNT(u) > 0 THEN true ELSE false END FROM User u WHERE u.email = :email")
boolean existsByEmailCustom(@Param("email") String email);

Native SQL (when JPQL isn't enough)

@Query(value = """
    SELECT p.*, c.name as category_name
    FROM products p
    INNER JOIN categories c ON p.category_id = c.id
    WHERE p.price BETWEEN :minPrice AND :maxPrice
    ORDER BY p.price ASC
    LIMIT :limit
    """, nativeQuery = true)
List<Object[]> findProductsInPriceRange(
    @Param("minPrice") BigDecimal min,
    @Param("maxPrice") BigDecimal max,
    @Param("limit") int limit);

Spring Data Specifications (Dynamic Filtering)

Best pattern for complex, dynamic queries:

// Specification class
public class ProductSpecification {

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

    public static Specification<Product> priceBetween(BigDecimal min, BigDecimal max) {
        return (root, query, cb) -> {
            if (min == null && max == null) return null;
            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> nameContains(String name) {
        return (root, query, cb) ->
            name == null ? null : cb.like(cb.lower(root.get("name")), "%" + name.toLowerCase() + "%");
    }
}

// Repository — extend JpaSpecificationExecutor
public interface ProductRepository extends JpaRepository<Product, Long>,
    JpaSpecificationExecutor<Product> { }

// Usage in service
public Page<ProductResponse> search(ProductFilterRequest filter, Pageable pageable) {
    Specification<Product> spec = Specification
        .where(ProductSpecification.hasCategory(filter.getCategoryId()))
        .and(ProductSpecification.priceBetween(filter.getMinPrice(), filter.getMaxPrice()))
        .and(ProductSpecification.nameContains(filter.getName()));

    return productRepository.findAll(spec, pageable).map(mapper::toResponse);
}

Solving N+1 Problems

Detect: Multiple identical queries in Hibernate logs (spring.jpa.show-sql=true)

Fix Strategy:

// ❌ N+1: loads order items in a loop
List<Order> orders = orderRepository.findAll();
orders.forEach(o -> o.getItems().size()); // N queries

// ✅ Option 1: JOIN FETCH for small datasets
@Query("SELECT DISTINCT o FROM Order o LEFT JOIN FETCH o.items")
List<Order> findAllWithItems();

// ✅ Option 2: @EntityGraph for repository methods
@EntityGraph(attributePaths = {"items", "customer"})
List<Order> findByStatus(OrderStatus status);

// ✅ Option 3: Batch loading (for large datasets)
@BatchSize(size = 30)
@OneToMany(mappedBy = "order")
private List<OrderItem> items;

Pagination with Fetch Join (Common Pitfall)

// ❌ Hibernate warning: HHH90003004 - paginates in memory
@Query("SELECT o FROM Order o LEFT JOIN FETCH o.items")
Page<Order> findAllWithItems(Pageable pageable);

// ✅ Correct approach: two queries
@Query(value = "SELECT o FROM Order o LEFT JOIN FETCH o.items WHERE o.status = :status",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
Page<Order> findByStatusWithItems(@Param("status") OrderStatus status, Pageable pageable);

Useful Hibernate/JPA Configuration

spring:
  jpa:
    show-sql: true              # Dev only
    properties:
      hibernate:
        format_sql: true        # Pretty print SQL
        generate_statistics: true  # Query performance stats
        default_batch_fetch_size: 30  # Global batch loading
    open-in-view: false         # Disable OSIV (anti-pattern in APIs)

Source: jyjeanne/ai-setup-forge — distributed by TomeVault.