Skip to content

jabrena/plinth / skills/511-frameworks-micronaut-jdbc

511-frameworks-micronaut-jdbc

Verified

Use when you need programmatic JDBC in Micronaut — pooled DataSource, parameterized SQL, io.micronaut.transaction.annotation.Transactional, batching, and domain exception translation. This should trigger for requests such as Review JDBC or SQL data access in a Micronaut project; Improve transactions and parameter binding for Micronaut JDBC; Translate SQLException to domain exceptions or stream large result sets; Fix self-invocation bypassing @Transactional in Micronaut; Review Hikari or pooled datasource usage in Micronaut JDBC. Part of Plinth Toolkit

FileKindSize
SKILL.mdskill md3.4 KB
references/511-frameworks-micronaut-jdbc.mdmarkdown31.1 KB

references/511-frameworks-micronaut-jdbc.md

View on GitHub
---
name: 511-frameworks-micronaut-jdbc
description: Use when you need to write or review programmatic JDBC in Micronaut — including Hikari-backed DataSource injection, PreparedStatement with bind parameters, mapping rows to Java records, transactions (io.micronaut.transaction.annotation.Transactional), batch updates, SQL text blocks, and domain-specific exception translation. Prefer explicit SQL without Micronaut Data when you need full control.
license: Apache-2.0
metadata:
  author: Juan Antonio Breña Moral
  version: 0.19.0
---
# Micronaut JDBC — programmatic SQL

## Role

You are a Senior software engineer with extensive experience in Micronaut and JDBC data access

## Goal

Micronaut pairs JDBC drivers (`micronaut-jdbc-hikari` plus `micronaut-sql-jdbc` or a driver BOM) with a pooled `javax.sql.DataSource` from configuration. Application code should inject `DataSource`, always bind parameters, map rows to immutable records or small DTOs, and declare transactions at the service boundary with `io.micronaut.transaction.annotation.Transactional`. Use Micronaut Data (`@512-frameworks-micronaut-data`) when repository-style generated access fits; use raw JDBC for reporting, bulk ETL, upserts, or maximum SQL control (as in hand-written repositories that open `Connection`/`PreparedStatement` directly). For Flyway migrations with Micronaut, use `@513-frameworks-micronaut-db-migrations-flyway`.

## Constraints

Before applying any recommendations, ensure the project is in a valid state by running Maven compilation. Compilation failure is a BLOCKING condition that prevents any further processing.

- **MANDATORY**: Run `./mvnw compile` or `mvn compile` before applying any change
- **PREREQUISITE**: Project must compile successfully and pass basic validation checks before any JDBC refactoring
- **CRITICAL SAFETY**: If compilation fails, IMMEDIATELY STOP and DO NOT CONTINUE with any recommendations
- **BLOCKING CONDITION**: Compilation errors must be resolved by the user before proceeding with data-access changes
- **NO EXCEPTIONS**: Under no circumstances should JDBC recommendations be applied to a project that fails to compile
- **VERIFY**: Run `./mvnw clean verify` or `mvn clean verify` after applying improvements

## Examples

### Table of contents

- Example 1: Injected DataSource
- Example 2: Transactional boundary
- Example 3: Insert with generated keys
- Example 4: Row mapping to records
- Example 5: Safe single-row queries
- Example 6: SQL exception translation
- Example 7: Batch updates
- Example 8: Streaming large result sets
- Example 9: Transaction propagation types
- Example 10: Self-invocation pitfall
- Example 11: Text blocks, upserts, and domain exceptions

### Example 1: Injected DataSource

Title: Parameterized query and record mapping
Description: Inject `DataSource`, open a connection per operation (or let transaction demarcation scope it), and map `ResultSet` rows to records. Model repositories as `@Singleton` with constructor injection.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Singleton
public class CustomerJdbcRepository {

    private final DataSource dataSource;

