SpringBoot Multiple Data Source JPA

Question

I have following code

Entity

 @Entity
    public class Employee {

        @Id
        @GeneratedValue
        long id;

        @Column(name="first_name")
        String firstName;

        @Column(name="last_name")
        String lastName;

        @Column(name="salary")
        int salary;

        @ManyToOne
        @JoinColumn(name="address")
        Address address;

    ..... setter & getter

    }

Repo

public interface EmpRepository extends JpaRepository<Employee, Long>{}

Service

@Service
public class EmpService {

    @Autowired
    private EmpRepository empRepo;

    @Autowired @Qualifier("primaryEntityManagerFactory") EntityManager em;

    public List<Employee> findAll(){
        return empRepo.findAll();
    }

}

Controller

@RestController
@RequestMapping(value = "/demo")
public class DemoController {

    @Autowired
    private EmpService empService;


    @RequestMapping("/abcd")
    public List<Employee> findAll(){
        return empService.findAll();
    }

}

Data Source Configuraion (Primary)

@Configuration
@EnableJpaRepositories(basePackages="com.example",
entityManagerFactoryRef = "primaryEntityManagerFactory")
public class DataSourceConfiguration {

    @Bean(name="primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(primaryEntityManagerFactory().getObject());
        return tm;
    }

    @Bean(name="primaryEntityManagerFactory")
    @Primary
    LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory() {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(primaryDataSource());
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        factoryBean.setPackagesToScan(DataSourceConfiguration.class.getPackage().getName());

        return factoryBean;
    }

}

Secondary Data Source Config

 @Configuration
@EnableJpaRepositories(basePackages="com.example",
entityManagerFactoryRef = "secondaryEntityManagerFactory")
public class DS2Configuration {

    @Bean(name="secondaryDataSource")
    @ConfigurationProperties(prefix = "datasource.secondary")

    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public JpaTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(secondaryEntityManagerFactory().getObject());
        return tm;
    }

    @Bean(name="secondaryEntityManagerFactory")
    LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory() {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(secondaryDataSource());
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        factoryBean.setPackagesToScan(DataSourceConfiguration.class.getPackage().getName());

        return factoryBean;
    }

}

application.properties

spring.jpa.show-sql = true
spring.jpa.properties.hibernate.show_sql=true
spring.jooq.sql-dialect=MYSQL
logging.level.org.springframework.data=DEBUG

# Primary DataSource configuration
datasource.primary.url=jdbc:mysql://127.0.0.1:3306/jpa
datasource.primary.username=root
datasource.primary.password=root

# Secondary DataSource configuration
datasource.secondary.url=jdbc:mysql://127.0.0.1:3306/jpa2
datasource.secondary.username=root
datasource.secondary.password=root

# Disable Spring DataSource auto-initialization
spring.datasource.initialize=false

server.port=8081

When I am actually running function findAll() from EmpService, it is always using primary datasource even when i am specifying secondary entitymanagefactory as

@Autowired
    @Qualifier("secondaryEntityManagerFactory")
    EntityManager em;

How to resolve this? P.S. - Please do not share blog links for this.

Answer 1

You need to place your Repository classes in two different directories. And change the

@EnableJpaRepositories(basePackages="com.example".....)  

line in both the places where you are configuring the datasource. That should fix your problem of
code runs but uses always primary Data Source

Regards

Answer 2

You need to add @Primary on this method : primaryEntityManagerFactory()

By the way, I think primaryTransactionManager() and equivalent secondary methods are superfluous, the factory bean methods return the same thing.

You also need to define a transactionManager per entityManager e.g.

@Bean
@Primary
public JpaTransactionManager transactionManager() {
    JpaTransactionManager tm = new JpaTransactionManager();
    tm.setEntityManagerFactory(yourPrimaryEntityManagerInjected);
    return tm;
}

Answer 3

This happens because there are more than one beans of type javax.persistence.EntityManagerFactory to help spring boot decide which bean to prefer

annotate the bean with @primary annotation

@primary official documentation