在Spring中初始化多个数据库 [英] Initialize multiple databases in Spring

问题描述

我在运行时使用AbstractRoutingDatasource在数据库之间进行路由。在informix数据库的实际情况下，一切正常。

对于测试，我创建了一个用于内存H2数据库的弹簧配置文件。
在运行测试配置文件的spring应用程序后，我使用h2控制台检查了本地数据库。没有模式创建。

我试图在资源和休眠中使用schema.sql：

  generate-ddl：true 
 hibernate：
 ddl-auto：create-drop 
  

hibernate throws

  java.lang.IllegalStateException：无法确定查找键的目标数据源[null] 
  

据我所知，hibernate试图只生成路由数据源。由于没有设置DatabaseContextHolder（null），因此失败。

所以这两种方法都失败了。有没有一种方法使用（相同）模式初始化所有数据库？

我在下面列出我的配置。

bootstrap.yml ：

  spring：
配置文件：acceptanceTest 
配置：
名称：standalone 
云：
配置：
启用：false 
发现：
启用：false 
 jpa：
数据库平台：org.hibernate.dialect.H2Dialect 
 generate-ddl：true 
 hibernate：
 ddl-auto：create-drop 
属性：
 hibernate：
 show_sql：false 
 use_sql_comments ：false 
 format_sql：false 
 h2：
控制台：
启用：true 
路径：/ h2 
数据源：
 mc：
 driver-class-name：'org.h2.Driver'
 url：'jdbc：h2：mem：test-mc-db; DB_CLOSE_DELAY = -1; DB_CLOSE_ON_EXIT = FALSE '
用户名：sa 
密码：''
平台：h2 
初始化：true 
类型：com.zaxxer.hikari.HikariDataSource 
 hikari：
 maximum-pool-size：10 
 minimum-idle：0 
 idle-timeout：60000 
 pool-name：MarkHikariPool 
 cw：
 driver-class -name：'org.h2.Driver'
 url：'jdbc：h2：mem：test-cw-db'
用户名：sa 
密码：''
类型： com.zaxxer.hikari.HikariDataSource 
 hikari：
 maximum-pool-size：10 
 minimum-idle：0 
 idle-timeout：60000 
 pool-name： MarkHikariPool 
  

MainApp.java：

