在spring boot中进行单元测试之前，通过data.sql文件在h2数据库中插入数据 [英] Insert data in h2 database through data.sql file before performing unit testing in spring boot

问题描述

我想在 spring boot+ JPA 中执行单元测试.为此，我创建了配置文件来为 dataSource、所有休眠属性、entityManagerFactory 和 transactionManager 创建 bean.一切都很完美.表是由模型类创建的.但现在我想通过data.sql文件在数据库的所有表中插入数据进行测试.我将 data.sql 文件保存在 src/main/resources 中，但它没有加载文件.那么如何在开始单元测试之前在 h2 数据库中加载数据.

这是我的配置文件 -

<预><代码>导入 java.util.Properties;导入 javax.sql.DataSource;导入 org.hibernate.cfg.Environment;导入 org.springframework.boot.autoconfigure.domain.EntityScan;导入 org.springframework.context.annotation.Bean;导入 org.springframework.context.annotation.Configuration;导入 org.springframework.context.annotation.PropertySource;导入 org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;导入 org.springframework.data.jpa.repository.config.EnableJpaRepositories;导入 org.springframework.jdbc.datasource.DriverManagerDataSource;导入 org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;导入 org.springframework.orm.jpa.JpaTransactionManager;导入 org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean；导入 org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter；导入 org.springframework.transaction.PlatformTransactionManager;导入 org.springframework.transaction.annotation.EnableTransactionManagement;@配置@EnableJpaRepositories(basePackages = "base_package_name")@EnableTransactionManagement公共类 JPAConfig {@豆公共数据源数据源(){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");数据源.setPassword("");返回数据源；}私有属性 hibernateProperties() {属性 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");返回属性；}@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());lcmfb.afterPropertiesSet();返回 lcmfb；}@豆公共 PlatformTransactionManager transactionManager(){JpaTransactionManager tm = new JpaTransactionManager();tm.setEntityManagerFactory(this.entityManagerFactoryBean().getObject());返回 tm;}@豆public PersistenceExceptionTranslationPostProcessor exceptionTranslation(){返回新的 PersistenceExceptionTranslationPostProcessor();}}

解决方案

根据 StackOverflow 上的另一个问题，您可以通过在测试中添加一个 @Configuration 类来初始化数据库，如下所示:

@Configuration公共类 DatabaseTestConfig {@豆公共数据源数据源(){返回新的 EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL).addScript("classpath:schema.sql").addScript("classpath:test-data.sql").build();}}

请注意，上述内容不使用 JPA，因此您可能需要根据自己的目的对其进行调整，但它应该会很好地提示您如何在 JPA 中进行调整.

然而，我倾向于在每个测试中使用 @Sql 注释，初始化数据然后清理它.虽然这意味着更多的重复，这通常是不好的，但它有助于确保测试始终从头开始.

参考资料

• 如何初始化内存中的 HSQLDB通过 Spring 使用脚本
• https://docs.spring.io/spring-framework/docs/current/spring-framework-reference/testing.html#spring-testing-annotation-sql
• https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-initialize-a-database-using-hibernate

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

解决方案

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

• How to initialize in-memory HSQLDB using script via Spring
• https://docs.spring.io/spring-framework/docs/current/spring-framework-reference/testing.html#spring-testing-annotation-sql
• https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-initialize-a-database-using-hibernate

这篇关于在spring boot中进行单元测试之前，通过data.sql文件在h2数据库中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！