Need to change the database Name of datasource url dynamically(Multi tenant database )

Question

I'm using Spring boot and Spring-data-Jpa. I am setting my data source URL, username, and password in the application.properties file. It works perfectly for one database connection,Now I am facing issue with my Database project structure that is based on the particular user his own database need to connect and get the result to a particular user database and I can achieve this using abstract data source, DataSourceBuilder at configuration level(it is one time can I able to change data source dynamically) but I need change in data source each time controller hits.

here is some code for application.properties and I have injected my datasource using autowire.

abstract datasource I have used and it is limited to the static client, But in my structure Clients Database keep on increasing so it's not useful for me

spring.datasource.url=jdbc:sqlserver://test-datbase:1433;dbName1 spring.datasource.username=userName spring.datasource.password=Password spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

Need code or method I can change my database connection on every hit to the controller

Note: I just need to change my database, My dialect and everything else will be the same.

Can you please rephrase and edit your question? Use some . and , instead of one large sentence. — M. Deinum, Apr 16 '19 at 11:32
You can use url filtering to make different connection at runtime, according to the url from which the request is coming you can check it and then connect to the respective db, you can also make the multiple dataSourceConnection objects of different db when the application starts and store in a HashMap and create a bean, Then you can Autowired this object through out your project to get the different db connection. — Bishal Jaiswal, Apr 16 '19 at 12:39

score 2 · Answer 1 · answered Apr 17 '19 at 10:28

2

Yes, we can do it by using placeholder. Set -DdbName1=YOUR_DB_NAME in environment variables. For example:

spring.datasource.url=jdbc:sqlserver://test-datbase:1433;${dbName1}
spring.datasource.username=userName
spring.datasource.password=Password
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

answered Apr 17 '19 at 10:28

Tayyab Razaq

348
2
11

thanks for reply. Can i switch the database on each time when user hit to controller? i think what you gave is one time switch of database. – shridhar Apr 17 '19 at 11:03
That's not possible. Application context initialized on server startup. Building new context on each hit to controller will slow down the performance. I didn't do it but with some changes, we can do but it's not recommended approach. – Tayyab Razaq Apr 18 '19 at 06:13
but how other microservices are able to connect to multi tenant? – shridhar Apr 23 '19 at 06:34

score 0 · Answer 2 · answered Apr 16 '19 at 12:55

0

Here is how I'd resolve such problem: You can create 2 separate data sources. Create qualifiers for them and both inject to your controller. Then in endpoint write logic which would select one of the sources to save info.

Here is how to add extra data source to you project:

https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7

answered Apr 16 '19 at 12:55

Naya

850
6
19

thank for answer, It is not useful for my problem, In your case i need to specify the datasource at initial stage i.e need to specify what are my list of datasource but presently my structure is based on a User, I will get a database connection details from one database from there i connect to other database. – shridhar Apr 16 '19 at 13:40

Daniel Jabłoński · Answer 3 · 2019-04-17T10:23:26.740

0

I think that it is a good idea to use Wildfly in this situation. At Wildfly, you can change the connected database using settings.

My solution: enter link description here

and please wirte your own PersistenceConfiguration class when you choose database enter link description here

if you would like to choose the base dynamically using methods in the java code

edited Apr 17 '19 at 10:23

answered Apr 17 '19 at 10:16

Daniel Jabłoński

25
9

