동적 SQL과 간편 삽입

package board.jdbc;

import java.util.Locale;
import java.util.Objects;

final class LikePattern {
    private LikePattern() {
    }

    static String containsLiteral(String input) {
        Objects.requireNonNull(input, "input");
        if (input.isBlank()) {
            throw new IllegalArgumentException("search text is blank");
        }
        String escaped = input.toLowerCase(Locale.ROOT)
                .replace("\\", "\\\\")
                .replace("%", "\\%")
                .replace("_", "\\_");
        return "%" + escaped + "%";
    }
}

package board.jdbc;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

import board.application.PostSearch;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

public final class PostSearchSql {
    public BuiltQuery build(PostSearch search) {
        Objects.requireNonNull(search, "search");
        var clauses = new ArrayList<String>();
        var parameters = new MapSqlParameterSource()
                .addValue("memberId", search.memberId())
                .addValue("from", search.from())
                .addValue("to", search.to())
                .addValue("limit", search.size() + 1);

        clauses.add("member_id = :memberId");
        clauses.add("published_on between :from and :to");

        if (search.keyword() != null) {
            clauses.add("(lower(title) like :keyword escape '\\' "
                    + "or lower(content) like :keyword escape '\\')");
            parameters.addValue(
                    "keyword", LikePattern.containsLiteral(search.keyword()));
        }
        if (search.minimumCharacters() != null) {
            clauses.add("char_length(content) >= :minimumCharacters");
            parameters.addValue(
                    "minimumCharacters", search.minimumCharacters());
        }
        search.after().ifPresent(cursor -> {
            clauses.add("(published_on < :cursorDate "
                    + "or (published_on = :cursorDate and id < :cursorId))");
            parameters
                    .addValue("cursorDate", cursor.publishedOn())
                    .addValue("cursorId", cursor.id());
        });

        String sql = """
                select id, member_id, title, content, published_on,
                       client_request_id, created_at, version
                  from posts
                 where %s
                 order by published_on desc, id desc
                 limit :limit
                """.formatted(String.join("\n   and ", clauses));
        return new BuiltQuery(sql, parameters);
    }

    public record BuiltQuery(
            String sql,
            MapSqlParameterSource parameters
    ) {
        public BuiltQuery {
            Objects.requireNonNull(sql, "sql");
            Objects.requireNonNull(parameters, "parameters");
        }
    }
}

package board.jdbc;

import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.Objects;

import javax.sql.DataSource;

import board.application.DuplicatePostRequestException;
import board.application.PostPersistenceException;
import board.application.postcreation.CreatePostUseCase.CreatePostCommand;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

final class SimplePostInsert {
    private final SimpleJdbcInsert insert;

    SimplePostInsert(DataSource dataSource) {
        this.insert = new SimpleJdbcInsert(
                Objects.requireNonNull(dataSource, "dataSource"))
                .withTableName("posts")
                .usingColumns(
                        "member_id",
                        "title",
                        "content",
                        "published_on",
                        "client_request_id",
                        "created_at",
                        "version")
                .usingGeneratedKeyColumns("id");
    }

    long insert(CreatePostCommand command, Instant createdAt) {
        Objects.requireNonNull(command, "command");
        Objects.requireNonNull(createdAt, "createdAt");
        var parameters = new MapSqlParameterSource()
                .addValue("member_id", command.memberId())
                .addValue("title", command.title())
                .addValue("content", command.content())
                .addValue("published_on", command.publishedOn())
                .addValue("client_request_id", command.clientRequestId())
                .addValue("created_at", OffsetDateTime.ofInstant(
                        createdAt, ZoneOffset.UTC))
                .addValue("version", 0L);
        try {
            Number key = insert.executeAndReturnKey(parameters);
            if (key == null) {
                throw failure("SimpleJdbcInsert did not return a key");
            }
            long id = key.longValue();
            if (id <= 0) {
                throw failure("SimpleJdbcInsert returned a non-positive key");
            }
            return id;
        } catch (DuplicateKeyException exception) {
            throw new DuplicatePostRequestException(
                    command.memberId(), command.clientRequestId(), exception);
        } catch (PostPersistenceException exception) {
            throw exception;
        } catch (DataAccessException exception) {
            throw new PostPersistenceException(
                    "SimpleJdbcInsert failed", exception);
        }
    }

    private PostPersistenceException failure(String message) {
        return new PostPersistenceException(
                message, new IllegalStateException(message));
    }
}

package board.jdbc;

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

import java.time.LocalDate;
import java.util.Optional;

import board.application.PostCursor;
import board.application.PostSearch;

import org.junit.jupiter.api.Test;

class PostSearchSqlTest {
    private static final LocalDate FROM = LocalDate.parse("2026-08-01");
    private static final LocalDate TO = LocalDate.parse("2026-08-31");

    private final PostSearchSql builder = new PostSearchSql();

    @Test
    void 필수_소유자와_날짜_정렬_limit은_항상_고정된다() {
        var query = builder.build(new PostSearch(
                41L, null, FROM, TO, null, Optional.empty(), 20));

        assertThat(query.sql())
                .contains("member_id = :memberId")
                .contains("published_on between :from and :to")
                .contains("order by published_on desc, id desc")
                .contains("limit :limit")
                .doesNotContain(":keyword", ":minimumCharacters", ":cursorId");
        assertThat(query.parameters().getValue("memberId")).isEqualTo(41L);
        assertThat(query.parameters().getValue("limit")).isEqualTo(21);
    }

    @Test
    void percent_underscore_역슬래시는_순서대로_이스케이프된다() {
        var query = builder.build(new PostSearch(
                41L,
                "100%_\\path",
                FROM,
                TO,
                12,
                Optional.empty(),
                30));

        assertThat(query.sql())
                .contains("lower(title) like :keyword escape '\\'")
                .contains("lower(content) like :keyword escape '\\'")
                .contains("char_length(content) >= :minimumCharacters");
        assertThat(query.parameters().getValue("keyword"))
                .isEqualTo("%100\\%\\_\\\\path%");
        assertThat(query.parameters().getValue("minimumCharacters"))
                .isEqualTo(12);
    }

    @Test
    void 다음_page는_날짜와_ID의_배타_튜플을_함께_사용한다() {
        var cursor = new PostCursor(LocalDate.parse("2026-08-20"), 77L);
        var query = builder.build(new PostSearch(
                41L, "Spring", FROM, TO,
                null, Optional.of(cursor), 10));

        assertThat(query.sql())
                .contains("published_on < :cursorDate")
                .contains("published_on = :cursorDate and id < :cursorId")
                .contains("order by published_on desc, id desc");
        assertThat(query.parameters().getValue("cursorDate"))
                .isEqualTo(cursor.publishedOn());
        assertThat(query.parameters().getValue("cursorId"))
                .isEqualTo(cursor.id());
        assertThat(query.parameters().getValue("limit")).isEqualTo(11);
    }
}