Hikaricp configuration for multiple datasources

Question

I have a multi database application. Users can select the database on the login page.

Then the database is routing selected database thanks for AbstractRoutingDataSource from Spring.

I want to use HikariCP, but it needs dataSourceUrl. But my Datasource URL changes dynamically. How can I configure Hikaricp for multiple databases?

File application.properties:

#database1 properties
app.database1.connection.url = url1
app.database1.connection.username = sameusername
app.database1.connection.password = samepassword
#database2 properties
app.database2.connection.url = url2
app.database2.connection.username = sameusername
app.database2.connection.password = samepassword

My Datasource configuration class example:

public class DataSourceConfiguration {

    @Autowired(required = false)
    private PersistenceUnitManager persistenceUnitManager;

    @Bean
    @ConfigurationProperties(prefix = "app.database1.connection")
    public DataSource database1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "app.database2.connection")
    public DataSource database2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public DataSource appDataSource() {
        DataSourceRouter router = new DataSourceRouter();
        final HashMap<Object, Object> map = new HashMap<>(3);
        map.put(DatabaseEnvironment.DATABASE1, database1DataSource());
        map.put(DatabaseEnvironment.DATABASE2, database2DataSource());
        router.setTargetDataSources(map);
        return router;
    }

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

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

My session scoped class instead of context holder:

@Component
@Scope(value = "session", proxyMode = ScopedProxyMode.TARGET_CLASS)
public class PreferredDatabaseSession implements Serializable {

    /**
     *
     */
    private static final long serialVersionUID = 1L;

    private DatabaseEnvironment preferredDb;

    public DatabaseEnvironment getPreferredDb() {
        return preferredDb;
    }

    public void setPreferredDb(DatabaseEnvironment preferredDb) {
        this.preferredDb = preferredDb;
    }

}

Answer 1

The below snippet works for me

first.datasource.jdbc-url=jdbc-url
    first.datasource.username=username
    first.datasource.password=password
    .
    .
    .
    .
    =================== In Java Configuration File ==================
    
    @Primary
   @Bean(name = "firstDataSource")
        @ConfigurationProperties(prefix = "first.datasource")
        public DataSource dataSource() {
            return DataSourceBuilder.create().build();
        }
    

@Primary
        @Bean(name = "firstEntityManagerFactory")
        public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(EntityManagerFactoryBuilder builder,
                @Qualifier("firstDataSource") DataSource dataSource) {
            Map<String, String> props = new HashMap<String, String>();
            props.put("spring.jpa.database-platform",  "org.hibernate.dialect.Oracle12cDialect");
    .
    .
    .
    
            return builder.dataSource(dataSource).packages("com.first.entity").persistenceUnit("firstDB")
                    .properties(props)
                    .build();
        }

    @Primary
        @Bean(name = "firstTransactionManager")
        public PlatformTransactionManager firstTransactionManager(
                @Qualifier("firstEntityManagerFactory") EntityManagerFactory firstEntityManagerFactory) {
            return new JpaTransactionManager(firstEntityManagerFactory);
        }

second.datasource.jdbc-url=jdbc-url
second.datasource.username=username
second.datasource.password=password
.
.
.
.
=================== In Java Configuration File ==================

   @Bean(name = "secondDataSource")
    @ConfigurationProperties(prefix = "second.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("secondDataSource") DataSource dataSource) {
        Map<String, String> props = new HashMap<String, String>();
        props.put("spring.jpa.database-platform",  "org.hibernate.dialect.Oracle12cDialect");
.
.
.

        return builder.dataSource(dataSource).packages("com.second.entity").persistenceUnit("secondDB")
                .properties(props)
                .build();
    }

    @Bean(name = "secondTransactionManager")
    public PlatformTransactionManager secondTransactionManager(
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory secondEntityManagerFactory) {
        return new JpaTransactionManager(secondEntityManagerFactory);
    }

Answer 2

If I understand your requirement correctly, you intend to define two data sources and for a given request you want to route your queries to a particular data source based on some condition.

The solution is:

File application.properties

#database1 properties
app.database1.connection.url = url1
app.database1.connection.username = username1
app.database1.connection.password = password1
#database2 properties
app.database2.connection.url = url2
app.database2.connection.username = username2
app.database2.connection.password = password2
#default
default.datasource.key=dataSource1

File CommonRoutingDataSource.java

public class CommonRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceName();
    }

    public void initDataSources(final DataSource dataSource1, final DataSource dataSource2,
            final String defaultDataSourceKey) {
        final Map<Object, Object> dataSourceMap = new HashMap<Object, Object>();
        dataSourceMap.put("dataSource1", dataSource1);
        dataSourceMap.put("dataSource2", dataSource2);
        this.setDefaultTargetDataSource(dataSourceMap.get(defaultDataSourceKey));
        this.setTargetDataSources(dataSourceMap);
    }

}

File DataSourceContextHolder.java

public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    private DataSourceContextHolder() {
        // Private no-op constructor
    }

    public static final void setDataSourceName(final String dataSourceName) {
        Assert.notNull(dataSourceName, "dataSourceName cannot be null");
        contextHolder.set(dataSourceName);
    }

    public static final String getDataSourceName() {
        return contextHolder.get();
    }

    public static final void clearDataSourceName() {
        contextHolder.remove();
    }

}

File DataSourceConfig.java

public class DataSourceConfig {

    @Autowired
    private Environment env;

    @Autowired
    @Bean(name = "dataSource")
    public DataSource getDataSource(final DataSource dataSource1, final DataSource dataSource2) {
        final CommonRoutingDataSource dataSource = new CommonRoutingDataSource();
        dataSource.initDataSources(dataSource1, dataSource2, env.getProperty("default.datasource.key"));
        return dataSource;
    }

    @Bean(name = "dataSource1")
    public DataSource getDataSource1() throws SQLException {
        // The exact DataSource class imported shall be as per your requirement - HikariCP, or Tomcat etc.
        final DataSource dataSource = new DataSource();
        dataSource.setDriverClassName();
        dataSource.setUrl(env.getProperty("app.database1.connection.url"));
        // Set all data source attributes from the application.properties file
        return dataSource;
    }

    @Bean(name = "dataSource2")
    public DataSource getDataSource2() throws SQLException {
        // The exact DataSource class imported shall be as per your requirement - HikariCP, or Tomcat etc.
        final DataSource dataSource = new DataSource();
        dataSource.setDriverClassName();
        dataSource.setUrl(env.getProperty("app.database2.connection.url"));
        // set all data source attributes from the application.properties file
        return dataSource;
    }

}

Now, somewhere in your code (either an Aspect or Controller), you need to dynamically set the data source conditionally:

DataSourceContextHolder.setDataSourceName("dataSource1");

Note: It's better to declare the data source names as enums rather than strings "dataSource1", "dataSource2", etc.