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

Can you also post the code where you execute the sql scripts? How do you handle the second data source? — Simon, Apr 21 '17 at 06:19
Other data connection is to mongodb server.The issue is that if I specify it as schema.sql then contents get executed but if i put same content in data.sql it does not execute — MasterCode, Apr 21 '17 at 10:20
@Simon Please refer https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html — MasterCode, Apr 25 '17 at 05:29
@sashok_bg The main issue is that when I specify spring.datasource.schema: data.sql it executes the script but when I run it as spring.datasource.data: data.sql it does not execute it. — MasterCode, Apr 25 '17 at 08:51
Maybe try to make it work with only one data source first. You should also let spring boot handle the work, I think you dont need to import the properties the way you do — sashok_bg, Apr 25 '17 at 13:31
@sashok_bg I have other application with one data source and that setup works fine with schema.sql and data.sql but I am facing issue with this two data source project.I am not able to understand the second solution that you mentioned. — MasterCode, Apr 25 '17 at 14:06
@11thdimension I guess problem is not with contents of these files,because I am keeping contents same but only scripts from schema.sql get executed not from the data.sql — MasterCode, Apr 28 '17 at 05:13

granadaCoder · Answer 1 · 2021-06-21T23:07:55.720

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.

Nice solution! I was stuck on this for a while. – Jonathan Chaplin May 28 '21 at 17:29 — Jonathan Chaplin, May 28 '21 at 17:29

score 1 · Accepted Answer · edited May 31 '18 at 05:38

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

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 : https://docs.spring.io/spring-boot/docs/2.0.0.M3/api/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializedEvent.html << this exists and >> this is 404>> https://docs.spring.io/spring-boot/docs/2.1.0.M1/api/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializedEvent.html — granadaCoder, Feb 22 '21 at 16:00

score 0 · Answer 3 · answered Apr 26 '17 at 06:47

0

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

answered Apr 26 '17 at 06:47

Pär Nilsson

2,259
15
19

Problem is not with two connection that works fine.But data initialization is not done.But schema script runs fine – MasterCode Apr 26 '17 at 14:14
Have you tried setting ddl-auto=none. "If you want to use the schema.sql initialization in a JPA app (with Hibernate) then ddl-auto=create-drop will lead to errors if Hibernate tries to create the same tables. To avoid those errors set ddl-auto explicitly to "" (preferable) or "none". Whether or not you use ddl-auto=create-drop you can always use data.sql to initialize new data." – Pär Nilsson Apr 26 '17 at 17:16

Spring Boot and Database Initialization not working properly

3 Answers3