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();
    }
}

howtodoinjava.com/spring-boot2/h2-database-example might help — seenukarthi
– seenukarthi, Commented Feb 21, 2020 at 7: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
– Filippo Possenti, Commented Feb 21, 2020 at 9:54

Filippo Possenti · Accepted Answer · 2020-02-21 11:12:12Z

6

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

edited Feb 21, 2020 at 11:12

answered Feb 21, 2020 at 9:53

Filippo Possenti

1,41010 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Omkar Patil Over a year ago

I just added my configuration file for unit testing. can you please refer that...

Filippo Possenti Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related