Adding a second datasource - SpringBoot Repository

Question

I'm trying to find the best approach to adding a second datasource to our application. The main purpose is to expose CRUD ops against the db via rest, & need to bounce against a 2nd db for authentication and role management. We are not using XML configs.

Is there a way to simply add a second datasource bean in the existing PersistenceConfig.java file, or do we need to replicate the whole config class for the second db instance?

The application:

package foo;

import foo.config.PersistenceConfig;
import foo.config.RepositoryRestConfig;
import foo.config.WebConfig;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@ComponentScan
@EnableJpaRepositories
@Import({PersistenceConfig.class, WebConfig.class, RepositoryRestConfig.class})
@EnableAutoConfiguration
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

The Repo:

package foo.repository;

import foo.Widget;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import java.util.List;

@RepositoryRestResource(collectionResourceRel = "widgets", path = "widgets")
public interface WidgetsRepository extends CrudRepository<Widget, Long> {
    List<Widget> findByWidgetId(@Param("widgetid") long widgetId);
}   

The persistence config :

package foo.config;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.rest.webmvc.config.RepositoryRestMvcConfiguration;
import org.springframework.orm.jpa.JpaDialect;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaDialect;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.PersistenceContext;
import javax.sql.DataSource;


@Configuration
@Import(RepositoryRestMvcConfiguration.class)
@EnableJpaRepositories
@EnableTransactionManagement

public class PersistenceConfig {

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setDatabase(Database.SQL_SERVER);
        vendorAdapter.setShowSql(true);

        final LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("foo.model");
        factory.setDataSource(dataSource());
        return factory;
    }

    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        dataSource.setUrl("jdbc:sqlserver://127.0.0.1:1433;databaseName=fooDB");
        dataSource.setUsername("sa");
        dataSource.setPassword("*******");
        dataSource.setTestOnBorrow(true);
        dataSource.setTestOnReturn(true);
        dataSource.setTestWhileIdle(true);
        dataSource.setTimeBetweenEvictionRunsMillis(1800000L);
        dataSource.setNumTestsPerEvictionRun(3);
        dataSource.setMinEvictableIdleTimeMillis(1800000L);
        dataSource.setValidationQuery("SELECT 1");
        return dataSource;
    }


    @Bean
    public JpaDialect jpaDialect() {
        return new HibernateJpaDialect();
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(entityManagerFactory().getObject());
        return txManager;
    }

}

Thank you for your assistance...

Answer 1

First of all, it's worth noting that almost all of the configuration in PersistenceConfig is redundant as Spring Boot will automatically configure it for you. Pretty much the only thing that is non-default and you need to specify is your DataSource configuration, for example the SQLServer URL.

There's a section in the documentation that describes how to configure two DataSources using @Primary and application.properties:

Creating more than one data source works the same as creating the first one. You might want to mark one of them as @Primary if you are using the default auto-configuration for JDBC or JPA (then that one will be picked up by any @Autowired injections)."

@Bean
@Primary
@ConfigurationProperties(prefix="datasource.primary")
public DataSource primaryDataSource() {
    return DataSourceBuilder.create().build();
}

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

You'd then configure these two DataSources using application.properties and the datasource.primary and datasource.secondary prefixes:

For example:

datasource.primary.jdbcUrl=jdbc:sqlserver://127.0.0.1:1433;databaseName=fooDB
datasource.primary.user=sa
datasource.primary.password=secret

datasource.primary.jdbcUrl=jdbc:sqlserver://127.0.0.1:1433;databaseName=barDB
datasource.primary.user=sa
datasource.primary.password=secret

Answer 2

Take a look at @Qualifier annotation. With this annotation you are able to define various beans of the same type and assign them names. It is equivalent of id parameter in bean XML tag.

This is relevant part of Spring documentation.