rsp
rsp

Reputation: 911

How to connect multiple databases in JPA?

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.

Upvotes: 10

Views: 33220

Answers (3)

iftekher2283
iftekher2283

Reputation: 41

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.

Upvotes: 0

Ben
Ben

Reputation: 54

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) {
} 

Upvotes: 1

MOnkey
MOnkey

Reputation: 841

Follow the below steps:

  1. Add an additional datasource configuration to your application.properties.

  2. Set the SQL Dialect to “default” in your application.properties to let Spring autodetect the different SQL Dialects of each datasource

  3. Create a Java Package for each datasource with two nested Packages
  4. Create a Configuration Class for the First database
  5. Create a Configuration Class for the Second database
  6. Create an Entity for the First database
  7. Create a Repository for the First database
  8. Create an Entity for the Second database
  9. 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:)

Upvotes: 13

Related Questions