<$ p $ （$ {$ de_md.mark}）
@EnableDiscoveryClient
@SpringBootApplication（
exclude =
{
DataSourceAutoConfiguration .class，
DataSourceTransactionManagerAutoConfiguration.class，
HibernateJpaAutoConfig uration.class
}
）
public class MainApp
{
public static void main（String [] args）
{

DataSourceConfiguration.java：

  @Configuration 
 @EnableJpaRepositories（
 basePackageClasses = {MarkTypeRepository.class，MarkRepository.class}，entityManagerFactoryRef =markEntityManager，
 transactionManagerRef =markTransactionManager
）
 @EnableTransactionManagement 
 public class DataSourceConfiguration 
 {
 @Autowired（required = false）
 private PersistenceUnitManager persistenceUnitManager; 
 
 @Bean 
 @ConfigurationProperties（app.jpa）
 @Primary 
 public JpaProperties jpaProperties（）
 {
 return new JpaProperties （）; 
 
 
 
 @ConfigurationProperties（prefix =datasource.mc）
 public DataSource mcDataSource（）
 {
 return DataSourceBuilder .create（）建立（）; 
 
 
 
 @ConfigurationProperties（prefix =datasource.cw）
 public DataSource cwDataSource（）
 {
 return DataSourceBuilder .create（）建立（）; 
 
 
 $Be 
 @Primary 
 public DataSource dataSource（）
 {
 DataSourceRouter router = new DataSourceRouter（）; 
 
 final HashMap< Object，Object> map = new HashMap<>（DatabaseEnvironment.values（）。length）; 
 map.put（DatabaseEnvironment.MC，mcDataSource（））; 
 map.put（DatabaseEnvironment.CW，cwDataSource（））; 
 router.setTargetDataSources（map）; 
返回路由器; 
 
 $ b $Be 
 @Primary 
 public LocalContainerEntityManagerFactoryBean markEntityManager（final JpaProperties jpaProperties）
 {
 EntityManagerFactoryBuilder builder = createEntityManagerFactoryBuilder（jpaProperties）; 
 $ b $ return builder.dataSource（dataSource（））。packages（MarkTypeEntity.class）.persistenceUnit（markEntityManager）。build（）; 
 
 
 
 @Primary 
 public JpaTransactionManager markTransactionManager（@Qualifier（markEntityManager）final EntityManagerFactory工厂）
 {
 return新的JpaTransactionManager（工厂）; 
} 
 
 private EntityManagerFactoryBuilder createEntityManagerFactoryBuilder（JpaProperties jpaProperties）
 {
 JpaVendorAdapter jpaVendorAdapter = createJpaVendorAdapter（jpaProperties）; 
返回新的EntityManagerFactoryBuilder（jpaVendorAdapter，jpaProperties.getProperties（），this.persistenceUnitManager）; 
} 
 
 private JpaVendorAdapter createJpaVendorAdapter（JpaProperties jpaProperties）
 {
 AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter（）; 
 adapter.setShowSql（jpaProperties.isShowSql（））; 
 adapter.setDatabase（jpaProperties.getDatabase（））; 
 adapter.setDatabasePlatform（jpaProperties.getDatabasePlatform（））; 
 adapter.setGenerateDdl（jpaProperties.isGenerateDdl（））; 
返回适配器; 
 
 
 $ / code> 




解决方案

After在过去两天里进行了大量的研究，我找到了初始化所有数据源的解决方案。
我创建了一个使用Hibernate的SchemaExport.class创建和初始化本地H2数据库的方法。
因为我只需要本地数据库进行测试，所以我在@Before cucumber方法中调用每个DatabaseEnvirement的方法。

  public void createDatabase（DatabaseEnvironment environment）
 throws Exception 
 {
 //从类路径中的hibernate.cfg.xml加载hibernate配置
 Configuration configuration = new Configuration（）。configure （）; 
 MetadataSources元数据= 
新的MetadataSources（新的StandardServiceRegistryBuilder（）。applySettings（configuration.getProperties（））。build（））; 
 
 ClassPathScanningCandidateComponentProvider scanner = new ClassPathScanningCandidateComponentProvider（true）; 
 scanner.addIncludeFilter（新的AnnotationTypeFilter（Entity.class））; 
 
 for（BeanDefinition def：scanner.findCandidateComponents（MarkEntity.class.getPackage（）。getName（）））
 {
 metadata.addAnnotatedClass（Class.forName（def.getBeanClassName （）））; 
} 
 
连接连接= DriverManager.getConnection（jdbc：h2：mem：test-+ environment.name（）。toLowerCase（），sa，）; 
 
 SchemaExport export = new SchemaExport（（MetadataImplementor）metadata.buildMetadata（），connection）; 
 export.create（true，true）; 
} 
 
 
 @Before 
 public void beforeTest（）
 throws异常
 {
 //初始化数据库$ b $ （DatabaseEnvironment env：DatabaseEnvironment.values（））
 {
 createDatabase（env）; 
 
 
 $ / code $ / pre 
 
 
这就是我的hibernate.cfg.xml的外观例如： 
 
 
 <？xml version ='1.0'encoding ='utf-8'？> 
<！DOCTYPE hibernate-configuration PUBLIC 
 -  // Hibernate / Hibernate配置DTD 3.0 // EN
http://hibernate.sourceforge.net/hibernate-configuration-3.0 .dtd> 
 
< hibernate-configuration> 
< session-factory> 
 
< property name =dialect> org.hibernate.dialect.H2Dialect< / property> 
 
< property name =show_sql> true< / property> 
 
< property name =hbm2ddl.auto> create-drop< / property> 
 
< / session-factory> 
< / hibernate-configuration> 
  
 
I'm using AbstractRoutingDatasource to route between databases in runtime. In real condition on the informix database everything works just fine.


For tests I created a spring profile to use in memory H2 databases.
After running the spring app whith the test profile I checked the local databases using the h2 console. No schema was created.

I tried using schema.sql in recources and hibernates:
generate-ddl: true
hibernate:
  ddl-auto: create-drop
hibernate throws
java.lang.IllegalStateException: Cannot determine target DataSource for lookup key [null]
As I understand hibernate ist trying to generate only the "routed" datasource. Since no DatabaseContextHolder is set (null) this fails.

So both ways fail.

Is there a way to initialize all databases with (the same) schema?

I list my configuration below.

bootstrap.yml:
spring:
  profiles: acceptanceTest
  config:
    name: standalone
  cloud:
    config:
      enabled: false
      discovery:
        enabled: false
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    generate-ddl: true
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        show_sql: false
        use_sql_comments: false
        format_sql: false
  h2:
    console:
      enabled: true
      path: /h2
datasource:
  mc:
    driver-class-name: 'org.h2.Driver'
    url: 'jdbc:h2:mem:test-mc-db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE'
    username: sa
    password: ''
    platform: h2
    initialize: true
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      maximum-pool-size: 10
      minimum-idle: 0
      idle-timeout: 60000
      pool-name: MarkHikariPool
  cw:
    driver-class-name: 'org.h2.Driver'
    url: 'jdbc:h2:mem:test-cw-db'
    username: sa
    password: ''
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      maximum-pool-size: 10
      minimum-idle: 0
      idle-timeout: 60000
      pool-name: MarkHikariPool
MainApp.java:
@ComponentScan({ "de.md.mark" })
@EnableDiscoveryClient
@SpringBootApplication(
    exclude =
        {
            DataSourceAutoConfiguration.class,
            DataSourceTransactionManagerAutoConfiguration.class,
            HibernateJpaAutoConfiguration.class
        }
)
public class MainApp
{
    public static void main(String[] args)
    {
DataSourceConfiguration.java:
@Configuration
@EnableJpaRepositories(
    basePackageClasses = { MarkTypeRepository.class, MarkRepository.class }, entityManagerFactoryRef = "markEntityManager",
    transactionManagerRef = "markTransactionManager"
)
@EnableTransactionManagement
public class DataSourceConfiguration
{
    @Autowired(required = false)
    private PersistenceUnitManager persistenceUnitManager;

    @Bean
    @ConfigurationProperties("app.jpa")
    @Primary
    public JpaProperties jpaProperties()
    {
        return new JpaProperties();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.mc")
    public DataSource mcDataSource()
    {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.cw")
    public DataSource cwDataSource()
    {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource dataSource()
    {
        DataSourceRouter router = new DataSourceRouter();

        final HashMap<Object, Object> map = new HashMap<>(DatabaseEnvironment.values().length);
        map.put(DatabaseEnvironment.MC, mcDataSource());
        map.put(DatabaseEnvironment.CW, cwDataSource());
        router.setTargetDataSources(map);
        return router;
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean markEntityManager(final JpaProperties jpaProperties)
    {
        EntityManagerFactoryBuilder builder = createEntityManagerFactoryBuilder(jpaProperties);

        return builder.dataSource(dataSource()).packages(MarkTypeEntity.class).persistenceUnit("markEntityManager").build();
    }

    @Bean
    @Primary
    public JpaTransactionManager markTransactionManager(@Qualifier("markEntityManager") final EntityManagerFactory factory)
    {
        return new JpaTransactionManager(factory);
    }

    private EntityManagerFactoryBuilder createEntityManagerFactoryBuilder(JpaProperties jpaProperties)
    {
        JpaVendorAdapter jpaVendorAdapter = createJpaVendorAdapter(jpaProperties);
        return new EntityManagerFactoryBuilder(jpaVendorAdapter, jpaProperties.getProperties(), this.persistenceUnitManager);
    }

    private JpaVendorAdapter createJpaVendorAdapter(JpaProperties jpaProperties)
    {
        AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setShowSql(jpaProperties.isShowSql());
        adapter.setDatabase(jpaProperties.getDatabase());
        adapter.setDatabasePlatform(jpaProperties.getDatabasePlatform());
        adapter.setGenerateDdl(jpaProperties.isGenerateDdl());
        return adapter;
    }
}

解决方案
After a lot research in the last two days I found a solution to initialize all datasources.
I created a method to create and initialize the local H2 databases using the SchemaExport.class of hibernate.
Since I only need the local db's for testing, I call the method for each DatabaseEnvirement in a @Before cucumber method.
public void createDatabase(DatabaseEnvironment environment)
throws Exception
{
    // load hibernate configuration from hibernate.cfg.xml in classpath
    Configuration configuration = new Configuration().configure();
    MetadataSources metadata =
    new MetadataSources(new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build());

    ClassPathScanningCandidateComponentProvider scanner = new ClassPathScanningCandidateComponentProvider(true);
    scanner.addIncludeFilter(new AnnotationTypeFilter(Entity.class));

    for (BeanDefinition def : scanner.findCandidateComponents(MarkEntity.class.getPackage().getName()))
    {
        metadata.addAnnotatedClass(Class.forName(def.getBeanClassName()));
    }

    Connection connection = DriverManager.getConnection("jdbc:h2:mem:test-" + environment.name().toLowerCase(), "sa", "");

    SchemaExport export = new SchemaExport((MetadataImplementor) metadata.buildMetadata(), connection);
    export.create(true, true);
}


@Before
public void beforeTest()
    throws Exception
{
    // initialise databases
    for (DatabaseEnvironment env : DatabaseEnvironment.values())
    {
        createDatabase(env);
    }
}
and this is how my hibernate.cfg.xml looks like:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <property name="dialect">org.hibernate.dialect.H2Dialect</property>

        <property name="show_sql">true</property>

        <property name="hbm2ddl.auto">create-drop</property>

    </session-factory>
</hibernate-configuration>


                        
这篇关于在Spring中初始化多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！