Initialize database without XML configuration, but

Question

I would like to know how to initialize a database without having to create an XML file.

I already use this kind of initialization that works fine, but in my current case I don't want to create an XML:

<jdbc:initialize-database data-source="dataSource">
  <jdbc:script location="classpath:com/foo/sql/db-schema.sql"/>
  <jdbc:script location="classpath:com/foo/sql/db-test-data.sql"/>
</jdbc:initialize-database>

I know I can create an embedded database with:

EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder.setType(H2).addScript("my-schema.sql").addScript("my-test-data.sql").build();

In my case, the database and schema are created using Liquibase.

I just want to initialize it with Spring and with my customized dataset, without having to create a new XML file each time just for that.

Is it possible?

Answer 1

The following lines of code inside your @Configuration class might work.

@Value("classpath:com/foo/sql/db-schema.sql")
private Resource schemaScript;

@Value("classpath:com/foo/sql/db-test-data.sql")
private Resource dataScript;

@Bean
public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
    final DataSourceInitializer initializer = new DataSourceInitializer();
    initializer.setDataSource(dataSource);
    initializer.setDatabasePopulator(databasePopulator());
    return initializer;
}

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

Answer 2

You have to create your own schema.sql and put it in your src/main/resources-folder.

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.DatabasePopulatorUtils;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

@Configuration
public class DataSourceInitializer {

    @Bean(name = "dataSource")
    public DataSource getDataSource(){
        DataSource dataSource = createDataSource();
        DatabasePopulatorUtils.execute(createDatabasePopulator(), dataSource);
        return dataSource;
    }

    private DatabasePopulator createDatabasePopulator() {
        ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator();
        databasePopulator.setContinueOnError(true);
        databasePopulator.addScript(new ClassPathResource("schema.sql"));
        return databasePopulator;
    }

    private SimpleDriverDataSource createDataSource() {
        SimpleDriverDataSource simpleDriverDataSource = new SimpleDriverDataSource();
        simpleDriverDataSource.setDriverClass(org.h2.Driver.class);
        simpleDriverDataSource.setUrl("jdbc:h2:target/database/example;AUTO_RECONNECT=TRUE");
        simpleDriverDataSource.setUsername("");
        simpleDriverDataSource.setPassword("");
        return simpleDriverDataSource;      
    }
}

Answer 3

After looking at Spring classes related to EmbeddedDatabaseBuilder I found out that the DatabaseBuilder is using some code looking like this:

ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
for (String sqlScript: sqlInitializationScripts ) {
  Resource sqlScriptResource = RESOURCE_LOADER.getResource(sqlScript);
  populator.addScript(sqlScriptResource);
}
DatabasePopulatorUtils.execute(populator, dataSource);

This will work fine for me, even if it will be on a @BeforeTest method and not on the Spring configuration.

Answer 4

It certainly is possible.

If you already have an @Configuration class that is being loaded by Spring's ApplicationContext, then you simply have to create a new @Bean method that will contain the code you have there already (with an additional return statement of course).

EmbeddedDatabase implements the DataSource interface, so it can easily be used with JdbcTemplate's.

@Bean
public DataSource db() {
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    builder.setType(H2).addScript("my-schema.sql").addScript("my-test-data.sql");
    return builder.build();
}