Shardingsphere is used to divide some data tables into databases and tables in multiple data sources

Time:2022-5-17

background

Recently, multiple data sources need to be used in the project, and some tables have a large amount of data, so they need to be divided into databases and tables; While the data volume of other data tables is relatively normal, a single table can be used.
Data source data from other groups may be used in the project, so multi data source support is required.
After investigation, it is convenient to configure multiple data sources. In this project, the strategy of dividing database and table is relatively simple, which can be divided according to only one field. Therefore, the popular and easy-to-use sharding JDBC is selected for the scheme of dividing database and table
The goal to be achieved is based on the student name field student_ Name performs table splitting, but does not need database splitting. The data table is student_ hist0 – student_ hist9

Introducing sharding JDBC Maven dependency

org.apache.shardingsphere
    sharding-jdbc-core
    4.1.1

Data source profile

spring:
  application:
    name: student-service-provider
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    defaultPropertyInclusion: non_null
    deserialization:
      FAIL_ON_UNKNOWN_PROPERTIES: false 
  #Format the returned time
  datasource:
    hikari:
      student:
        url: jdbc:mysql://127.0.0.1:3306/student_service?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useTimezone=true&serverTimezone=GMT%2
        username: root
        password: root123
      log1:
        url: jdbc:mysql://127.0.0.1:3306/log1?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root123
      log2:
        url: jdbc:mysql://127.0.0.1:3306/log2?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
        username: root
        password: root123

Configure multiple data source code

Datasourceproperties data sources

import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public class DataSourceProperties {

    private HikariDataSource student;
	private HikariDataSource log1;
	private HikariDataSource log2;

}

Dynamicdatasource dynamic data source

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    /*
    Current data source name
     */
    private static final ThreadLocal dataSourceContextHolder = new ThreadLocal<>();

    /*
    Set data source name
     */
    public static void setDataSourceName(String dataSourceName) {
        dataSourceContextHolder.set(dataSourceName);
    }

    /*
    Get data source name
     */
    public static String getDataSourceName() {
        return dataSourceContextHolder.get();
    }

    /*
    Clear data source name
     */
    public static void clearDataSource() {
        dataSourceContextHolder.remove();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSourceName();
    }
}

Multidatasource tag

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface MultiDataSource {
    String value() default DataSourceConfig.DEFAULT_DATASOURCE_NAME;
}

Here’s the point, because the table is divided according to a field of the table, which is a string type. Therefore, you need to calculate the table according to the consistency hash code of the string. The preciseshardingalgorithm class needs to be implemented in sharding JDBC
For example: want to be in student student_ The hist table is divided according to the student’s name, and the logical table is student_ Hist, the real table is student_ hist0 – student_ hist9
DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT=10

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;

public class PreciseNodeIdShardingAlgorithm implements PreciseShardingAlgorithm {

    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        for (String tbnm : collection) {
            if (tbnm.endsWith("hist" + (getHash(preciseShardingValue.getValue()) % DataSourceConfig.SHARD_MMS_DATASOURCE_TABLE_COUNT))) {
                return tbnm;
            }
        }
        throw new UnsupportedOperationException();
    }
	
	private static int getHash(String str) {
        final int p = 16777619;
        int hash = (int) 2166136261L;
        for (int i = 0; i < str.length(); i++)
            hash = (hash ^ str.charAt(i)) * p;
        hash += hash << 13;
        hash ^= hash >> 7;
        hash += hash << 3;
        hash ^= hash >> 17;
        hash += hash << 5;
 
        //If the calculated absolute value is negative
        if (hash < 0)
            hash = Math.abs(hash);
        return hash;
    }
}

Multi data source assembly datasourceconfig. You need to specify the default data source. When you don’t need to use the table with split table, you can use the default data source. Otherwise, you need to specify the data source with split table.
When configuring the table splitting policy, if database splitting is not required, tableruleconfiguration can be set setDatabaseShardingStrategyConfig();

import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Configuration
public class DataSourceConfig {

    public static final String DEFAULT_DATASOURCE_NAME = "defaultDataSource";
    public static final String MMS_DATASOURCE_NAME = "mmsDatasource";
    public static final String SHARD_MMS_DATASOURCE_NAME = "shardMmsDatasource";

    public static int SHARD_MMS_DATASOURCE_TABLE_COUNT = 10;

    @Autowired
    private DataSourceProperties properties;

    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        //Default data source
        dynamicDataSource.setDefaultTargetDataSource(properties.getMms());
        //Configure multiple data sources
        Map dsMap = new HashMap();
        dsMap.put(DEFAULT_DATASOURCE_NAME, properties.getStudent());
        dsMap.put(MMS_DATASOURCE_NAME, properties.getStudent());
        dsMap.put(SHARD_MMS_DATASOURCE_NAME, buildShardDatasources());
        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    public DataSource buildShardDatasources() {
        //Configure multiple data sources
        Map dsMap = new HashMap();
        dsMap.put("shardMms", properties.getMms());
        TableRuleConfiguration stuHisTableRuleConfig = new TableRuleConfiguration("student_hist", "shardMms.student_hist${0.." + (SHARD_MMS_DATASOURCE_TABLE_COUNT - 1) + "}");
//        tableRuleConfiguration.setDatabaseShardingStrategyConfig();
        stuHisTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("student_name", new PreciseNodeIdShardingAlgorithm()));

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(stuHisTableRuleConfig);
        try {
            Properties properties = new Properties();
            properties.setProperty("sql.show", "true");
            return ShardingDataSourceFactory.createDataSource(dsMap, shardingRuleConfig, properties);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new IllegalArgumentException();
        }
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }

}

For multi data source switching datasourceaspect, when multi data source switching is required, the annotation method multi datasource shall be used on the service method and the data source shall be specified.

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import java.lang.reflect.Method;

@Aspect
@Configuration
@Slf4j
@Order(1)
public class DataSourceAspect {

    //Pointcut, all annotation methods in the service
    @Pointcut("execution(* com.huitong..service..*.*(..)) && @annotation(com.huitong.app.config.datasource.MultiDataSource)")
    public void dataSourceAspect() {
    }

    @Around("dataSourceAspect()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        Method method = signature.getMethod();
        MultiDataSource ds = method.getAnnotation(MultiDataSource.class);
        if (ds != null) {
            DynamicDataSource.setDataSourceName(ds.value());
        }
        try {
            return joinPoint.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
        }
    }
}

reference:

Recommended Today

Introduction to js decorator (introduction to ts decorator)

reference js decorator @Decorator Decorator – Ruan Yifeng TS Decorator(2): Metadata TS Documentation – Decorators hint TypeScript has fully implemented the decorator, and the js decorator is a syntax that is still in the proposal. If you use js instead of ts, you need to configure Babel to use it. The prerequisite knowledge you need […]