Setup H2 in Server Mode using Java Based Configuration

Question

I have spring XML that enables me to start H2 database in server mode using the following configuration:

<beans profile="test-h2">
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <property name="driverClassName" value="org.h2.Driver"/>
        <property name="url" value="jdbc:h2:target/h2/pps;AUTO_SERVER=TRUE"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
    </bean>
    <bean id="entityManagerFactory" parent="entityManagerFactoryCommonParent">
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
                <prop key="hibernate.show_sql">true</prop>
            </props>
        </property>
    </bean>

I want to convert to java based configuration. I have seem a post here: Start and setup in-memory DB using Spring asking somewhat the same question and I have looked at http://docs.spring.io/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#jdbc-embedded-database-support for Embedded Database but it does not say how to set H2 mode to Server mode. It is starting the server for me in "mem" mode only.

I have the following code:

EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();

builder.setType(EmbeddedDatabaseType.H2);
builder.setName(DATABASE_NAME);
builder.addScript(H2_SCHEMA);
builder.addScript(H2_TEST);
return builder.build();

Maybe using EmbeddedDatabaseBuilder(ResourceLoader) might work. Does anyone has some sample code for it?

yousafsajjad · Accepted Answer · 2013-11-26T15:13:38.813

Here's the code that will allow you start H2 database in server mode using java based spring configuration:

private static final String H2_JDBC_URL_TEMPLATE = "jdbc:h2:%s/target/db/sample;AUTO_SERVER=TRUE";
@Value("classpath:seed-data.sql")
private Resource H2_SCHEMA_SCRIPT;

@Value("classpath:test-data.sql")
private Resource H2_DATA_SCRIPT;

@Value("classpath:drop-data.sql")
private Resource H2_CLEANER_SCRIPT;


@Bean
public DataSource dataSource(Environment env) throws Exception {
        return createH2DataSource();
}


@Autowired
@Bean
public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {

    final DataSourceInitializer initializer = new DataSourceInitializer();
    initializer.setDataSource(dataSource);
    initializer.setDatabasePopulator(databasePopulator());
    initializer.setDatabaseCleaner(databaseCleaner());
    return initializer;
}


private DatabasePopulator databasePopulator() {
    final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.addScript(H2_SCHEMA_SCRIPT);
    populator.addScript(H2_DATA_SCRIPT);
    return populator;
}

private DatabasePopulator databaseCleaner() {
    final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.addScript(H2_CLEANER_SCRIPT);
    return populator;
}

private DataSource createH2DataSource() {
    String jdbcUrl = String.format(H2_JDBC_URL_TEMPLATE, System.getProperty("user.dir"));
    JdbcDataSource ds = new JdbcDataSource();       
    ds.setURL(jdbcUrl);
    ds.setUser("sa");
    ds.setPassword("");

    return ds;
}

@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(entityManagerFactory);
    return transactionManager;
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory(Environment env) throws Exception {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(Boolean.TRUE);
    vendorAdapter.setShowSql(Boolean.TRUE);     

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setPersistenceUnitName("sample");
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.sample.model");
    factory.setDataSource(dataSource(env));     

    factory.setJpaProperties(jpaProperties());
    factory.setLoadTimeWeaver(new InstrumentationLoadTimeWeaver());

    return factory;
}

Properties jpaProperties() {
    Properties props = new Properties();
    props.put("hibernate.query.substitutions", "true 'Y', false 'N'");
    props.put("hibernate.hbm2ddl.auto", "create-drop");
    props.put("hibernate.show_sql", "false");
    props.put("hibernate.format_sql", "true");

    return props;
}

As of now - this is complete overkill. The same thing can be done in a couple of lines. https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/ I'm not affiliated with the author in any way - but his instructions certainly worked for me. — John Deverall, Nov 20 '16 at 21:14

score 1 · Answer 2 · edited May 23 '17 at 12:10

I belive that's not possible. The term Embedded database as in databases that do not need a server, and are embedded in an application, so you should not use EmbeddedDatabase for this.

In h2 documentation the term "Embedded" is enfatized with "Local" - (Connecting to an Embedded (Local) Database), and when they use "Server" they talk about remote connections where the database is managed by an server. To reinforce the idea EmbeddedDataSource interface adds only a single method that is not present in the datasource "shutdown" interface that is used to shut down the database usually when the application is shutdown, via @Bean(destroyMethod="shutdown") for instance.

See more details in here:

http://h2database.com/html/features.html#database_url

When is a database called as an Embedded database?

http://docs.spring.io/spring/docs/3.0.x/api/org/springframework/jdbc/datasource/embedded/EmbeddedDatabase.html