Database Engineering
with Spring Boot

spring:
  jpa:
    show-sql: true                          # prints SQL to stdout
    properties:
      hibernate:
        format_sql: true                    # pretty-print SQL
        use_sql_comments: true              # adds HQL source as comment

logging:
  level:
    org.hibernate.SQL: DEBUG                # see all SQL
    org.hibernate.type.descriptor.sql: TRACE  # see bind parameter values

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

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator = "order_seq")
    @SequenceGenerator(name = "order_seq", sequenceName = "order_id_seq",
                       allocationSize = 50)  // batch 50 IDs per DB call
    private Long id;

    // Use enums — stored as strings, not numbers (never change meaning)
    @Enumerated(EnumType.STRING)
    @Column(nullable = false, length = 20)
    private OrderStatus status;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal total;

    // Many-to-one: always LAZY — never load the whole customer for every order query
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "customer_id", nullable = false)
    private Customer customer;

    // One-to-many: always LAZY
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL,
               orphanRemoval = true, fetch = FetchType.LAZY)
    private List<OrderItem> items = new ArrayList<>();

    // Audit fields — populated automatically by Spring Data
    @CreatedDate
    @Column(nullable = false, updatable = false)
    private LocalDateTime createdAt;

    @LastModifiedDate
    @Column(nullable = false)
    private LocalDateTime updatedAt;

    // Optimistic locking — prevents lost updates
    @Version
    private Long version;

    // Equals/hashCode based on business key — NEVER on id alone for new entities
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof Order)) return false;
        Order other = (Order) o;
        return id != null && id.equals(other.getId());
    }

    @Override
    public int hashCode() { return getClass().hashCode(); }
}

@Service
@Transactional
public class OrderService {

    public void demonstrateLifecycle(Long orderId) {
        // TRANSIENT — not yet known to Hibernate
        Order newOrder = new Order();
        newOrder.setStatus(OrderStatus.PENDING);

        // TRANSIENT → MANAGED (scheduled for INSERT on flush)
        entityManager.persist(newOrder);

        // MANAGED — load from DB, entity tracked by persistence context
        Order existing = orderRepository.findById(orderId).orElseThrow();

        // Still MANAGED — this change is tracked, UPDATE will run on flush
        existing.setStatus(OrderStatus.PROCESSING);
        // NO save() call needed — dirty checking handles it automatically!

        // MANAGED → REMOVED (DELETE on flush)
        entityManager.remove(existing);

        // DETACHED entity re-attached — Hibernate does a SELECT then UPDATE
        Order detached = getDetachedOrderFromSomewhere();
        Order reattached = entityManager.merge(detached);
    }
    // @Transactional ends → flush → SQL executes → context closes → all entities DETACHED
}

// COMMON BUG: modifying a DETACHED entity
@Service
public class BuggyService {
    public Order getOrder(Long id) {
        return orderRepository.findById(id).orElseThrow();
        // Returns DETACHED entity — @Transactional ended when method returned
    }

    public void updateOrder(Order order) {
        order.setStatus(OrderStatus.SHIPPED);
        // BUG: order is DETACHED — this change is SILENTLY IGNORED
        // You must call orderRepository.save(order) to re-attach and update
    }
}

// ── THE PROBLEM ──────────────────────────────────────────────
@Service
public class OrderService {
    @Transactional
    public Order getOrder(Long id) {
        return orderRepository.findById(id).orElseThrow();
        // @Transactional ends here — session CLOSES
    }
}

@RestController
public class OrderController {
    public OrderDto getOrder(Long id) {
        Order order = orderService.getOrder(id);
        order.getItems().size(); // BOOM: LazyInitializationException
        // order.items is a proxy — session is already closed
    }
}

// ── FIX 1: Fetch join in the query (BEST for known access patterns) ──
@Query("SELECT o FROM Order o LEFT JOIN FETCH o.items WHERE o.id = :id")
Optional<Order> findByIdWithItems(@Param("id") Long id);

