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.

score 7 · Accepted Answer · answered Jan 05 '20 at 11:28

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");
 }

score 4 · Answer 2 · edited Jun 20 '20 at 09:12

4

From the official documentation

ConnectionFactory

@Configuration
public class ApplicationConfiguration extends AbstractR2dbcConfiguration {

  @Override
  @Bean
  public ConnectionFactory connectionFactory() {
    return …;
  }
}

This approach lets you use the standard io.r2dbc.spi.ConnectionFactory instance, with the container using Spring’s AbstractR2dbcConfiguration. As compared to registering a ConnectionFactory instance directly, the configuration support has the added advantage of also providing the container with an ExceptionTranslator implementation that translates R2DBC exceptions to exceptions in Spring’s portable DataAccessException hierarchy for data access classes annotated with the @Repository annotation.

(...)

AbstractR2dbcConfiguration also registers DatabaseClient, which is required for database interaction and for Repository implementation.

I have written a blog post about how to set get started with R2DBC here. Here is an example using the H2 database

My guess is that you are not initialising the connection factory correctly and hence not getting a proper DatabaseClient.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 05 '20 at 04:39

Toerktumlare

12,548
3
35
54

1

I do not think so. From the startup logging, the `ConnectionFactory` is ok, and `ConnectionFactoryInitializer` init successfully(create and insert data), but when init `DataInitializer` to insert another data, failed due to not found table "posts". I mentioned using `.file("./testdb")` instead of `InMemory`, it works. – Hantsy Jan 05 '20 at 05:09
well apparently it doesn't otherwise you wouldn't have written here. – Toerktumlare Jan 05 '20 at 05:11
@ThomasAndolf , in your article you mentioned an application.properties using postgresql, but: How Can we use the application.properties with H2DB? – GtdDev Jul 10 '20 at 00:49
1

If you don’t set the properties, the application will default to using H2DB – Toerktumlare Jul 10 '20 at 06:30

R2dbc H2 issues when using inMemory database

2 Answers2