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

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