Spring Boot with multiple data sources using same repositories and model classes?

Question

I have to do a Spring Boot version 1.5 application that can do like this: it creates an object and try to persist to both data sources (example: 2 databases named: test_book_1 and test_book_2 in Postgresql).

I have found an example that could work for 2 different objects (Author: A, Book: B) which can be stored in different databases (A goes to test_book_1 and B goes to test_book_2). This is a good example but it is not what I wanted. Store separate objects to different data sources

I got the idea that I need to define 2 custom JPA DatabaseConfigurations and need to config them to manage the same repository and domain class. However, Spring only use the second class as Qualifier to inject for JPA repository (I understand that when both configurations point to same class then the second one can override).

The question is, how can I tell Spring to let it knows that when it should inject the correct Bean (BookRepository) from the wanted data source (I wanted to persist the object to both data sources, not just the second one).

Here is the modified code from the example link above.

An application.properties file which is modified to create 2 database in Postgresql instead of 1 in Postgresql and 1 in Mysql.

server.port=8082
# -----------------------
# POSTGRESQL DATABASE CONFIGURATION
# -----------------------
    spring.postgresql.datasource.url=jdbc:postgresql://localhost:5432/test_book_db
spring.postgresql.datasource.username=petauser
spring.postgresql.datasource.password=petapasswd
spring.postgresql.datasource.driver-class-name=org.postgresql.Driver

# ------------------------------
# POSTGRESQL 1 DATABASE CONFIGURATION
# ------------------------------

   spring.mysql.datasource.url=jdbc:postgresql://localhost:5432/test_author_db
spring.mysql.datasource.username=petauser
spring.mysql.datasource.password=petapasswd
spring.mysql.datasource.driver-class-name=org.postgresql.Driver

package: com.roufid.tutorial.configuration class APostgresqlConfiguration

package com.roufid.tutorial.configuration;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.roufid.tutorial.entity.postgresql.Book;

/**
 * Spring configuration of the "PostgreSQL" database.
 *
 * @author Radouane ROUFID.
 *
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "postgresqlEntityManager",
        transactionManagerRef = "postgresqlTransactionManager",
        basePackages = "com.roufid.tutorial.dao.postgresql"
)
public class APostgresqlConfiguration {

    /**
     * PostgreSQL datasource definition.
     *
     * @return datasource.
     */
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.postgresql.datasource")
    public DataSource postgresqlDataSource() {
        return DataSourceBuilder
                .create()
                .build();
    }

    /**
     * Entity manager definition.
     *
     * @param builder an EntityManagerFactoryBuilder.
     * @return LocalContainerEntityManagerFactoryBean.
     */
    @Primary
    @Bean(name = "postgresqlEntityManager")
    public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(postgresqlDataSource())
                .properties(hibernateProperties())
                .packages(Book.class)
                .persistenceUnit("postgresqlPU")
                .build();
    }

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

    private Map<String, Object> hibernateProperties() {

        Resource resource = new ClassPathResource("hibernate.properties");

        try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
            return properties.entrySet().stream()
                    .collect(Collectors.toMap(
                            e -> e.getKey().toString(),
                            e -> e.getValue())
                    );
        } catch (IOException e) {
            return new HashMap<String, Object>();
        }
    }
}

package: com.roufid.tutorial.configuration class MysqlConfiguration

package com.roufid.tutorial.configuration;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.roufid.tutorial.entity.mysql.Author;
import com.roufid.tutorial.entity.postgresql.Book;

/**
 * Spring configuration of the "mysql" database.
 *
 * @author Radouane ROUFID.
 *
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "mysqlEntityManager",
        transactionManagerRef = "mysqlTransactionManager",
        basePackages = "com.roufid.tutorial.dao.postgresql"
)
public class MysqlConfiguration {

    /**
     * MySQL datasource definition.
     *
     * @return datasource.
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder
                .create()
                .build();
    }

    /**
     * Entity manager definition.
     *
     * @param builder an EntityManagerFactoryBuilder.
     * @return LocalContainerEntityManagerFactoryBean.
     */
    @Bean(name = "mysqlEntityManager")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(mysqlDataSource())
                .properties(hibernateProperties())
                .packages(Book.class)
                .persistenceUnit("mysqlPU")
                .build();
    }

    /**
     * @param entityManagerFactory
     * @return
     */
    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManager") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> hibernateProperties() {

        Resource resource = new ClassPathResource("hibernate.properties");
    }
}    try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
            return properties.entrySet().stream()
                    .collect(Collectors.toMap(
                            e -> e.getKey().toString(),
                            e -> e.getValue())
                    );
        } catch (IOException e) {
            return new HashMap<String, Object>();
        }
    }
}

package com.roufid.tutorial.dao.postgresql class BookRepository

package com.roufid.tutorial.dao.postgresql;

import org.springframework.data.repository.CrudRepository;

import com.roufid.tutorial.entity.postgresql.Book;

/**
 * Book repository.
 * 
 * @author Radouane ROUFID.
 *
 */
public interface BookRepository extends CrudRepository<Book, Long> {

}

package com.roufid.tutorial.entity.postgresql class Book

package com.roufid.tutorial.entity.postgresql;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "BOOK")
public class Book implements Serializable {

