How to configure Spring boot for work with two databases?

Question

I am using Spring Boot 2.X with Hibernate 5 to connect two different MySQL databases (Bar and Foo) on different servers. I am trying to list all the information of an entity (own attributes and @OneToMany and @ManyToOne relations) from a method in a REST Controller.

I have followed several tutorials to do this, thus, I am able to get all the information for my @Primary database (Foo), however, I always get an exception for my secondary database (Bar) when retrieving the @OneToMany sets. If I swap the @Primary annotation to the Bar database, I able to get the data from the Bar database but not for the Foo database . Is there a way to resolve this?

This is the exception I am getting:

...w.s.m.s.DefaultHandlerExceptionResolver :
Failed to write HTTP message: org.springframework.http.converter.HttpMessageNotWritableException: 
    Could not write JSON document: failed to lazily initialize a collection of role: 
        com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]-com.foobar.bar.domain.Bar["manyBars"]); 
    nested exception is com.fasterxml.jackson.databind.JsonMappingException:
        failed to lazily initialize a collection of role: 
        com.foobar.bar.domain.Bar.manyBars, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->com.foobar.bar.domain.Bar["manyBars"])

My application.properties:

# MySQL DB - "foo"
spring.datasource.url=jdbc:mysql://XXX:3306/foo?currentSchema=public
spring.datasource.username=XXX
spring.datasource.password=XXX
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# MySQL DB - "bar"
bar.datasource.url=jdbc:mysql://YYYY:3306/bar?currentSchema=public
bar.datasource.username=YYYY
bar.datasource.password=YYYY
bar.datasource.driver-class-name=com.mysql.jdbc.Driver
# JPA
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

My @Primary DataSource configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef = "transactionManager",
        basePackages = {"com.foobar.foo.repo"})
public class FooDbConfig {

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

    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.foobar.foo.domain")
                .persistenceUnit("foo")
                .build();
    }

    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

My secondary DataSource configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "barEntityManagerFactory",
        transactionManagerRef = "barTransactionManager", basePackages = {"com.foobar.bar.repo"})
public class BarDbConfig {

    @Bean(name = "barDataSource")
    @ConfigurationProperties(prefix = "bar.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "barEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("barDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.foobar.bar.domain")
                .persistenceUnit("bar")
                .build();
    }

    @Bean(name = "barTransactionManager")
    public PlatformTransactionManager barTransactionManager(
            @Qualifier("barEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) {
        return new JpaTransactionManager(barEntityManagerFactory);
    }
}

The REST Controller class:

@RestController
public class FooBarController {

    private final FooRepository fooRepo;
    private final BarRepository barRepo;

    @Autowired
    FooBarController(FooRepository fooRepo, BarRepository barRepo) {
        this.fooRepo = fooRepo;
        this.barRepo = barRepo;
    }

    @RequestMapping("/foo")
    public List<Foo> listFoo() {
        return fooRepo.findAll();
    }

    @RequestMapping("/bar")
    public List<Bar> listBar() {
        return barRepo.findAll();
    }

    @RequestMapping("/foobar/{id}")
    public String fooBar(@PathVariable("id") Integer id) {
        Foo foo = fooRepo.findById(id);
        Bar bar = barRepo.findById(id);

        return foo.getName() + " " + bar.getName() + "!";
    }

}

The Foo/Bar repositories:

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
  Foo findById(Integer id);
}

@Repository
public interface BarRepository extends JpaRepository<Bar, Long> {
  Bar findById(Integer id);
}

The entities for the @Primary datasource. The entities of the second datasource are the same (only changing the class names):

@Entity
@Table(name = "foo")
public class Foo {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "foo")
    @JsonIgnoreProperties({"foo"})
    private Set<ManyFoo> manyFoos = new HashSet<>(0);

    // Constructors, Getters, Setters
}

@Entity
@Table(name = "many_foo")
public class ManyFoo {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;

    @Column(name = "name")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JsonIgnoreProperties({"manyFoos"})
    private Foo foo;

    // Constructors, Getters, Setters
}  

Finally, my application main:

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

It is important to remark that the solution should keep the Lazy property for both databases in order to maintain an optimal performance.

Edit 1: If both catalogs ("databases" in MySQL terminology) are in same database ("server") the Rick James solution works!!

The problem remains when catalogs (MySQL databases) are in different databases (servers) and it is tried to keep Lazy the property

Many thanks.

Answer 1

*ToMany Collections are lazy by default in Hibernate & JPA. The error is because Jackson is trying to serialize the OneToMany when the entity manager (aka session in hibernate-speak) is closed. Hence, lazy collections cannot be retrieved.

Spring Boot with JPA by default provides an OpenEntityManagerInViewFilter for the primary EM. This allows for read-only DB access, but, by default only works for the primary EM.

You have 3 options:

1) You can add a join fetch, e.g. How does the FetchMode work in Spring Data JPA

2) You can add a OpenEntityManagerInViewFilter for the non primary entity manager and add it to your context.

Please note that this implies a challenge, for each Bar and Foo instance, your app will go back to the database to retrieve the OneToMany. This is the part that isn't working for Bar, but is for Foo. This implies a scalability problem (called the N + 1 problem by some) as for each foo and bar, you run an additional query, which will get slow for non-trivial amounts of Foos and Bars.

3) An alternative is to make your collection on Bar and Foo eager (see this https://docs.oracle.com/javaee/7/api/javax/persistence/OneToMany.html#fetch-- ) but this needs to be analyzed carefully if scalability is a concern at all for you.

I'd recommend option #1.

Answer 2

Two databases (aka "catalogs") on the same server? Use only one connection. Then reference thus:

Foo.table1
Bar.table2

Use that syntax wherever you would have a simple table name.

Different servers

It gets messy if the data is not in the same machine. A couple of ideas:

• Fetch the data from each catalog, then manipulate in application code. The framework probably has no hooks for doing anything to both servers at the same time.
• Use MariaDB and its FEDERATEDX Engine.