Spring Boot Spring Data JPA with multiple DataSources

Question

I m trying to connect each  Repositories to different DataSource s  with Spring Boot and Spring Data JPA  I used the following  http   xantorohara blogspot com 2013 11 spring-boot-jdbc-with-multiple html  as a referrence   Here is the code I am using in an attempt to implement a similar solution using Spring Data JPA   CustomerDbConfig java First data source connection    Configuration  EnableJpaRepositories          entityManagerFactoryRef    orderEntityManager           transactionManagerRef    orderTransactionManager           basePackages     com mm repository customer    public class CustomerDbConfig         Bean name    customerEntityManager       public LocalContainerEntityManagerFactoryBean entityManagerFactory            LocalContainerEntityManagerFactoryBean em   new LocalContainerEntityManagerFactoryBean            em setDataSource dataSource             em setPackagesToScan new String     com mm domain customer              JpaVendorAdapter vendorAdapter   new HibernateJpaVendorAdapter            em setJpaVendorAdapter vendorAdapter           em setJpaProperties additionalJpaProperties             em setPersistenceUnitName  customerPersistence            em setPackagesToScan  com mm domain customer             return em             Properties additionalJpaProperties            Properties properties   new Properties            properties setProperty  hibernate hbm2ddl auto    create-drop            properties setProperty  hibernate dialect    org hibernate dialect H2Dialect            properties setProperty  hibernate show sql    true             return properties              Bean     public DataSource dataSource            return DataSourceBuilder create                    url  jdbc h2 mem customer H2                    driverClassName  org h2 Driver                    username  sa                    password                      build                   Bean name    customerTransactionManager       public PlatformTransactionManager transactionManager EntityManagerFactory emf           JpaTransactionManager transactionManager   new JpaTransactionManager            transactionManager setEntityManagerFactory emf            return transactionManager            CustomerDbConfig java  Second data source    Configuration  EnableJpaRepositories          entityManagerFactoryRef    orderEntityManager           transactionManagerRef    orderTransactionManager           basePackages     com mm repository customer    public class CustomerDbConfig         Bean name    customerEntityManager       public LocalContainerEntityManagerFactoryBean entityManagerFactory            LocalContainerEntityManagerFactoryBean em   new LocalContainerEntityManagerFactoryBean            em setDataSource dataSource             em setPackagesToScan new String     com mm domain customer              JpaVendorAdapter vendorAdapter   new HibernateJpaVendorAdapter            em setJpaVendorAdapter vendorAdapter           em setJpaProperties additionalJpaProperties             em setPersistenceUnitName  customerPersistence            em setPackagesToScan  com mm domain customer             return em             Properties additionalJpaProperties            Properties properties   new Properties            properties setProperty  hibernate hbm2ddl auto    create-drop            properties setProperty  hibernate dialect    org hibernate dialect H2Dialect            properties setProperty  hibernate show sql    true             return properties              Bean     public DataSource dataSource            return DataSourceBuilder create                    url  jdbc h2 mem customer H2                    driverClassName  org h2 Driver                    username  sa                    password                      build                   Bean name    customerTransactionManager       public PlatformTransactionManager transactionManager EntityManagerFactory emf           JpaTransactionManager transactionManager   new JpaTransactionManager            transactionManager setEntityManagerFactory emf            return transactionManager            Customer java  model    Entity  Table name    customer    Data  EqualsAndHashCode exclude     id    public class Customer         Id      GeneratedValue strategy   GenerationType AUTO      private Integer id        Column name    name   nullable   false      private String name        Column name    age   nullable   false      private Integer age          Order java  model    Entity  Table name    order    Data  EqualsAndHashCode exclude     id    public class Order         Id      GeneratedValue strategy   GenerationType AUTO      private Integer id        Column name    code   nullable   false      private Integer code        Column name    quality   nullable   false      private Integer quality         CustomerRepository java  public interface CustomerRepository extends JpaRepository lt Customer  Integer gt        OrderRepository java  public interface OrderRepository extends JpaRepository lt Order  Integer gt         Finally  Application java   Configuration  ComponentScan  EnableAutoConfiguration public class Application extends SpringApplication          public static void main String   args                SpringApplication run Application class  args                       Bean         public ServletRegistrationBean h2Console                 ServletRegistrationBean reg   new ServletRegistrationBean new WebServlet      console                  reg setLoadOnStartup 1               return reg                During start the following exceptions are thrown   -10-10 15 45 24 757 ERROR 1549 ---             main  o s boot SpringApplication                 Application startup failed  org springframework beans factory UnsatisfiedDependencyException  Error creating bean with name  customerTransactionManager  defined in class path resource  com mm boot multidb CustomerConfig class   Unsatisfied dependency expressed through constructor argument with index 0 of type  javax persistence EntityManagerFactory     No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager  nested exception is org springframework beans factory NoUniqueBeanDefinitionException  No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager     at org springframework beans factory support ConstructorResolver createArgumentArray ConstructorResolver java 747      at org springframework beans factory support ConstructorResolver instantiateUsingFactoryMethod ConstructorResolver java 462      at org springframework beans factory support AbstractAutowireCapableBeanFactory instantiateUsingFactoryMethod AbstractAutowireCapableBeanFactory java 1095      at org springframework beans factory support AbstractAutowireCapableBeanFactory createBeanInstance AbstractAutowireCapableBeanFactory java 990      at org springframework beans factory support AbstractAutowireCapableBeanFactory doCreateBean AbstractAutowireCapableBeanFactory java 504      at org springframework beans factory support AbstractAutowireCapableBeanFactory createBean AbstractAutowireCapableBeanFactory java 475      at org springframework beans factory support AbstractBeanFactory 1 getObject AbstractBeanFactory java 302      at org springframework beans factory support DefaultSingletonBeanRegistry getSingleton DefaultSingletonBeanRegistry java 228      at org springframework beans factory support AbstractBeanFactory doGetBean AbstractBeanFactory java 298      at org springframework beans factory support AbstractBeanFactory getBean AbstractBeanFactory java 193      at org springframework beans factory support DefaultListableBeanFactory preInstantiateSingletons DefaultListableBeanFactory java 706      at org springframework context support AbstractApplicationContext finishBeanFactoryInitialization AbstractApplicationContext java 762      at org springframework context support AbstractApplicationContext refresh AbstractApplicationContext java 482      at org springframework boot context embedded EmbeddedWebApplicationContext refresh EmbeddedWebApplicationContext java 109      at org springframework boot SpringApplication refresh SpringApplication java 691      at org springframework boot SpringApplication run SpringApplication java 320      at org springframework boot SpringApplication run SpringApplication java 952      at org springframework boot SpringApplication run SpringApplication java 941      at com mm boot multidb Application main Application java 17  Caused by  org springframework beans factory NoUniqueBeanDefinitionException  No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager     at org springframework beans factory support DefaultListableBeanFactory doResolveDependency DefaultListableBeanFactory java 974      at org springframework beans factory support DefaultListableBeanFactory resolveDependency DefaultListableBeanFactory java 862      at org springframework beans factory support ConstructorResolver resolveAutowiredArgument ConstructorResolver java 811      at org springframework beans factory support ConstructorResolver createArgumentArray ConstructorResolver java 739          18 common frames omitted  Exception in thread  main  org springframework beans factory UnsatisfiedDependencyException  Error creating bean with name  customerTransactionManager  defined in class path resource  com mm boot multidb CustomerConfig class   Unsatisfied dependency expressed through constructor argument with index 0 of type  javax persistence EntityManagerFactory     No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager  nested exception is org springframework beans factory NoUniqueBeanDefinitionException  No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager     at org springframework beans factory support ConstructorResolver createArgumentArray ConstructorResolver java 747      at org springframework beans factory support ConstructorResolver instantiateUsingFactoryMethod ConstructorResolver java 462      at org springframework beans factory support AbstractAutowireCapableBeanFactory instantiateUsingFactoryMethod AbstractAutowireCapableBeanFactory java 1095      at org springframework beans factory support AbstractAutowireCapableBeanFactory createBeanInstance AbstractAutowireCapableBeanFactory java 990      at org springframework beans factory support AbstractAutowireCapableBeanFactory doCreateBean AbstractAutowireCapableBeanFactory java 504      at org springframework beans factory support AbstractAutowireCapableBeanFactory createBean AbstractAutowireCapableBeanFactory java 475      at org springframework beans factory support AbstractBeanFactory 1 getObject AbstractBeanFactory java 302      at org springframework beans factory support DefaultSingletonBeanRegistry getSingleton DefaultSingletonBeanRegistry java 228      at org springframework beans factory support AbstractBeanFactory doGetBean AbstractBeanFactory java 298      at org springframework beans factory support AbstractBeanFactory getBean AbstractBeanFactory java 193      at org springframework beans factory support DefaultListableBeanFactory preInstantiateSingletons DefaultListableBeanFactory java 706      at org springframework context support AbstractApplicationContext finishBeanFactoryInitialization AbstractApplicationContext java 762      at org springframework context support AbstractApplicationContext refresh AbstractApplicationContext java 482      at org springframework boot context embedded EmbeddedWebApplicationContext refresh EmbeddedWebApplicationContext java 109      at org springframework boot SpringApplication refresh SpringApplication java 691      at org springframework boot SpringApplication run SpringApplication java 320      at org springframework boot SpringApplication run SpringApplication java 952      at org springframework boot SpringApplication run SpringApplication java 941      at com mm boot multidb Application main Application java 17  Caused by  org springframework beans factory NoUniqueBeanDefinitionException  No qualifying bean of type  javax persistence EntityManagerFactory  is defined  expected single matching bean but found 2  customerEntityManager orderEntityManager     at org springframework beans factory support DefaultListableBeanFactory doResolveDependency DefaultListableBeanFactory java 974      at org springframework beans factory support DefaultListableBeanFactory resolveDependency DefaultListableBeanFactory java 862      at org springframework beans factory support ConstructorResolver resolveAutowiredArgument ConstructorResolver java 811      at org springframework beans factory support ConstructorResolver createArgumentArray ConstructorResolver java 739          18 more   Full code for the sample can be found on GitHub  https   github com tonym2105 samples tree master boot-multidb-sample   Thank you in advance for the help

User · Answer

thanks to the answers of Steve Park and Rafal Borowiec I got my code working, however, I had one issue: the DriverManagerDataSource is a "simple" implementation and does NOT give you a ConnectionPool (check http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/datasource/DriverManagerDataSource.html).

Hence, I replaced the functions which returns the DataSource for the secondDB to.

public DataSource <secondaryDB>DataSource() {
    // use DataSourceBuilder and NOT DriverManagerDataSource 
    // as this would NOT give you ConnectionPool
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
    dataSourceBuilder.url(databaseUrl);
    dataSourceBuilder.username(username);
    dataSourceBuilder.password(password);
    dataSourceBuilder.driverClassName(driverClassName);
    return dataSourceBuilder.build();
}

Also, if do you not need the EntityManager as such, you can remove both the entityManager() and the @Bean annotation.

Plus, you may want to remove the basePackages annotation of your configuration class: maintaining it with the factoryBean.setPackagesToScan() call is sufficient.

User · Answer

I checked the source code you provided on GitHub  There were several mistakes   typos in the configuration   In CustomerDbConfig   OrderDbConfig you should refer to customerEntityManager and packages should point at existing packages    Configuration  EnableJpaRepositories      entityManagerFactoryRef    customerEntityManager       transactionManagerRef    customerTransactionManager       basePackages     com mm boot multidb repository customer    public class CustomerDbConfig     The packages to scan in customerEntityManager and orderEntityManager were both not pointing at proper package   em setPackagesToScan  com mm boot multidb model customer      Also the injection of proper EntityManagerFactory did not work  It should be    Bean name    customerTransactionManager   public PlatformTransactionManager transactionManager EntityManagerFactory customerEntityManager        The above was causing the issue and the exception  While providing the name in a  Bean method you are sure you get proper EMF injected   The last thing I have done was to disable to automatic configuration of JpaRepositories    EnableAutoConfiguration exclude   JpaRepositoriesAutoConfiguration class    And with all fixes the application starts as you probably expect

User · Answer

I have written a complete article at Spring Boot JPA Multiple Data Sources Example  In this article  we will learn how to configure multiple data sources and connect to multiple databases in a typical Spring Boot web application  We will use Spring Boot 2 0 5  JPA  Hibernate 5  Thymeleaf and H2 database to build a simple Spring Boot multiple data sources web application

User · Answer

don t know why  but it works  Two configuration are the same  just change xxx to your name    Configuration  EnableTransactionManagement  EnableJpaRepositories      entityManagerFactoryRef    xxxEntityManager       transactionManagerRef    xxxTransactionManager       basePackages     aaa xxx     public class RepositoryConfig    Autowired private Environment env    Bean  Primary  ConfigurationProperties prefix  datasource xxx   public DataSource xxxDataSource         return DataSourceBuilder create   build        Bean public LocalContainerEntityManagerFactoryBean xxxEntityManager         LocalContainerEntityManagerFactoryBean em   new LocalContainerEntityManagerFactoryBean        em setDataSource xxxDataSource         em setPackagesToScan new String     aaa xxx          HibernateJpaVendorAdapter vendorAdapter   new HibernateJpaVendorAdapter        em setJpaVendorAdapter vendorAdapter       HashMap lt String  Object gt  properties   new HashMap lt String  Object gt         properties put  hibernate show sql   env getProperty  hibernate show sql         properties put  hibernate hbm2ddl auto   env getProperty  hibernate hbm2ddl auto         properties put  hibernate dialect   env getProperty  hibernate dialect         em setJpaPropertyMap properties        return em      Bean name    xxxTransactionManager   public PlatformTransactionManager xxxTransactionManager         JpaTransactionManager tm   new JpaTransactionManager        tm setEntityManagerFactory xxxEntityManager   getObject         return tm

User · Answer

here is my solution  base on spring-boot 1 2 5 RELEASE      application properties   first datasource driver-class-name com mysql jdbc Driver first datasource url jdbc mysql   127 0 0 1 3306 test first datasource username  first datasource password  first datasource validation-query select 1  second datasource driver-class-name com mysql jdbc Driver second datasource url jdbc mysql   127 0 0 1 3306 test2 second datasource username  second datasource password  second datasource validation-query select 1      DataSourceConfig java    Configuration public class DataSourceConfig        Bean      Primary      ConfigurationProperties prefix  first datasource       public DataSource firstDataSource             DataSource ds    DataSourceBuilder create   build            return ds              Bean      ConfigurationProperties prefix  second datasource       public DataSource secondDataSource             DataSource ds    DataSourceBuilder create   build            return ds

User · Answer

There is another way to have multiple dataSources by using  EnableAutoConfiguration and application properties   Basically put multiple dataSource configuration info on application properties and generate default setup  dataSource and entityManagerFactory  automatically for first dataSource by  EnableAutoConfiguration  But for next dataSource  create dataSource  entityManagerFactory and transactionManager all manually by the info from property file   Below is my example to setup two dataSources  First dataSource is setup by  EnableAutoConfiguration which can be assigned only for one configuration  not multiple  And that will generate  transactionManager  by DataSourceTransactionManager  that looks default transactionManager generated by the annotation  However I have seen the transaction not beginning issue on the thread from scheduled thread pool only for the default DataSourceTransactionManager and also when there are multiple transaction managers   So I create transactionManager manually by JpaTransactionManager also for the first dataSource with assigning  transactionManager  bean name and default entityManagerFactory  That JpaTransactionManager for first dataSource surely resolves the weird transaction issue on the thread from ScheduledThreadPool   Update for Spring Boot 1 3 0 RELEASE  I found my previous configuration with  EnableAutoConfiguration for default dataSource has issue on finding entityManagerFactory with Spring Boot 1 3 version  Maybe default entityManagerFactory is not generated by  EnableAutoConfiguration  once after I introduce my own transactionManager  So now I create entityManagerFactory by myself  So I don t need to use  EntityScan  So it looks I m getting more and more out of the setup by  EnableAutoConfiguration   Second dataSource is setup without  EnableAutoConfiguration and create  anotherTransactionManager  by manual way   Since there are multiple transactionManager extends from PlatformTransactionManager  we should specify which transactionManager to use on each  Transactional annotation  Default Repository Config   Configuration  EnableTransactionManagement  EnableAutoConfiguration  EnableJpaRepositories          entityManagerFactoryRef    entityManagerFactory           transactionManagerRef    transactionManager           basePackages     com mysource repository    public class RepositoryConfig        Autowired     JpaVendorAdapter jpaVendorAdapter        Autowired     DataSource dataSource        Bean name    entityManager       public EntityManager entityManager             return entityManagerFactory   createEntityManager                Primary      Bean name    entityManagerFactory       public EntityManagerFactory entityManagerFactory             LocalContainerEntityManagerFactoryBean emf   new LocalContainerEntityManagerFactoryBean            emf setDataSource dataSource           emf setJpaVendorAdapter jpaVendorAdapter           emf setPackagesToScan  com mysource model            emf setPersistenceUnitName  default          lt - giving  default  as name         emf afterPropertiesSet            return emf getObject                Bean name    transactionManager       public PlatformTransactionManager transactionManager             JpaTransactionManager tm   new JpaTransactionManager            tm setEntityManagerFactory entityManagerFactory             return tm            Another Repository Config   Configuration  EnableTransactionManagement  EnableJpaRepositories          entityManagerFactoryRef    anotherEntityManagerFactory           transactionManagerRef    anotherTransactionManager           basePackages     com mysource anothersource repository    public class AnotherRepositoryConfig        Autowired     JpaVendorAdapter jpaVendorAdapter        Value    another datasource url        private String databaseUrl        Value    another datasource username        private String username        Value    another datasource password        private String password        Value    another dataource driverClassName        private String driverClassName        Value    another datasource hibernate dialect        private String dialect       public DataSource dataSource             DriverManagerDataSource dataSource   new DriverManagerDataSource databaseUrl  username  password           dataSource setDriverClassName driverClassName           return dataSource              Bean name    anotherEntityManager       public EntityManager entityManager             return entityManagerFactory   createEntityManager                Bean name    anotherEntityManagerFactory       public EntityManagerFactory entityManagerFactory             Properties properties   new Properties            properties setProperty  hibernate dialect   dialect            LocalContainerEntityManagerFactoryBean emf   new LocalContainerEntityManagerFactoryBean            emf setDataSource dataSource             emf setJpaVendorAdapter jpaVendorAdapter           emf setPackagesToScan  com mysource anothersource model          lt - package for entities         emf setPersistenceUnitName  anotherPersistenceUnit            emf setJpaProperties properties           emf afterPropertiesSet            return emf getObject                Bean name    anotherTransactionManager       public PlatformTransactionManager transactionManager             return new JpaTransactionManager entityManagerFactory               application properties    database configuration spring datasource url jdbc h2 file   main-source AUTO SERVER TRUE spring datasource username sa spring datasource password  spring datasource driver-class-name org h2 Driver spring datasource continueOnError true spring datasource initialize false    another database configuration another datasource url jdbc sqlserver   localhost 1433 DatabaseName another  another datasource username username another datasource password  another datasource hibernate dialect org hibernate dialect SQLServer2008Dialect  another datasource driverClassName com microsoft sqlserver jdbc SQLServerDriver   Choose proper transactionManager for  Transactional annotation  Service for first datasource   Service  mainService    Transactional  transactionManager   public class DefaultDataSourceServiceImpl implements DefaultDataSourceService                      Service for another datasource   Service  anotherService    Transactional  anotherTransactionManager   public class AnotherDataSourceServiceImpl implements AnotherDataSourceService

[java] Spring Boot, Spring Data JPA with multiple DataSources

Examples related to java

Examples related to spring

Examples related to jpa

Examples related to spring-data

Examples related to spring-boot