[Java Spring] Test Code에서의 @Sql 어노테이션의 문제점

@ActiveProfiles("test") //test profile
@SpringBootTest
@ExtendWith(SpringExtension.class)
@ContextConfiguration
@Sql("classpath:CommentRepository.sql")
class CommentRepositoryImplTest {
    @Autowired
    DataSource dataSource;
    @Autowired
    CommentRepository commentRepository;
    @BeforeEach
    void beforeEach(){
        Comment comment = new Comment(1L,
                new MemberProfileEntity("set_image_path","set_nickname","set_introduce","set_tag"),
                Date.valueOf(now()),
                null,
                new CommentSaveDto(PostType.MEMO,1L,"set_text"));
        commentRepository.createComment(comment);
    }

    @Test
    @DisplayName("올바른 댓글 형식에서의 댓글 저장")
    void createValidComment(){
        Comment comment = new Comment(1L,
                new MemberProfileEntity("test_image_path","test_nickname","test_introduce","test_tag"),
                Date.valueOf(now()),
                null,
                new CommentSaveDto(PostType.MEMO,1L,"comment_text"));
        assertEquals(commentRepository.createComment(comment),2);
    }
}

이렇게 했을 때의 문제점이 무엇일까?
바로 모든 메서드에 @Sql 어노테이션이 동작한다는 점이다.
@Sql 어노테이션에 포함되어있는 Sql문은

create sequence comment_seq;

create table comment.info(
    id int8 primary key DEFAULT nextval('comment_seq'::regclass),
    author_id int8 not null,
    author_image_path varchar(300),
    author_name VARCHAR(15) not null,
    post_type varchar not null,
    post_id int8 not null,
    likes int8 not null default 0,
    re_comments int8 not null default 0,
    comment_text text not null,
    created_date timestamp,
    updated_date timestamp
);

insert into comment.info (author_id, author_image_path, author_name, post_type, post_id, comment_text, created_date)
values (1,"image_path","writer_name","MEMO",1,"test for comment_text",2023-09-01);

이렇게 되어있어서 매 메서드마다 해당 Sql을 실행하면 중복 오류가 뜰 수 밖에 없다.

이를 해결하기 위해서는

@ActiveProfiles("test") //test profile
@SpringBootTest
@ExtendWith(SpringExtension.class)
@ContextConfiguration
//@Sql("classpath:CommentRepository.sql")
class CommentRepositoryImplTest {
    @Autowired
    DataSource dataSource;
    @Autowired
    CommentRepository commentRepository;

    @BeforeAll
    static void setup(@Autowired DataSource dataSource) {
        try (Connection conn = dataSource.getConnection()) {
            // you'll have to make sure conn.autoCommit = true (default for e.g. H2)
            // e.g. url=jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1;MODE=MySQL
            ScriptUtils.executeSqlScript(conn, new ClassPathResource("CommentRepository.sql"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @BeforeEach
    void beforeEach(){
        Comment comment = new Comment(1L,
                new MemberProfileEntity("회원 프로필 이미지 저장 경로","회원 닉네임","회원 자기소개","회원 개인 태그"),
                Date.valueOf(now()),
                null,
                new CommentSaveDto(PostType.MEMO,1L,"댓글 내용 저장"));
        commentRepository.createComment(comment);
    }

    @Test
    @DisplayName("올바른 댓글 형식에서의 댓글 저장")
    void createValidComment(){
        Comment comment = new Comment(1L,
                new MemberProfileEntity("프로필 이미지 경로","회원 닉네임","회원 자기소개","회원 개인 태그"),
                Date.valueOf(now()),
                null,
                new CommentSaveDto(PostType.MEMO,1L,"댓글 내용 저장"));
        assertDoesNotThrow(()->commentRepository.createComment(comment));
    }
}

다음처럼 @BeforeAll 어노테이션이 담긴 메서드에 ScriptUtils를 활용하여 작성해야 한다. 스택오버플로우의 도움을 좀 받았다.

https://stackoverflow.com/questions/47775560/how-can-springs-test-annotation-sql-behave-like-beforeclass