Search code examples
spring-bootspring-data-jpaspring-rest

Spring boot with multiple database connections

I made a simple SpringBoot REST application for testing purposes where the tables are in 2 databases, one is Mysql and one is Postgresql. To configure the 2 connections I used the instructions from here, at point 6 - "Multiple Databases in Spring Boot" and all seemed to be fine, the 2 connections were initiated but only the primary connection works.

So if the Mysql connection is annotated as @Primay only Mysql REST services work, on Postgresql the error for all tables is "org.hibernate.hql.internal.ast.QuerySyntaxException: <Entity_Name> is not mapped". But if I make a single change and set @Primary on the Postgresql connection then all Postgres tables are working and all Mysql tables give the same error(table not mapped).

So somehow I think the right connection is not autoselected based on the package.

UPDATE: I found another tutorial here using different database types, I followed the instructions but the result is the same, all tables in the secondary database give the error "org.hibernate.hql.internal.ast.QuerySyntaxException: <Entity_Name> is not mapped". I think the secondary connection is not used, somehow the primary one defaults on the wrong tables but I don't know why.

I uploaded this small Github project with my work. https://github.com/victorqedu/MultipleSpringBootDS

UPDATE: In the DAO class a have autowired the constructor and @Autowire is setting the wrong EntityManager(I think this is the source of the problem), could I manually specify the right EntityManager?

@Autowired
public AntibiogramaAntibioticeDAOHibernateImpl(EntityManager theEntityManager) {
    entityManager = theEntityManager;
}

I also tried the annotation @PersistenceContext on the EntityManager but the result is the same.

@PersistenceContext
private EntityManager entityManager;

I'm not sure the problem is EntityManagaer or the Session that I obtain from EntityManager.unwrap, seems to be little documentation about this...

Solution

  • This can be solved with Qualifier in a short description if you have multiple same type of beans(like EntityManager) you should use qualifier to wire them.

    Therefore in your code you should

     public AntibiogramaAntibioticeDAOHibernateImpl(
@Qualifier("primaryEntityManagerFactory") EntityManager theEntityManager)