Vfrdtyky

Refer the official documentation

Creating more than one data source works 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();

}

Update 2018-01-07 with Spring Boot 1.5.8.RELEASE

Most answers do not provide how to use them (as datasource itself and as transaction), only how to config them.

You can see the runnable example and some explanation in https://www.surasint.com/spring-boot-with-multiple-databases-example/

I copied some code here.

First you have to set application.properties like this

#Database

database1.datasource.url=jdbc:mysql://localhost/testdb

database1.datasource.username=root

database1.datasource.password=root

database1.datasource.driver-class-name=com.mysql.jdbc.Driver



database2.datasource.url=jdbc:mysql://localhost/testdb2

database2.datasource.username=root

database2.datasource.password=root

database2.datasource.driver-class-name=com.mysql.jdbc.Driver

Then define them as providers (@Bean) like this:

@Bean(name = "datasource1")

@ConfigurationProperties("database1.datasource")

@Primary

public DataSource dataSource(){

    return DataSourceBuilder.create().build();

}



@Bean(name = "datasource2")

@ConfigurationProperties("database2.datasource")

public DataSource dataSource2(){

    return DataSourceBuilder.create().build();

}

Note that I have @Bean(name="datasource1") and @Bean(name="datasource2"), then you can use it when we need datasource as @Qualifier("datasource1") and @Qualifier("datasource2") , for example

@Qualifier("datasource1")

@Autowired

private DataSource dataSource;

If you do care about transaction, you have to define DataSourceTransactionManager for both of them, like this:

@Bean(name="tm1")

@Autowired

@Primary

DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) {

    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);

    return txm;

}



@Bean(name="tm2")

@Autowired

DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {

    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);

    return txm;

}

Then you can use it like

@Transactional //this will use the first datasource because it is @primary

or

@Transactional("tm2")

This should be enough. See example and detail in the link above.

Here is the Complete solution

#First Datasource (DB1)

db1.datasource.url: url

db1.datasource.username:user

db1.datasource.password:password



#Second Datasource (DB2)

db2.datasource.url:url

db2.datasource.username:user

db2.datasource.password:password

Since we are going to get access two different databases (db1, db2), we need to configure each data source configuration separately like:

public class DB1_DataSource {

@Autowired

private Environment env;

@Bean

@Primary

public LocalContainerEntityManagerFactoryBean db1EntityManager() {

    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

    em.setDataSource(db1Datasource());

    em.setPersistenceUnitName("db1EntityManager");

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();

    em.setJpaVendorAdapter(vendorAdapter);

    HashMap<string, object=""> properties = new HashMap<>();

    properties.put("hibernate.dialect",

            env.getProperty("hibernate.dialect"));

    properties.put("hibernate.show-sql",

            env.getProperty("jdbc.show-sql"));

    em.setJpaPropertyMap(properties);

    return em;

}



@Primary

@Bean

public DataSource db1Datasource() {



    DriverManagerDataSource dataSource

            = new DriverManagerDataSource();

    dataSource.setDriverClassName(

            env.getProperty("jdbc.driver-class-name"));

    dataSource.setUrl(env.getProperty("db1.datasource.url"));

    dataSource.setUsername(env.getProperty("db1.datasource.username"));

    dataSource.setPassword(env.getProperty("db1.datasource.password"));



    return dataSource;

}



@Primary

@Bean

public PlatformTransactionManager db1TransactionManager() {



    JpaTransactionManager transactionManager

            = new JpaTransactionManager();

    transactionManager.setEntityManagerFactory(

            db1EntityManager().getObject());

    return transactionManager;

}

}

Second Datasource :

public class DB2_DataSource {



@Autowired

private Environment env;



@Bean

public LocalContainerEntityManagerFactoryBean db2EntityManager() {

    LocalContainerEntityManagerFactoryBean em

            = new LocalContainerEntityManagerFactoryBean();

    em.setDataSource(db2Datasource());

    em.setPersistenceUnitName("db2EntityManager");

    HibernateJpaVendorAdapter vendorAdapter

            = new HibernateJpaVendorAdapter();

    em.setJpaVendorAdapter(vendorAdapter);

    HashMap<string, object=""> properties = new HashMap<>();

    properties.put("hibernate.dialect",

            env.getProperty("hibernate.dialect"));

    properties.put("hibernate.show-sql",

            env.getProperty("jdbc.show-sql"));

    em.setJpaPropertyMap(properties);

    return em;

}



@Bean

public DataSource db2Datasource() {

    DriverManagerDataSource dataSource

            = new DriverManagerDataSource();

    dataSource.setDriverClassName(

            env.getProperty("jdbc.driver-class-name"));

    dataSource.setUrl(env.getProperty("db2.datasource.url"));

    dataSource.setUsername(env.getProperty("db2.datasource.username"));

    dataSource.setPassword(env.getProperty("db2.datasource.password"));



    return dataSource;

}



@Bean

public PlatformTransactionManager db2TransactionManager() {

    JpaTransactionManager transactionManager

            = new JpaTransactionManager();

    transactionManager.setEntityManagerFactory(

            db2EntityManager().getObject());

    return transactionManager;

}

}

Here you can find the complete Example on my blog :
Spring Boot with Multiple DataSource Configuration

I used mybatis - springboot 2.0 tech stack,
solution:

//application.properties - start

    sp.ds1.jdbc-url=jdbc:mysql://localhost:3306/mydb?useSSL=false

    sp.ds1.username=user

    sp.ds1.password=pwd

    sp.ds1.testWhileIdle=true

    sp.ds1.validationQuery=SELECT 1

    sp.ds1.driverClassName=com.mysql.jdbc.Driver





    sp.ds2.jdbc-url=jdbc:mysql://localhost:4586/mydb?useSSL=false

    sp.ds2.username=user

    sp.ds2.password=pwd

    sp.ds2.testWhileIdle=true

    sp.ds2.validationQuery=SELECT 1

    sp.ds2.driverClassName=com.mysql.jdbc.Driver



//application.properties - end



//configuration class



    @Configuration

    @ComponentScan(basePackages = "com.mypkg")

    public class MultipleDBConfig {





        public static final String SQL_SESSION_FACTORY_NAME_1 = "sqlSessionFactory1";

        public static final String SQL_SESSION_FACTORY_NAME_2 = "sqlSessionFactory2";



        public static final String MAPPERS_PACKAGE_NAME_1 = "com.mypg.mymapper1";

        public static final String MAPPERS_PACKAGE_NAME_2 = "com.mypg.mymapper2";





        @Bean(name = "mysqlDb1")

        @Primary

        @ConfigurationProperties(prefix = "sp.ds1")

        public DataSource dataSource1() {

            System.out.println("db1 datasource");

            return DataSourceBuilder.create().build();

        }



        @Bean(name = "mysqlDb2")

        @ConfigurationProperties(prefix = "sp.ds2")

        public DataSource dataSource2() {

            System.out.println("db2 datasource");

            return  DataSourceBuilder.create().build();

        }



        @Bean(name = SQL_SESSION_FACTORY_NAME_1)

        @Primary

        public SqlSessionFactory sqlSessionFactory1(@Qualifier("mysqlDb1") DataSource dataSource1) throws Exception {

            System.out.println("sqlSessionFactory1");

            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

            sqlSessionFactoryBean.setTypeHandlersPackage(MAPPERS_PACKAGE_NAME_1);

            sqlSessionFactoryBean.setDataSource(dataSource1);

            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();

            sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);

            sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);

            return sqlSessionFactory;

        }



        @Bean(name = SQL_SESSION_FACTORY_NAME_2)

        public SqlSessionFactory sqlSessionFactory2(@Qualifier("mysqlDb2") DataSource dataSource2) throws Exception {

            System.out.println("sqlSessionFactory2");

            SqlSessionFactoryBean diSqlSessionFactoryBean = new SqlSessionFactoryBean();

            diSqlSessionFactoryBean.setTypeHandlersPackage(MAPPERS_PACKAGE_NAME_2);

            diSqlSessionFactoryBean.setDataSource(dataSource2);

            SqlSessionFactory sqlSessionFactory = diSqlSessionFactoryBean.getObject();

            sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);

            sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);

            return sqlSessionFactory;

        }



        @Bean

        @Primary

        public MapperScannerConfigurer mapperScannerConfigurer1() {

            System.out.println("mapperScannerConfigurer1");

            MapperScannerConfigurer configurer = new MapperScannerConfigurer();

            configurer.setBasePackage(MAPPERS_PACKAGE_NAME_1);

            configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_1);

            return configurer;

        }



        @Bean

        public MapperScannerConfigurer mapperScannerConfigurer2() {

            System.out.println("mapperScannerConfigurer2");

            MapperScannerConfigurer configurer = new MapperScannerConfigurer();

            configurer.setBasePackage(MAPPERS_PACKAGE_NAME_2);

            configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_2);

            return configurer;

        }







    }

Note :
1)@Primary -> @primary

2)---."jdbc-url" in properties -> After Spring Boot 2.0 migration: jdbcUrl is required with driverClassName

I also had to setup connection to 2 datasources from Spring Boot application, and it was not easy - the solution mentioned in the Spring Boot documentation didn't work. After a long digging through the internet I made it work and the main idea was taken from this article and bunch of other places.

The following solution is written in Kotlin and works with Spring Boot 2.1.3 and Hibernate Core 5.3.7. Main issue was that it was not enough just to setup different DataSource configs, but it was also necessary to configure EntityManagerFactory and TransactionManager for both databases.

Here is config for the first (Primary) database:

@Configuration

@EnableJpaRepositories(

    entityManagerFactoryRef = "firstDbEntityManagerFactory",

    transactionManagerRef = "firstDbTransactionManager",

    basePackages = ["org.path.to.firstDb.domain"]

)

@EnableTransactionManagement

class FirstDbConfig {



    @Bean

    @Primary

    @ConfigurationProperties(prefix = "spring.datasource.firstDb")

    fun firstDbDataSource(): DataSource {

        return DataSourceBuilder.create().build()

    }



    @Primary

    @Bean(name = ["firstDbEntityManagerFactory"])

    fun firstDbEntityManagerFactory(

        builder: EntityManagerFactoryBuilder,

        @Qualifier("firstDbDataSource") dataSource: DataSource

    ): LocalContainerEntityManagerFactoryBean {

        return builder

            .dataSource(dataSource)

            .packages(SomeEntity::class.java)

            .persistenceUnit("firstDb")

            // Following is the optional configuration for naming strategy

            .properties(

                singletonMap(

                    "hibernate.naming.physical-strategy",

                    "org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl"

                )

            )

            .build()

    }



    @Primary

    @Bean(name = ["firstDbTransactionManager"])

    fun firstDbTransactionManager(

        @Qualifier("firstDbEntityManagerFactory") firstDbEntityManagerFactory: EntityManagerFactory

    ): PlatformTransactionManager {

        return JpaTransactionManager(firstDbEntityManagerFactory)

    }

}

And this is config for second database:

@Configuration

@EnableJpaRepositories(

    entityManagerFactoryRef = "secondDbEntityManagerFactory",

    transactionManagerRef = "secondDbTransactionManager",

    basePackages = ["org.path.to.secondDb.domain"]

)

@EnableTransactionManagement

class SecondDbConfig {



    @Bean

    @ConfigurationProperties("spring.datasource.secondDb")

    fun secondDbDataSource(): DataSource {

        return DataSourceBuilder.create().build()

    }



    @Bean(name = ["secondDbEntityManagerFactory"])

    fun secondDbEntityManagerFactory(

        builder: EntityManagerFactoryBuilder,

        @Qualifier("secondDbDataSource") dataSource: DataSource

    ): LocalContainerEntityManagerFactoryBean {

        return builder

            .dataSource(dataSource)

            .packages(EntityFromSecondDb::class.java)

            .persistenceUnit("secondDb")

            .build()

    }



    @Bean(name = ["secondDbTransactionManager"])

    fun secondDbTransactionManager(

        @Qualifier("secondDbEntityManagerFactory") secondDbEntityManagerFactory: EntityManagerFactory

    ): PlatformTransactionManager {

        return JpaTransactionManager(secondDbEntityManagerFactory)

    }

}

The properties for datasources are like this:

spring.datasource.firstDb.jdbc-url=

spring.datasource.firstDb.username=

spring.datasource.firstDb.password=



spring.datasource.secondDb.jdbc-url=

spring.datasource.secondDb.username=

spring.datasource.secondDb.password=

Issue with properties was that I had to define jdbc-url instead of url because otherwise I had an exception.

p.s.
Also you might have different naming schemes in your databases, which was the case for me. Since Hibernate 5 does not support all previous naming schemes, I had to use solution from this answer - maybe it will also help someone as well.

# Here '1stDB' is the database name

spring.datasource.url=jdbc:mysql://localhost/A

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver





# Here '2ndDB' is the database name

spring.second-datasourcee.url=jdbc:mysql://localhost/B

spring.second-datasource.username=root

spring.second-datasource.password=root

spring.second-datasource.driver-class-name=com.mysql.jdbc.Driver





    @Bean

    @Primary

    @ConfigurationProperties(prefix = "spring.datasource")

    public DataSource firstDataSource() {

        return DataSourceBuilder.create().build();

    }



    @Bean

    @ConfigurationProperties(prefix = "spring.second-datasource")

    public DataSource secondDataSource() {

       return DataSourceBuilder.create().build();

    }

@Primary annotation when used against a method like below works good if the two data sources are on the same db location/server.

@Bean(name = "datasource1")

@ConfigurationProperties("database1.datasource")

@Primary

public DataSource dataSource(){

  return DataSourceBuilder.create().build();

}



@Bean(name = "datasource2")

@ConfigurationProperties("database2.datasource")

public DataSource dataSource2(){

  return DataSourceBuilder.create().build();

}

If the data sources are on different servers its better to use @Component along with @Primary annotation. The following code snippet works well on two different data sources at different locations

database1.datasource.url = jdbc:mysql://127.0.0.1:3306/db1

database1.datasource.username = root

database1.datasource.password = mysql

database1.datasource.driver-class-name=com.mysql.jdbc.Driver



database2.datasource1.url = jdbc:mysql://192.168.113.51:3306/db2

database2.datasource1.username = root

database2.datasource1.password = mysql

database2.datasource1.driver-class-name=com.mysql.jdbc.Driver



@Configuration

@Primary

@Component

@ComponentScan("com.db1.bean")

class DBConfiguration1{

    @Bean("db1Ds")

    @ConfigurationProperties(prefix="database1.datasource")

    public DataSource primaryDataSource() {

        return DataSourceBuilder.create().build();

    }



}



@Configuration

@Component

@ComponentScan("com.db2.bean")

class DBConfiguration2{

    @Bean("db2Ds")

    @ConfigurationProperties(prefix="database2.datasource1")

    public DataSource primaryDataSource() {

        return DataSourceBuilder.create().build();

    }



}

My requirement was slightly different but used two data sources.

I have used two data sources for same JPA entities from same package. One for executing DDL at the server startup to create/update tables and another one is for DML at runtime.

The DDL connection should be closed after DDL statements are executed, to prevent further usage of super user previlleges anywhere in the code.

Properties

spring.datasource.url=jdbc:postgresql://Host:port

ddl.user=ddluser

ddl.password=ddlpassword

dml.user=dmluser

dml.password=dmlpassword

spring.datasource.driver-class-name=org.postgresql.Driver

Data source config classes

//1st Config class for DDL Data source

  public class DatabaseDDLConfig {

        @Bean

        public LocalContainerEntityManagerFactoryBean ddlEntityManagerFactoryBean() {

            LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();

            PersistenceProvider persistenceProvider = new 

            org.hibernate.jpa.HibernatePersistenceProvider();

            entityManagerFactoryBean.setDataSource(ddlDataSource());

            entityManagerFactoryBean.setPackagesToScan(new String { 

            "com.test.two.data.sources"});

            HibernateJpaVendorAdapter vendorAdapter = new 

            HibernateJpaVendorAdapter();

            entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);

            HashMap<String, Object> properties = new HashMap<>();

            properties.put("hibernate.dialect", 

            "org.hibernate.dialect.PostgreSQLDialect");

            properties.put("hibernate.physical_naming_strategy", 

            "org.springframework.boot.orm.jpa.hibernate.

            SpringPhysicalNamingStrategy");

            properties.put("hibernate.implicit_naming_strategy", 

            "org.springframework.boot.orm.jpa.hibernate.

            SpringImplicitNamingStrategy");

            properties.put("hibernate.hbm2ddl.auto", "update");

            entityManagerFactoryBean.setJpaPropertyMap(properties);

            entityManagerFactoryBean.setPersistenceUnitName("ddl.config");

            entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);

            return entityManagerFactoryBean;

        }





    @Bean

    public DataSource ddlDataSource() {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));

        dataSource.setUrl(env.getProperty("spring.datasource.url"));

        dataSource.setUsername(env.getProperty("ddl.user");

        dataSource.setPassword(env.getProperty("ddl.password"));

        return dataSource;

    }



    @Bean

    public PlatformTransactionManager ddlTransactionManager() {

        JpaTransactionManager transactionManager = new JpaTransactionManager();

        transactionManager.setEntityManagerFactory(ddlEntityManagerFactoryBean().getObject());

        return transactionManager;

    }

}

//2nd Config class for DML Data source

public class DatabaseDMLConfig {



    @Bean

    @Primary

    public LocalContainerEntityManagerFactoryBean dmlEntityManagerFactoryBean() {

        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();

        PersistenceProvider persistenceProvider = new org.hibernate.jpa.HibernatePersistenceProvider();

        entityManagerFactoryBean.setDataSource(dmlDataSource());

        entityManagerFactoryBean.setPackagesToScan(new String { "com.test.two.data.sources" });

        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();

        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);

        entityManagerFactoryBean.setJpaProperties(defineJpaProperties());

        entityManagerFactoryBean.setPersistenceUnitName("dml.config");

        entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);

        return entityManagerFactoryBean;

    }



    @Bean

    @Primary

    public DataSource dmlDataSource() {

        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));

        dataSource.setUrl(envt.getProperty("spring.datasource.url"));

        dataSource.setUsername("dml.user");

        dataSource.setPassword("dml.password");

        return dataSource;

    }



    @Bean

    @Primary

    public PlatformTransactionManager dmlTransactionManager() {

        JpaTransactionManager transactionManager = new JpaTransactionManager();

        transactionManager.setEntityManagerFactory(dmlEntityManagerFactoryBean().getObject());

        return transactionManager;

    }





  }

//Usage of DDL data sources in code.

public class DDLServiceAtStartup {



//Import persistence unit ddl.config for ddl purpose.



@PersistenceUnit(unitName = "ddl.config")

private EntityManagerFactory entityManagerFactory;



public void executeDDLQueries() throws ContentServiceSystemError {

    try {

        EntityManager entityManager = entityManagerFactory.createEntityManager();

        entityManager.getTransaction().begin();

        entityManager.createNativeQuery("query to create/update table").executeUpdate();

        entityManager.flush();

        entityManager.getTransaction().commit();

        entityManager.close();



        //Close the ddl data source to avoid from further use in code.

        entityManagerFactory.close();

    } catch(Exception ex) {}

}

//Usage of DML data source in code.

public class DDLServiceAtStartup {

  @PersistenceUnit(unitName = "dml.config")

  private EntityManagerFactory entityManagerFactory;



  public void createRecord(User user) {

     userDao.save(user);

  }

}