SQL for Java Developers

Introduction

Spring Data JPA hides SQL — until production latency spikes, the read replica falls over, or a reconciliation job finds missing rows. Every Java backend engineer must read EXPLAIN plans, design indexes, write correct JOINs, and understand transaction isolation — because Hibernate generates SQL from your entity graph, and bad SQL becomes bad Java at scale.

This lesson covers SQL from the Java developer's perspective: relational joins (INNER, LEFT, correlated subqueries), index strategies (B-tree, composite, covering), ACID transactions and isolation levels, and query optimization patterns that appear daily in payment ledgers, order systems, and reporting pipelines.

You don't need to be a DBA — but you must speak SQL fluently enough to review Hibernate logs, pair with DBAs on index design, and debug "works in dev, slow in prod" incidents.

Business problem

SQL ignorance causes production incidents:

• Full table scans: Missing index on account_id — 50M row transfer table, 30s queries, connection pool exhausted.
• Phantom reads: Report totals wrong because READ UNCOMMITTED or wrong isolation in long report query.
• Cartesian products: JOIN missing ON clause in native query — returns billions of rows, OOM in app server.
• Lock contention: UPDATE without index — locks entire table during batch settlement.
• N+1 at SQL layer: ORM generates 1001 queries — fix is often SQL join or batch fetch, not more RAM.

Why this topic exists

SQL is the persistence contract beneath JPA:

• Relational model: Tables, keys, constraints — Java objects map to rows, not the other way around.
• JOINs express relationships: Account → Transfer → LedgerEntry — SQL traverses graph efficiently with planner-chosen plans.
• Indexes make queries fast: B-tree lookups O(log n) vs sequential scan O(n) — difference between 5ms and 5min.
• Transactions guarantee correctness: Debit + credit atomic — partial writes are financial bugs.
• Optimization is measurable: EXPLAIN ANALYZE — evidence-based tuning, not guesswork.

Core concepts

Five SQL pillars for Java developers:

• JOINs: INNER (matching rows only), LEFT (preserve left table), self-join, EXISTS vs IN for subqueries.
• Indexes: Primary key, unique, composite (a,b), partial (WHERE status='ACTIVE') — match WHERE and JOIN columns.
• Transactions: BEGIN … COMMIT/ROLLBACK — ACID. Spring @Transactional maps to JDBC transaction.
• Isolation levels: READ COMMITTED (default PostgreSQL), REPEATABLE READ, SERIALIZABLE — trade consistency vs concurrency.
• Query optimization: EXPLAIN, avoid SELECT *, limit columns, pagination, avoid functions on indexed columns.

Internal architecture

SQL execution path from Java to disk:

TransferService (@Transactional)
         │
         ▼
  Hibernate / JdbcTemplate
         │
         ▼
  JDBC Driver → PostgreSQL
         │
         ▼
  Query Parser → Optimizer (cost-based)
         │
         ├── Index Scan on transfers(account_id)  ← fast
         └── Seq Scan on transfers               ← slow (no index)

Transaction timeline (READ COMMITTED):
  T1: BEGIN → UPDATE accounts SET balance=... → (row lock)
  T2: BEGIN → SELECT balance → sees committed value only
  T1: COMMIT → releases lock
  T2: UPDATE may block until T1 commits

Index design for: SELECT * FROM transfers WHERE account_id = ? AND created_at > ?
  CREATE INDEX idx_transfers_account_created ON transfers(account_id, created_at DESC);

Joins, indexes, transactions, and query plans:

Code walkthrough

SQL patterns — joins, indexes, transactions, optimization:

• Composite index: Column order matters — (from_account_id, created_at) serves WHERE + ORDER BY.
• INNER vs LEFT: INNER for required match; LEFT when preserving rows from one side.
• CHECK constraints: DB-enforced invariants — balance never negative even if app bug.
• EXPLAIN ANALYZE: Actual execution time — not just estimated plan.

