Spring boot multiple data sources using EntityManager

Question

I'm trying to setup a Springboot (v2.0.0.BUILD-SNAPSHOT) project with multiple datasources using this tutorial from INFOQ

https://www.infoq.com/articles/Multiple-Databases-with-Spring-Boot

But instead of JdbcTemplate i need to use multiple EntityManagers

Here's what i have so far

Application.properties

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

Application.java

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);
    }
}

ApplicationConfiguration.java

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();
    }

}

GenericDAO.java

public abstract class GenericDAO<T extends Serializable> {

    private Class<T> clazz = null;

    @PersistenceContext
    protected EntityManager entityManager;

    public void setClazz(Class<T> clazzToSet) {
        this.clazz = clazzToSet;
    }

    public T findOne(Integer id) {          
        return this.entityManager.find(this.clazz, id);
    }

    public List<T> findAll() {
        return this.entityManager.createQuery("from " + this.clazz.getName()).getResultList();
    }

    @Transactional
    public void save(T entity) {
        this.entityManager.persist(setModifiedAt(entity));
    }
}

PersonDAO.java

@Repository
@PersistenceContext(name = "primaryEM")
public class PersonDAO extends GenericDAO<Person> {
    public PersonDAO() {
        this.setClazz(Person.class);
    }
}

ProductDAO.java

@Repository
@PersistenceContext(name = "secondaryEM")
public class ProductDAO extends GenericDAO<Product> {
    public ProductDAO() {
        this.setClazz(Product.class);
    }
}

TestService.java

@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);
    }

}

Problem is that it doesn't work. When i try to persist "Product" (secondary ds), it also try to persist to the @Primary datasource.

How can i do this similar to the JdbcTemplate example from the article ?

What am i doing wrong ?

Thanks !

UPDATE (Tried @Deepak solution)

Try the below

@Repository
public class PersonDAO extends GenericDAO<Person> {
    @Autowired
    public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Person.class);
    }
}

ProductDAO

@Repository
public class ProductDAO extends GenericDAO<Product> {
    @Autowired
    public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Product.class);
    }
}

Also Remove @PersistenceContext annotation from GenericDAO

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: 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<java.util.Map<java.lang.String, java.lang.Object>> 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 
--> ERROR

Seems it's building both entities with the @Primary datasource dialect (In this case "SQLServer2012Dialect").

Secondary EntityManager should be "Oracle12cDialect".

UPDATE (SOLUTION)

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:

1) add correct dialects to application.properties file

spring.primary.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.secondary.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect

2) Import application.properties dialect value into ApplicationConfiguration.java

@Value("${spring.primary.hibernate.dialect}")
private String dialect;

3) Force it into EntityManagerFactory

@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;
}

Now it works.

Is there a more elegant way of doing this ?

Solution 1

Try the below

@Repository
public class PersonDAO extends GenericDAO<Person> {
    @Autowired
    public PersonDAO(@Qualifier("primaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Person.class);
    }
}

ProductDAO

@Repository
public class ProductDAO extends GenericDAO<Product> {
    @Autowired
    public ProductDAO(@Qualifier("secondaryEM") EntityManager entityManager) {
        this.entityManager = entityManager;
        this.setClazz(Product.class);
    }
}

Also Remove @PersistenceContext annotation from GenericDAO

Solution 2

I think you should change "@PersistenceContext(name = "secondaryEM")" to "@PersistenceContext(unitName = "secondaryEM")" in order to specify the persistence unit.

Comments

[Bruno]

It didn't worked... still tries to use EntityManager declared as @Primary in ApplicationConfiguration.

[iZian]

wouldn't it be "@PersistenceContext(unitName = "secondaryPU")" ? I'm having similar issues here and I think it stems from the difference between a qualifier's autowiring behaviour and a PersistenceContext's unit name behaviour. I'm seeing that having to PU's and no unit name defined, I seem to get a duplication of my beans, one for each PU. but the annotation is in library code so I'm here looking for a way to qualify the bean autowiring against a specific bean made for a specific PU, when they don't / can't have names.

[Meriam]

it s more about 'name' and 'unitName', i think the value of the attribute doesn't really matter as you have potentially defined it in your configuration.

[russellhoff]

@Deepak is right: just remove @PersistenceContext and add @Autowired @Qualifier

[Gordon Hopper]

When you call the getProperties method directly: emf.setJpaProperties(primaryHibernateProperties()); This creates a new Properties object. It doesn't use the bean definition. In this case it doesn't matter, because Properties is a trivial object. The better way would be to add the bean as a method parameter. @Bean(name = "primaryEM") public LocalContainerEntityManagerFactoryBean storingEntityManagerFactory( EntityManagerFactoryBuilder builder, @Qualifier("primaryDB") DataSource ds, @Qualifier ("primaryhibernateproperties") Properties properties)

[Berényi Lajos]

Thank you your comment. My answer: yes and no. Your suggestion is correct, but my code doesn’t call the method directly eighter - otherwise it would return an empty property object, but it is filled with data from the properties file -, so because of the annotations my code also uses the bean, Spring handels it.

[Bharath]

@Deepak, what if multiple tables are there in a databases, do we need to create a Repository class for all those tables, is there any better way to do that.

[Deepak]

It depends on how you are going to read those tables. For instance your tables have relationships and you only query the parent table then you don't need to create separate repository for all tables.