Original: Taste of Little Sister (wechat official ID: XjjDog), welcome to share, please reserve the source.

In many cases, we do need to access multiple data sources within a service. It makes the whole design less elegant, but the real world needs it. Say your business serves two large clients, but you want them to share a single set of code.

In other words, your code doesn’t initially consider multi-tenancy, but later has this painful requirement. But it’s not explosive tenant growth.

In addition to introducing some depots table components, Spring provides AbstractRoutingDataSource way itself, make it possible for most of the data source management. In fact, there are many restrictions on the use of the sub-table component, you have to comb through the mountain of shit first, then have to endure the harsh requirements of the middleware for your SQL; Instead is some wild way, can let the code change momentum as far as possible to reduce.

Action is better than action. Let’s take a look at the implementation.

1. Fundamentals

Multiple data sources can dynamically switch core is spring provides AbstractRoutingDataSource bottom class for data source routing. AbstractRoutingDataSource implements the DataSource interface, so we can be directly injected into the DataSource attribute.

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

For example, controllers distribute business logic by taking the values passed by the front-end business. It can manually set the database identity of the current request and route it to the correct library table.

@controller public class ARDTestController {@getMapping ("test") public void chifeng(){@controller public class ARDTestController {@getMapping ("test") public void chifeng(){ We can put it in the ThreadLocal DataSourceContextHolder. SetDbKey (" db - a "); }}Copy the code

So how does an SQL statement know which data source it needs to switch to when it executes? Do I need to pass the db-a property all the way through?

In Java, you can bind this passthrough property using ThreadLocal. Implementations like Spring’s nested transactions also run on ThreadLocal. So datasourcecontextholder. is essentially a class that operates on 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(); }}Copy the code

2. Configure the code

First, we customize the format of the configuration file. The following code configures the db-A and db-B databases.

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;Copy the code

We then parse it to 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; }}Copy the code

Next, you need to configure the default data source for the entire application. As you can see, its main logic is to fetch this pre-set value from ThreadLocal at runtime.

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

Finally, set the default DataSource for the entire project. Note that after generating DynamicDataSource, we also need to provide the values of targetDataSource and defaultTargetDataSource properties to run properly.

@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 need to set the dataSource. SetDefaultTargetDataSource (targetDataSource. The values (). The 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; }}Copy the code

Problem 3.

With this simple code, you can implement Spring’s simple multi-source management. But obviously, it still has many problems.

  1. You need to select a product design mode to switch services.

  2. The front-end can store attributes by placing them in localStroage, and variables can be passed each time using interceptors.

  3. For each request, the backend needs to bring the target DB, which can be placed in a ThreadLocal. ThreadLocal, however, has thread passthrough issues. If a task has child threads enabled, variables cannot be shared.

  4. Since tables are dynamically selected, JPA auto-create and update modes will not be available. It is not convenient for testing and unit testing, and you also need to specify the library to point to each time you test the interface.

  5. Because it is to modify the mode of the data source, each time to add a library, you need to restart online. Data source destruction is a problem if you want to be dynamic.

End

There are many default restriction policies for a microservice, for example, services between different domains cannot share a database. These basic principles, make micro service clean and refreshing, are some basic principles.

Similarly, if we had given each table the tenant’s field ID at the beginning of the design, it would have been much easier to write code. But there are not so many ifs in the world.

Why do principles exist? To be broken, of course.

Programming is just a tool, anyway the code in their own hands, how to play, depending on the need, but also depending on the mood. All roads lead to Rome.

Xjjdog is a public account that doesn’t allow programmers to get sidetracked. Focus on infrastructure and Linux. Ten years architecture, ten billion daily flow, and you discuss the world of high concurrency, give you a different taste. My personal wechat xjjdog0, welcome to add friends, further communication.

Recommended reading:

2. What does the album taste like

3. Bluetooth is like a dream 4. 5. Lost architect, leaving only a script 6. Bugs written by the architect are unusual

Taste of little sister

Not envy mandarin duck not envy fairy, a line of code half a day

319 original content