    @Inject
    public CustomerJdbcRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<CustomerRow> findByStatus(String status) throws Exception {
        String sql = "SELECT id, email FROM customer WHERE status = ?";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, status);
            try (ResultSet rs = ps.executeQuery()) {
                List<CustomerRow> out = new ArrayList<>();
                while (rs.next()) {
                    out.add(new CustomerRow(rs.getLong("id"), rs.getString("email")));
                }
                return out;
            }
        }
    }
}

record CustomerRow(long id, String email) { }
```

**Bad example:**

```java
public List<CustomerRow> findByStatus(String status) throws Exception {
    String sql = "SELECT id, email FROM customer WHERE status = '" + status + "'";
    // SQL injection — never build SQL with string concatenation from inputs
}
```

### Example 2: Transactional boundary

Title: @Transactional on application service
Description: Place `@Transactional` from `io.micronaut.transaction.annotation` on the service layer so multiple JDBC operations commit or roll back together.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;

@Singleton
public class OrderService {

    private final OrderJdbcRepository orders;

    @Inject
    public OrderService(OrderJdbcRepository orders) {
        this.orders = orders;
    }

    @Transactional
    public void placeOrder(long customerId, String sku) {
        orders.insertOrder(customerId, sku);
        orders.insertInventoryMovement(sku, -1);
    }
}
```

**Bad example:**

```java
// Bad: each repository method opens and commits separately with no coordinating transaction
public void placeOrder(long customerId, String sku) {
    orders.insertOrder(customerId, sku);
    orders.insertInventoryMovement(sku, -1);
}
```

### Example 3: Insert with generated keys

Title: RETURNING or getGeneratedKeys
Description: Prefer database-specific `RETURNING` clauses or `Statement.RETURN_GENERATED_KEYS` for inserts; handle absence of rows explicitly.

**Good example:**

```java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

long insert(Connection c, String email) throws Exception {
    String sql = "INSERT INTO customer(email) VALUES (?)";
    try (PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
        ps.setString(1, email);
        ps.executeUpdate();
        var keys = ps.getGeneratedKeys();
        if (!keys.next()) {
            throw new IllegalStateException("No generated key");
        }
        return keys.getLong(1);
    }
}
```

**Bad example:**

```java
// Bad: assuming last inserted id from another connection or session
long id = selectMaxIdPlusOne();
```

### Example 4: Row mapping to records

Title: Extract a private mapRow method for reuse, clarity, and null safety
Description: Extract `ResultSet`-to-record conversion into a private `mapRow(ResultSet)` method. This keeps query methods readable, centralizes null-handling and type conversion, and makes the mapping easy to test and reuse across multiple queries.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;

record OrderRow(long id, long customerId, String status, Instant createdAt) { }

@Singleton
public class OrderJdbcRepository {

    private final DataSource dataSource;

    @Inject
    public OrderJdbcRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<OrderRow> findByCustomer(long customerId) throws SQLException {
        String sql = "SELECT id, customer_id, status, created_at FROM orders WHERE customer_id = ?";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, customerId);
            try (ResultSet rs = ps.executeQuery()) {
                List<OrderRow> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(mapRow(rs)); // centralised, reusable
                }
                return rows;
            }
        }
    }

    private OrderRow mapRow(ResultSet rs) throws SQLException {
        return new OrderRow(
            rs.getLong("id"),
            rs.getLong("customer_id"),
            rs.getString("status"),
            rs.getTimestamp("created_at").toInstant()
        );
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Singleton
public class OrderJdbcRepository {

    private final DataSource dataSource;

    public OrderJdbcRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<Object[]> findByCustomer(long customerId) throws SQLException {
        // Bad: Object[] loses type safety; inline mapping duplicated at every call site
        String sql = "SELECT id, customer_id, status, created_at FROM orders WHERE customer_id = ?";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, customerId);
            try (ResultSet rs = ps.executeQuery()) {
                List<Object[]> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(new Object[]{ rs.getLong("id"), rs.getString("status") });
                }
                return rows;
            }
        }
    }
}
```

### Example 5: Safe single-row queries

Title: Wrap in Optional — always check ResultSet.next() before reading columns
Description: A query that may return zero rows must check `ResultSet.next()` and return `Optional.empty()` when absent. Calling `rs.next()` without checking the return value, or reading columns before the cursor is on a row, results in `NullPointerException` or incorrect data.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Optional;

record CustomerRow(long id, String email, String status) { }

@Singleton
public class CustomerJdbcRepository {

    private final DataSource dataSource;

    @Inject
    public CustomerJdbcRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Optional<CustomerRow> findById(long id) throws SQLException {
        String sql = "SELECT id, email, status FROM customer WHERE id = ?";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(new CustomerRow(
                        rs.getLong("id"),
                        rs.getString("email"),
                        rs.getString("status")
                    ));
                }
                return Optional.empty();
            }
        }
    }
}
```

