Spring R2DBC使用postgres初始化数据库的正确方法

Question

我有以下代码：

@Component
public class TemplateDatabaseLoader {
    private Logger LOGGER = LoggerFactory.getLogger(TemplateDatabaseLoader.class);

    @Bean
    public CommandLineRunner demo(DatabaseClient databaseClient, ItemRepository itemRepository) {
        return args -> {
            databaseClient.execute(
                    "CREATE TABLE item (" +
                            "id SERIAL PRIMARY KEY," +
                            "name VARCHAR(255)," +
                            "price REAL" +
                        ");"
            ).fetch().all().blockLast(Duration.ofSeconds(10));
            itemRepository.save(new Item("Alf alarm clock", 19.99)).block();
            LOGGER.debug("COMMAND LINE RUNNER");
            itemRepository.save(new Item("Smurf TV tray", 24.99)).block();
        };
    }
}

以及：

@SpringBootApplication
public class DemoApplication extends AbstractR2dbcConfiguration {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Bean
    public ConnectionFactory connectionFactory() {
        PostgresqlConnectionFactory connectionFactory = new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
                .host("127.0.0.1")
                .database("cart")
                .username("cart")
                .password("cart").build());
        return connectionFactory;
    }

    @Bean(name={"r2dbcDatabaseClient"})
    DatabaseClient databaseClient() {
        return DatabaseClient.create(connectionFactory());
    }
}

我得到以下错误：

Suppressed: java.lang.Exception: #block terminated with an error
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: relation "item" already exists

以及更早的错误：

Caused by: java.lang.ClassNotFoundException: org.springframework.jdbc.CannotGetJdbcConnectionException

如果我将代码修改为：

CREATE TABLE IF NOT EXISTS item

然后，我不再得到关于项目关系存在的错误，但是，似乎事务被完全取消了？

我得到以下输出：

2020-09-21 17:31:58.476 DEBUG 16639 --- [  restartedMain] com.example.demo.TemplateDatabaseLoader  : COMMAND LINE RUNNER
2020-09-21 17:31:58.476 DEBUG 16639 --- [actor-tcp-nio-2] i.r.postgresql.util.FluxDiscardOnCancel  : received cancel signal

所以我的问题是

>

正确的方法是什么？

为什么我的CommandLineRunner代码似乎执行了两次？该表在运行代码后不会持久化，因此它似乎必须执行两次才能获得关于现有表的第一个错误。

谢谢你。

Answer 1

我成功了。我添加了一个新类以从文件加载架构：

@Configuration
public class InitializerConfiguration {
    private Logger LOGGER = LoggerFactory.getLogger(InitializerConfiguration.class);

    @Bean
    public ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {

        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);

        CompositeDatabasePopulator populator = new CompositeDatabasePopulator();
        populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));
        initializer.setDatabasePopulator(populator);

        return initializer;
    }
}

这将加载架构。资源下的sql。我的TemplateDatabaseLoader现在如下所示：

@Component
public class TemplateDatabaseLoader {
    private Logger LOGGER = LoggerFactory.getLogger(TemplateDatabaseLoader.class);

    @Bean
    public CommandLineRunner demo(ItemRepository itemRepository) {
        return args -> {
            itemRepository.save(new Item("Alf alarm clock", 19.99)).block();
            itemRepository.save(new Item("Smurf TV tray", 24.99)).block();
        };
    }
}

这将加载这两个项目。