25

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.

AndroidDev
  • 888
  • 3
  • 13
  • 27
  • Please explain the need for 1K identical tables. (The link seemed to have the false impression that it would somehow be better than a single billion-row table.) – Rick James Feb 16 '17 at 22:10
  • I am using JPA and JDBC – AndroidDev Feb 23 '17 at 17:30
  • 2
    its 2019 and i have same issue for a lagacy product if have found solution please share – Pavan Kumar T S Apr 12 '19 at 13:40
  • I can think of a perfectly good example: moving a web app into the cloud, and rather than deploying the same war file anew for every customer, instead choose the schema based on the authentication details. That's the scenario that I'm looking at. – SeverityOne Jun 08 '19 at 21:03

2 Answers2

20

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.

buræquete
  • 14,226
  • 4
  • 44
  • 89
  • 2
    No, I am using signle db but different schemas and schema names are given through an api body, I need to query from multiple schemas and respond. – AndroidDev Feb 23 '17 at 17:31
  • can you tell me how can I kill and recreate the connection, it will be helpful. Thank you for your response. – AndroidDev Feb 23 '17 at 18:49
  • @LakshmiNaresh My suggestion would work with same database with different schemas, maybe it was a bit vague due to the missing url part. Did you try using it? – buræquete Feb 23 '17 at 22:15
  • @LakshmiNaresh kill & create connection would be really inefficient for your app, though it must be said that multiple connections to same db would cause an extra load on the target db, but at least that covers your requirement with efficiency. – buræquete Feb 23 '17 at 22:18
  • @LakshmiNaresh how many schemas are we talking about? Can't you prepare datasources for all possible schemas, and use the one that is requested from API call, doing multiple schema query with single datasource would be extremely slow (killing & reconnecting) – buræquete Feb 23 '17 at 22:37
  • If you are still persistent on single connection & dynamically changing it -> http://stackoverflow.com/questions/13507522/dynamically-change-spring-data-source . As you see it ain't pretty. – buræquete Feb 24 '17 at 02:56
  • Hi, the number of schemas are in the hands of the user, he can create any number and he can delete them too. – AndroidDev Feb 24 '17 at 06:38
  • @LakshmiNaresh that is a terrible structure... Being that dynamic with such databases is not easy, why not use nosql databases? MongoDB etc? You can check the link I shared for using same connection for different schemas, but that also need those schemas to be defined in your product, I don't think it would be possible to have it completely dynamic. – buræquete Feb 24 '17 at 06:40
  • My Problem still persists, I am still in search of a proper solution. – AndroidDev Feb 27 '17 at 14:09
  • There is no possible way to do it as you are suggesting, you have to define schemas beforehand at all cases, you should be using NoSQL dbs. – buræquete Feb 27 '17 at 14:11
  • @AndroidDev, I also have similar requirement to deal with dynamic created schemas under single data source. Did you find any solution of your problem ? – DeepInJava Jul 26 '18 at 19:45
  • can you expand your answer? it's not clear to me in what class should this methods be implemented and how to use it, i.e. in a RestController. – cirovladimir Aug 22 '18 at 17:23
  • @cirovladimir sorry I just saw your message, these ought to be in some sort of `@Configuration` class for Spring Boot, I hope you did find your answer! – buræquete Jul 19 '19 at 02:10
1

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.

LJR
  • 151
  • 10