**Bad example:**

```java
import javax.sql.DataSource;
import java.sql.*;

public class CustomerJdbcRepository {

    private final DataSource dataSource;

    public CustomerJdbcRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public CustomerRow findById(long id) throws SQLException {
        String sql = "SELECT id, email, status FROM customer WHERE id = ?";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next(); // Bad: unchecked — throws or returns wrong data when no row exists
                return new CustomerRow(rs.getLong("id"), rs.getString("email"), rs.getString("status"));
            }
        }
    }
}

record CustomerRow(long id, String email, String status) { }
```

### Example 6: SQL exception translation

Title: Translate SQLException subtypes to domain exceptions at service boundaries
Description: Catch `SQLIntegrityConstraintViolationException` (unique constraints, FK violations) and other `SQLException` subtypes at the service layer and re-throw as domain-meaningful exceptions. Never let raw `SQLException` propagate to `@Controller` methods or callers that should not know about the database schema.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;

@Singleton
public class AccountService {

    private final DataSource dataSource;

    @Inject
    public AccountService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional
    public void register(String email) {
        String sql = "INSERT INTO accounts (email) VALUES (?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, email);
            ps.executeUpdate();
        } catch (SQLIntegrityConstraintViolationException ex) {
            throw new EmailAlreadyRegisteredException(email, ex);
        } catch (SQLException ex) {
            throw new AccountPersistenceException("Failed to register: " + email, ex);
        }
    }
}

public class EmailAlreadyRegisteredException extends RuntimeException {
    public EmailAlreadyRegisteredException(String email, Throwable cause) {
        super("Email already registered: " + email, cause);
    }
}

public class AccountPersistenceException extends RuntimeException {
    public AccountPersistenceException(String message, Throwable cause) {
        super(message, cause);
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import javax.sql.DataSource;
import java.sql.*;

@Singleton
public class AccountService {

    private final DataSource dataSource;

    public AccountService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Bad: propagates raw SQLException — callers must catch java.sql.* and
    // lose all domain context; unique constraint violation looks the same as a timeout
    @Transactional
    public void register(String email) throws SQLException {
        String sql = "INSERT INTO accounts (email) VALUES (?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, email);
            ps.executeUpdate();
        }
    }
}
```

### Example 7: Batch updates

Title: addBatch / executeBatch for bulk inserts and updates
Description: For inserting or updating many rows, use `PreparedStatement.addBatch()` to stage statements and `executeBatch()` to send them in a single round-trip. Executing one statement per loop iteration multiplies latency and overwhelms the connection pool.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

@Singleton
public class ItemBatchRepository {

    private final DataSource dataSource;

    @Inject
    public ItemBatchRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional
    public void insertItems(List<String> names) throws SQLException {
        String sql = "INSERT INTO items (name) VALUES (?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            for (String name : names) {
                ps.setString(1, name);
                ps.addBatch();
            }
            ps.executeBatch(); // single round-trip for all rows
        }
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

@Singleton
public class ItemBatchRepository {

    private final DataSource dataSource;

    public ItemBatchRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Bad: N separate round-trips — catastrophic for large lists
    @Transactional
    public void insertItems(List<String> names) throws SQLException {
        for (String name : names) {
            String sql = "INSERT INTO items (name) VALUES (?)";
            try (Connection c = dataSource.getConnection();
                 PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(1, name);
                ps.executeUpdate();
            }
        }
    }
}
```

### Example 8: Streaming large result sets

Title: setFetchSize for server-side cursor; process rows one at a time
Description: For large exports or aggregations, set `setFetchSize()` on the `PreparedStatement` to enable a server-side cursor that streams rows in pages. Combined with `ResultSet.TYPE_FORWARD_ONLY` and `CONCUR_READ_ONLY`, this prevents loading the entire result set into heap memory before processing begins.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.*;

@Singleton
public class AuditExportRepository {

