Insert data in h2 database through data.sql file before performing unit testing in spring boot

Question

I want to perform unit testing in spring boot+ JPA. For that I created configuration file to create bean for dataSource, all hibernate properties, entityManagerFactory and transactionManager. Everything going perfect. Tables are getting created by model classes. but now I want to insert data in all tables of database for testing through data.sql file. I kept data.sql file in src/main/resources but it not loading the file. So how can I load data in h2 database before starting with unit testing.

This is my cofiguration file -


import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;


@Configuration
@EnableJpaRepositories(basePackages = "base_package_name")
@EnableTransactionManagement
public class JPAConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1");
        /*dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
        dataSource.setUrl("jdbc:hsqldb:mem:testdb");*/
        dataSource.setUsername("sa");
        dataSource.setPassword("");

        return dataSource;
    }

    private Properties hibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
        properties.put("hibernate.hbm2ddl.auto", "create");
        properties.put("hibernate.show_sql", "true");
        properties.put("hibernate.format_sql", "false");
        return properties;
    }

    @Bean(name="entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(){

        LocalContainerEntityManagerFactoryBean lcemfb
            = new LocalContainerEntityManagerFactoryBean();

        lcemfb.setDataSource(this.dataSource());
        lcemfb.setPackagesToScan(new String[] {"Package_to_scan"});

        HibernateJpaVendorAdapter va = new HibernateJpaVendorAdapter();
        lcemfb.setJpaVendorAdapter(va);

        lcemfb.setJpaProperties(this.hibernateProperties());

        lcemfb.afterPropertiesSet();

        return lcemfb;

    }


    @Bean
    public PlatformTransactionManager transactionManager(){

        JpaTransactionManager tm = new JpaTransactionManager();

        tm.setEntityManagerFactory(
            this.entityManagerFactoryBean().getObject() );

        return tm;

    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation(){
        return new PersistenceExceptionTranslationPostProcessor();
    }
}

https://howtodoinjava.com/spring-boot2/h2-database-example/ might help — seenukarthi, Feb 21 '20 at 07:40
Hi, I wrote an answer but it would be helpful if you could post the portions of code related to your configuration. — Filippo Possenti, Feb 21 '20 at 09:54

Filippo Possenti · Answer 1 · 2020-02-21T11:12:12.350

According to another question on StackOverflow, you can initialise a database by adding a @Configuration class to your tests, as follows:

@Configuration
public class DatabaseTestConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.HSQL)
            .addScript("classpath:schema.sql")
            .addScript("classpath:test-data.sql")
            .build();
    }
}

To note that the above doesn't use JPA so you may have to adjust it for your own purposes, but it should give a good hint on how you could do it in JPA as well.

My preference however leans towards using the @Sql annotation in each test, to initialise the data and then to clean it up. While this means more repetition, which is usually bad, it helps ensuring that tests always run from a clean slate.

References

I just added my configuration file for unit testing. can you please refer that... — Omkar Patil, Feb 21 '20 at 10:29
I'll take a look. In the meantime, I suggest playing around with `spring.jpa.hibernate.ddl-auto`. Also, note that in `entityManagerFactoryBean` you are not using the DataSource defined in the bean's configuration but instead you're creating another instance of it. Add a `DataSource` parameter to the `entityManagerFactoryBean` method and replace `lcemfb.setDataSource(this.dataSource());` with `lcemfb.setDataSource(dataSource);`. I don't think it will solve your problem but it will allow you to split the DataSource configuration in a separate file for testing purposes. — Filippo Possenti, Feb 21 '20 at 11:15

Insert data in h2 database through data.sql file before performing unit testing in spring boot

1 Answers1

Linked