当前位置：网站首页>Generate multiple databases at the same time based on multiple data sources and zero code, add, delete, modify and check restful API interfaces - mysql, PostgreSQL, Oracle, Microsoft SQL server multip

Generate multiple databases at the same time based on multiple data sources and zero code, add, delete, modify and check restful API interfaces - mysql, PostgreSQL, Oracle, Microsoft SQL server multip

2022-07-18 22:54:00 【InfoQ】

Multiple data sources

review

Through the introduction of the previous article , Currently, mainstream databases are supported , Include MySql,PostgreSql,Oracle,Microsoft SQL Server etc. , By configuring zero code CRUD Additions and deletions RESTful API. Adopt 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 through configuration ？ At this time, it is necessary to introduce the function of multiple data sources .

brief introduction

utilize spring boot Multi data source function , It can support different types of databases at the same time mysql,oracle,postsql,sql server etc. , And different databases of the same type schema. Zero code generates different types of databases at the same time, adding, deleting, changing and querying RESTful api, And support the secondary development of cross database data access in the same interface .

UI Interface

Configure a data source , Multiple slave data sources , Each data source is configured and accessed independently .

The core principle

Configure database connection string

To configure application.properties,spring.datasource It is the default master data source ,spring.datasource.hikari.data-sources[] The array is from the 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 sources ——DynamicDataSource

Spring boot Abstract classes are provided AbstractRoutingDataSource, Replication interface determineCurrentLookupKey, Before executing the query , Set the data source for , So as to realize dynamic switching of data sources .

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

data source Context——DataSourceContextHolder

The default master data source name is datasource, Save the name from the data source in ThreadLocal Variable CONTEXT_HOLDER Inside ,ThreadLocal It's called a thread variable , intend ThreadLocal The variables filled in belong to the current thread , This variable is isolated from other threads , That is, this variable is unique to the current thread .

stay RestController Set the data source you need to access in advance according to your needs key, That is to call setDataSource Method , Call when accessing data getDataSource Method to get the data source key, Finally passed on to DynamicDataSource.

public class DataSourceContextHolder {
 // Default data source primary=dataSource
 private static final String DEFAULT_DATASOURCE = &quot;dataSource&quot;;

 // Save the data source connected by the thread 
 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 (&quot;primary&quot;.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 configuration file application.properties Data source information in , structure DataSource And through the interface setTargetDataSources Set from data source . Data sources key and DataSourceContextHolder in key One-to-one correspondence

@Component
@EnableConfigurationProperties(DataSourceProperties.class)
@ConfigurationProperties(prefix = &quot;spring.datasource.hikari&quot;)
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);

 });
 });

 // to update 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(&quot;name&quot;, &quot;primary&quot;);
 dataSourceNameMap.put(&quot;caption&quot;, &quot; The main data source &quot;);
 dataSourceNameMap.put(&quot;database&quot;, 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(&quot;name&quot;, entry.getKey());
 t.put(&quot;caption&quot;, entry.getKey());
 DataSourceProperties p = entry.getValue();
 t.put(&quot;database&quot;, 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(&quot;name&quot;).toString().equals(dataSource))
 .findFirst();
 if (op.isPresent()) {
 return op.get().get(&quot;database&quot;);
 } else {
 return dataSourceNames.stream()
 .filter(t -> t.get(&quot;name&quot;).toString().equals(&quot;primary&quot;))
 .findFirst().get().get(&quot;database&quot;);
 }
 }


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

 return databaseName;
 }

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

Dynamic data source configuration ——DynamicDataSourceConfig

First, cancel the automatic database configuration of the system , Set up exclude = { DataSourceAutoConfiguration.class }

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

Then customize Bean, Define master data sources respectively dataSource And dynamic data sources dynamicDataSource, And injected into JdbcTemplate,NamedParameterJdbcTemplate, and DataSourceTransactionManager in , Automatically identify the corresponding data source when accessing data .

// Data source configuration class 
@Configuration
@EnableConfigurationProperties(DataSourceProperties.class)
public class DynamicDataSourceConfig {
 private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceConfig.class);

 @Resource
 private DataSourceProperties dataSourceProperties;

 @Bean(name = &quot;dataSource&quot;)
 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 // When implementation classes of the same type exist , Select the class marked by the annotation 
 @Bean(&quot;dynamicDataSource&quot;)
 public DynamicDataSource dynamicDataSource(){
 DynamicDataSource dynamicDataSource = new DynamicDataSource();
 // Default data source 
 dynamicDataSource.setDefaultTargetDataSource(getDataSource());

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

 // Transaction manager DataSourceTransactionManager Construction parameters need DataSource
 // Here we can see that what we give is dynamicDS This bean
 @Bean
 public PlatformTransactionManager transactionManager(){
 return new DataSourceTransactionManager(dynamicDataSource());
 }

 // there JdbcTemplate Construction parameters also need a DataSource, To realize data source switching query ,
 // It's also used here dynamicDS This bean
 @Bean(name = &quot;jdbcTemplate&quot;)
 public JdbcTemplate getJdbc(){
 return new JdbcTemplate(dynamicDataSource());
 }

 // there JdbcTemplate Construction parameters also need a DataSource, To realize data source switching query ,
 // It's also used here dynamicDS This bean
 @Bean(name = &quot;namedParameterJdbcTemplate&quot;)
 public NamedParameterJdbcTemplate getNamedJdbc(){
 return new NamedParameterJdbcTemplate(dynamicDataSource());
 }
}

Request header filter ——HeadFilter

Intercept all http request , from header It parses the data source that needs to be accessed at present , Then set it to the thread variable HEADER_HOLDER in .

@WebFilter(filterName = &quot;headFilter&quot;, urlPatterns = &quot;/*&quot;)
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 (!&quot;/api/auth/login&quot;.equals(request.getRequestURI())
 && !&quot;/api/auth/jwt/login&quot;.equals(request.getRequestURI())
 && !&quot;/api/auth/logout&quot;.equals(request.getRequestURI())
 && !&quot;/api/metadata/dataSources&quot;.equals(request.getRequestURI())) {
 String dataSource = request.getParameter(&quot;dataSource&quot;);
 HeadRequestWrapper headRequestWrapper = new HeadRequestWrapper(request);
 if (StringUtils.isEmpty(dataSource)) {
 dataSource = headRequestWrapper.getHeader(&quot;dataSource&quot;);
 if (StringUtils.isEmpty(dataSource)) {
 dataSource = &quot;primary&quot;;
 headRequestWrapper.addHead(&quot;dataSource&quot;, dataSource);
 }
 }

 DataSourceContextHolder.setHeaderDataSource(dataSource);

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

The practical application

The previous preparation for dynamic data source configuration has been completed , Finally, we define the section DataSourceAspect

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

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

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

stay API Corresponding controller To intercept , Get the current request header data source key, And then execute joinPoint.proceed(), Finally, restore the data source . Of course. service The data source can also be switched many times internally , Just call DataSourceContextHolder.setDataSource() that will do . For example, you can choose from mysql Database reads data , Then save to oracle In the database .

Front end Integration

Set in the request header dataSource For the corresponding data source , such as primary Represents the master data source ,postgresql Indicates from the data source postgresql, Specific names and application.properties Configuration is consistent .

First call the local configuration dataSource

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

And then in axios There is a unified interception configuration

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

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

The effect is as follows

Summary

This article mainly introduces the function of multiple data sources , In the same Java In the program , Through the multi data source function , No need for a line of code , We can get the basic information of different databases crud function , Include API and UI.

crudapi brief introduction

crudapi yes crud+api Combine , Indicates the interface for adding, deleting, modifying and querying , It is a zero code configurable product . Use crudapi You can say goodbye to the boring addition, deletion, modification and query code , Let you focus more on your business , Save a lot of cost , So as to improve work efficiency .crudapi The goal is to make it easier to process data , Everyone can use it for free ！ No programming , Automatically generated by configuration crud Additions and deletions RESTful API, Provide background UI Manage business data . Based on mainstream open source frameworks , Own independent intellectual property rights , Support secondary development .

demo demonstration

crudapi It's a product level zero code platform , Unlike automatic code generators , You don't have to generate Controller、Service、Repository、Entity And so on , The program can be used as soon as it runs , real 0 Code , It can cover basic business independent CRUD RESTful API.

Official website address ：

https://crudapi.cn

Address of the test ：

https://demo.crudapi.cn/crudapi/login