DataSource·커넥션 풀

package board.jdbc;

import java.sql.SQLException;
import java.time.LocalDate;

import javax.sql.DataSource;

public final class PostCountQuery {
    private static final String SQL = """
            select count(*)
              from posts
             where member_id = ? and published_on = ?
            """;

    private final DataSource dataSource;

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

    public int count(long memberId, LocalDate publishedOn)
            throws SQLException {
        try (var connection = dataSource.getConnection();
             var statement = connection.prepareStatement(SQL)) {
            statement.setLong(1, memberId);
            statement.setObject(2, publishedOn);
            try (var result = statement.executeQuery()) {
                if (!result.next()) {
                    throw new SQLException("count row missing");
                }
                return result.getInt(1);
            }
        }
    }
}

package board.jdbc;

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

import java.time.LocalDate;

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 PostCountQueryTest {
    @Test
    void query가_정확한_count를_읽고_connection을_반환한다()
            throws Exception {
        var config = new HikariConfig();
        config.setJdbcUrl(
                "jdbc:h2:mem:post_count;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);
            try (var connection = dataSource.getConnection();
                 var statement = connection.createStatement()) {
                statement.execute("""
                        insert into members(
                          id, email, password_hash, name,
                          active, daily_character_limit)
                        values (
                          41, '[email protected]', 'hash', '회원41',
                          true, 10000),
                          (42, '[email protected]', 'hash', '회원42',
                          true, 10000)
                        """);
                statement.execute("""
                        insert into posts(
                          member_id, title, content, published_on,
                          client_request_id, created_at, version)
                        values
                          (41, 'A', '본문 A', date '2026-08-29',
                           'count_req_A1', timestamp with time zone
                           '2026-08-29 00:00:00+00', 0),
                          (41, 'B', '본문 B', date '2026-08-29',
                           'count_req_B2', timestamp with time zone
                           '2026-08-29 00:00:01+00', 0),
                          (42, 'C', '본문 C', date '2026-08-29',
                           'count_req_C3', timestamp with time zone
                           '2026-08-29 00:00:02+00', 0)
                        """);
            }

            var query = new PostCountQuery(dataSource);

            assertThat(query.count(
                    41L, LocalDate.of(2026, 8, 29))).isEqualTo(2);
            assertThat(query.count(
                    41L, LocalDate.of(2026, 8, 30))).isZero();
            assertThat(dataSource.getHikariPoolMXBean()
                    .getActiveConnections()).isZero();
        }
    }
}

package board.jdbc;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertThrows;

import java.sql.Connection;
import java.sql.SQLTransientConnectionException;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;

class PoolExhaustionTest {
    @Test
    void 최대_연결이_사용_중이면_timeout으로_실패하고_반환_뒤_회복한다()
            throws Exception {
        var config = new HikariConfig();
        config.setJdbcUrl(
                "jdbc:h2:mem:pool_exhaustion;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setMaximumPoolSize(1);
        config.setMinimumIdle(0);
        config.setConnectionTimeout(250);

        try (var dataSource = new HikariDataSource(config)) {
            Connection held = dataSource.getConnection();
            try {
                assertThat(dataSource.getHikariPoolMXBean()
                        .getActiveConnections()).isEqualTo(1);
                long started = System.nanoTime();

                assertThrows(
                        SQLTransientConnectionException.class,
                        dataSource::getConnection);

                long elapsedMillis =
                        (System.nanoTime() - started) / 1_000_000;
                assertThat(elapsedMillis)
                        .isGreaterThanOrEqualTo(200)
                        .isLessThan(2_000);
                assertThat(dataSource.getHikariPoolMXBean()
                        .getThreadsAwaitingConnection()).isZero();
            } finally {
                held.close();
            }

            try (var recovered = dataSource.getConnection()) {
                assertThat(recovered.isValid(1)).isTrue();
            }
            assertThat(dataSource.getHikariPoolMXBean()
                    .getActiveConnections()).isZero();
        }
    }
}

package board.jdbc;

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

import java.sql.Connection;
import java.sql.SQLException;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;

class PoolStateResetTest {
    @Test
    void 같은_물리_session을_다시_빌려도_autoCommit은_복원된다()
            throws Exception {
        var config = new HikariConfig();
        config.setJdbcUrl(
                "jdbc:h2:mem:pool_reset;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setMaximumPoolSize(1);
        config.setMinimumIdle(1);

        try (var dataSource = new HikariDataSource(config)) {
            long firstSession;
            try (Connection first = dataSource.getConnection()) {
                firstSession = sessionId(first);
                first.setAutoCommit(false);
                first.rollback();
            }

            try (Connection second = dataSource.getConnection()) {
                assertThat(sessionId(second)).isEqualTo(firstSession);
                assertThat(second.getAutoCommit()).isTrue();
            }
        }
    }

    private long sessionId(Connection connection) throws SQLException {
        try (var statement = connection.createStatement();
             var result = statement.executeQuery("select session_id()")) {
            result.next();
            return result.getLong(1);
        }
    }
}