Spring Boot, Spring Data JPA with Multiple Datasources

Spring Boot 2 Multiple Datasource - work only the one with @Primay annotation

I could resolve the problem with the following changes in my code:

application.properties

I changed the spring.jpa.hibernate.ddl-auto from none to validate and made some other chages

#Credenciales Datasource (InMpData)
spring.datasource.url=jdbc\:oracle\:thin\:[connection] #Not showing for security
spring.datasource.username=[user]
spring.datasource.password=[password]
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

#Credenciales Datasource (SGC)
spring.sgcdatasource.url=jdbc\:oracle\:thin\:[connection] #Not showing for security
spring.sgcdatasource.username=[user]
spring.sgcdatasource.password=[password]
spring.sgcdatasource.driver-class-name=oracle.jdbc.driver.OracleDriver

#Hibernate config
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.ddl-auto=validate

# HikariCP settings
# spring.datasource.hikari.*
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=5

pom.xml

Added a new dependency

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>provided</scope>
</dependency>

Divided the DatabaseConfiguration.java in two separeated files InMpConfig.java and SgcConfig.java

InMpConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.indra.vmo.edenorte.repository.inmp", entityManagerFactoryRef = "inMpEntityManagerFactory", transactionManagerRef = "inMpTransactionManager")
public class InMpConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties inMpDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.configuration")
    public DataSource inMpDataSource() {
        return inMpDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    
    /*Primary Entity manager*/
    @Primary
    @Bean(name = "inMpEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean inMpEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(inMpDataSource()).packages("com.indra.vmo.edenorte.entity.inmp").build();
    }
    
    @Primary
    @Bean
    public PlatformTransactionManager inMpTransactionManager(
            final @Qualifier("inMpEntityManagerFactory") LocalContainerEntityManagerFactoryBean inMpEntityManagerFactory) {
        return new JpaTransactionManager(inMpEntityManagerFactory.getObject());
    }
    
}

SgcConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.indra.vmo.edenorte.repository.sgc", entityManagerFactoryRef = "sgcEntityManagerFactory", transactionManagerRef = "sgcTransactionManager")
public class SgcConfig {

    @Bean
    @ConfigurationProperties("spring.sgcdatasource")
    public DataSourceProperties sgcDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean
    @ConfigurationProperties("spring.sgcdatasource.configuration")
    public DataSource sgcDataSource() {
        return sgcDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    
    @Bean(name = "sgcEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean sgcEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(sgcDataSource()).packages("com.indra.vmo.edenorte.entity.sgc").build();
    }
    
    @Bean(name = "sgcTransactionManager")
    public PlatformTransactionManager sgcTransactionManager(
            final @Qualifier("sgcEntityManagerFactory") LocalContainerEntityManagerFactoryBean sgcEntityManagerFactory) {
        return new JpaTransactionManager(sgcEntityManagerFactory.getObject());
    }
    
}

And made some changes to the models, such as add the @Table and @Column annotation

@Entity
@Data
@Table(name = "CLIENTES")
public class Clientes implements Serializable {

    @Id
    @NotNull
    @Column(name = "COD_CLI")
    private Integer codCli;
    
    @Column(name = "USUARIO")
    private String usuario;
    
    @Column(name = "F_ACTUAL")
    private Date fActual;

I hope this is helpful for someone else.

Multiple Datasources in Spring Boot using same repository and entities

The solution you are searching is well discussed in this artiche with the save action here

Just a couple of notes... You can configure two database and you can also set the same entities, but you cannot point at the same repository because each repository is related to a specific dataSource and transactionManager. Moreover, if you want to have the same entity ids across two databases you have to manage them manually.

Spring JPA – Multiple Databases with the same Repositories

Ideally, it is better to have two separate microservices connecting to two different data sources.

Spring Boot configure and use two data sources

Here you go.

Add in your application.properties file:

#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver

#second db ...
spring.secondDatasource.url = [url]
spring.secondDatasource.username = [username]
spring.secondDatasource.password = [password]
spring.secondDatasource.driverClassName = oracle.jdbc.OracleDriver

Add in any class annotated with @Configuration the following methods:

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

@Bean
@ConfigurationProperties(prefix="spring.secondDatasource")
public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
}

Multiple datasource for one model

Found the solution. You have to create 2 Entity manager, 2 Transaction manager, 2 Datasource config classes and 2 Repository in 2 different folders. Here is my code

https://github.com/j-arpit/MULTIDATABASE.git

---

Related Topics