    private static final long serialVersionUID = -9019470250770543773L;

    @Id
    private Long id;

    @Column
    private String name;

    @Column
    private Long authorId;

    ...
    // Setters, Getters

}

And a test class to inject the BookRepository which will use the MysqlConfiguration class (second datasource) only.

@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTest {
@Autowired
private BookRepository bookRepository;
@Before
public void init() {   
    Book book = new Book();
    book.setId(bookId);
    book.setName("Spring Boot Book");

    // How can it persist to the first datasource?  
    bookRepository.save(book);
}

}

Answer 1

Looks like you need multitenancy support.

There is a Spring based solution for this

You need to implement CurrentTenantIdentifierResolver interface

public String resolveCurrentTenantIdentifier()

And extend

AbstractDataSourceBasedMultiTenantConnectionProviderImpl

to return DataSource for the tenant

See more here

Answer 2

So I think I got an answer myself (I want to stick with Spring JPA and Hibernate only). So here is what I did, inspired from Spring Booth with 2 different data sources

The most important class is the config class to manually create 2 data sources (2 databases in Postgresql)

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "sourceEntityManagerFactory",
        basePackages = "application"
)
public class PersistenceConfig {

    @Autowired
    private JpaVendorAdapter jpaVendorAdapter;

    private String databaseUrl = "jdbc:postgresql://localhost:5432/test_book_db";

    private String targetDatabaseUrl = "jdbc:postgresql://localhost:5432/test_author_db";

    private String username = "petauser";

    private String password = "petapasswd";

    private String driverClassName = "org.postgresql.Driver";

    private String dialect = "org.hibernate.dialect.PostgreSQLDialect";

    private String ddlAuto = "update";

    @Bean
    public EntityManager sourceEntityManager() {
        return sourceEntityManagerFactory().createEntityManager();
    }

    @Bean
    public EntityManager targetEntityManager() {
        return targetEntityManagerFactory().createEntityManager();
    }

    @Bean
    @Primary
    public EntityManagerFactory sourceEntityManagerFactory() {
        return createEntityManagerFactory("source", databaseUrl);
    }

    @Bean
    public EntityManagerFactory targetEntityManagerFactory() {
        return createEntityManagerFactory("target", targetDatabaseUrl);
    }

    @Bean(name = "transactionManager")
    @Primary
    public PlatformTransactionManager sourceTransactionManager() {
        return new JpaTransactionManager(sourceEntityManagerFactory());
    }

    @Bean
    public PlatformTransactionManager targetTransactionManager() {
        return new JpaTransactionManager(targetEntityManagerFactory());
    }

    private EntityManagerFactory createEntityManagerFactory(final String persistenceUnitName,
            final String databaseUrl) {
        final LocalContainerEntityManagerFactoryBean entityManagerFactory = new LocalContainerEntityManagerFactoryBean();

        final DriverManagerDataSource dataSource = new DriverManagerDataSource(databaseUrl, username, password);
        dataSource.setDriverClassName(driverClassName);
        entityManagerFactory.setDataSource(dataSource);

        entityManagerFactory.setJpaVendorAdapter(jpaVendorAdapter);
        entityManagerFactory.setPackagesToScan("application.domain");
        entityManagerFactory.setPersistenceUnitName(persistenceUnitName);

        final Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", dialect);
        properties.setProperty("hibernate.hbm2ddl.auto", ddlAuto);
        entityManagerFactory.setJpaProperties(properties);

        entityManagerFactory.afterPropertiesSet();
        return entityManagerFactory.getObject();
    }

}

Because of I want to copy a stored entity from source database to a target database. So I used Spring JPA to read the object from source database

public interface StorageEntryRepository extends     CrudRepository<StorageEntry, Long> {

}

And I made a service class to check if the entity which is existed by value (someValue contain a substring "Book") in target database before persisting it in target database by Hibernate (the StorageEntry here is a domain class from the example link above).

@Service
@Transactional(rollbackFor = Exception.class)
public class StorageEntryService {

    @Autowired
    private StorageEntryRepository storageEntryRepository;

    @PersistenceContext(unitName = "target")
    private EntityManager targetEntityManager;

    public void save(StorageEntry storageEntry) throws Exception {

        // this.storageEntryRepository.save(storageEntry);

        // Load an stored entry from the source database
        StorageEntry storedEntry = this.storageEntryRepository.findOne(12L);                
        //this.storageEntryRepository.save(storageEntry);
        // Save also to a different database
        final Session targetHibernateSession = targetEntityManager.unwrap(Session.class);
        Criteria criteria = targetHibernateSession.createCriteria(StorageEntry.class);

        criteria.add(Restrictions.like("someValue", "%Book1%"));
        List<StorageEntry> storageEntries = criteria.list();

        if (storageEntries.isEmpty()) {
            targetEntityManager.merge(storedEntry);
            // No flush then nodata is saved in the different database
            targetHibernateSession.flush();
            System.out.println("Stored the new object to target database.");
        } else {
            System.out.println("Object already existed in target database.");
        }


    }
}

So it ends up with I can use both JPA from the current working application and just need to make another application with a config class and a service class to do this migration of existing objects to a new database.