// ── FIX 2: Use @EntityGraph (cleaner than JPQL join fetch) ────────────
@EntityGraph(attributePaths = {"items", "items.product", "customer"})
Optional<Order> findById(Long id);

// ── FIX 3: Map to DTO inside the transaction (BEST for APIs) ──────────
@Service
public class OrderService {
    @Transactional(readOnly = true)
    public OrderDetailDto getOrder(Long id) {
        Order order = orderRepository.findByIdWithItems(id).orElseThrow();
        return orderMapper.toDetailDto(order);
        // DTO created inside transaction — no lazy loading issues ever
    }
}

// ── WRONG FIX: spring.jpa.open-in-view=true (default!) ───────────────
// Keeps the session open through the HTTP request lifecycle
// Allows lazy loading in controllers — but silently fires N+1 queries
// ALWAYS set: spring.jpa.open-in-view=false in production

// This looks innocent — but generates N+1 queries
@GetMapping("/orders/summary")
public List<OrderSummaryDto> getOrders() {
    List<Order> orders = orderRepository.findAll();  // SELECT * FROM orders  (1 query)

    return orders.stream()
        .map(o -> new OrderSummaryDto(
            o.getId(),
            o.getCustomer().getName(),    // SELECT * FROM customers WHERE id=?  (1 per order!)
            o.getTotal()
        ))
        .toList();
}
// With 500 orders: 1 + 500 = 501 queries
// With 10,000 orders: 10,001 queries — minutes of wall time

// ── FIX 1: JOIN FETCH ────────────────────────────────────────────────
@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findByStatusWithCustomer(@Param("status") OrderStatus status);
// Generates: SELECT o.*, c.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id
// = 1 query regardless of order count

// ── FIX 2: @EntityGraph ─────────────────────────────────────────────
@EntityGraph(attributePaths = {"customer"})
List<Order> findByStatus(OrderStatus status);

// ── FIX 3: @BatchSize (for collections where join fetch multiplies rows) ──
@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 50)  // loads items in batches of 50 IDs: WHERE order_id IN (?,?,?...)
private List<OrderItem> items;

// ── FIX 4: Project to DTO in JPQL — avoids entity loading entirely ────
@Query("""
    SELECT new com.myapp.dto.OrderSummaryDto(
        o.id, c.name, o.total, o.status, o.createdAt
    )
    FROM Order o JOIN o.customer c
    WHERE o.status = :status
    ORDER BY o.createdAt DESC
""")
List<OrderSummaryDto> findSummaryByStatus(@Param("status") OrderStatus status);

// 1. Enable SQL logging + count queries manually (crude but always works)
// logging.level.org.hibernate.SQL: DEBUG

// 2. Hibernate Statistics — counts queries per request
@Configuration
public class HibernateConfig {
    @Bean
    public HibernatePropertiesCustomizer hibernateStats() {
        return props -> props.put("hibernate.generate_statistics", "true");
    }
}
// Check log for: "Session Metrics" — shows query count, cache hits, time

// 3. datasource-proxy — logs query count per request with stack trace
// pom: com.github.gavlyukovskiy:datasource-proxy-spring-boot-starter
// application.yml:
// decorator.datasource.datasource-proxy.query.log-level: INFO

// 4. Hypersistence Optimizer — commercial, catches N+1 at test time
// Throws exception in tests when N+1 is detected — CI fails, never ships

// RULE: Always test list endpoints with 100+ items in dev.
// N+1 is invisible with 5 items but catastrophic with 500.

// ── SELF-INVOCATION BUG ─────────────────────────────────────────────
@Service
public class OrderService {

    public void processOrder(Long id) {
        createShipment(id);   // BUG: calls through 'this', not the proxy
                               // @Transactional on createShipment is IGNORED
    }

