JDBC 데이터베이스 연결

rootProject.name = "jdbc-transaction-contracts"

plugins {
    java
}

group = "board"
version = "1.0.0"

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(25)
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation(enforcedPlatform(
        "org.springframework.boot:spring-boot-dependencies:4.1.1"))
    implementation("org.springframework.boot:spring-boot-starter-jdbc")

    testImplementation(enforcedPlatform(
        "org.springframework.boot:spring-boot-dependencies:4.1.1"))
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("com.h2database:h2")
    testRuntimeOnly("org.junit.platform:junit-platform-launcher")

    constraints {
        testImplementation("org.junit.jupiter:junit-jupiter") {
            version { strictly("6.0.3") }
        }
    }
}

tasks.withType<JavaCompile>().configureEach {
    options.encoding = "UTF-8"
    options.release = 25
    options.compilerArgs.add("-parameters")
}

tasks.test {
    useJUnitPlatform()
}

package board.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Objects;
import java.util.Properties;

public final class JdbcConnectionProbe {
    public DatabaseIdentity connect(
            String url,
            String username,
            String password
    ) throws SQLException {
        Objects.requireNonNull(url, "url");
        Objects.requireNonNull(username, "username");
        Objects.requireNonNull(password, "password");

        var properties = new Properties();
        properties.setProperty("user", username);
        properties.setProperty("password", password);

        try (var connection = DriverManager.getConnection(url, properties)) {
            var metadata = connection.getMetaData();
            return new DatabaseIdentity(
                    metadata.getDatabaseProductName(),
                    metadata.getDatabaseProductVersion(),
                    metadata.getDriverName(),
                    metadata.getDriverVersion(),
                    connection.getAutoCommit());
        }
    }

    public record DatabaseIdentity(
            String product,
            String productVersion,
            String driver,
            String driverVersion,
            boolean autoCommit
    ) {
        public DatabaseIdentity {
            requireText(product, "product");
            requireText(productVersion, "productVersion");
            requireText(driver, "driver");
            requireText(driverVersion, "driverVersion");
        }

        private static void requireText(String value, String name) {
            if (value == null || value.isBlank()) {
                throw new IllegalArgumentException(name + " required");
            }
        }
    }
}

spring.datasource.url=jdbc:h2:mem:board;MODE=PostgreSQL;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=
spring.sql.init.mode=always
spring.datasource.hikari.pool-name=board-pool
spring.datasource.hikari.maximum-pool-size=12
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=2000
spring.datasource.hikari.validation-timeout=1000
spring.datasource.hikari.max-lifetime=1740000
spring.datasource.hikari.keepalive-time=120000

create table schema_metadata (
    singleton_key integer primary key,
    schema_version integer not null,
    constraint ck_schema_metadata_singleton
        check (singleton_key = 1),
    constraint ck_schema_metadata_version
        check (schema_version > 0)
);

insert into schema_metadata(singleton_key, schema_version)
values (1, 1);

create table members (
    id bigint generated by default as identity primary key,
    email varchar(255) not null,
    password_hash varchar(255) not null,
    name varchar(60) not null,
    active boolean not null default true,
    daily_character_limit integer not null default 10000,
    constraint ck_members_id check (id > 0),
    constraint uq_members_email unique (email),
    constraint ck_members_daily_limit
        check (daily_character_limit > 0)
);

create table posts (
    id bigint generated by default as identity primary key,
    member_id bigint not null,
    title varchar(80) not null,
    content varchar(720) not null,
    published_on date not null,
    client_request_id varchar(64) not null,
    created_at timestamp with time zone not null,
    version bigint not null default 0,
    constraint ck_posts_id check (id > 0),
    constraint ck_posts_title
        check (char_length(trim(title)) between 1 and 80),
    constraint ck_posts_content
        check (char_length(trim(content)) >= 1
            and char_length(content) <= 720),
    constraint ck_posts_client_request
        check (regexp_like(
            client_request_id,
            '^[A-Za-z0-9_-]{8,64}$')),
    constraint ck_posts_version check (version >= 0),
    constraint fk_posts_member
        foreign key (member_id) references members(id),
    constraint uq_posts_member_request
        unique (member_id, client_request_id),
    constraint uq_posts_outbox_identity
        unique (id, member_id, client_request_id)
);

create table daily_post_stats (
    member_id bigint not null,
    published_on date not null,
    total_characters integer not null default 0,
    constraint pk_daily_post_stats
        primary key (member_id, published_on),
    constraint ck_daily_post_stats_total
        check (total_characters >= 0),
    constraint fk_daily_post_stats_member
        foreign key (member_id) references members(id)
);