    private final DataSource dataSource;

    @Inject
    public AuditExportRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional
    public void exportAuditLog(PrintWriter writer) throws SQLException {
        String sql = "SELECT id, action, created_at FROM audit_log ORDER BY created_at";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql,
                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
            ps.setFetchSize(500); // stream rows 500 at a time — no full load into heap
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    writer.printf("%d,%s,%s%n",
                        rs.getLong("id"),
                        rs.getString("action"),
                        rs.getTimestamp("created_at"));
                }
            }
        }
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

record AuditRow(long id, String action, java.sql.Timestamp createdAt) { }

@Singleton
public class AuditExportRepository {

    private final DataSource dataSource;

    public AuditExportRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Bad: loads all rows into a List before writing — OOM risk on large tables
    @Transactional
    public void exportAuditLog(PrintWriter writer) throws SQLException {
        List<AuditRow> rows = new ArrayList<>();
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(
                 "SELECT id, action, created_at FROM audit_log ORDER BY created_at");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                rows.add(new AuditRow(rs.getLong("id"), rs.getString("action"), rs.getTimestamp("created_at")));
            }
        }
        for (AuditRow r : rows) {
            writer.printf("%d,%s,%s%n", r.id(), r.action(), r.createdAt());
        }
    }
}
```

### Example 9: Transaction propagation types

Title: REQUIRED for coordinated work, REQUIRES_NEW for independent commits
Description: The default `REQUIRED` propagation joins an existing transaction or starts a new one — correct for most service calls. Use `REQUIRES_NEW` when an operation (e.g. audit logging, metrics recording) must commit independently even if the caller's transaction later rolls back. Micronaut maps these semantics via `io.micronaut.transaction.TransactionDefinition.Propagation`.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.TransactionDefinition.Propagation;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;

@Singleton
public class AuditRepository {

    private final DataSource dataSource;

    @Inject
    public AuditRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // REQUIRES_NEW: always commits independently — audit entry survives even if
    // the caller's transaction rolls back
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logEvent(String event, long entityId) throws SQLException {
        String sql = "INSERT INTO audit_log (event, entity_id, occurred_at) VALUES (?, ?, NOW())";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, event);
            ps.setLong(2, entityId);
            ps.executeUpdate();
        }
    }
}

@Singleton
public class OrderService {

    private final DataSource dataSource;
    private final AuditRepository auditRepository;

    @Inject
    public OrderService(DataSource dataSource, AuditRepository auditRepository) {
        this.dataSource = dataSource;
        this.auditRepository = auditRepository;
    }

    // REQUIRED: joins existing transaction or starts one
    @Transactional
    public void placeOrder(long customerId, String sku) throws SQLException {
        String sql = "INSERT INTO orders (customer_id, sku) VALUES (?, ?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, customerId);
            ps.setString(2, sku);
            ps.executeUpdate();
        }
        auditRepository.logEvent("order-placed", customerId); // commits in its own transaction
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import javax.sql.DataSource;
import java.sql.*;

@Singleton
public class OrderService {

    private final DataSource dataSource;

    public OrderService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // Bad: no @Transactional — each repository call runs in its own auto-commit connection;
    // if the inventory update fails, the order insert is already committed and cannot be rolled back
    public void placeOrder(long customerId, String sku) throws SQLException {
        insertOrder(customerId, sku);
        insertInventoryMovement(sku, -1); // failure here cannot undo insertOrder
    }

    private void insertOrder(long customerId, String sku) throws SQLException { }
    private void insertInventoryMovement(String sku, int delta) throws SQLException { }
}
```

