Spring Data JPA: Soft Delete, Entity Listeners & @EntityGraph

@Entity
@Table(name = "products")
@SQLDelete(sql = "UPDATE products SET deleted_at = NOW() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")   // Hibernate 6+ (Spring Boot 3)
// For Hibernate 5 / Spring Boot 2.x: @Where(clause = "deleted_at IS NULL")
public class Product {

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

    private String name;
    private BigDecimal price;

    @Column(name = "deleted_at")
    private Instant deletedAt;   // null = active, non-null = soft-deleted

    // getters / setters
}

public interface ProductRepository extends JpaRepository<Product, Long> {
    // All standard methods automatically exclude deleted_at IS NOT NULL rows
    List<Product> findByPriceLessThan(BigDecimal price);
}

@Service
@Transactional
public class ProductService {

    private final ProductRepository repo;

    // "Deletes" the product — runs UPDATE SET deleted_at = NOW()
    public void softDelete(Long id) {
        repo.deleteById(id);
    }

    // Returns only active products (deleted_at IS NULL)
    public List<Product> findAll() {
        return repo.findAll();
    }
}

public interface ProductRepository extends JpaRepository<Product, Long> {

    // Find all deleted products — bypass the @SQLRestriction filter
    @Query(value = "SELECT * FROM products WHERE deleted_at IS NOT NULL",
           nativeQuery = true)
    List<Product> findAllDeleted();

    // Find a specific product including deleted ones
    @Query(value = "SELECT * FROM products WHERE id = :id",
           nativeQuery = true)
    Optional<Product> findByIdIncludingDeleted(@Param("id") Long id);

    // Restore — clear the deleted_at timestamp
    @Modifying
    @Query(value = "UPDATE products SET deleted_at = NULL WHERE id = :id",
           nativeQuery = true)
    void restoreById(@Param("id") Long id);
}

@Entity
@Table(name = "orders")
@SQLDelete(sql = "UPDATE orders SET deleted_at = NOW() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
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<>();

    @Column(name = "deleted_at")
    private Instant deletedAt;
}

// OrderItem also soft-deletable
@Entity
@Table(name = "order_items")
@SQLDelete(sql = "UPDATE order_items SET deleted_at = NOW() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class OrderItem {

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

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id")
    private Order order;

    private String productId;
    private Integer quantity;

    @Column(name = "deleted_at")
    private Instant deletedAt;
}

public class ProductEventListener {

    @PrePersist
    public void beforeCreate(Product product) {
        if (product.getPrice() == null || product.getPrice().compareTo(BigDecimal.ZERO) < 0) {
            throw new IllegalArgumentException("Product price must be non-negative");
        }
    }

    @PostPersist
    public void afterCreate(Product product) {
        // Publish domain event — Spring ApplicationEventPublisher via static holder
        SpringContext.publishEvent(new ProductCreatedEvent(product.getId()));
    }

    @PostLoad
    public void afterLoad(Product product) {
        // Decrypt sensitive fields loaded from DB
        // e.g., product.setVendorCode(cipher.decrypt(product.getVendorCode()));
    }
}

@Entity
@Table(name = "products")
@EntityListeners(ProductEventListener.class)
@SQLDelete(sql = "UPDATE products SET deleted_at = NOW() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class Product {
    // ...
}

@Entity
@Table(name = "orders")
@NamedEntityGraph(
    name = "Order.withItemsAndCustomer",
    attributeNodes = {
        @NamedAttributeNode("items"),
        @NamedAttributeNode("customer")
    }
)
public class Order {

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

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

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

    private OrderStatus status;
    private BigDecimal total;
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    // Use the named graph — fetches items + customer in a single JOIN query
    @EntityGraph("Order.withItemsAndCustomer")
    Optional<Order> findById(Long id);

    // Ad-hoc graph — no need for @NamedEntityGraph on the entity
    @EntityGraph(attributePaths = {"items", "customer"})
    List<Order> findByStatus(OrderStatus status);

    // Only fetch items (not customer) for the order list page
    @EntityGraph(attributePaths = "items")
    List<Order> findByCustomerId(Long customerId);
}

# application.yml — log SQL with parameters for development
spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        generate_statistics: true   # prints query count on session close

<!-- pom.xml: p6spy for detailed SQL logging including actual bind values -->
<dependency>
  <groupId>com.github.gavlyukovskiy</groupId>
  <artifactId>datasource-proxy-spring-boot-starter</artifactId>
  <version>1.9.1</version>
</dependency>

// ❌ N+1 — loads all orders then fires a query per order for its items
List<Order> orders = orderRepo.findAll();
orders.forEach(o -> o.getItems().size());  // N extra queries!

// ✅ Fix 1: @EntityGraph — JOIN FETCH in one query
@EntityGraph(attributePaths = "items")
List<Order> findAll();

// ✅ Fix 2: JPQL JOIN FETCH
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findByStatusWithItems(@Param("status") OrderStatus status);

// ✅ Fix 3: @BatchSize — issues one IN query instead of N queries
@OneToMany(mappedBy = "order")
@BatchSize(size = 30)    // loads items for up to 30 orders per query
private List<OrderItem> items;