Create javax.sql.DataSource and specify used schema

Question

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.

jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema — Thirumal
– Thirumal, Commented Jul 22, 2020 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
– MatthiasDec, Commented Jul 22, 2020 at 16:32
For multi-tenancy stackoverflow.com/questions/42179442/… — Thirumal
– Thirumal, Commented Jul 22, 2020 at 16:35
Does this answer your question? Spring Boot Configure and Use Two DataSources — Thirumal
– Thirumal, Commented Jul 22, 2020 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
– MatthiasDec, Commented Jul 22, 2020 at 16:46

MatthiasDec · Accepted Answer · 2020-07-23 10:07:32Z

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 !

edited Jul 23, 2020 at 10:07

answered Jul 23, 2020 at 8:48

MatthiasDec

1852 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shoejep Over a year ago

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

MatthiasDec Over a year ago

Thanks for your comment, i'll edit my response as well

Collectives™ on Stack Overflow

Create javax.sql.DataSource and specify used schema

EDIT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related