This is the 11th day of my participation in the August More Text Challenge. For details, see:August is more challenging

demand

Dynamic data sources are used in many specific application scenarios, such as multi-tenant scenarios. During system login, users need to switch to the database corresponding to the user. For example, if service A needs to access database A and service B needs to access database B, dynamic data source solution can be used. As a qualified programmer must be the first time to go to baidu, but since I wrote this blog so sure is not very good integration to the project, I wrote an article in the online said spring-based AbstractRoutingDataSource can be achieved, but I tried not line, Because my own project is not using the jdbcTemplate or I may use the wrong position, anyway, but it does give me inspiration. First of all, I must know how to get db Connect, look at the source code, have a good idea

The source code to debug

Mybatis plus is integrated with MyBatis. SqlSessionFactory is an interface to myBatis plus. The only thing that implements this interface in the project is DefaultSqlSessionFactory and if you put a breakpoint in the project you know it’s in org.apache.ibatis.session.defaults.DefaultSqlSessionFactory#openSessionFromDataSource

private SqlSession openSessionFromDataSource(ExecutorType execType, TransactionIsolationLevel level, boolean autoCommit) {
    Transaction tx = null;
    try {
      final Environment environment = configuration.getEnvironment();
      final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment);
      / / the key lies in the environment. GetDataSource ()
      tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit);
      final Executor executor = configuration.newExecutor(tx, execType);
      return new DefaultSqlSession(configuration, executor, autoCommit);
    } catch (Exception e) {
      closeTransaction(tx); // may have fetched a connection so lets call close()
      throw ExceptionFactory.wrapException("Error opening session. Cause: " + e, e);
    } finally{ ErrorContext.instance().reset(); }}Copy the code

The dataSource is here in the org. Springframework. Boot. Autoconfigure. JDBC. DataSourceConfiguration injection (don’t ask why we know it, Because the xiaobian project uses spring’s default connection pool, it is injected through the following code

@ConditionalOnClass(HikariDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
	static class Hikari {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")
		public HikariDataSource dataSource(DataSourceProperties properties) {
			HikariDataSource dataSource = createDataSource(properties,
					HikariDataSource.class);
			if (StringUtils.hasText(properties.getName())) {
				dataSource.setPoolName(properties.getName());
			}
			returndataSource; }}Copy the code

Train of thought

Source analysis found that the data source is through the environment. GetDataSource () way to obtain, and at that time I thought that we replace the environment, later found no, this class involves the code too much, can’t be rewritten And then thought that this is an interface

public interface DataSource  extends CommonDataSource.Wrapper {

  Connection getConnection(a) throws SQLException;

  Connection getConnection(String username, String password)
    throws SQLException;
}
Copy the code

Means that no matter how, must carry on the database operations must call the method that implements this interface, so we can replace the org. Apache. Ibatis. Mapping. The Environment of the dataSource can achieve our purpose, the above analysis of this class is injected, This is convenient, and there are two options:

  • Customize a dataSource, declare a property, select the specified dataSource using the tenant’s key, and then call the dataSourcegetConnectionmethods
	 private static ConcurrentMap<String, DataSource> dataSourceConcurrentMap = new ConcurrentHashMap<>();
	 
	 @Override
  	 public Connection getConnection(a) throws SQLException {
   	String dbKey = threadLocal.get();
       DataSource dataSource = dataSourceConcurrentMap.get(dbKey);
       return getDataSource().getConnection();
   }
 
Copy the code
  • This is also a rewrite of a dataSource, but a more thorough rewrite is equivalent to rewriting a database connection pool. This is also a similar operation, based on tenant information to create specifiedconnect, and maintain their own database connection lifecycle

Although the latter is more elegant and exciting to implement, I prefer the former, both in terms of difficulty and speed (I don’t have the ability to implement the latter).

Code implementation

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.core.NamedThreadLocal;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;


@Slf4j
public class DyDataSource extends HikariDataSource {
	// Global data source type
    Class<? extends DataSource> type;

	// Tenant information of the current thread
    private static ThreadLocal<String> threadLocal = new NamedThreadLocal<>("TARGET_DATA_SOURCE");

 	// Data source container
    private static ConcurrentMap<String, DataSource> dataSourceConcurrentMap = new ConcurrentHashMap<>();

    public DyDataSource(Class<? extends DataSource> type) {
        super(a);this.type = type;
        log.info("DyDataSource init ...........");
    }

    @Override
    public Connection getConnection(a) throws SQLException {

        return getDataSource().getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return getDataSource().getConnection(username, password);
    }

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

	// Obtain the data source based on the tenant information
    public DataSource getDataSource(a){

        String dbKey = threadLocal.get();
        if (StringUtils.isEmpty(dbKey)) {
            throw new RuntimeException("DbKey not specified");
        }

       DataSource dataSource = dataSourceConcurrentMap.get(dbKey);
        if (dataSource == null) {
            / / initialization
            synchronized (dbKey.intern()) {
                if (dataSource == null) { dataSource = initDataSource(dbKey); }}}if (dataSource == null) {
            throw new RuntimeException("dataSource is null");
        }

        return dataSource;

    }
	// Initialize according to the tenant information
    private DataSource initDataSource(String dbKey){

        DataSourceProperties dataSourceProperties = new DataSourceProperties();
        dataSourceProperties.setDriverClassName("com.mysql.jdbc.Driver");
        if (dbKey.equals("inner")) {
            dataSourceProperties.setUrl("jdbc:mysql://inner.com:3307/crawler?zeroDateTimeBehavior=convertToNull&characterEncoding=utf-8&useUnicode=true&useSSL=f alse");
            dataSourceProperties.setUsername("root");
            dataSourceProperties.setPassword("123456");
        } else if (dbKey.equals("local")) {
            dataSourceProperties.setUrl("jdbc:mysql://inner.com:3307/more_db?zeroDateTimeBehavior=convertToNull&characterEncoding=utf-8&useUnicode=true&useSSL=f alse");
            dataSourceProperties.setUsername("root");
            dataSourceProperties.setPassword("123456");
        }else {
            return null;
        }

        // Copy from the source code, probably binding data
        returndataSourceProperties.initializeDataSourceBuilder().type(type).build(); }}Copy the code

Inject into the Spring container

@Configuration
public class DbConfig {
    @Bean
    public DyDataSource dataSource(a) {
        return newDyDataSource(HikariDataSource.class); }}Copy the code

To invoke the DAO layer, you need to manually set the tenant information


@RestController
@RequestMapping("/test")
public class TestController {
    @Autowired
    TableTestServiceImpl testService;

    @GetMapping("getById")
    public TableTest getById(a){
        DyDataSource.setDbKey("uuu");
        return testService.getById(1); }}Copy the code

conclusion

The code is implemented but there are some pitfalls

  • The current dataSource is only created but not recycled, so we need to write a life cycle maintenance, otherwise there are more and more dataSource, but may need less than 10%, because there are more dataSource, the database connection is always alive, In extreme cases, a large number of TCP connections are consumed
  • [bug Mc-10808] – “dataSource” : DCL = “dataSource”] – “dataSource” : DCL = “dataSource”

For the first question, we may use redis to do LRU expiration elimination strategy, but we have not found a solution for the second one. If you have ideas, please comment and let me learn. Thank you!