时间戳

首页 > 解决方案 > 使用 r2dbc 和 flyway 在 Spring Boot 应用程序中设置 h2

java - 使用 r2dbc 和 flyway 在 Spring Boot 应用程序中设置 h2

问题描述

我正在使用 Spring Boot 和名为 r2dbc 的反应式 jdbc 驱动程序。在我的主应用程序中，我使用 Postgres 作为数据库，现在我想使用 h2 进行测试。Flyway 迁移正在使用设置，但是当 Spring 应用程序能够插入记录时。

这是我的设置和代码

@SpringBootTest
class CustomerRepositoryTest {

    @Autowired
    CustomerRepository repository;

    @Test
    void insertToDatabase() {
        repository.saveAll(List.of(new Customer("Jack", "Bauer"),
                new Customer("Chloe", "O'Brian"),
                new Customer("Kim", "Bauer"),
                new Customer("David", "Palmer"),
                new Customer("Michelle", "Dessler")))
                .blockLast(Duration.ofSeconds(10));
    }
}

这是我得到的错误

 :: Spring Boot ::        (v2.3.4.RELEASE)

2020-10-14 15:59:18.538  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : Starting CustomerRepositoryTest on imalik8088.fritz.box with PID 25279 (started by imalik in /Users/imalik/code/private/explore-java/spring-example)
2020-10-14 15:59:18.540  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : No active profile set, falling back to default profiles: default
2020-10-14 15:59:19.108  INFO 25279 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
2020-10-14 15:59:19.273  INFO 25279 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 160ms. Found 1 R2DBC repository interfaces.
2020-10-14 15:59:19.894  INFO 25279 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 6.5.0 by Redgate
2020-10-14 15:59:20.052  INFO 25279 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:h2:mem:///DBNAME (H2 1.4)
2020-10-14 15:59:20.118  INFO 25279 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.022s)
2020-10-14 15:59:20.131  INFO 25279 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "PUBLIC"."flyway_schema_history" ...
2020-10-14 15:59:20.175  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2020-10-14 15:59:20.178  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.0.0 - schma
2020-10-14 15:59:20.204  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.036s)
2020-10-14 15:59:20.689  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : Started CustomerRepositoryTest in 2.466 seconds (JVM running for 3.326)

2020-10-14 15:59:21.115 DEBUG 25279 --- [           main] o.s.d.r2dbc.core.DefaultDatabaseClient   : Executing SQL statement [INSERT INTO customer (first_name, last_name) VALUES ($1, $2)]


org.springframework.data.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [INSERT INTO customer (first_name, last_name) VALUES ($1, $2)]; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [42102] [42S02] Tabelle "CUSTOMER" nicht gefunden
Table "CUSTOMER" not found; SQL statement:
INSERT INTO customer (first_name, last_name) VALUES ($1, $2) [42102-200]

我的 src/test/resources/application.yaml 看起来像这样：

spring:
  r2dbc:
    url: r2dbc:h2:mem:///DBNAME?options=DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password:

  flyway:
    url: jdbc:h2:mem:///DBNAME
    baseline-on-migrate: true
    user: sa
    password:

有什么想法缺少什么或设置有什么问题吗？如果需要更多信息，请告诉我。

添加/解决方案：

jdbc 和 r2dbc 的 url 模式不同。我的工作解决方案如下：

url: r2dbc:h2:file:///./tmp/test-database
url: jdbc:h2:file:./tmp/test-database

为了设置 Flyway，您必须配置 Flyway：

// Flyway is not compatible with r2dbc yet, therefore this config class is created
@Configuration
public class FlywayConfig {

    private final Environment env;

    public FlywayConfig(final Environment env) {
        this.env = env;
    }

    @Bean(initMethod = "migrate")
    public Flyway flyway() {
        return new Flyway(Flyway.configure()
                .baselineOnMigrate(true)
                .dataSource(
                        env.getRequiredProperty("spring.flyway.url"),
                        env.getRequiredProperty("spring.flyway.user"),
                        env.getRequiredProperty("spring.flyway.password"))
        );
    }
}

标签： javaspring-bootflywayspring-data-r2dbc

解决方案

我在内存中设置和访问 h2 数据库进行测试时遇到了同样的问题：

  • 使用JDBC驱动程序进行数据库迁移的 Liquibase
  • 使用R2DBC驱动程序测试 Reactive Crud Repository

出现错误：

org.springframework.data.r2dbc.BadSqlGrammarException: executeMany; 错误的 SQL 语法 [INSERT INTO MY_TABLE... 找不到表“MY_TABLE”...

受克里斯解决方案的启发，我将src/testresources/application.properties文件配置如下：

spring.r2dbc.url=r2dbc:h2:mem:///~/db/testdb
spring.r2dbc.username=sa
spring.r2dbc.password=

spring.liquibase.url=jdbc:h2:mem:~/db/testdb;DB_CLOSE_DELAY=-1
spring.liquibase.user=sa
spring.liquibase.password=
spring.liquibase.enabled=true

推荐阅读