### Example 10: Self-invocation pitfall

Title: Calling @Transactional via this.method() can bypass interception
Description: Micronaut applies `@Transactional` through AOP. When a method calls another method on the same bean instance via `this.method()`, the call may not pass through the transactional interceptor, so nested transactional behaviour is not applied. Extract the inner concern to a separate `@Singleton` bean and inject it so calls go through the managed proxy.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;

// Separate bean — interception applies for every cross-bean call
@Singleton
public class AuditRepository {

    private final DataSource dataSource;

    @Inject
    public AuditRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional
    public void logEvent(String event, long entityId) throws SQLException {
        String sql = "INSERT INTO audit_log (event, entity_id) VALUES (?, ?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, event);
            ps.setLong(2, entityId);
            ps.executeUpdate();
        }
    }
}

@Singleton
public class OrderService {

    private final DataSource dataSource;
    private final AuditRepository auditRepository; // injected — calls go through bean proxy

    @Inject
    public OrderService(DataSource dataSource, AuditRepository auditRepository) {
        this.dataSource = dataSource;
        this.auditRepository = auditRepository;
    }

    @Transactional
    public void placeOrder(long customerId, String sku) throws SQLException {
        // ... insert order ...
        auditRepository.logEvent("order-placed", customerId); // intercepted — @Transactional honoured
    }
}
```

**Bad example:**

```java
import io.micronaut.context.annotation.Singleton;
import io.micronaut.transaction.annotation.Transactional;
import javax.sql.DataSource;
import java.sql.*;

@Singleton
public class OrderService {

    private final DataSource dataSource;

    public OrderService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void placeOrder(long customerId, String sku) throws SQLException {
        // ... insert order ...
        this.logAudit("order-placed", customerId); // Bad: self-invocation — @Transactional on logAudit may not apply
    }

    @Transactional
    public void logAudit(String event, long entityId) throws SQLException {
        String sql = "INSERT INTO audit_log (event, entity_id) VALUES (?, ?)";
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, event);
            ps.setLong(2, entityId);
            ps.executeUpdate();
        }
    }
}
```

### Example 11: Text blocks, upserts, and domain exceptions

Title: Readable multi-line SQL and fail-fast translation
Description: Use Java text blocks for multi-line SQL (PostgreSQL `ON CONFLICT`, etc.). Keep `PreparedStatement` parameters bound; translate `SQLException` to a single domain runtime type so layers above the repository stay persistence-agnostic. Structured debug logging belongs at appropriate levels — avoid logging secrets or full row payloads at INFO.

**Good example:**

```java
import io.micronaut.context.annotation.Singleton;
import jakarta.inject.Inject;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Singleton
public class GreekGodRepository {

    private static final Logger LOG = LoggerFactory.getLogger(GreekGodRepository.class);

    private static final String SELECT_ORDERED = "SELECT name FROM greek_god ORDER BY name";
    private static final String UPSERT = """
            INSERT INTO greek_god (name) VALUES (?)
            ON CONFLICT (name) DO NOTHING
            """;

    private final DataSource dataSource;

    @Inject
    public GreekGodRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<String> findAllNamesOrdered() {
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(SELECT_ORDERED);
             ResultSet rs = ps.executeQuery()) {
            List<String> names = new ArrayList<>();
            while (rs.next()) {
                names.add(rs.getString(1));
            }
            LOG.debug("Loaded {} Greek god names from database", names.size());
            return names;
        } catch (SQLException e) {
            throw new GreekGodsDataAccessException("Failed to load Greek god names", e);
        }
    }

    public void upsertByName(String name) {
        try (Connection c = dataSource.getConnection();
             PreparedStatement ps = c.prepareStatement(UPSERT)) {
            ps.setString(1, name);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new GreekGodsDataAccessException("Failed to upsert Greek god: " + name, e);
        }
    }
}

