使用 R2DBC 和 Jooq 初始化 MySQL Testcontainer

Question

我想在存储库级别为当前使用 Kotlin、Jooq 和 R2dbc 的微服务编写集成测试。 我希望我的测试也能在 R2dbc 模式下工作，但由于某种原因出现此异常：

Caused by: org.testcontainers.containers.JdbcDatabaseContainer$NoDriverFoundException: Could not get Driver
    at org.testcontainers.containers.JdbcDatabaseContainer.getJdbcDriverInstance(JdbcDatabaseContainer.java:187)
    at org.testcontainers.containers.JdbcDatabaseContainer.createConnection(JdbcDatabaseContainer.java:209)
    at org.testcontainers.containers.JdbcDatabaseContainer.waitUntilContainerStarted(JdbcDatabaseContainer.java:147)
    at org.testcontainers.containers.GenericContainer.tryStart(GenericContainer.java:466)
    ... 10 common frames omitted
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

可能，我必须指出我只想使用 r2dbc，而不是 jdbc？我已经看过规范，但不确定我是否应用了TC_INITSCRIPT< /code> 和 TC_IMAGE_TAG 正确。 我不使用 Spring Data r2dbc（仅限 jooq），这就是为什么 ResourceDatabasePopulator 不适合我。

我的测试如下所示：

@SpringBootTest(classes = [UserServiceApp::class])
@ActiveProfiles(profiles = ["test"])
@AutoConfigureWebTestClient
class UserServiceAppIT(@Autowired val client: WebTestClient) {

    @Nested
    inner class Find {

        @Test
        @DisplayName("Find existing user by id")
        fun `existing user credentials returns OK`() {
            val expectedUser = getCredentialsUser()  //this is a class with expected data
            val response = client.get()
                .uri("/user/2")       //this is my endpoint
                .accept(MediaType.APPLICATION_JSON)
                .exchange()
                .expectStatus().isOk
                .expectBody(UserCredentialsModel::class.java)
                .returnResult()
                .responseBody

            assertThat(response)
                .isNotNull
                .isEqualTo(expectedUser)
        }
}

yaml 文件中的测试配置：

server.port: 8080
spring:
  application:
    name: User Service Test
  r2dbc:
    url: r2dbc:tc:mysql:///pharmacy?TC_IMAGE_TAG=8.0.26&TC_INITSCRIPT=classpath/resources/init.sql
    password: root
    username: root
    pool:
      initial-size: 1
      max-size: 10
      max-idle-time: 30m

依赖项（gradle）：

buildscript {
    ext {
        springDependencyVersion = '1.0.11.RELEASE'
        springBootVersion = '2.5.3'
        kotlinVersion = '1.5.0'
        jooqPluginVersion = '6.0'
        springdocVersion = '1.5.10'
        r2dbcMySQLVersion = '0.8.2.RELEASE'
        r2dbcPoolVersion = '0.8.7.RELEASE'
        mockKVersion = '1.12.0'
        kotestVersion = '4.4.3'
        kotlinJsonVersion = '1.2.1'
        kotlinDateVersion = '0.2.1'
        testcontainersVersion = '1.16.0'
    }
}

Answer 1

将 Jooq 与 R2dbc 集成很容易。

@Configuration
class JooqConfig {

    @Bean
    fun dslContext(connectionFactory: ConnectionFactory) =
        using(TransactionAwareConnectionFactoryProxy(connectionFactory), SQLDialect.POSTGRES)

}

注意：如果您使用 Spring 2.7.x，请不要包含 Jooq starter。 Jooq自动配置仅支持Jdbc。

使用 Jooq 的示例。

class PostRepositoryImpl(private val dslContext: DSLContext) : PostRepositoryCustom {
    override fun findByKeyword(title: String): Flow<PostSummary> {
        val sql = dslContext
            .select(
                POSTS.ID,
                POSTS.TITLE,
                field("count(comments.id)", SQLDataType.BIGINT)
            )
            .from(
                POSTS
                    .leftJoin(COMMENTS.`as`("comments"))
                    .on(COMMENTS.POST_ID.eq(POSTS.ID))
            )
            .where(
                POSTS.TITLE.like("%$title%")
                    .and(POSTS.CONTENT.like("%$title%"))
                    .and(COMMENTS.CONTENT.like("%$title%"))
            )
            .groupBy(POSTS.ID)


        return Flux.from(sql)
            .map { r -> PostSummary(r.value1(), r.value2(), r.value3()) }
            .asFlow();
    }

