CODEWITHSUNDEEP
mysqldatabasejpaspring-boot
AndroidDev
AndroidDev

Reputation: 938

Spring Boot: How to use multiple schemas and dynamically choose which one to use at runtime

I have the same question as below, but I want to know the answer. Spring Boot: How to use multiple schemas and dynamically choose which one to use for every request at runtime

Please help me in finding answer for

How can I have one database connection and specify a different schema for every request?

Thank you in advance.

Upvotes: 25

Views: 31340

Answers (2)

LJR
LJR

Reputation: 301

While specifying the datasource, connect to one of the schemas.

  #    DATABASE_PROPERTIES--------------------------------------------------------------------------
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/wa_blog_dev?currentSchema=wa_blog_dev

In the entities, specify the schema if that entity belongs to a different schema.

@Entity
@Table(name = "answer", schema = "another_schema")

In this way, your current schema is different and the schema specified on the entity is different.

I am not sure if you have the same table in every schema.

Upvotes: 1

buræquete
buræquete

Reputation: 14698

Wouldn't it work to have multiple data sources defined, and depending on your request, change to the one with the correct schema?

spring.datasource.url = jdbc:oracle:thin:@//maui:1521/xe
spring.datasource.username = schema1
spring.datasource.password = ...

spring.datasource2.url = jdbc:oracle:thin:@//maui:1521/xe
spring.datasource2.username = schema2
spring.datasource2.password = ..

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

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

Otherwise you'd need to kill & re-create the connection to keep using the singular data source, but that would be really slow for your application since it would need reconnecting again and again. It would be better for you to use some NoSQL database to achieve this sorta dynamic data storage.

Upvotes: 21

Related Questions