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

I have a problem, I'm trying to use two Oracle databases in spring boot using DataSource, The DataSource wiht the @Primay annotation works fine but the oher one only gives me:

SQL Error: 942, SQLState: 42000
ORA-00942: table or view does not exist

I don't have idea what I should do now. Any help will be welcomed. Thanks!

application.properties

spring.datasource.url=jdbc\:oracle\:thin\:[connection] #Not showing for security
spring.datasource.password=[password]
spring.datasource.configuration.maximum-pool-size=30

spring.sgc-datasource.url=jdbc\:oracle\:[connection] #Not showing for security
spring.sgc-datasource.username=[user]
spring.sgc-datasource.password=[password]
spring.sgc-datasource.max-total=30

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.database=default
spring.jpa.hibernate.ddl-auto=none

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.indra.vmo.edenorte</groupId>
    <artifactId>InMpData</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>InMpData</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

DatabaseConfiguration.java

package com.indra.vmo.edenorte.config;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.zaxxer.hikari.HikariDataSource;

@Configuration(proxyBeanMethods = false)
public class DatabaseConfiguration {
    
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties inMpDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.configuration")
    public HikariDataSource inMpDataSource(DataSourceProperties inMpDataSourceProperties) {
        return inMpDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    
    @Bean
    @ConfigurationProperties("spring.sgc-datasource")
    public DataSourceProperties sgcDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean
    @ConfigurationProperties("spring.sgc-datasource.configuration")
    public HikariDataSource sgcDataSource(DataSourceProperties sgcDataSourceProperties) {
        return sgcDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    
}

Repository from 1st DB

package com.indra.vmo.edenorte.repository.inmp;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.indra.vmo.edenorte.entity.inmp.MpLocalidadesCoordenadas;

@Repository
public interface IMpLocalidadesCoordenadasRepository extends JpaRepository<MpLocalidadesCoordenadas, String> {

}

Repository from 2nd DB

package com.indra.vmo.edenorte.repository.sgc;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.indra.vmo.edenorte.entity.sgc.Clientes;

@Repository
public interface IClientesRepository extends JpaRepository<Clientes, Integer> {

    @Query("select c from Clientes c where c.docId=?1")
    public Clientes findBydocId(String docId);
    
    @Query("select c from Clientes c where c.docId=?1 and c.tipDoc=?2")
    public Clientes findBydocIdU(String docId, String tipDoc);
}

CodePudding user response：

SpringBoot auto-config works perfectly for a single datasource. For multiple datasources, you will need to manually configure the EntityManagerFactory beans for each of the datasources. See this article

CodePudding user response：

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.