Spring Boot Configure and Use Two DataSources

Question

How can I configure and use two data sources   For example here is what I have for the first data source   application properties   first db spring datasource url    url  spring datasource username    username  spring datasource password    password  spring datasource driverClassName   oracle jdbc OracleDriver   second db       Application class   SpringBootApplication public class SampleApplication       public static void main String   args            SpringApplication run SampleApplication class  args             How do I modify application properties to add another data source  How do I autowire it to be used by a different repository

User · Answer

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

User · Answer

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 lt String  Object gt  properties   new HashMap lt  gt                 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

User · Answer

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 lt string  object    gt  properties   new HashMap lt  gt         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 lt string  object    gt  properties   new HashMap lt  gt         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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here you go  Add in your application properties file   first db spring datasource url    url  spring datasource username    username  spring datasource password    password  spring datasource driverClassName   oracle jdbc OracleDriver   second db     spring secondDatasource url    url  spring secondDatasource username    username  spring secondDatasource password    password  spring secondDatasource driverClassName   oracle jdbc OracleDriver  Add in any class annotated with  Configuration the following methods   Bean  Primary  ConfigurationProperties prefix  quot spring datasource quot   public DataSource primaryDataSource         return DataSourceBuilder create   build        Bean  ConfigurationProperties prefix  quot spring secondDatasource quot   public DataSource secondaryDataSource         return DataSourceBuilder create   build

User · Answer

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

[java] Spring Boot Configure and Use Two DataSources

Examples related to java

Examples related to spring

Examples related to spring-boot

Examples related to spring-mvc

Examples related to datasource