How to connect to two databases using spring boot application?-CodePudding

I have to connect to two oracle databases from a spring boot application. Below is what I have done so far:

application.properties

 #############################################
## Database Configuration
#############################################
# HikariCP settings
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.poolName=HikariPoolOrcl
# JPA settings
spring.jpa.database=default
spring.datasource.dialect=org.hibernate.dialect.OracleDialect
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver.class=oracle.jdbc.driver.OracleDriver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect
spring.jpa.database-platform=org.hibernate.dialect.OracleDialect
############################################
# OracleDB connection settings
###########################################
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=userdev
spring.datasource.password=pass123
spring.datasource.pool-size=30
############################################
# OracleDB connection settings FOR IL DB
###########################################
spring.il.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.il.datasource.jdbc-url=jdbc:oracle:thin:@//192.126.98.77:1521/apimdbuat
spring.il.datasource.username=userdev
spring.il.datasource.password=Ahjhj20
spring.il.datasource.pool-size=30

the user model in the first database

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "User")
@Table(name = "users")
public class User {
    @Id
    @SequenceGenerator(name = "user_sequence", sequenceName = "user_sequence", allocationSize = 1

    )

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_sequence"

    )
    @Column(nullable = false, updatable = false)
    private Long id;

    @Column(nullable = false, length = 64)
    @NotBlank(message = "Firstname is required")
    private String firstname;
    @NotBlank(message = "Lastname is required")
    @Column(nullable = false, length = 64)
    private String lastname;

    @NotBlank(message = "Username is required")
    @Column(nullable = false, length = 64, unique = true)
    private String username;

    @Column(nullable = false, length = 64, unique = true)
    @Email
    @NotEmpty(message = "Email is required")
    private String email;

    @NotBlank(message = "Password is required")
    @Column(nullable = false, length = 64)
    @JsonIgnore
    private String password;

    private String profileImgUrl;
    private Date lastLoginDate;
    
    private Date joinDate;
    @JsonProperty("isActive")
    private boolean isActive;
    @JsonProperty("isNotLocked")
    private boolean isNotLocked;


    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id")

    )
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Set<Role> roles = new HashSet<>();

and here is the model for the second database

@AllArgsConstructor
@NoArgsConstructor

@Entity
@Table(
        name = "app_config"
)
public class AppConfig {

    @Id
    @Column(
            name = "ID"
    )
    @GeneratedValue(
            strategy = GenerationType.AUTO
    )
    private Long id;
    private String appCode;

    private String appName;
    private String version;

}

Repositories:

public interface UserRepository extends JpaRepository<User, Long> {

    boolean existsByEmail(String email);

    boolean existsByUsername(String username);


} 
public interface AppConfigRepository extends JpaRepository<AppConfig, Long> {
}

and finally the configuration classes

@Configuration(proxyBeanMethods = false)
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager",
        basePackages = {"com.app.models",
                "com.app.repositories"}
)
public class PrimaryDatabaseConfig {
    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariDataSource dataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                              @Qualifier("primaryDataSource") DataSource primaryDataSource) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.app.repositories", "com.app.models")
                .build();
    }

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager",
        basePackages = {"com.app.il_models",
                "com.app.il_repositories"}
)
public class SecondaryDatabaseConfig {
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.il.datasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                                @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.app.il_models",
                        "com.app.il_repositories")
                .build();
    }

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

The first time I got exceptions related to jdbc url and after fixing it the application started normally but when I call the api of login which take username/password I get the following error

SQL Error: 904, SQLState: 42000
ORA-00904: "USER0_"."PROFILEIMGURL": invalid identifier
Unauthorized error: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet


{
    "code": "401",
    "message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"
}

PS: Everything worked fine when it was one database.

Thank you.

CodePudding user response：

You need to add two distinct datasources in the application.properties file. like this :

 spring.datasource.url= jdbc:mysql://<host1>:<port>/<database1>
 spring.datasource.username= <username1>
 spring.datasource.password= <password1>
 
 spring.second-datasource.url= jdbc:mysql://<host2>:<port>/<database2>
 spring.second-datasource.username= <username2>
 spring.second-datasource.password= <password2>

Then, create two separate EntityManagerFactoryBeans, like this .

    @Primary
    @Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityScanPackage entitiesScanPackage,
            DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource);
        emf.setPackagesToScan(entitiesScanPackage.getPackageName());
        emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        emf.setJpaProperties(jpaProperties());
        return emf;
    }
    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntitiesScanPackage entitiesScanPackage,
            @Qualifier("secondDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource);
        emf.setPackagesToScan(entitiesScanPackage.getPackageName());
        emf.setJpaVendorAdapter(new HibernateJpaVendor

Thank you.

CodePudding user response：

For some reasons, after connecting to two databases at the same time, spring boot models attributes names does not match the names of columns in the database although it works fine when it was only one database. To resolve that I had to annotate all attrbutes with @Column and force the fields names as they are in the database, I also forced the table names with @Table

@Column(name = "JOIN_DATE")
    private Date joinDate;

@Table(name = "USERS")