i don't think your solution works i need datasource need to create at runtime and call database using Spring data Jpa Persistence EntityManager object, your solution can also be done through abstract datasource. – shridhar Apr 17 '19 at 11:34
In my app I set DataSource in DEVPersistenceContext.class (https://github.com/TheBestSoftInTheWorld/SpringBoot_2_different_connections_to_the_database/blob/master/src/main/java/com/example/springbootapp/configuration/persistence/context/DEVPersistenceContext.java). If you would like to change it dynamically please do it in DEVPersistenceContext.java class – Daniel Jabłoński Apr 17 '19 at 11:55
in persistence file write url1=app.datasource.url=jdbc:postgresql://localhost:5432/base1 url2=app.datasource.url=jdbc:postgresql://localhost:5432/base2 and choose database in DEVPersistenceContext.class (https://github.com/TheBestSoftInTheWorld/SpringBoot_2_different_connections_to_the_database/blob/master/src/main/java/com/example/springbootapp/configuration/persistence/context/DEVPersistenceContext.java)return DataSourceBuilder .url(env.getProperty("url1")) – Daniel Jabłoński Apr 17 '19 at 12:00

score 0 · Answer 4 · answered Apr 26 '19 at 09:48

with the help of below link i can able to set my multiple datasource when server get start

https://fizzylogic.nl/2016/01/24/make-your-spring-boot-application-multi-tenant-aware-in-2-steps/

but i want to remove configuration annotation like below and set my tenant using method as below,but through this i am not able to connect to database.

public class MultitenantConfiguration {
  @Bean
@ConfigurationProperties(
        prefix = "spring.datasource"
)
public DataSource dataSource(ArrayList<String> names) {


    Map<Object,Object> resolvedDataSources = new HashMap<>();

    for(String  dbName: names) {

        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(this.getClass().getClassLoader());
        dataSourceBuilder.driverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
                .url("jdbc:sqlserver://abc.server;databaseName="+dbName+"")
                .username("userName")
                .password("Password");


        resolvedDataSources.put(dbName, dataSourceBuilder.build());
    }



    MultitenantDataSource dataSource = new MultitenantDataSource();
    dataSource.setDefaultTargetDataSource(defaultDataSource());
    dataSource.setTargetDataSources(resolvedDataSources);
    dataSource.afterPropertiesSet();


    return dataSource;
}

/**
 * Creates the default data source for the application
 * @return
 */
private DataSource defaultDataSource() {
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(this.getClass().getClassLoader())
            .driverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
            .url("jdbc:abc.server;databaseName=test")
            .username("UserName")
            .password("Password");

    return dataSourceBuilder.build();
}
  }

score 0 · Answer 5 · answered May 12 '19 at 06:28

I've done a project that I can create multiple dataSources with your specific changeSets, so if you need to add another dataSource, it would just change your application.yml, no longer needing to change the code. But if not use, just remove the liquibase that works too!

On each hit for your controller, you need to get an X-TenantId header, which will change your ThreadLocal, which in turn changes the datasource according with tenant

Code complete: https://github.com/dijalmasilva/spring-boot-multitenancy-datasource-liquibase

application.yml

spring:
  dataSources:
    - tenantId: db1
      url: jdbc:postgresql://localhost:5432/db1
      username: postgres
      password: 123456
      driver-class-name: org.postgresql.Driver
      liquibase:
        enabled: true
        default-schema: public
        change-log: classpath:db/master/changelog/db.changelog-master.yaml
    - tenantId: db2
      url: jdbc:postgresql://localhost:5432/db2
      username: postgres
      password: 123456
      driver-class-name: org.postgresql.Driver
    - tenantId: db3
      url: jdbc:postgresql://localhost:5432/db3
      username: postgres
      password: 123456
      driver-class-name: org.postgresql.Driver

TenantContext

public class TenantContext {

    private static ThreadLocal<String> currentTenant = new ThreadLocal<>();

    static String getCurrentTenant() {
        return currentTenant.get();
    }

    static void setCurrentTenant(String tenant) {
        currentTenant.set(tenant);
    }

    static void clear() {
        currentTenant.remove();
    }
}

Filter to Controllers

public class TenantFilter extends GenericFilterBean {

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {

        final String X_TENANT_ID = "X-TenantID";

        final HttpServletRequest httpServletRequest = (HttpServletRequest) servletRequest;
        final String tenantId = httpServletRequest.getHeader(X_TENANT_ID);

        if (tenantId == null) {
            final HttpServletResponse response = (HttpServletResponse) servletResponse;
            response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
            response.setContentType(MediaType.APPLICATION_JSON_VALUE);
            response.getWriter().write("{\"error\": \"No tenant header supplied\"}");
            response.getWriter().flush();
            TenantContext.clear();
            return;
        }

        TenantContext.setCurrentTenant(tenantId);
        filterChain.doFilter(servletRequest, servletResponse);
    }
}

Configuration class if use liquibase

@Configuration
@ConditionalOnProperty(prefix = "spring.liquibase", name = "enabled", matchIfMissing = true)
@EnableConfigurationProperties(LiquibaseProperties.class)
@AllArgsConstructor
public class LiquibaseConfiguration {

    private LiquibaseProperties properties;
    private DataSourceProperties dataSourceProperties;

    @Bean
    @DependsOn("tenantRoutingDataSource")
    public MultiTenantDataSourceSpringLiquibase liquibaseMultiTenancy(Map<Object, Object> dataSources,
                                                                      @Qualifier("taskExecutor") TaskExecutor taskExecutor) {
        // to run changeSets of the liquibase asynchronous
        MultiTenantDataSourceSpringLiquibase liquibase = new MultiTenantDataSourceSpringLiquibase(taskExecutor);
        dataSources.forEach((tenant, dataSource) -> liquibase.addDataSource((String) tenant, (DataSource) dataSource));
        dataSourceProperties.getDataSources().forEach(dbProperty -> {
            if (dbProperty.getLiquibase() != null) {
                liquibase.addLiquibaseProperties(dbProperty.getTenantId(), dbProperty.getLiquibase());
            }
        });

        liquibase.setContexts(properties.getContexts());
        liquibase.setChangeLog(properties.getChangeLog());
        liquibase.setDefaultSchema(properties.getDefaultSchema());
        liquibase.setDropFirst(properties.isDropFirst());
        liquibase.setShouldRun(properties.isEnabled());
        return liquibase;
    }

}

Code complete: https://github.com/dijalmasilva/spring-boot-multitenancy-datasource-liquibase

Need to change the database Name of datasource url dynamically(Multi tenant database )

5 Answers5