public class GreekGodsDataAccessException extends RuntimeException {
    public GreekGodsDataAccessException(String message, Throwable cause) {
        super(message, cause);
    }
}
```

**Bad example:**

```java
// Bad: concatenated multi-line SQL without bind params; swallows or logs-and-ignores SQLException
public void upsertByName(String name) {
    String sql = "INSERT INTO greek_god (name) VALUES ('" + name + "') ON CONFLICT (name) DO NOTHING";
    // ...
}
```


## Output Format

- **ANALYZE** JDBC code for SQL injection risk, parameter binding style, try-with-resources coverage, transaction boundaries, missing `Optional` on single-row queries, exception translation, batch opportunities, and streaming gaps for large result sets
- **CATEGORIZE** findings by impact (SECURITY for injection risk, CORRECTNESS for missing transactions or unsafe single-row access, PERFORMANCE for N+1 / missing batch / missing fetch-size streaming, MAINTAINABILITY for inline mapping)
- **APPLY** improvements: introduce parameter binding, extract `mapRow` methods, wrap single-row queries in `Optional`, add `@Transactional` at service boundaries, add batch operations, set `setFetchSize` for large exports, translate `SQLException` subtypes to domain exceptions
- **HANDLE** exceptions: translate `SQLIntegrityConstraintViolationException` and other `SQLException` subtypes at the service layer; wrap in meaningful domain exceptions preserving the cause
- **TEST** with `@MicronautTest` and Testcontainers (or a test `DataSource`) for realistic database coverage; avoid mocking `DataSource` or `Connection` for behaviour you care about — use real schema with test configuration
- **RECOMMEND** Micronaut Data (`@512-frameworks-micronaut-data`) when generated repositories and entities fit; keep raw JDBC for ad-hoc SQL, reporting, bulk ETL, and database-specific SQL
- **VALIDATE** with `./mvnw compile` before and `./mvnw clean verify` after substantive edits


## Safeguards

- **BLOCKING SAFETY CHECK**: ALWAYS run `./mvnw compile` or `mvn compile` before ANY JDBC refactoring — compilation failure is a HARD STOP
- **SQL INJECTION**: Never build SQL by concatenating untrusted input — use `PreparedStatement` bind parameters exclusively
- **CONNECTION LEAKS**: Every `Connection`, `PreparedStatement`, and `ResultSet` must be closed — always use try-with-resources
- **TRANSACTION BOUNDARIES**: Multi-step writes must share a `@Transactional` boundary at the service layer; never rely on auto-commit for coordinated operations
- **SINGLE-ROW SAFETY**: Always check `ResultSet.next()` before reading columns; return `Optional.empty()` when no row is found rather than `null` or throwing
- **EXCEPTION TRANSLATION**: Translate `SQLIntegrityConstraintViolationException` and other `SQLException` subtypes to domain exceptions at service boundaries; preserve the original cause for diagnostics
- **BATCH SAFETY**: Wrap batch inserts/updates in `@Transactional`; call `executeBatch()` inside the same try-with-resources block as `prepareStatement()`
- **STREAMING**: A server-side cursor via `setFetchSize()` requires the `ResultSet` to remain open while rows are processed — do not close the connection until streaming is complete
- **SELF-INVOCATION**: Avoid calling `@Transactional` methods via `this.method()` within the same bean when you rely on interceptor behaviour — extract to a separate `@Singleton` collaborator
- **INCREMENTAL SAFETY**: Change data-access code in small steps, covered by `@MicronautTest` integration tests; do not rely on `./mvnw compile` alone to verify correctness
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall