CODEWITHSUNDEEP
javaspring-bootspring-data-jpadatasource
shridhar
shridhar

Reputation: 79

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

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.

Upvotes: 1

Views: 4807

Answers (5)

Dijalma Silva
Dijalma Silva

Reputation: 2088

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

Upvotes: 0

shridhar
shridhar

Reputation: 79

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();
}
  }

Upvotes: 0

Tayyab Razaq
Tayyab Razaq

Reputation: 378

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

Upvotes: 2

Daniel Jabłoński
Daniel Jabłoński

Reputation: 25

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

Upvotes: 0

Naya
Naya

Reputation: 870

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

Upvotes: 0

Related Questions