JDBC 리소스 수명 주기

package board.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

import javax.sql.DataSource;

public final class JdbcPostQuery {
    private static final String FIRST_PAGE = """
            select id, member_id, title, content, published_on,
                   client_request_id, created_at, version
              from posts
             where member_id = ?
               and published_on between ? and ?
             order by published_on desc, id desc
             limit ?
            """;
    private static final String NEXT_PAGE = """
            select id, member_id, title, content, published_on,
                   client_request_id, created_at, version
              from posts
             where member_id = ?
               and published_on between ? and ?
               and (published_on < ?
                    or (published_on = ? and id < ?))
             order by published_on desc, id desc
             limit ?
            """;

    private final DataSource dataSource;

    public JdbcPostQuery(DataSource dataSource) {
        this.dataSource = Objects.requireNonNull(dataSource, "dataSource");
    }

    public Page findPage(
            long memberId,
            LocalDate from,
            LocalDate to,
            Optional<PostCursor> after,
            int pageSize
    ) throws SQLException {
        Objects.requireNonNull(from, "from");
        Objects.requireNonNull(to, "to");
        Objects.requireNonNull(after, "after");
        if (memberId <= 0 || from.isAfter(to)
                || pageSize < 1 || pageSize > 100) {
            throw new IllegalArgumentException("invalid query boundary");
        }

        var rows = new ArrayList<PostRow>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(
                     after.isPresent() ? NEXT_PAGE : FIRST_PAGE)) {
            bind(statement, memberId, from, to, after, pageSize + 1);
            statement.setFetchSize(pageSize + 1);
            statement.setQueryTimeout(2);
            try (ResultSet result = statement.executeQuery()) {
                while (result.next()) {
                    rows.add(map(result));
                }
            }
        }

        boolean hasMore = rows.size() > pageSize;
        if (hasMore) {
            rows.removeLast();
        }
        var copied = List.copyOf(rows);
        var nextCursor = hasMore
                ? Optional.of(PostCursor.from(copied.getLast()))
                : Optional.<PostCursor>empty();
        return new Page(copied, nextCursor);
    }

    private void bind(
            PreparedStatement statement,
            long memberId,
            LocalDate from,
            LocalDate to,
            Optional<PostCursor> after,
            int limit
    ) throws SQLException {
        statement.setLong(1, memberId);
        statement.setObject(2, from);
        statement.setObject(3, to);
        if (after.isPresent()) {
            var cursor = after.orElseThrow();
            statement.setObject(4, cursor.publishedOn());
            statement.setObject(5, cursor.publishedOn());
            statement.setLong(6, cursor.id());
            statement.setInt(7, limit);
        } else {
            statement.setInt(4, limit);
        }
    }

    private PostRow map(ResultSet result) throws SQLException {
        return new PostRow(
                result.getLong("id"),
                result.getLong("member_id"),
                result.getString("title"),
                result.getString("content"),
                result.getObject("published_on", LocalDate.class),
                result.getString("client_request_id"),
                result.getObject("created_at", OffsetDateTime.class)
                        .toInstant(),
                result.getLong("version"));
    }

    public record Page(
            List<PostRow> rows,
            Optional<PostCursor> nextCursor
    ) {
        public Page {
            rows = List.copyOf(rows);
            nextCursor = Objects.requireNonNull(
                    nextCursor, "nextCursor");
            if (rows.isEmpty() && nextCursor.isPresent()) {
                throw new IllegalArgumentException(
                        "empty page cannot have a next cursor");
            }
        }
    }
}

package board.jdbc;

import java.time.Instant;
import java.time.LocalDate;
import java.util.Objects;

public record PostRow(
        long id,
        long memberId,
        String title,
        String content,
        LocalDate publishedOn,
        String clientRequestId,
        Instant createdAt,
        long version
) {
    public PostRow {
        if (id <= 0 || memberId <= 0 || version < 0) {
            throw new IllegalArgumentException(
                    "invalid post identity or version");
        }
        Objects.requireNonNull(publishedOn, "publishedOn");
        Objects.requireNonNull(createdAt, "createdAt");
        if (title == null || title.isBlank() || title.length() > 80) {
            throw new IllegalArgumentException("invalid title");
        }
        if (content == null || content.isBlank()
                || content.length() > 720) {
            throw new IllegalArgumentException("invalid content");
        }
        if (clientRequestId == null
                || !clientRequestId.matches("[A-Za-z0-9_-]{8,64}")) {
            throw new IllegalArgumentException(
                    "invalid clientRequestId");
        }
    }
}

