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

Question

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.

Answer 1

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.

Answer 2

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.