Guides database persistence and data modeling with Spring Boot 3.x, Spring Data JPA, PostgreSQL, and Flyway. Trigger: When working with JPA entities, repositories, database migrations, queries, stored procedures, or data modeling in Spring Boot.
| Component | Version / Tool |
|---|---|
| Java | 21+ |
| Spring Boot | 3.x |
| Spring Data JPA | Latest compatible |
| Database | PostgreSQL (recommended) |
| Migrations | Flyway |
| Cache (optional) | Redis + Spring Cache |
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() {}
}
| Rule | Detail |
|---|---|
| Table naming | Plural snake_case: orders, order_items |
| Column naming | snake_case via spring.jpa.hibernate.naming.physical-strategy or explicit @Column(name = ...) |
| IDs | @GeneratedValue(strategy = GenerationType.IDENTITY) for PostgreSQL serial/bigserial |
| No-arg constructor | protected — required by JPA, prevents accidental public instantiation |
| Fetch strategy | ALWAYS FetchType.LAZY on @ManyToOne and @OneToMany. Never rely on EAGER defaults. |
| Bidirectional sync | Owner side manages the relationship. Add helper methods addItem() / removeItem() that sync both sides. |
equals / hashCode | Base on @Id or a natural business key. NEVER use all fields. |
| Relationship | Annotation | Fetch | Cascade | Notes |
|---|---|---|---|---|
| Parent → Children | @OneToMany(mappedBy, cascade=ALL, orphanRemoval=true) | LAZY | ALL | Owner = child side |
| Child → Parent | @ManyToOne(fetch=LAZY, optional=false) | LAZY | NONE | @JoinColumn here |
| Many-to-Many | @ManyToMany + @JoinTable | LAZY | PERSIST, MERGE | Use Set, not List |
| Lookup/Enum table | @ManyToOne(fetch=LAZY) | LAZY | NONE | Read-only reference |
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.
@Id)@Embedded for value objects, NOT separate tablesEnable 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"));
}
}
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.
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.
| Create index when | Do NOT index when |
|---|---|
Column used in WHERE clauses frequently | Table has < 1000 rows |
Column used in JOIN conditions | Column has very low cardinality (boolean) |
Column used in ORDER BY | Column is updated very frequently (write-heavy) |
| Unique business constraint needed | You already have a covering index |
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);
idx_{table}_{columns} — e.g., idx_orders_customer_id, idx_products_sku.
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);
}
@QueryFor 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
);
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);
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>.
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).
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;
$$;
@Procedurepublic 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 { ... }
EntityManagerFor 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]);
}
}
application.yml: