Generate multiple databases at the same time based on zero code from multiple data sources

Multiple data sources

review

Through the introduction of the previous article, it has supported mainstream databases, including MySql, PostgreSql, Oracle, Microsoft SQL Server, etc., and realized CRUD addition, deletion, modification, and query RESTful API by configuring zero code. Using the abstract factory design pattern, you can seamlessly switch between different types of databases.
But if you need to support different types of databases at the same time, how to manage it through configuration? At this time, it is necessary to introduce the function of multiple data sources.

Introduction

Using spring boot's multi-data source function, it can support different types of databases mysql, oracle, postsql, sql server, etc., as well as different schemas for the same type of database. Zero code simultaneously generates RESTful apis for adding, deleting, modifying and checking different types of databases, and supports secondary development of cross-database data access in the same interface.

UI interface

Configure one data source and multiple slave data sources, each of which can be configured and accessed independently of each other.

core principle

Configure database connection string

Configure application.properties, spring.datasource is the default master data source, and spring.datasource.hikari.data-sources[] array is the slave data source

 #primary
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/crudapi?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=root

#postgresql
spring.datasource.hikari.data-sources[0].postgresql.driverClassName=org.postgresql.Driver
spring.datasource.hikari.data-sources[0].postgresql.url=jdbc:postgresql://localhost:5432/crudapi
spring.datasource.hikari.data-sources[0].postgresql.username=postgres
spring.datasource.hikari.data-sources[0].postgresql.password=postgres

#sqlserver
spring.datasource.hikari.data-sources[1].sqlserver.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.hikari.data-sources[1].sqlserver.url=jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=crudapi
spring.datasource.hikari.data-sources[1].sqlserver.username=sa
spring.datasource.hikari.data-sources[1].sqlserver.password=Mssql1433

#oracle
spring.datasource.hikari.data-sources[2].oracle.url=jdbc:oracle:thin:@//localhost:1521/XEPDB1
spring.datasource.hikari.data-sources[2].oracle.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.hikari.data-sources[2].oracle.username=crudapi
spring.datasource.hikari.data-sources[2].oracle.password=crudapi

#mysql
spring.datasource.hikari.data-sources[3].mysql.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.data-sources[3].mysql.url=jdbc:mysql://localhost:3306/crudapi2?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.hikari.data-sources[3].mysql.username=root
spring.datasource.hikari.data-sources[3].mysql.password=root

Dynamic Data Source - DynamicDataSource

Spring boot provides the abstract class AbstractRoutingDataSource, which overrides the interface determineCurrentLookupKey, and can set the data source to be used before executing the query, thereby realizing dynamic data source switching.

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

Data Source Context - DataSourceContextHolder

The default main data source name is datasource, and the slave data source name is stored in the ThreadLocal variable CONTEXT_HOLDER. ThreadLocal is called a thread variable, which means that the variable filled in ThreadLocal belongs to the current thread, and the variable is isolated from other threads, that is, the A variable is a variable unique to the current thread.

In RestController, set the data source key that needs to be accessed in advance in advance, that is, call the setDataSource method, call the getDataSource method to obtain the data source key when accessing the data, and finally pass it to the DynamicDataSource.

 public class DataSourceContextHolder {
    //默认数据源primary=dataSource
    private static final String DEFAULT_DATASOURCE = "dataSource";

    //保存线程连接的数据源
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    private static final ThreadLocal<String> HEADER_HOLDER = new ThreadLocal<>();

    public static String getDataSource() {
      String dataSoure = CONTEXT_HOLDER.get();
        if (dataSoure != null) {
          return dataSoure;
        } else {
          return DEFAULT_DATASOURCE;
        }
    }

    public static void setDataSource(String key) {
        if ("primary".equals(key)) {
          key = DEFAULT_DATASOURCE;
        }
        CONTEXT_HOLDER.set(key);
    }

    public static void cleanDataSource() {
        CONTEXT_HOLDER.remove();
    }

    public static void setHeaderDataSource(String key) {
      HEADER_HOLDER.set(key);
    }

    public static String getHeaderDataSource() {
      String dataSoure = HEADER_HOLDER.get();
        if (dataSoure != null) {
          return dataSoure;
        } else {
          return DEFAULT_DATASOURCE;
        }
    }
}

Dynamic database provider - DynamicDataSourceProvider

When the program starts, read the data source information in the configuration file application.properties, build the DataSource and set the slave data source through the interface setTargetDataSources. The key of the data source corresponds to the key in the DataSourceContextHolder one-to-one

 @Component
@EnableConfigurationProperties(DataSourceProperties.class)
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public class DynamicDataSourceProvider implements DataSourceProvider {
  @Autowired
  private DynamicDataSource dynamicDataSource;

  private List<Map<String, DataSourceProperties>> dataSources;

  private Map<Object,Object> targetDataSourcesMap;

  @Resource
  private DataSourceProperties dataSourceProperties;

  private DataSource buildDataSource(DataSourceProperties prop) {
        DataSourceBuilder<?> builder = DataSourceBuilder.create();
        builder.driverClassName(prop.getDriverClassName());
        builder.username(prop.getUsername());
        builder.password(prop.getPassword());
        builder.url(prop.getUrl());
        return builder.build();
    }

    @Override
    public List<DataSource> provide() {
      Map<Object,Object> targetDataSourcesMap = new HashMap<>();
      List<DataSource> res = new ArrayList<>();
      if (dataSources != null) {
            dataSources.forEach(map -> {
                Set<String> keys = map.keySet();
                keys.forEach(key -> {
                    DataSourceProperties properties = map.get(key);
                    DataSource dataSource = buildDataSource(properties);
                    targetDataSourcesMap.put(key, dataSource);

                });
            });

            //更新dynamicDataSource
            this.targetDataSourcesMap = targetDataSourcesMap;
            dynamicDataSource.setTargetDataSources(targetDataSourcesMap);
            dynamicDataSource.afterPropertiesSet();
      }

        return res;
    }

    @PostConstruct
    public void init() {
        provide();
    }

    public List<Map<String, DataSourceProperties>> getDataSources() {
        return dataSources;
    }

    public void setDataSources(List<Map<String, DataSourceProperties>> dataSources) {
        this.dataSources = dataSources;
    }

    public List<Map<String, String>> getDataSourceNames() {
      List<Map<String, String>> dataSourceNames = new ArrayList<Map<String, String>>();
      Map<String, String> dataSourceNameMap = new HashMap<String, String>();
      dataSourceNameMap.put("name", "primary");
      dataSourceNameMap.put("caption", "主数据源");
      dataSourceNameMap.put("database", parseDatabaseName(dataSourceProperties));
      dataSourceNames.add(dataSourceNameMap);

      if (dataSources != null) {
        dataSources.forEach(map -> {
          Set<Map.Entry<String, DataSourceProperties>> entrySet = map.entrySet();
              for (Map.Entry<String, DataSourceProperties> entry : entrySet) {
                Map<String, String> t = new HashMap<String, String>();
                t.put("name", entry.getKey());
                t.put("caption", entry.getKey());
                DataSourceProperties p = entry.getValue();
                t.put("database", parseDatabaseName(p));

                dataSourceNames.add(t);
              }
          });
      }

        return dataSourceNames;
    }

    public String getDatabaseName() {
      List<Map<String, String>> dataSourceNames = this.getDataSourceNames();
      String dataSource = DataSourceContextHolder.getDataSource();

      Optional<Map<String, String>> op = dataSourceNames.stream()
      .filter(t -> t.get("name").toString().equals(dataSource))
      .findFirst();
      if (op.isPresent()) {
        return op.get().get("database");
      } else {
        return dataSourceNames.stream()
        .filter(t -> t.get("name").toString().equals("primary"))
        .findFirst().get().get("database");
      }
    }


    private String parseDatabaseName(DataSourceProperties p) {
      String url = p.getUrl();
      String databaseName = "";
      if (url.toLowerCase().indexOf("databasename") >= 0) {
        String[] urlArr = p.getUrl().split(";");
        for (String u : urlArr) {
          if (u.toLowerCase().indexOf("databasename") >= 0) {
            String[] uArr = u.split("=");
            databaseName = uArr[uArr.length - 1];
          }
        }
      } else {
        String[] urlArr = p.getUrl().split("\\?")[0].split("/");
        databaseName = urlArr[urlArr.length - 1];
      }

      return databaseName;
    }

  public Map<Object,Object> getTargetDataSourcesMap() {
    return targetDataSourcesMap;
  }
}

Dynamic Data Source Configuration - DynamicDataSourceConfig

First cancel the system automatic database configuration, set exclude = { DataSourceAutoConfiguration.class }

 @SpringBootApplication(exclude = { DataSourceAutoConfiguration.class })
public class ServiceApplication {
    public static void main(String[] args) {
        SpringApplication.run(ServiceApplication.class, args);
    }
}

Then customize the Bean, define the main data source dataSource and dynamic data source dynamicDataSource respectively, and inject them into JdbcTemplate, NamedParameterJdbcTemplate, and DataSourceTransactionManager, and automatically identify the corresponding data source when accessing data.

 //数据源配置类
@Configuration
@EnableConfigurationProperties(DataSourceProperties.class)
public class DynamicDataSourceConfig {
    private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceConfig.class);

    @Resource
    private DataSourceProperties dataSourceProperties;

    @Bean(name = "dataSource")
    public DataSource getDataSource(){
        DataSourceBuilder<?> builder = DataSourceBuilder.create();
        builder.driverClassName(dataSourceProperties.getDriverClassName());
        builder.username(dataSourceProperties.getUsername());
        builder.password(dataSourceProperties.getPassword());
        builder.url(dataSourceProperties.getUrl());
        return builder.build();
    }

    @Primary //当相同类型的实现类存在时，选择该注解标记的类
    @Bean("dynamicDataSource")
    public DynamicDataSource dynamicDataSource(){
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        //默认数据源
        dynamicDataSource.setDefaultTargetDataSource(getDataSource());

        Map<Object,Object> targetDataSourcesMap = new HashMap<>();
        dynamicDataSource.setTargetDataSources(targetDataSourcesMap);
        return dynamicDataSource;
    }

    //事务管理器DataSourceTransactionManager构造参数需要DataSource
    //这里可以看到我们给的是dynamicDS这个bean
    @Bean
    public PlatformTransactionManager transactionManager(){
        return new DataSourceTransactionManager(dynamicDataSource());
    }

    //这里的JdbcTemplate构造参数同样需要一个DataSource,为了实现数据源切换查询，
    //这里使用的也是dynamicDS这个bean
    @Bean(name = "jdbcTemplate")
    public JdbcTemplate getJdbc(){
        return new JdbcTemplate(dynamicDataSource());
    }

    //这里的JdbcTemplate构造参数同样需要一个DataSource,为了实现数据源切换查询，
    //这里使用的也是dynamicDS这个bean
    @Bean(name = "namedParameterJdbcTemplate")
    public NamedParameterJdbcTemplate getNamedJdbc(){
        return new NamedParameterJdbcTemplate(dynamicDataSource());
    }
}

Request header filter - HeadFilter

Intercept all http requests, parse the data source that needs to be accessed from the header, and then set it to the thread variable HEADER_HOLDER.

 @WebFilter(filterName = "headFilter", urlPatterns = "/*")
public class HeadFilter extends OncePerRequestFilter {
    private static final Logger log = LoggerFactory.getLogger(HeadFilter.class);

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) throws ServletException, IOException {
      if (!"/api/auth/login".equals(request.getRequestURI())
        && !"/api/auth/jwt/login".equals(request.getRequestURI())
        && !"/api/auth/logout".equals(request.getRequestURI())
        && !"/api/metadata/dataSources".equals(request.getRequestURI())) {
        String dataSource = request.getParameter("dataSource");
          HeadRequestWrapper headRequestWrapper = new HeadRequestWrapper(request);
          if (StringUtils.isEmpty(dataSource)) {
            dataSource = headRequestWrapper.getHeader("dataSource");
                if (StringUtils.isEmpty(dataSource)) {
                  dataSource = "primary";
                  headRequestWrapper.addHead("dataSource", dataSource);
                }
            }

            DataSourceContextHolder.setHeaderDataSource(dataSource);

            // finish
            filterChain.doFilter(headRequestWrapper, response);
      } else {
        filterChain.doFilter(request, response);
      }
    }
}

practical application

The previous dynamic data source configuration preparation work has been completed, and finally we define the aspect DataSourceAspect

 @Aspect
public class DataSourceAspect {
  private static final Logger log = LoggerFactory.getLogger(DataSourceAspect.class);

  @Pointcut("within(cn.crudapi.api.controller..*)")
  public void applicationPackagePointcut() {
  }

  @Around("applicationPackagePointcut()")
  public Object dataSourceAround(ProceedingJoinPoint joinPoint) throws Throwable {
    String dataSource = DataSourceContextHolder.getHeaderDataSource();
    DataSourceContextHolder.setDataSource(dataSource);
    try {
      return joinPoint.proceed();
    } finally {
      DataSourceContextHolder.cleanDataSource();
    }
  }
}

Intercept in the controller corresponding to the API, obtain the current request header data source key, then execute joinPoint.proceed(), and finally restore the data source. Of course, you can switch the data source multiple times within the service, just call DataSourceContextHolder.setDataSource(). For example, data can be read from the mysql database and then saved to the oracle database.

Front-end integration

In the request header, set dataSource as the corresponding data source. For example, primary represents the main data source, and postgresql represents the secondary data source postgresql. The specific name can be consistent with the configuration of application.properties.

The first place to call configures the dataSource

 const table = {
  list: function(dataSource, tableName, page, rowsPerPage, search, query, filter) {
    return axiosInstance.get("/api/business/" + tableName,
      {
        params: {
          offset: (page - 1) * rowsPerPage,
          limit: rowsPerPage,
          search: search,
          ...query,
          filter: filter
        },
        dataSource: dataSource
      }
    );
  },
}

Then unified interception configuration in axios

 axiosInstance.interceptors.request.use(
  function(config) {
    if (config.dataSource) {
      console.log("config.dataSource = " + config.dataSource);
      config.headers["dataSource"] = config.dataSource;
    }

    return config;
  },
  function(error) {
    return Promise.reject(error);
  }
);

The effect is as follows

summary

This article mainly introduces the multi-data source function. In the same Java program, through the multi-data source function, we can get the basic crud functions of different databases, including API and UI, without a single line of code.

Introduction to crudapi

Crudapi is a combination of crud+api, which means adding, deleting, modifying and checking interfaces. It is a zero-code configurable product. Using crudapi can say goodbye to the boring addition, deletion, modification and checking of code, allowing you to focus more on business, save a lot of costs, and improve work efficiency.
The goal of crudapi is to make working with data easier and free for everyone!
Without programming, it can automatically generate crud, add, delete, modify and check RESTful API through configuration, and provide background UI to manage business data. Based on the mainstream open source framework, it has independent intellectual property rights and supports secondary development.

demo

Crudapi is a product-level zero-code platform. Unlike automatic code generators, crudapi does not need to generate business codes such as Controller, Service, Repository, and Entity. The program can be used when it is running. It is truly zero-code and can cover basic business-independent CRUD. RESTful APIs.

Official website address: https://crudapi.cn
Test address: https://demo.crudapi.cn/crudapi/login

With source code address

GitHub address

https://github.com/crudapi/crudapi-admin-web

Gitee address

https://gitee.com/crudapi/crudapi-admin-web

Due to network reasons, GitHub may be slow, you can change it to access Gitee, and the code will be updated synchronously.