create table post_event_outbox (
    id bigint generated by default as identity primary key,
    post_id bigint not null,
    member_id bigint not null,
    occurred_at timestamp with time zone not null,
    client_request_id varchar(64) not null,
    published_at timestamp with time zone,
    constraint ck_post_event_outbox_id check (id > 0),
    constraint ck_post_event_outbox_request
        check (regexp_like(
            client_request_id,
            '^[A-Za-z0-9_-]{8,64}$')),
    constraint fk_post_event_outbox_post_identity
        foreign key (post_id, member_id, client_request_id)
        references posts(id, member_id, client_request_id),
    constraint uq_post_event_outbox_post unique (post_id)
);

package board.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Objects;

import javax.sql.DataSource;

public record DatabaseHealth(
        Status status,
        String product,
        int schemaVersion
) {
    public DatabaseHealth {
        Objects.requireNonNull(status, "status");
        if (product == null || product.isBlank() || schemaVersion < 1) {
            throw new IllegalArgumentException("invalid database health");
        }
    }

    public static DatabaseHealth inspect(
            DataSource dataSource,
            String expectedProduct,
            int expectedSchemaVersion
    ) throws SQLException {
        Objects.requireNonNull(dataSource, "dataSource");
        if (expectedProduct == null || expectedProduct.isBlank()
                || expectedSchemaVersion < 1) {
            throw new IllegalArgumentException("invalid expected database");
        }

        try (Connection connection = dataSource.getConnection()) {
            String actualProduct = connection.getMetaData()
                    .getDatabaseProductName();
            int actualSchemaVersion = readSchemaVersion(connection);
            Status status;
            if (!actualProduct.equals(expectedProduct)
                    || actualSchemaVersion < expectedSchemaVersion) {
                status = Status.NOT_READY;
            } else if (actualSchemaVersion > expectedSchemaVersion) {
                status = Status.DEGRADED;
            } else {
                status = Status.READY;
            }
            return new DatabaseHealth(
                    status, actualProduct, actualSchemaVersion);
        }
    }

    private static int readSchemaVersion(Connection connection)
            throws SQLException {
        try (var statement = connection.prepareStatement("""
                select schema_version
                  from schema_metadata
                 where singleton_key = 1
                """);
             var result = statement.executeQuery()) {
            if (!result.next()) {
                throw new SQLException("schema metadata row missing");
            }
            int version = result.getInt(1);
            if (result.wasNull() || version < 1 || result.next()) {
                throw new SQLException("invalid schema metadata");
            }
            return version;
        }
    }

    public enum Status {
        READY, DEGRADED, NOT_READY
    }
}

package board.jdbc;

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

import org.h2.jdbcx.JdbcDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

class JdbcConnectionProbeTest {
    @Test
    void H2_identity와_schema_준비_상태를_구별한다()
            throws Exception {
        var dataSource = new JdbcDataSource();
        dataSource.setURL(
                "jdbc:h2:mem:probe;MODE=PostgreSQL;DB_CLOSE_DELAY=-1");
        dataSource.setUser("sa");
        new ResourceDatabasePopulator(
                new ClassPathResource("schema.sql")).execute(dataSource);

        var identity = new JdbcConnectionProbe().connect(
                dataSource.getURL(),
                "sa",
                "");

        assertThat(identity.product()).isEqualTo("H2");
        assertThat(identity.driver()).contains("H2 JDBC Driver");
        assertThat(identity.productVersion()).isNotBlank();
        assertThat(identity.driverVersion()).isNotBlank();
        assertThat(identity.autoCommit()).isTrue();

        assertThat(DatabaseHealth.inspect(
                dataSource, "H2", 1).status())
                .isEqualTo(DatabaseHealth.Status.READY);
        assertThat(DatabaseHealth.inspect(
                dataSource, "H2", 2).status())
                .isEqualTo(DatabaseHealth.Status.NOT_READY);

        try (var connection = dataSource.getConnection();
             var statement = connection.createStatement()) {
            assertThat(statement.executeUpdate("""
                    update schema_metadata
                       set schema_version = 2
                     where singleton_key = 1
                    """)).isEqualTo(1);
        }

        var newer = DatabaseHealth.inspect(dataSource, "H2", 1);
        assertThat(newer.status())
                .isEqualTo(DatabaseHealth.Status.DEGRADED);
        assertThat(newer.schemaVersion()).isEqualTo(2);
    }
}