Java Programming Questions

JDBC try-with-resources example (Java 8+) and why it helps

String sql = "SELECT id, name FROM users WHERE active = ?";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setBoolean(1, true);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            // process row
        }
    }
} catch (SQLException e) {
    // handle/log appropriately
}

try-with-resources ensures Connection, PreparedStatement, and ResultSet are closed in reverse order even if exceptions occur, preventing leaks common with manual finally blocks. It reduces boilerplate and avoids subtle errors where one close throws and prevents others from running.

Compare OFFSET/LIMIT vs Keyset

• OFFSET/LIMIT: easy but becomes slow for large offsets because DB skips rows. Results unstable with concurrent inserts/deletes.
• Keyset (cursor) pagination: uses where clauses on indexed columns (e.g., id, created_at) to seek from last seen values; efficient and stable.

Example keyset SQL (posts sorted by created_at desc, id desc as tiebreaker)

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < ( :last_created_at, :last_id )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Intuition: fetch posts older than last cursor.

Implementing in Java

• Spring Data: write @Query with the above SQL and pass last cursor params; return Page-like results.
• Manual JDBC/JPA: maintain lastCreatedAt and lastId in client; build query dynamically.

Trade-offs when sorting by multiple columns

• Need composite index on (created_at DESC, id DESC) to be efficient.
• Keyset cannot easily jump to arbitrary page number; suitable for 'Next'/'Prev' navigation.
• For complex sort (non-unique combos), include deterministic tiebreaker (PK) to maintain stable ordering.

Instrumenting endpoint with Micrometer

• Use Spring Boot Actuator with Micrometer. Create a Timer or use @Timed on controller/service.
• Metric naming: http_server_requests_latency_seconds (or app_http_request_latency_seconds)
• Tags: method, uri (use templated path), status, service_version, region.

Example code snippet:

@Bean
public Timer requestTimer(MeterRegistry registry){
  return Timer.builder("app.http.server.requests")
    .description("HTTP request latency")
    .publishPercentileHistogram()
    .tags("service","orders")
    .register(registry);
}
// Or use @Timed("app.http.server.requests") on controller

Prometheus query for 95th percentile over last 5m (histogram):
Use histogram_quantile on histogram buckets:

histogram_quantile(0.95, sum(rate(app_http_server_requests_seconds_bucket[5m])) by (le, uri))

If using summaries, you can query quantiles but they are client-side and not aggregatable well across instances.

Pitfalls

• Label cardinality: avoid high-cardinality tags (user_id, request_id). Use templated uri, not raw path parameters.
• Histogram vs Summary: histograms are recommended for Prometheus because they aggregate across instances; summaries compute quantiles per-instance and don't aggregate well. Choose buckets thoughtfully to cover expected latencies.

Requirements & constraints

• Strong consistency: updates visible immediately to subsequent reads across instances.
• Java/Spring Boot, relational DB, multi-instance.

Architecture recommendation

• Distributed cache (Redis) as single source for cached profiles to ensure coherence across instances.
• Use cache-aside pattern with atomic invalidation: write to DB inside tx, then invalidate or update cache atomically.

Invalidation/eviction strategy

• On read: try Redis; on miss, read DB and write-through to Redis with short TTL.
• On update: within DB transaction, update row, then publish a cache invalidation message (or update cache) after commit.

Atomic invalidation across instances

• Use DB transaction + outbox pattern: write update and an outbox row in same DB tx. A reliable publisher reads outbox and publishes an invalidation event to Redis pub/sub or a message broker.
• Consumers (app instances) subscribe and evict/update local caches upon event.

Multi-instance coherence

• Central Redis avoids split-brain; pub/sub ensures near-real-time invalidation.
• To avoid race (read-after-update) use read-after-write: client that performs update should update cache synchronously after commit (or include new value in response) so immediate reads see latest.

Trade-offs

• Distributed cache adds network latency but guarantees coherence; adds complexity (outbox, pub/sub). TTLs and eviction needed to bound memory.

Recommended indexes

1. On orders(user_id, total_amount, created_at DESC, id) — composite index to support WHERE user_id = ? AND total_amount > 100 and ORDER BY created_at DESC. Include id to avoid lookup.
2. On order_items(order_id) — index supports join; if often counting, use order_items(order_id) or a covering index (order_id) is fine.

Why they help

• Composite index narrows rows for the user and amount predicate and supplies ordering, allowing index-only or index scan followed by limited sort.
• order_items index speeds JOIN and GROUP BY aggregation.

Trade-offs

• Write amplification: composite indexes increase insert/update cost and storage.
• Storage: more disk used for indexes.

Other optimizations

• Denormalize item_count into orders and maintain via trigger or application logic for fast reads; reduces join and aggregation cost.
• Maintain a materialized view aggregated by order for heavy-read patterns.
• If counting is rare but acceptable slightly stale, cache counts or use async backfill.

Query rewrite

• Pre-aggregate: SELECT o., oi_count.item_count FROM orders o JOIN (SELECT order_id, COUNT() item_count FROM order_items GROUP BY order_id) oi_count ON oi_count.order_id=o.id WHERE ... LIMIT 50; with proper indexes this is faster.

Choose denormalization when read-latency is critical and write-frequency manageable.