R2dbc H2 issues when using inMemory database

Question

I was trying to taste R2dbc and using Embedded H2 like:

public ConnectionFactory connectionFactory() {
        //ConnectionFactory factory = ConnectionFactories.get("r2dbc:h2:mem:///test?options=DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
        return new H2ConnectionFactory(
                H2ConnectionConfiguration.builder()
                        //.inMemory("testdb")
                        .file("./testdb")
                        .username("user")
                        .password("password").build()
        );
    }

And I defined a bean to create tables and init data.

@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")));
        populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("data.sql")));
        initializer.setDatabasePopulator(populator);

        return initializer;
    }

And I also defined another Component to set up data by java codes.

@Component
@Slf4j
class DataInitializer {

    private final DatabaseClient databaseClient;

    public DataInitializer(DatabaseClient databaseClient) {
        this.databaseClient = databaseClient;
    }

    @EventListener(value = ContextRefreshedEvent.class)
    public void init() {
        log.info("start data initialization  ...");
        this.databaseClient.insert()
            .into("posts")
            //.nullValue("id", Integer.class)
            .value("title", "First post title")
            .value("content", "Content of my first post")
            .map((r, m) -> r.get("id", Integer.class))
            .all()
            .log()
            .thenMany(
                this.databaseClient.select()
                    .from("posts")
                    .orderBy(Sort.by(desc("id")))
                    .as(Post.class)
                    .fetch()
                    .all()
                    .log()
            )
            .subscribe(null, null, () -> log.info("initialization done..."));
    }

}

If I used .inMemory("testdb") in the ConnectionFactory bean definition, when the Spring ApplicationContext is initialized, it failed because can not find table POSTS when initializing DataInitializer. From the startup logging, ConnectionFactoryInitializer is initialized successfully, and table POSTS is created and data is inserted as expected by executing the schema.sql and data.sql.

But switching to use .file("./testdb"), it worked.

The complete code is here.

Answer 1

I got the answer from Spring Data R2dc developer, @mp911de. see #issue269

The issue is related to H2‘s behavior to close the database when the last connection gets closed. Please configure the DB_CLOSE_DELAY=-1 option so H2 retains the database. Alternatively, use the H2ConnextionFactory.inMemory(...) factory method to create a Closeable connection factory that does not depend on the in-use connection count.

Change my codes to the following, it works:

 public ConnectionFactory connectionFactory() {       
     return H2ConnectionFactory.inMemory("testdb");
 }