    @Transactional  // This is NEVER applied when called from above
    public void createShipment(Long id) {
        // no transaction — changes may not be saved
    }
}
// FIX: inject the service into itself, or split into two beans

// ── PROPAGATION ────────────────────────────────────────────────────
@Transactional(propagation = Propagation.REQUIRED)      // default: join existing or create new
@Transactional(propagation = Propagation.REQUIRES_NEW)  // always create new, suspend outer
@Transactional(propagation = Propagation.SUPPORTS)      // join if exists, non-tx if not
@Transactional(propagation = Propagation.MANDATORY)     // must have outer tx — throws if not
@Transactional(propagation = Propagation.NEVER)         // must NOT be in tx — throws if is
@Transactional(propagation = Propagation.NOT_SUPPORTED) // suspend outer tx, run non-tx
@Transactional(propagation = Propagation.NESTED)        // savepoint within outer tx

// REQUIRES_NEW use case: audit log that must always commit even if main tx rolls back
@Service
public class AuditService {
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logEvent(AuditEvent event) {
        auditRepository.save(event);  // commits independently of caller's transaction
    }
}

// ── READONY OPTIMIZATION ───────────────────────────────────────────
@Transactional(readOnly = true)
public List<OrderDto> findAllOrders() {
    // readOnly = true:
    // - Hibernate skips dirty checking (no need to track changes for read)
    // - Database driver may use read replicas
    // - Flush mode set to NEVER (no accidental writes)
    // Use for ALL read-only operations — meaningful performance gain
}

// ── ROLLBACK RULES ────────────────────────────────────────────────
@Transactional(rollbackOn = Exception.class)    // rollback on any Exception
@Transactional(noRollbackFor = BusinessException.class)  // don't rollback for this

// Default: Spring only rolls back on RuntimeException and Error
// Checked exceptions do NOT trigger rollback by default!

@Service
public class PaymentService {

    private final AuditService auditService;

    @Transactional
    public PaymentResult processPayment(PaymentRequest request) {
        try {
            // This runs in Transaction A
            Payment payment = createPayment(request);
            chargeCard(payment);

            // Audit log in SEPARATE transaction B — commits even if A fails
            auditService.logPaymentAttempt(payment, "SUCCESS");

            return PaymentResult.success(payment);
        } catch (PaymentException e) {
            // Transaction A will roll back — payment NOT saved
            // But audit log (Transaction B) was already committed
            auditService.logPaymentAttempt(null, "FAILED: " + e.getMessage());
            throw e;
        }
    }
}

@Service
public class AuditService {
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logPaymentAttempt(Payment payment, String result) {
        // Always commits independently — critical for audit trails
        auditRepository.save(new AuditEvent(payment, result, Instant.now()));
    }
}

spring:
  datasource:
    hikari:
      # Pool size — formula: (core_count * 2) + effective_spindle_count
      # For I/O-bound workloads: 10-20 is often ideal
      maximum-pool-size: 20
      minimum-idle: 5            # keep 5 warm connections always

      # Timeout settings
      connection-timeout: 30000  # 30s max wait to get a connection from pool
      idle-timeout: 600000       # 10min — remove idle connections
      max-lifetime: 1800000      # 30min — recycle connections (prevent stale)
      keepalive-time: 60000      # ping idle connections every 60s

      # Validation
      connection-test-query: SELECT 1  # for MySQL (Postgres uses ping by default)

      # Pool name — appears in logs and JMX
      pool-name: OrderServicePool

      # Leak detection — logs stack trace if connection held > this duration
      leak-detection-threshold: 5000  # 5 seconds

// HikariCP exposes metrics via Micrometer — view in Actuator
// GET /actuator/metrics/hikaricp.connections.active
// GET /actuator/metrics/hikaricp.connections.pending
// GET /actuator/metrics/hikaricp.connections.idle

// In application.yml, expose via Prometheus:
// management.endpoints.web.exposure.include: metrics, prometheus

