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.

Answer 1

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 !