    override suspend fun countByKeyword(title: String): Long {
        val sql = dslContext
            .select(
                DSL.field("count(distinct(posts.id))", SQLDataType.BIGINT)
            )
            .from(
                POSTS
                    .leftJoin(COMMENTS.`as`("comments"))
                    .on(COMMENTS.POST_ID.eq(POSTS.ID))
            )
            .where(
                POSTS.TITLE.like("%$title%")
                    .and(POSTS.CONTENT.like("%$title%"))
                    .and(COMMENTS.CONTENT.like("%$title%"))
            )
        return Mono.from(sql).map { it.value1() ?: 0 }.awaitSingle()
    }

}

TestContainers 数据库需要 Jdbc 驱动程序，将带有 testcontainter 的 MySQL Jdbc 驱动程序添加到测试范围中。

以下是使用 Postgres 和 Testcontainers 的示例。

@OptIn(ExperimentalCoroutinesApi::class)
@Testcontainers
@DataR2dbcTest()
@Import(JooqConfig::class, R2dbcConfig::class)
class PostRepositoriesTest {
    companion object {
        private val log = LoggerFactory.getLogger(PostRepositoriesTest::class.java)


        @Container
        val postgreSQLContainer = PostgreSQLContainer("postgres:12")
            .withCopyFileToContainer(
                MountableFile.forClasspathResource("/init.sql"),
                "/docker-entrypoint-initdb.d/init.sql"
            )

        @JvmStatic
        @DynamicPropertySource
        fun registerDynamicProperties(registry: DynamicPropertyRegistry) {
            registry.add("spring.r2dbc.url") {
                "r2dbc:postgresql://${postgreSQLContainer.host}:${postgreSQLContainer.firstMappedPort}/${postgreSQLContainer.databaseName}"
            }
            registry.add("spring.r2dbc.username") { postgreSQLContainer.username }
            registry.add("spring.r2dbc.password") { postgreSQLContainer.password }
        }

    }

    @Autowired
    lateinit var postRepository: PostRepository

    @Autowired
    lateinit var dslContext: DSLContext

    @BeforeEach
    fun setup() = runTest {
        log.info(" clear sample data ...")
        val deletedPostsCount = Mono.from(dslContext.deleteFrom(POSTS)).awaitSingle()
        log.debug(" deletedPostsCount: $deletedPostsCount")

    }

    @Test
    fun `query sample data`() = runTest {
        log.debug(" add new sample data...")
        val insertPostSql = dslContext.insertInto(POSTS)
            .columns(POSTS.TITLE, POSTS.CONTENT)
            .values("jooq test", "content of Jooq test")
            .returningResult(POSTS.ID)
        val postId = Mono.from(insertPostSql).awaitSingle()
        log.debug(" postId: $postId")

        val insertCommentSql = dslContext.insertInto(COMMENTS)
            .columns(COMMENTS.POST_ID, COMMENTS.CONTENT)
            .values(postId.component1(), "test comments")
            .values(postId.component1(), "test comments 2")

        val insertedCount = Mono.from(insertCommentSql).awaitSingle()

        log.info(" insertedCount: $insertedCount")

        val querySQL = dslContext
            .select(
                POSTS.TITLE,
                POSTS.CONTENT,
                multiset(
                    select(COMMENTS.CONTENT)
                        .from(COMMENTS)
                        .where(COMMENTS.POST_ID.eq(POSTS.ID))
                ).`as`("comments")
            )
            .from(POSTS)
            .orderBy(POSTS.CREATED_AT)

        Flux.from(querySQL).asFlow()
            .onEach { log.info("querySQL result: $it") }
            .collect()

        val posts = postRepository.findByKeyword("test").toList()
        posts shouldNotBe null
        posts.size shouldBe 1
        posts[0].commentsCount shouldBe 2

        postRepository.countByKeyword("test") shouldBe 1
    }

// other tests

我的示例项目基于 Postgres、R2dbc 和 Spring Data R2dbc： https://github.com/hantsy/spring-r2dbc-sample/blob/master/jooq-kotlin-co-gradle