Run data scripts after database build

Question

So I'm surprised an answer for this wasn't easily found, but I want to insert some data after the database gets generated.

RootConfig.java:

...
    @Bean
    public DataSource dataSource() throws SQLException {
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        return builder.setType(EmbeddedDatabaseType.HSQL)
                .setName("db")
                .addScript("setup_data.sql")
                .continueOnError(true)
                .build();
    }

    @Bean
    public EntityManagerFactory entityManagerFactory() throws SQLException {


        EclipseLinkJpaVendorAdapter vendorAdapter = new EclipseLinkJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(true);
        vendorAdapter.setShowSql(true);
        vendorAdapter.setDatabase(Database.HSQL);

        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);

        Map<String, Object> props = new HashMap<>();
        props.put("eclipselink.weaving", "false");
        props.put("eclipselink.target-database", HSQLPlatform.class.getName());
        props.put("eclipselink.cache.shared.default", "false");
        props.put("eclipselink.logging.parameters", "true");
        props.put("eclipselink.logging.level", "FINEST");
        props.put("eclipselink.logging.level.sql", "FINEST");
        props.put("eclipselink.logging.level.cache", "FINEST");

        factory.setJpaPropertyMap(props);

        factory.setPackagesToScan("com.citysports.leaguesports.domain");
        factory.setDataSource(dataSource());
        factory.afterPropertiesSet();

        return factory.getObject();
    }
...

I am generating the ddl, but when I addScript('setup_data.sql') I get an error because it hasn't generated the tables yet. How do I have the script run after the ddl generation?

Answer 1

You can use DatabasePopulator. To do so, put the following bean definitions in your configuration class.

@Bean
public ResourceDatabasePopulator databasePopulator() {
    ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.setSqlScriptEncoding("UTF-8");
    populator.addScript(new ClassPathResource("setup_data.sql"));
    return populator;
}

@Bean
public InitializingBean populatorExecutor() {
    return new InitializingBean() {
        @Override
        public void afterPropertiesSet() throws Exception {
            DatabasePopulatorUtils.execute(databasePopulator(), dataSource());
        }
    };
}

if you're using Java 8, you can simplify the InitializingBean definition to this form using lambdas:

@Bean
public InitializingBean populatorExecutor() {
    return () -> DatabasePopulatorUtils.execute(databasePopulator(), dataSource());
}

Basically you define a populator which has scripts you want to execute and the InitializingBean takes care of running those scripts when the data source bean is ready.

Hope this solution works for you