How to connect multiple databases in JPA?

Question

I have a Spring Boot App which is currently connected to a single database using JPA. Connection details in its application.properties file:

spring.datasource.url=jdbc:oracle:thin:@localhost:1522:orcl
spring.datasource.username=example
spring.datasource.password=example
spring.datasource.driver.class=oracle.jdbc.driver.OracleDriver

Now I have a new database I would like to connect to, so my application connects with 2 databases now. I can connect it using JDBC, there I will have to manually write connection code.

I have looked for solutions. Some are in hibernate with multiple config files. Others are using JDBC.

I tried looking in spring docs if there are provisions for defining multiple datasources in application.properties file, but couldn't find anything.

I would like to know if a solution exists using JPA alone. Thanks for your time.

have you checked this baeldung.com/spring-data-jpa-multiple-databases? — MOnkey
– MOnkey, Commented Mar 23, 2020 at 15:01
I have seen it, but cannot access his git repo to understand the whole demo. I am working behind a proxy. Thanks. — rsp
– rsp, Commented Mar 23, 2020 at 15:04
you can also check this one, this one is looking even more simpler, medium.com/@joeclever/… — MOnkey
– MOnkey, Commented Mar 23, 2020 at 15:08

MOnkey · Accepted Answer · 2020-03-23 15:28:14Z

Follow the below steps:

Add an additional datasource configuration to your application.properties.
Set the SQL Dialect to “default” in your application.properties to let Spring autodetect the different SQL Dialects of each datasource
Create a Java Package for each datasource with two nested Packages
Create a Configuration Class for the First database
Create a Configuration Class for the Second database
Create an Entity for the First database
Create a Repository for the First database
Create an Entity for the Second database
Create a Repository for the Second database

Full code is available here,

https://github.com/jahe/spring-boot-multiple-datasources

Above steps are took from this tutorials

https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7

Hope this will helps:)

Robert · Accepted Answer · 2021-06-29 17:33:05Z

1

Create Different DataSource bean in your configuration file. You should able to provide all db related configuration in beans:

@Bean
public DataSource db1(String url, String dbuser, String password) {
} 


@Bean
public DataSource db2(String url, String dbuser, String password) {
}

edited Jun 29, 2021 at 17:33

Robert

9,05660 gold badges189 silver badges210 bronze badges

answered Mar 23, 2020 at 15:14

Ben

544 bronze badges

Comments

iftekher2283 · Accepted Answer · 2022-09-13 12:19:44Z

Just add second DB source parameters in application.properties file as below

#DATABASE_ONE
spring.datasource.url=jdbc:oracle:thin:@//host:port/instancename
spring.datasource.username=SCHEMA_ONE
spring.datasource.password=SCHEMA_ONE_PASSWORD

#DATABASE_TWO
spring.second-datasource.jdbcUrl=jdbc:oracle:thin:@//host:port/instancename
spring.second-datasource.username=SCHEMA_TWO
spring.second-datasource.password=SCHEMA_TWO_PASSWORD

Use the schema key in @Table annotation to define which database to be used for the entity

@Entity
@Table(schema = "SCHEMA_ONE", name="TABLE_NAME")
public class ClassOne{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int paramOne;
    private String paramTwo;
    private String paramThree;
    private int paramFour;
    private Date paramFive;
}

@Entity
@Table(schema = "SCHEMA_TWO", name="TABLE_NAME")
public class ClassTwo{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int paramOne;
    private String paramTwo;
    private String paramThree;
    private int paramFour;
    private Date paramFive;
}

Write the repositories and services as it.

Collectives™ on Stack Overflow

How to connect multiple databases in JPA?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related