SpringBoot项目配置多数据源（使用SpringDataJpa前提下添加JdbcTemplate连接vertica）

背景

由于“续转卡文件下发平台对接迁移”的需求，营销风险管理平台系统（SpringBoot项目）将续转卡文件的上传方式由原来的调用pass-steaming作业从sqlSever直接传输到ftp，改为先在Vertica建立临时表，再调用Ucp作业将Vertica的临时表转移至目的ftp。

设计

在不影响项目原有使用SpringDataJpa操作sqlServer逻辑的基础上，添加jdbcTemplate操作Vertica数据库

过程及问题

原有sqlServer DataSource的相关配置在application.properties文件中，在applicationContext.xml中添加vertica DataSource及vertica jdbcTemplate的配置，项目启动报错：

2022-01-25 09:18:20.021  INFO 27420 --- [           main] c.m.v.c.i.AbstractPoolBackedDataSource   : Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> rir9c9am13micw31u0wl9k|5f06a4ec, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> null, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> rir9c9am13micw31u0wl9k|5f06a4ec, idleConnectionTestPeriod -> 100, initialPoolSize -> 1, jdbcUrl -> jdbc:vertica://99.47.151.179:5433/cmb01, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 2, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, preferredTestQuery -> select 1, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
2022-01-25 09:18:21.811  INFO 27420 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2022-01-25 09:18:21.844  INFO 27420 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2022-01-25 09:18:22.012  INFO 27420 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.2.17.Final}
2022-01-25 09:18:22.016  INFO 27420 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2022-01-25 09:18:22.119  INFO 27420 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2022-01-25 09:18:23.580  WARN 27420 --- [           main] o.h.e.j.e.i.JdbcEnvironmentInitiator     : HHH000342: Could not obtain connection to query metadata : Unable to determine Dialect to use [name=Vertica Database, majorVersion=9]; user must register resolver or explicitly set 'hibernate.dialect'
2022-01-25 09:18:23.584  WARN 27420 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
2022-01-25 09:18:23.586  WARN 27420 --- [           main] o.s.b.f.support.DisposableBeanAdapter    : Destroy method 'close' on bean with name 'eurekaRegistration' threw an exception: org.springframework.beans.factory.BeanCreationNotAllowedException: Error creating bean with name 'org.springframework.cloud.netflix.eureka.EurekaClientAutoConfiguration$RefreshableEurekaClientConfiguration': Singleton bean creation not allowed while singletons of this factory are in destruction (Do not request a bean from a BeanFactory in a destroy method implementation!)
2022-01-25 09:18:23.608  INFO 27420 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService
2022-01-25 09:18:23.623  INFO 27420 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Shutting down ExecutorService 'hunterAsyncExecutor'
2022-01-25 09:18:23.624  INFO 27420 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
......
org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'entityManagerFactory' defined in class path resource.
......
Caused by:
org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

控制台提示hibernate的dialect没有设置，可是这里jdbcTemplate和vertica datasource的配置和hibernate应该是不存在关联的，因此开始排查问题。

排查

从报错日志：

Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]

中定位到HibernateJpaConfiguration这个类，

可以看到该类使用了@ConditionalOnSingleCandidate(DataSource.class) 注解，进一步查看这个注解：

从注解的解释中可以看出，当BeanFactory中已经存在了多个符合条件的Bean时，会指定匹配primary Bean。这里的Bean对应的就是DataSource。

在未添加Vertica DataSource的时候，项目正常启动，应用上下文中有且只有一个sqlServer DataSource被HibernateJpaConfiguration识别加载；添加Vertica DataSource之后，applicationContext.xml中配置的Vertica DataSource首先被HibernateJpaConfiguration加载，由于Hibernate已配置的方言列表中未包含Vertica，因此报错:

org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set。

解决方法

既然问题是由于Vertica DataSource首先被HibernateJpaConfiguration加载导致的，那么只需要和原来保持一致，使sqlServer DataSource优先被加载即可解决问题。因此将applicationContext.xml中的vertica DataSource和vertica JdbcTemplate改为代码的方式配置，同样以代码的方式配置sqlServer DataSource，并添加@Primary注解，使其被优先加载。

@Configuration
public class VerticaJdbcTemplateConfig {
    @Primary
    @Bean(name = "DataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariDataSource primaryDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
 
 
    @Bean(name = "verticaDataSource")
    @ConfigurationProperties(prefix = "vertica.datasource")
    ComboPooledDataSource verticaDataSource() {
        return DataSourceBuilder.create()
                .type(ComboPooledDataSource.class).build();
    }
 
    @Bean(name = "verticaJdbcTemplate")
    JdbcTemplate verticaJdbcTemplate(@Qualifier("verticaDataSource") ComboPooledDataSource comboPooledDataSource) {
        return new JdbcTemplate(comboPooledDataSource);
    }
}

在application.properties中配置DataSource具体参数