// Key metrics to alert on:
// hikaricp.connections.pending > 0  — connections are being waited for
// hikaricp.connections.active / hikaricp.connections.max > 0.8  — 80% utilization
// hikaricp.connections.timeout.total increasing — requests timing out

// Programmatic access
@Component
public class PoolHealthChecker {
    @Autowired HikariDataSource dataSource;

    public PoolStats getStats() {
        HikariPoolMXBean pool = dataSource.getHikariPoolMXBean();
        return new PoolStats(
            pool.getActiveConnections(),
            pool.getIdleConnections(),
            pool.getThreadsAwaitingConnection(),
            pool.getTotalConnections()
        );
    }
}

-- Always use EXPLAIN ANALYZE in PostgreSQL to diagnose slow queries
EXPLAIN ANALYZE
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC
LIMIT 20;

-- Look for:
-- "Seq Scan" on large tables → missing index
-- "Rows Removed by Filter: 50000" → poor selectivity
-- high "actual time" → bottleneck found
-- "Nested Loop" with large outer rows → join optimization needed

-- Bad output (no index):
-- Seq Scan on orders  (cost=0.00..45231.00 rows=123 width=200)
--                     (actual time=0.1..2340.5 rows=123 loops=1)
--   Filter: (status = 'PENDING')
--   Rows Removed by Filter: 500000

-- Good output (with index):
-- Index Scan using idx_orders_status_created on orders
--                     (actual time=0.05..1.2 rows=123 loops=1)
--   Index Cond: (status = 'PENDING')

-- ALWAYS INDEX: foreign keys (Postgres doesn't auto-index them like MySQL)
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Composite index — column ORDER matters. Left-prefix rule.
-- This index supports: WHERE status=? AND created_at>?
--                      WHERE status=?
--                  NOT: WHERE created_at>?  (status must be first)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Partial index — smaller, faster for filtered queries
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'PENDING';
-- Only indexes pending orders — much smaller than a full index

-- Covering index — includes extra columns to avoid table lookups
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id) INCLUDE (status, total, created_at);
-- Query can be answered from index alone — no heap access

-- CONCURRENTLY — add index without locking the table (Postgres)
CREATE INDEX CONCURRENTLY idx_orders_new ON orders(email);
-- Takes longer but production traffic is not blocked

@Service
@RequiredArgsConstructor
public class BulkImportService {

    private final JdbcTemplate jdbcTemplate;

    // Insert 100,000 rows efficiently
    public void bulkInsertProducts(List<ProductImportDto> products) {
        String sql = "INSERT INTO products (name, sku, price, category) VALUES (?,?,?,?)";

        jdbcTemplate.batchUpdate(sql, products, 1000, (ps, product) -> {
            ps.setString(1, product.name());
            ps.setString(2, product.sku());
            ps.setBigDecimal(3, product.price());
            ps.setString(4, product.category());
        });
        // Sends 100 batches of 1000 rows each — MUCH faster than 100,000 individual inserts
    }

    // Complex analytics query — not possible in JPQL
    public List<RevenueByCategory> getMonthlyRevenue(int year) {
        return jdbcTemplate.query("""
            SELECT c.name AS category,
                   EXTRACT(MONTH FROM o.created_at) AS month,
                   SUM(oi.quantity * oi.unit_price) AS revenue
            FROM orders o
            JOIN order_items oi ON oi.order_id = o.id
            JOIN products p ON oi.product_id = p.id
            JOIN categories c ON p.category_id = c.id
            WHERE EXTRACT(YEAR FROM o.created_at) = ?
              AND o.status = 'COMPLETED'
            GROUP BY c.name, month
            ORDER BY month, revenue DESC
        """, (rs, row) -> new RevenueByCategory(
            rs.getString("category"),
            rs.getInt("month"),
            rs.getBigDecimal("revenue")
        ), year);
    }
}