Spring Persistence

1. Data Modeling — Entity Design

Every persistent entity MUST follow this structure:

@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_orders_customer_id", columnList = "customer_id"),
    @Index(name = "idx_orders_status_created", columnList = "status, created_at")
})
@EntityListeners(AuditingEntityListener.class)
public class Order {

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

    @Column(nullable = false, length = 50)
    private String status;

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

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

    @Embedded
    private Money totalAmount;

    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;

    @LastModifiedDate
    @Column(name = "last_modified_at")
    private Instant lastModifiedAt;

    // protected no-arg constructor for JPA
    protected Order() {}
}

Entity Rules

Relationships Decision Table

Embeddable Value Objects

Use @Embeddable for value objects that have no identity of their own:

@Embeddable
public class Money {

    @Column(name = "amount", nullable = false, precision = 19, scale = 4)
    private BigDecimal amount;

    @Column(name = "currency", nullable = false, length = 3)
    private String currency;

    protected Money() {}

    public Money(BigDecimal amount, String currency) {
        this.amount = amount;
        this.currency = currency;
    }
}

Use for: addresses, monetary values, date ranges, coordinates — anything that is defined by its attributes, not by an ID.

Normalized Model Checklist

• Every entity has a single-column surrogate primary key (@Id)
• No repeated groups — extract into child entities
• No transitive dependencies — every non-key column depends on the whole PK
• Reference data in separate lookup tables with FK constraints
• Use @Embedded for value objects, NOT separate tables

2. Auditing

Setup

Enable JPA auditing in configuration:

@Configuration
@EnableJpaAuditing
public class JpaAuditingConfig {

    @Bean
    public AuditorAware<String> auditorAware() {
        return () -> Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication())
                .filter(Authentication::isAuthenticated)
                .map(Authentication::getName)
                .or(() -> Optional.of("system"));
    }
}

Audit Base Entity

Extract audit fields into a mapped superclass:

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableEntity {

    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;

    @LastModifiedDate
    @Column(name = "last_modified_at")
    private Instant lastModifiedAt;

    @CreatedBy
    @Column(name = "created_by", nullable = false, updatable = false, length = 100)
    private String createdBy;

    @LastModifiedBy
    @Column(name = "last_modified_by", length = 100)
    private String lastModifiedBy;
}

Entities extend AuditableEntity to get automatic audit fields.

Audit Trail Table for Critical Operations

For operations that require a full history (financial, compliance), create a dedicated audit trail:

@Entity
@Table(name = "audit_trail", indexes = {
    @Index(name = "idx_audit_entity", columnList = "entity_type, entity_id"),
    @Index(name = "idx_audit_timestamp", columnList = "timestamp")
})
public class AuditTrailEntry {

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

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

    @Column(name = "entity_id", nullable = false)
    private Long entityId;

    @Column(name = "action", nullable = false, length = 20)
    private String action; // CREATE, UPDATE, DELETE

    @Column(name = "changed_fields", columnDefinition = "jsonb")
    private String changedFields; // JSON diff of what changed

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

    @Column(name = "timestamp", nullable = false)
    private Instant timestamp;
}

Populate via JPA @EntityListeners or Spring @EventListener / ApplicationEventPublisher.

3. Indexes

When to Create Indexes

Declaring Indexes

On the entity via @Table:

@Table(name = "products", indexes = {
    @Index(name = "idx_products_sku", columnList = "sku", unique = true),
    @Index(name = "idx_products_category_price", columnList = "category_id, price")
})

Or in a Flyway migration for more control:

CREATE INDEX CONCURRENTLY idx_products_category_price
    ON products (category_id, price);

Index Naming Convention

idx_{table}_{columns} — e.g., idx_orders_customer_id, idx_products_sku.

4. Non-trivial Queries

Derived Query Methods

Only for simple cases (1-2 conditions):

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByStatusAndCustomerId(String status, Long customerId);
    Optional<Order> findByOrderNumber(String orderNumber);
}

JPQL with @Query

For joins, aggregations, and compound filters:

@Query("""
    SELECT o FROM Order o
    JOIN FETCH o.customer c
    JOIN FETCH o.items i
    WHERE o.status = :status
      AND o.createdAt >= :since
    ORDER BY o.createdAt DESC
    """)
List<Order> findRecentOrdersByStatus(
    @Param("status") String status,
    @Param("since") Instant since
);

@Query("""
    SELECT new com.example.dto.CustomerOrderSummary(
        c.id, c.name, COUNT(o), SUM(o.totalAmount.amount)
    )
    FROM Customer c
    LEFT JOIN c.orders o
    WHERE o.createdAt BETWEEN :start AND :end
    GROUP BY c.id, c.name
    HAVING COUNT(o) > :minOrders
    """)
List<CustomerOrderSummary> getCustomerOrderSummaries(
    @Param("start") Instant start,
    @Param("end") Instant end,
    @Param("minOrders") long minOrders
);

Native SQL

When JPQL is insufficient (PostgreSQL-specific functions, CTEs, window functions):

@Query(value = """
    WITH ranked AS (
        SELECT o.*, ROW_NUMBER() OVER (
            PARTITION BY o.customer_id ORDER BY o.created_at DESC
        ) AS rn
        FROM orders o
        WHERE o.status = :status
    )
    SELECT * FROM ranked WHERE rn = 1
    """, nativeQuery = true)
List<Order> findLatestOrderPerCustomerByStatus(@Param("status") String status);

Specifications Pattern for Dynamic Queries

public class OrderSpecifications {

    public static Specification<Order> hasStatus(String status) {
        return (root, query, cb) ->
            status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<Order> createdAfter(Instant date) {
        return (root, query, cb) ->
            date == null ? null : cb.greaterThanOrEqualTo(root.get("createdAt"), date);
    }

    public static Specification<Order> customerNameContains(String name) {
        return (root, query, cb) -> {
            if (name == null) return null;
            Join<Order, Customer> customer = root.join("customer");
            return cb.like(cb.lower(customer.get("name")), "%" + name.toLowerCase() + "%");
        };
    }
}

Usage in service:

Specification<Order> spec = Specification
    .where(OrderSpecifications.hasStatus(filter.getStatus()))
    .and(OrderSpecifications.createdAfter(filter.getSince()))
    .and(OrderSpecifications.customerNameContains(filter.getCustomerName()));

Page<Order> results = orderRepository.findAll(spec, pageable);

Repository must extend JpaSpecificationExecutor<Order>.

Projections and DTOs

Use interface-based projections for read-only views:

public interface OrderSummaryProjection {
    Long getId();
    String getStatus();
    @Value("#{target.customer.name}")
    String getCustomerName();
    Instant getCreatedAt();
}

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<OrderSummaryProjection> findByStatus(String status);
}

Use constructor-based DTOs in @Query for aggregations (see JPQL example above with SELECT new).

5. Stored Procedures

Migration Script (Flyway)

Create V3__add_recalculate_customer_totals_procedure.sql:

CREATE OR REPLACE FUNCTION recalculate_customer_totals(p_customer_id BIGINT)
RETURNS TABLE(total_orders BIGINT, total_amount NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(o.id) AS total_orders,
        COALESCE(SUM(o.amount), 0) AS total_amount
    FROM orders o
    WHERE o.customer_id = p_customer_id
      AND o.status != 'CANCELLED';
END;
$$;

Calling from JPA — Option A: @Procedure

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Procedure(name = "recalculate_customer_totals")
    Object[] recalculateCustomerTotals(@Param("p_customer_id") Long customerId);
}

With @NamedStoredProcedureQuery on the entity:

@Entity
@NamedStoredProcedureQuery(
    name = "recalculate_customer_totals",
    procedureName = "recalculate_customer_totals",
    parameters = {
        @StoredProcedureParameter(
            name = "p_customer_id",
            type = Long.class,
            mode = ParameterMode.IN
        )
    }
)
public class Customer { ... }

Calling from JPA — Option B: EntityManager

For more control or complex return types:

@Repository
public class CustomerRepositoryCustomImpl {

    @PersistenceContext
    private EntityManager em;

    public CustomerTotals recalculateCustomerTotals(Long customerId) {
        StoredProcedureQuery query = em.createStoredProcedureQuery("recalculate_customer_totals");
        query.registerStoredProcedureParameter("p_customer_id", Long.class, ParameterMode.IN);
        query.setParameter("p_customer_id", customerId);
        query.execute();

        Object[] result = (Object[]) query.getSingleResult();
        return new CustomerTotals((Long) result[0], (BigDecimal) result[1]);
    }
}

6. Database Migrations (Flyway)

Setup

application.yml: