title: Spring boot multiple data sources using EntityManager
body: <p>I'm trying to setup a Springboot (v2.0.0.BUILD-SNAPSHOT) project with multiple datasources using this tutorial from INFOQ</p>
  
  <p><a href="https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot" rel="noreferrer">https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot</a></p>
  
  <p>But instead of JdbcTemplate i need to use multiple EntityManagers</p>
  
  <p>Here's what i have so far</p>
  
  <blockquote>
    <p>Application.properties</p>
  </blockquote>
  
  <pre><code>spring.primary.url=jdbc:sqlserver://localhost:2433;databaseName=TEST
  spring.primary.username=root
  spring.primary.password=root
  spring.primary.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
  
  spring.secondary.url=jdbc:oracle:thin:@//localhost:1521/DB
  spring.secondary.username=oracle
  spring.secondary.password=root
  spring.secondary.driverClassName=oracle.jdbc.OracleDriver
  </code></pre>
  
  <blockquote>
    <p>Application.java</p>
  </blockquote>
  
  <pre><code>package com.test;
  
  import org.springframework.boot.SpringApplication;
  import org.springframework.boot.autoconfigure.SpringBootApplication;
  
  @SpringBootApplication
  public Application {
      public static void main(String[] args) {
          SpringApplication.run(Application.class, args);
      }
  }
  </code></pre>
  
  <blockquote>
    <p>ApplicationConfiguration.java</p>
  </blockquote>
  
  <pre><code>package com.test.config;
  import javax.sql.DataSource;
  import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
  import org.springframework.boot.context.properties.ConfigurationProperties;
  import org.springframework.context.annotation.Bean;
  import org.springframework.context.annotation.Configuration;
  import org.springframework.context.annotation.Primary;
  
  @Configuration
  public class ApplicationConfiguration {
  
      @Primary
      @Bean(name = "primaryDB")
      @ConfigurationProperties(prefix = "spring.primary")
      public DataSource postgresDataSource() {
          return  DataSourceBuilder.create().build();
      }
  
      @Bean(name = "primaryEM")
      public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
          EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds) {
          return builder
              .dataSource(ds)
              .packages("com.test.supplier1")
              .persistenceUnit("primaryPU")
              .build();
      }
  
      @Bean(name = "secondaryDB")
      @ConfigurationProperties(prefix = "spring.secondary")
      public DataSource mysqlDataSource() {
          return DataSourceBuilder.create().build();
      }
  
      @Bean(name = "secondaryEM")
      public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
          EntityManagerFactoryBuilder builder, @Qualifier("secondaryDB") DataSource ds) {
      return builder
              .dataSource(ds)
              .packages("com.test.supplier2")
              .persistenceUnit("secondaryPU")
              .build();
      }
  
  }
  </code></pre>
  
  <blockquote>
    <p>GenericDAO.java</p>
  </blockquote>
  
  <pre><code>public abstract class GenericDAO&lt;T extends Serializable&gt; {
  
      private Class&lt;T&gt; clazz = null;
  
      @PersistenceContext
      protected EntityManager entityManager;
  
      public void setClazz(Class&lt;T&gt; clazzToSet) {
          this.clazz = clazzToSet;
      }
  
      public T findOne(Integer id) {          
          return this.entityManager.find(this.clazz, id);
      }
  
      public List&lt;T&gt; findAll() {
          return this.entityManager.createQuery("from " + this.clazz.getName()).getResultList();
      }
  
      @Transactional
      public void save(T entity) {
          this.entityManager.persist(setModifiedAt(entity));
      }
  }
  </code></pre>
  
  <blockquote>
    <p>PersonDAO.java</p>
  </blockquote>
  
  <pre><code>@Repository
  @PersistenceContext(name = "primaryEM")
  public class PersonDAO extends GenericDAO&lt;Person&gt; {
      public PersonDAO() {
          this.setClazz(Person.class);
      }
  }
  </code></pre>
  
  <blockquote>
    <p>ProductDAO.java</p>
  </blockquote>
  
  <pre><code>@Repository
  @PersistenceContext(name = "secondaryEM")
  public class ProductDAO extends GenericDAO&lt;Product&gt; {
      public ProductDAO() {
          this.setClazz(Product.class);
      }
  }
  </code></pre>
  
  <blockquote>
    <p>TestService.java</p>
  </blockquote>
  
  <pre><code>@Service
  public class TestService {
  
      @Autowired
      PersonDAO personDao;
  
      @Autowired
      ProductDAO productDao;
  
      // This should write to primary datasource
      public void savePerson(Person person) {
          personDao.save(person);
      }
  
      // This should write to secondary datasource
      public void saveProduct(Product product) {
          productDao.save(product);
      }
  
  }
  </code></pre>
  
  <p>Problem is that it doesn't work. When i try to persist "Product" (secondary ds), it also try to persist to the @Primary datasource.</p>
  
  <p>How can i do this similar to the JdbcTemplate example from the article ?</p>
  
  <p>What am i doing wrong ?</p>
  
  <p>Thanks !</p>
  
  <h2>UPDATE (Tried @Deepak solution)</h2>
  
  <p>Try the below </p>
  
  <pre><code>@Repository
  public class PersonDAO extends GenericDAO&lt;Person&gt; {
      @Autowired
      public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
          this.entityManager = entityManager;
          this.setClazz(Person.class);
      }
  }
  </code></pre>
  
  <p>ProductDAO </p>
  
  <pre><code>@Repository
  public class ProductDAO extends GenericDAO&lt;Product&gt; {
      @Autowired
      public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
          this.entityManager = entityManager;
          this.setClazz(Product.class);
      }
  }
  </code></pre>
  
  <p>Also Remove @PersistenceContext annotation from GenericDAO</p>
  
  <pre><code>  .   ____          _            __ _ _
   /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
  ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
   \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
    '  |____| .__|_| |_|_| |_\__, | / / / /
   =========|_|==============|___/=/_/_/_/
   :: Spring Boot ::  (v2.0.0.BUILD-SNAPSHOT)
  
  com.test.Application                     : Starting Application on...   
  com.test.Application                     : No active profile set, falling back to default profiles: default 
  ConfigServletWebServerApplicationContext : Refreshing org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@69b2283a: startup date [Thu Apr 20 15:28:59 BRT 2017]; root of context hierarchy  
  .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!   
  .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!   
  f.a.AutowiredAnnotationBeanPostProcessor : JSR-330 'javax.inject.Inject' annotation found and supported for autowiring  
  o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8081 (http) 
  o.apache.catalina.core.StandardService   : Starting service Tomcat  
  org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.12    
  o.a.c.c.C.[Tomcat].[localhost].[/    : Initializing Spring embedded WebApplicationContext
  o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 4001 ms  
  
  o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]  
  o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]   
  o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]    
  o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]  
  o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]  
  
  j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'primaryPU' 
  o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [  name: primaryPU ...]    
  org.hibernate.Version                    : HHH000412: Hibernate Core {5.2.9.Final}  
  org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found    
  o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final} 
  org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect 
  j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'primaryPU'    
  
  j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'secondaryPU'   
  o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [  name: secondaryPU   ...]
  org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect 
  j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'secondaryPU'
  
  s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@69b2283a: startup date [Thu Apr 20 15:28:59 BRT 2017]; root of context hierarchy  
  s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity&lt;java.util.Map&lt;java.lang.String, java.lang.Object&gt;&gt; org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.error(javax.servlet.http.HttpServletRequest)    
  s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)  
  o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/** onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
  o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/** onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
  o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
  o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup    
  s.a.ScheduledAnnotationBeanPostProcessor : No TaskScheduler/ScheduledExecutorService bean found for scheduled processing    
  o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8081 (http)   
  io.test.Application                      : Started Application in 76.21 seconds (JVM running for 77.544)    
  
  org.hibernate.SQL                        : select next value for SEQ_TDAI_ID    
  o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 923, SQLState: 42000  
  o.h.engine.jdbc.spi.SqlExceptionHelper   : ORA-00923: FROM keyword not found where expected 
  --&gt; ERROR
  </code></pre>
  
  <p>Seems it's building both entities with the @Primary datasource dialect (In this case "<strong>SQLServer2012Dialect</strong>").</p>
  
  <p>Secondary EntityManager should be "<strong>Oracle12cDialect</strong>".</p>
  
  <h2>UPDATE (SOLUTION)</h2>
  
  <p>Seems the connections are ok, only problem is the wrong dialect (seems it defaults to the @Primary DataSource dialect), so the solution is to force it on EntityManagerFactory, heres my quickfix for it:</p>
  
  <p>1) add correct dialects to <code>application.properties</code> file</p>
  
  <pre><code>spring.primary.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
  spring.secondary.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
  </code></pre>
  
  <p>2) Import application.properties dialect value into <code>ApplicationConfiguration.java</code></p>
  
  <pre><code>@Value("${spring.primary.hibernate.dialect}")
  private String dialect;
  </code></pre>
  
  <p>3) Force it into EntityManagerFactory</p>
  
  <pre><code>@Bean(name = "primaryEM")
  public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds) {
  
      Properties properties = new Properties();
      properties.setProperty("hibernate.dialect", dialect);
  
      LocalContainerEntityManagerFactoryBean emf = builder
          .dataSource(ds)
          .packages("com.test.supplier1")
          .persistenceUnit("primaryPU")
          .build();
  
      emf.setJpaProperties(properties);
  
      return emf;
  }
  </code></pre>
  
  <p>Now it works.</p>
  
  <p>Is there a more elegant way of doing this ?</p>