In many cases, we do need to access multiple data sources in one service. Although it makes the overall design less elegant, the real world does need it. For example, your business serves two larger customers, but you want them to share a set of code.

In other words, your code didn't consider the function of multi tenancy at the beginning, but there is such a painful demand behind it. But fortunately, it is not explosive tenant growth.

In addition to introducing some sub database and sub table components, Spring itself provides the way of AbstractRoutingDataSource, which makes the management of most data sources possible. In fact, there are many restrictions on the use of database and table components. You have to sort out this shit mountain first, and then endure the harsh requirements of middleware on your SQL; Instead, there are some wild ways to minimize the amount of code changes.

Action is better than heart. Next, let's take a look at its specific implementation.

1. Basic principles

The core of dynamic switching of multiple data sources is that spring provides the AbstractRoutingDataSource class at the bottom for data source routing. AbstractRoutingDataSource implements the DataSource interface, so we can inject it directly into the properties of DataSource.

We mainly inherit this class and implement the method determineCurrentLookupKey(), which only needs to return the name of a database.

For example, the Controller distributes business logic by getting the value passed by the front-end business. It can manually set the database ID of the current request, and then route it to the correct database table.

@Controller
public class ARDTestController {
    @GetMapping("test")
    public void chifeng(){
        //db-a should be an attribute passed down from the upper layer. We can put it in ThreadLocal
        DataSourceContextHolder.setDbKey("db-a");
    }
}

So when the sql statement is executed, how does it know which data source it needs to switch to? Do you need to pass db-a through all the time?

In Java, ThreadLocal can be used to bind this transparent property. The principles of Spring's nested transactions and other implementations are also based on ThreadLocal. So, DataSourceContextHolder It is essentially a class that operates ThreadLocal.

public class DataSourceContextHolder {
    private static InheritableThreadLocal<String> dbKey = new InheritableThreadLocal<>();

    public static void setDbKey(String key){
        dbKey.set(key);
    }

    public static String getDbKey(){
        return dbKey.get();
    }
}

2. Configuration code

First, we customized the format of the configuration file. As shown in the following code, db-a and db-b databases are configured.

multi:
  dbs:
    db-a:
      driver-class-name: org.h2.Driver
      url: jdbc:h2:mem:dba;MODE=MYSQL;DATABASE_TO_UPPER=false;
    db-b:
      driver-class-name: org.h2.Driver
      url: jdbc:h2:mem:dbb;MODE=MYSQL;DATABASE_TO_UPPER=false;

Then, we parse it into properties.

@ConfigurationProperties(prefix = "multi")
@Configuration
public class DbsProperties {
    private Map<String, Map<String, String>> dbs = new HashMap<>();

    public Map<String, Map<String, String>> getDbs() {
        return dbs;
    }

    public void setDbs(Map<String, Map<String, String>> dbs) {
        this.dbs = dbs;
    }
}

Next, you need to configure the default data source for the entire application. As you can see, its main logic is to get the value set in advance from ThreadLocal during operation.

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDbKey();
    }
}

The last step is to set the default DataSource in the whole project. Note that after generating the DynamicDataSource, we also need to provide the values of the targetDataSource and defaultTargetDataSource properties before it can run normally.

@Configuration
public class DynamicDataSourceConfiguration {
    @Autowired
    DbsProperties properties;

    @Bean
    public DataSource dataSource(){
        DynamicDataSource dataSource = new DynamicDataSource();
        final Map<Object,Object> targetDataSource  = getTargetDataSource();
        dataSource.setTargetDataSources(targetDataSource);
        //TODO default database needs to be set
        dataSource.setDefaultTargetDataSource(targetDataSource.values().iterator().next());
        return dataSource;
    }

    private Map<Object,Object> getTargetDataSource(){
        Map<Object,Object> dataSources = new HashMap<>();
        this.properties.getDbs().entrySet().stream()
                .forEach(e->{
                    DriverManagerDataSource dmd = new DriverManagerDataSource();
                    dmd.setUrl(e.getValue().get("url"));
                    dmd.setDriverClassName(e.getValue().get("driver-class-name"));
                    dataSources.put(e.getKey(),dmd);
                });
        return  dataSources;
    }
}

3. Problems

Through the above simple code, you can realize the simple multi data source management of Spring. But obviously, it still has many problems.

1. Product design mode selection and business switching are required.
2. The front end can save the attributes by placing them in the localStroage, and the variables can be passed every time by using the interceptor.
3. Each time the backend requests, it needs to bring the target db, which can be placed in ThreadLocal. However, ThreadLocal has the problem of thread transparent transmission. If the sub thread is enabled in the task, the variables cannot be shared.
4. Because the table is dynamically selected, JPA automatic creation and update modes will not be available. It is inconvenient for testing and unit testing. When testing the interface, you also need to specify the library pointed to each time.
5. Because it is the mode of modifying the data source, you need to restart and go online every time you add a library. If you want to be dynamic, data source destruction is a problem.

End

For a micro service, there are many default restriction policies. For example, services in different domains cannot share a database. These basic principles are some basic principles that make the micro service clean and refreshing.

Similarly, if we add the field ID of the tenant to each table at the beginning of the design, the code writing will be much smoother. But there are not so many ifs in the world.

Why do principles exist? Of course, it's for people to break.

Programming is just a tool. Anyway, the code is in your own hands. How to play depends on your needs and mood. All roads lead to Rome, winding paths lead to secluded places, and the scenery is infinitely good.