package board.jdbc;

import java.time.LocalDate;
import java.util.Objects;

public record PostCursor(LocalDate publishedOn, long id) {
    public PostCursor {
        Objects.requireNonNull(publishedOn, "publishedOn");
        if (id <= 0) {
            throw new IllegalArgumentException("positive id required");
        }
    }

    public static PostCursor from(PostRow row) {
        Objects.requireNonNull(row, "row");
        return new PostCursor(row.publishedOn(), row.id());
    }
}

package board.jdbc;

import static org.assertj.core.api.Assertions.assertThat;

import java.time.Instant;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Optional;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

class JdbcPostQueryTest {
    @Test
    void keyset은_45행을_한번씩_읽고_connection을_반환한다()
            throws Exception {
        var config = new HikariConfig();
        config.setJdbcUrl(
                "jdbc:h2:mem:lifetime;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setMaximumPoolSize(1);
        config.setMinimumIdle(0);

        try (var dataSource = new HikariDataSource(config)) {
            new ResourceDatabasePopulator(
                    new ClassPathResource("schema.sql"))
                    .execute(dataSource);
            seed(dataSource);

            var query = new JdbcPostQuery(dataSource);
            var all = new ArrayList<PostRow>();
            var pageSizes = new ArrayList<Integer>();
            var cursorStates = new ArrayList<Boolean>();
            Optional<PostCursor> cursor = Optional.empty();
            do {
                var page = query.findPage(
                        41L,
                        LocalDate.parse("2026-07-01"),
                        LocalDate.parse("2026-07-31"),
                        cursor,
                        20);
                all.addAll(page.rows());
                pageSizes.add(page.rows().size());
                cursorStates.add(page.nextCursor().isPresent());
                cursor = page.nextCursor();
                assertThat(dataSource.getHikariPoolMXBean()
                        .getActiveConnections()).isZero();
            } while (cursor.isPresent());

            assertThat(pageSizes).containsExactly(20, 20, 5);
            assertThat(cursorStates).containsExactly(true, true, false);
            assertThat(all).hasSize(45);
            assertThat(all).extracting(PostRow::id)
                    .doesNotHaveDuplicates();
            assertThat(all).allSatisfy(row -> {
                assertThat(row.memberId()).isEqualTo(41L);
                assertThat(row.clientRequestId())
                        .startsWith("request-");
                assertThat(row.version()).isZero();
            });
            for (int index = 1; index < all.size(); index++) {
                var previous = all.get(index - 1);
                var current = all.get(index);
                assertThat(previous.publishedOn().isAfter(
                        current.publishedOn())
                        || previous.publishedOn().equals(
                                current.publishedOn())
                        && previous.id() > current.id()).isTrue();
            }
        }
    }

    private void seed(HikariDataSource dataSource) throws Exception {
        try (var connection = dataSource.getConnection();
             var member = connection.prepareStatement("""
                     insert into members(
                         id, email, password_hash, name)
                     values (?, ?, ?, ?)
                     """);
             var post = connection.prepareStatement("""
                     insert into posts(
                         member_id, title, content, published_on,
                         client_request_id, created_at)
                     values (?, ?, ?, ?, ?, ?)
                     """)) {
            member.setLong(1, 41L);
            member.setString(2, "[email protected]");
            member.setString(3, "hash");
            member.setString(4, "회원 41");
            member.executeUpdate();

            for (int index = 1; index <= 45; index++) {
                post.setLong(1, 41L);
                post.setString(2, "JDBC " + index);
                post.setString(3, "본문 " + index);
                post.setObject(4, LocalDate.of(2026, 7, 14)
                        .minusDays((index - 1) / 15));
                post.setString(5, "request-%04d".formatted(index));
                post.setObject(6, OffsetDateTime.ofInstant(
                        Instant.parse("2026-07-14T00:00:00Z")
                                .plusSeconds(index),
                        ZoneOffset.UTC));
                post.addBatch();
            }
            assertThat(post.executeBatch()).hasSize(45);
        }
    }
}