Spring Boot and Database Initialization not working properly

Question

The scripts in schema.sql gets executes but scripts from data.sql are not executing, not sure what I am missing?

I am using Spring Boot with two data source my data base configuration is as follows

@PropertySource({ "classpath:application.properties" })
@Configuration
@EnableJpaRepositories(
    basePackages = "com.projectx.mysql", 
    entityManagerFactoryRef = "userEntityManager", 
    transactionManagerRef = "userTransactionManager"
)
public class DataBaseConfig {

    @Autowired
    Environment env;


    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(primaryDataSource());
        em.setPackagesToScan(new String[] { "com.projectx.mysql" });
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<String, Object>();
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.hibernate.ddl-auto_mysql"));
        properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect_mysql"));
        properties.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }


    @Primary
    @Bean
    public PlatformTransactionManager userTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(userEntityManager().getObject());
        return transactionManager;
    }
}

and .properties file configuration as follows

spring.datasource.initialize=true
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.show-sql: true
spring.jpa.hibernate.ddl-auto_mysql=update
spring.jpa.properties.hibernate.dialect_mysql=org.hibernate.dialect.MySQL5Dialect

Answer 1

For those of you who stumble upon this question in a SpringBoot 2.1+ world.

First, what I think is the full class name of the important class (the object of the "publishEvent"... "org.springframework.boot.autoconfigure.jdbc.DataSourceInitializedEvent"

For future readers, this class seems to have disappeared between these two versions :

Below does exist:

https://docs.spring.io/spring-boot/docs/2.0.0.M3/api/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializedEvent.html

Below no longer exists:

https://docs.spring.io/spring-boot/docs/2.1.0.M1/api/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializedEvent.html

Here is how I "coded up" the seed data ("data.sql") .. when I had a Java-Config heavy class.

import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

@Bean   
public DataSource getDataSource() {
    //not shown 
}


@Bean
public DataSourceInitializer dataSourceInitializer(DataSource ds) {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));

    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDataSource(ds);
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    return dataSourceInitializer;
}

Debug tip. You may want to use a file name that is NOT a magic file name ("data.sql" is a magic name) to purposely avoid spring boot magic. Especially since spring boot 2.5.

Answer 2

The issue was with datasource initialization when we see content of org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer class that takes care of Database initialization through *.sql files.It has post construct method as follows

    @PostConstruct
    public void init() {
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running DDL scripts)");
            return;
        }
        if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
                false).length > 0) {
            this.dataSource = this.applicationContext.getBean(DataSource.class);
        }
        if (this.dataSource == null) {
            logger.debug("No DataSource found so not initializing");
            return;
        }
        runSchemaScripts();
    } 

The runSchemaScripts() method will initialize the data before hibernate schema creation and update operation is perfomed so if database schema is not generated then these method will create schema if you provide that in SQL script, but I want to perform operation after the schema is created/updated, for that class contains

    @Override
    public void onApplicationEvent(DataSourceInitializedEvent event) {
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running data scripts)");
            return;
        }
        // NOTE the event can happen more than once and
        // the event datasource is not used here
        if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
        }
    }

this is called if after the hibernate schema creation/updation operation when we have spring boots default Datasource creation mechanism. But as I was creating Datasource by myself,so it was not creating DataSourceInitializedEvent,so the data initilization scripts data.sql was not executed. So I have changed my Data source creation logic to create DataSourceInitializedEvent as follows and that solved my issue.

    @Autowired
    private ConfigurableApplicationContext applicationContext;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean userEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(primaryDataSource());
        em.setPackagesToScan(new String[] { "com.projectx.mysql" });
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<String, Object>();
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.hibernate.ddl-auto_mysql"));
        properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect_mysql"));
        properties.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql"));
        em.setJpaPropertyMap(properties);

        this.applicationContext.publishEvent(new DataSourceInitializedEvent(primaryDataSource()));

        return em;
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

Added this.applicationContext.publishEvent(new DataSourceInitializedEvent(primaryDataSource())); to create the DataSourceInitializedEvent event

Answer 3

I managed to get 2 data-sources instantiated and initiate schema and data in one of them with this test project. Hope that helps, maybe i missed some requirement of yours that makes my suggestions invalid :(

For ref (guess you already saw this): https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-two-datasources