0

I'm currently working on implementing schema-based multi-tenancy for my Spring Boot / JdbcTemplate API application. I figured out that for it to work, in the DAO layer, I need to dynamically change the schema of the DataSource used by JDBCTemplate during the runtime, in other word create a new one, but I can't find any information on how to set properly the schema for the DataSource I'm creating.

EDIT

Here are some details that might be important : the schema is defined in the url of the API endpoints I created, as a mandatory variable: if the user calls the URL localhost:9090/schema/MyNewSchema/Test, the schema variable is MyNewSchema and I have to create a DataSource with the proper pointed schema MyNewSchema.

MatthiasDec
  • 145
  • 1
  • 11
  • jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema – Thirumal Jul 22 '20 at 16:26
  • @Thirumal thanks for you answer, but I try to configure dynamically the schema used by the DataSource, so adding this to the project properties works fine if the schema is static I think – MatthiasDec Jul 22 '20 at 16:32
  • For `multi-tenancy` https://stackoverflow.com/questions/42179442/spring-boot-how-to-use-multiple-schemas-and-dynamically-choose-which-one-to-use/42360877 – Thirumal Jul 22 '20 at 16:35
  • Does this answer your question? [Spring Boot Configure and Use Two DataSources](https://stackoverflow.com/questions/30337582/spring-boot-configure-and-use-two-datasources) – Thirumal Jul 22 '20 at 16:37
  • not really, sorry i was not clear in my post, i edited it: the schema is defined in the url with a variable: if the user calls the api with the variable schema="MyNewSchema", I should create a DataSource with MyNewSchema for targeted schema. Considering that the schema is defined by the user, setting the dataSource possible properties is not an option – MatthiasDec Jul 22 '20 at 16:46

1 Answers1

0

Found a possible answer if someone faces the same issue : https://springboot-vuejs-reactjs.blogspot.com/2019/08/springboot-multi-tenancy-with.html

I decided to use an AbstractDataSource class, as mentioned in the article, and to override the public DataSource dataSource() bean in the @SpringBootApplication class, with an injection of the AbstractDataSource class created earlier. This allows me to manipulate the DataSource used by JDBCTemplate dynamically during the application runtime.

Furthermore, in my AbstractDataSource class, the DataSource objects I manipulate are HikariDataSource, allowing me to define the schema I want the DataSource to point with a hikariDs.setConnectionInitSql("ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA") statement (cf Configure OracleDataSource programmatically in Spring Boot with a default schema). In my case, I'm using Postgresql, so the SQL statement is SET SEARCH_PATH TO <schema-name>

It works pretty well !

MatthiasDec
  • 145
  • 1
  • 11
  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/26758079) – Shoejep Jul 23 '20 at 09:12
  • Thanks for your comment, i'll edit my response as well – MatthiasDec Jul 23 '20 at 09:17