-- Schema (PostgreSQL)
CREATE TABLE accounts (
    id          UUID PRIMARY KEY,
    customer_id UUID NOT NULL,
    balance_cents BIGINT NOT NULL CHECK (balance_cents >= 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE transfers (
    id              UUID PRIMARY KEY,
    from_account_id UUID NOT NULL REFERENCES accounts(id),
    to_account_id   UUID NOT NULL REFERENCES accounts(id),
    amount_cents    BIGINT NOT NULL CHECK (amount_cents > 0),
    status          VARCHAR(20) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Composite index for common query pattern
CREATE INDEX idx_transfers_from_created
    ON transfers(from_account_id, created_at DESC);

-- INNER JOIN: transfers with account names
SELECT t.id, t.amount_cents, a_from.balance_cents AS from_balance
FROM transfers t
INNER JOIN accounts a_from ON t.from_account_id = a_from.id
WHERE t.from_account_id = '550e8400-e29b-41d4-a716-446655440000'
  AND t.created_at >= now() - interval '30 days'
ORDER BY t.created_at DESC
LIMIT 50;

-- LEFT JOIN: all accounts even without transfers
SELECT a.id, COUNT(t.id) AS transfer_count
FROM accounts a
LEFT JOIN transfers t ON a.id = t.from_account_id
WHERE a.status = 'ACTIVE'
GROUP BY a.id;

-- Transaction (JDBC)
-- connection.setAutoCommit(false);
-- try { debit(); credit(); connection.commit(); }
-- catch { connection.rollback(); }

-- EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transfers WHERE from_account_id = '550e8400-e29b-41d4-a716-446655440000';

Production example

Production SQL — Spring @Transactional + native query optimization:

• readOnly=true: Hibernate optimization hint — no flush, connection read-only where supported.
• JdbcTemplate for reports: Complex aggregations clearer in SQL than JPQL.
• FOR UPDATE: Row lock during transfer — prevents concurrent double-spend.
• CREATE INDEX CONCURRENTLY: PostgreSQL — no table lock during index build in prod.

@Service
@RequiredArgsConstructor
public class TransferReportService {
    private final JdbcTemplate jdbc;

    @Transactional(readOnly = true)
    public List<TransferSummary> dailySummary(LocalDate date) {
        String sql = """
            SELECT t.from_account_id, COUNT(*) AS cnt, SUM(t.amount_cents) AS total
            FROM transfers t
            WHERE t.created_at >= ? AND t.created_at < ?
              AND t.status = 'COMPLETED'
            GROUP BY t.from_account_id
            HAVING SUM(t.amount_cents) > ?
            """;
        return jdbc.query(sql,
            ps -> {
                ps.setTimestamp(1, Timestamp.valueOf(date.atStartOfDay()));
                ps.setTimestamp(2, Timestamp.valueOf(date.plusDays(1).atStartOfDay()));
                ps.setLong(3, 1_000_000L);
            },
            (rs, row) -> new TransferSummary(
                rs.getObject("from_account_id", UUID.class),
                rs.getLong("cnt"), rs.getLong("total")));
    }
}

// Pessimistic lock — native SQL FOR UPDATE
@Query(value = "SELECT * FROM accounts WHERE id = :id FOR UPDATE", nativeQuery = true)
Optional<AccountEntity> findByIdForUpdate(@Param("id") UUID id);

// Flyway migration — index in versioned script
-- V003__transfers_report_index.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transfers_status_created
    ON transfers(status, created_at) WHERE status = 'COMPLETED';

Enterprise case study

E-commerce order report outage: A Black Friday dashboard query JOINed orders, order_items, products, and customers without selective WHERE — 400M row intermediate result. Missing composite index on orders(created_at, status). Fix: add partial index WHERE status IN ('PAID','SHIPPED'), rewrite to filter orders first in subquery, paginate results. Secondary: long-running report used default isolation — phantom rows caused totals to shift mid-export; switched report to REPEATABLE READ snapshot or materialized view refreshed hourly.

• Symptom: RDS CPU 100%, replica lag 20 minutes during report cron.
• EXPLAIN showed: Seq Scan on orders — no usable index for date range + status.
• Fix: Index + query rewrite + read replica dedicated to reporting.

Performance considerations

SQL performance for Java teams:

• Index selectivity: Index low-cardinality columns alone (status) rarely helps — combine with high-cardinality.
• Covering index: INCLUDE columns — index-only scan avoids heap fetch.
• Batch inserts: JDBC batch or COPY — 1000 single INSERTs vs one batch = 100x faster.
• Connection pool vs threads: Pool size ≈ concurrent DB queries — not thread count with virtual threads.
• Limit result sets: Always pagination — OFFSET expensive at depth; keyset pagination for large tables.

Security considerations

SQL security:

• Parameterized queries: JdbcTemplate and JPA bind parameters — never string concat user input.
• Least privilege: App DB user no DDL in prod — migrations via CI role.
• Row-level security: PostgreSQL RLS for multi-tenant — tenant_id policy at DB layer.
• Audit sensitive reads: Log access to PII columns — compliance requirement.

Scalability considerations

Scaling SQL workloads:

• Read replicas: Route @Transactional(readOnly=true) to replica — watch replication lag.
• Partitioning: transfers by created_at month — partition pruning on time-range queries.
• Archival: Cold data to object storage — keep hot tables small for index efficiency.
• Connection pooling: HikariCP — tune maxLifetime below DB idle timeout.

Production challenges

Real SQL production issues:

• Lock wait timeout: Long TX holds row locks — other transfers block — keep TX short.
• Deadlock in DB: Two TX lock rows in opposite order — PostgreSQL detects, one rolls back — retry logic needed.
• Index bloat: Unused indexes slow writes — monitor pg_stat_user_indexes.
• Migration lock: ALTER TABLE blocks writes — use online migration tools (pt-online-schema-change, CONCURRENTLY).
• ORM-generated bad SQL: Hibernate cartesian product on eager fetch — fix fetch strategy or explicit join.

Common mistakes

• SELECT * in production queries — wastes I/O; name columns explicitly.
• Function on indexed column: WHERE YEAR(created_at)=2026 — index unusable; use range instead.
• Missing index on foreign key columns — JOIN and CASCADE slow.
• Long transaction holding connection during HTTP call to external API.
• Using OFFSET 100000 for pagination — scans skipped rows; use keyset pagination.

Debugging guide

Debug SQL from Java services:

• Enable SQL logging: logging.level.org.hibernate.SQL=DEBUG (dev only — verbose).
• pg_stat_statements: Find top queries by total_time in PostgreSQL.
• EXPLAIN from logs: Copy Hibernate SQL → prepend EXPLAIN ANALYZE in psql.
• HikariCP metrics: pending threads, connection timeout — pool vs slow query diagnosis.

# PostgreSQL — slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

# Active locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted;

# Spring — bind parameters logged
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Best practices

• Every foreign key column has an index — JOIN and ON DELETE performance.
• Review EXPLAIN for any query running > 100ms in production.
• Keep transactions short — no external I/O inside @Transactional.
• Use Flyway/Liquibase for schema — never manual prod DDL.
• Parameterize all dynamic SQL — PreparedStatement always.
• Choose isolation deliberately — default READ COMMITTED sufficient for most OLTP.

Anti-patterns

• ORM-only mindset — never reading generated SQL in logs.
• God table with 200 columns — normalize or partition.
• Sargable violations — wrapping indexed columns in functions.
• SELECT COUNT(*) on billion-row table for pagination total every request.
• Using database as message queue — table grows unbounded without archival.

Staff engineer notes

• If Hibernate is slow, the first step is reading SQL — not tuning JVM heap.
• Composite index column order: equality filters first, then range, then ORDER BY columns.
• FOR UPDATE is a scalpel — row lock only what you need, release fast.
• Report queries belong on replica or batch job — never compete with OLTP on primary.
• In code review: native query without EXPLAIN in PR description for new hot path is a question.

Interview questions

Hands-on exercise

Lab: SQL for Java developers

• Write INNER JOIN query: transfers + account balances for last 7 days.
• Add composite index — compare EXPLAIN before/after.
• Simulate transaction: debit + credit in single JDBC transaction with rollback test.
• Rewrite OFFSET pagination to keyset pagination on id.
• Find N+1 in Hibernate log — replace with JOIN FETCH or @EntityGraph.

Press Run to execute. Real javac + JVM via remote API. Falls back to simulator on network failure.

Architecture trade-offs

• Normalization vs denormalization: Normal form for OLTP; denormalize read models for reports.
• Pessimistic vs optimistic locking: FOR UPDATE vs @Version — contention vs retry.
• JPQL vs native SQL: Portability vs planner control and DB features.
• Index count: More indexes faster reads, slower writes — measure write path impact.

Summary

SQL literacy separates Java developers who ship reliable backends from those who blame Hibernate. Master joins, indexes, transactions, and EXPLAIN-driven optimization — then JPA becomes a tool you control, not a black box that controls you. Next: JPA and Hibernate internals — persistence context, dirty checking, N+1, and caching.

Key takeaways

• JOINs express relational data — INNER for matches, LEFT to preserve driving table rows.
• Index WHERE and JOIN columns — composite order: equality, range, ORDER BY.
• Transactions are ACID — keep them short, no external I/O inside @Transactional.
• EXPLAIN ANALYZE is mandatory for slow query investigation — read SQL Hibernate generates.
• Production: parameterized queries, Flyway migrations, read replicas for reports, keyset pagination.