ShardingSphere JDBC branch library implements multiple database sources

This is the second day of my participation in the First Challenge 2022

Introduction to the

Based on Shardingsphere JDBC 5.0.0 version, Sharding branch library is used to realize the daily use of multi-data sources in the database, combined with Spring Boot and Mybatis Plus

Data Source Requirements

The database initial statement is as follows:

create database demo1;
create database demo2;

create table `demo1`.table1 (
    id int
);

create table `demo2`.table2 (
    id int
);

create table `demo1`.sharding_table (
    id int
);

create table `demo2`.sharding_table (
    id int
);

insert into `demo1`.sharding_table (id) values(1);
insert into `demo2`.sharding_table (id) values(1);
Copy the code

Database 1 has tables table1 and sharding_table

Database 2 has tables: table2, sharding_table

Requirements are as follows:

When accessing table Table1, access the database Demo1
When accessing table Table2, access the database Demo2
When accessing the sharding_table table, the corresponding data is accessed according to the custom incoming parameters. For this article, the data source to be accessed is stored in ThreadLocal, and the corresponding data source is accessed after being retrieved

Key code Examples

GitHub address: github.com/lw124392545…

Defining data sources

To configure ShardingSphere JDBC data source, the key code is as follows:

Table table1 accesses data source DB0 and table table2 accesses data source db1

# shardingSphere branch library Settings
shardingsphere:
  Configure the real data source
  datasources:
    # database 1
    db0:
      jdbcurl: The ${DB1_URL: JDBC: mysql: / / 127.0.0.1:3306 / not? useUnicode=true&serverTimezone=UTC}
      username: ${DB1_USER:root}
      password: ${DB1_PASS:root}
    # database 2
    db1:
      jdbcurl: The ${DB2_URL: JDBC: mysql: / / 127.0.0.1:3306 / demo2? useUnicode=true&serverTimezone=UTC}
      username: ${DB2_USER:root}
      password: ${DB2_PASS:root}
  rules:
    table1: db0
    table2: db1
Copy the code

If you use yamL file configuration of ShardingSphere, you have not found the way to use environment variables, so it is not convenient to modify, so you use Java code directly to configure

@Slf4j
@Configuration
public class ShardingDataSourceMybatisPlusConfig extends MybatisPlusAutoConfiguration {

    private final MultipleDbConfig multipleDbConfig;

    @Primary
    @Bean("dataSource")
    public DataSource getDataSource(a) throws SQLException {
        // Configure the real data source
        Map<String, MultipleDbConfig.DbSource> dbs = multipleDbConfig.getDatasources();
        Map<String, DataSource> dataSourceMap = new HashMap<>(dbs.size());
        for (String dbName: dbs.keySet()) {
            MultipleDbConfig.DbSource dbConfig = dbs.get(dbName);
            HikariDataSource dataSource = new HikariDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setJdbcUrl(dbConfig.getJdbcUrl());
            dataSource.setUsername(dbConfig.getUsername());
            dataSource.setPassword(dbConfig.getPassword());
            dataSourceMap.put(dbName, dataSource);
        }

        // Configure sharding rules
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        Table table1 to data source DB0, table table2 to data source db1
        Map<String, String> rules = multipleDbConfig.getRules();
        for (final String table: rules.keySet()) {
            // configure to add t_ORDER table rules
            final String actualDataNodes = String.join(".", rules.get(table), table);
            shardingRuleConfig.getTables().add(new ShardingTableRuleConfiguration(table, actualDataNodes));
        }

        // To configure sharding_table access, you need to customize the implementation of the sub-database and sub-table algorithm
        ShardingTableRuleConfiguration ShardingTableRuleConfiguration = new ShardingTableRuleConfiguration("sharding_table"."db${0.. 1}.sharding_table");
        shardingRuleConfig.setDefaultDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("id"."customDbSharding"));
        shardingRuleConfig.setDefaultTableShardingStrategy(new StandardShardingStrategyConfiguration("id"."customTableSharding"));
        shardingRuleConfig.getTables().add(ShardingTableRuleConfiguration);

        // Configure the library sorting algorithm
        Properties dbShardingAlgorithmProps = new Properties();
        dbShardingAlgorithmProps.setProperty("strategy"."standard");
        dbShardingAlgorithmProps.setProperty("algorithmClassName"."com.shardingsphere.shardingdb.config.CustomDbSharding");
        shardingRuleConfig.getShardingAlgorithms().put("customDbSharding".new ShardingSphereAlgorithmConfiguration("CLASS_BASED", dbShardingAlgorithmProps));

        // Configure the sub-table algorithm
        Properties tableShardingAlgorithmProps = new Properties();
        tableShardingAlgorithmProps.setProperty("strategy"."standard");
        tableShardingAlgorithmProps.setProperty("algorithmClassName"."com.shardingsphere.shardingdb.config.CustomTableSharding");
        shardingRuleConfig.getShardingAlgorithms().put("customTableSharding".new ShardingSphereAlgorithmConfiguration("CLASS_BASED", tableShardingAlgorithmProps));

        // Enable Sql logging
        final Properties properties = new Properties();
        properties.setProperty("sql-show"."true");

        / / create ShardingSphereDataSource
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), properties);
    }

    @Override
    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource")DataSource dataSource) throws Exception {
        return super.sqlSessionFactory(getDataSource());
    }

    @Override
    @Bean("sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
        return super.sqlSessionTemplate(sqlSessionFactory); }}Copy the code

As you can see from the code, most of it is still easy to directly repair the configuration file later for extension

The code for the custom branch is as follows: basically get the data source name information in ThreadLocal and return it to Shardingsphere so that the corresponding data source can be accessed

This straightforward approach is used for simplicity in the example, but other information can be put in and converted to the corresponding data source as required

public final class CustomDbSharding implements StandardShardingAlgorithm<Integer> {

    @Override
    public void init(a) {}@Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
        String dbName = ThreadLocalCache.threadLocal.get();
        for (String each : availableTargetNames) {
            if (each.equals(dbName)) {
                returneach; }}return null;
    }

    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Integer> shardingValue) {
        return availableTargetNames;
    }

    @Override
    public String getType(a) {
        return null; }}Copy the code

In fact, there should be no custom sub-table, but in order to show a complete, so also made a custom sub-table, here is directly return can be

public final class CustomTableSharding implements StandardShardingAlgorithm<Integer> {

    @Override
    public void init(a) {}@Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
        for (String each : availableTargetNames) {
            return each;
        }
        return null;
    }

    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Integer> shardingValue) {
        return availableTargetNames;
    }

    @Override
    public String getType(a) {
        return null; }}Copy the code

Entity and Mapper definitions

Simply write:

@Data
@TableName("sharding_table")
public class ShardingTable {
    private Long id;
}

@Data
@TableName("table1")
public class Table1 {
    private Long id;
}

@Data
@TableName("table2")
public class Table2 {

    private Long id;
}

@Repository
public interface ShardingTableMapper extends BaseMapper<ShardingTable> {}@Repository
public interface Table1Mapper extends BaseMapper<Table1> {}@Repository
public interface Table2Mapper extends BaseMapper<Table2> {}Copy the code

The validation test

We’ve written our test class, and we just test it

@ExtendWith(SpringExtension.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class ShardingDbTest {

    @Autowired
    private Table1Mapper table1Mapper;
    @Autowired
    private Table2Mapper table2Mapper;
    @Autowired
    private ShardingTableMapper shardingTableMapper;

    @Test
    public void test(a) {
        final List<Table1> l1 = table1Mapper.selectList(null);
        l1.forEach(System.out::println);

        final List<Table2> l2 = table2Mapper.selectList(null);
        l2.forEach(System.out::println);

        ThreadLocalCache.threadLocal.set("db1");
        System.out.println(shardingTableMapper.selectById(1L));

        ThreadLocalCache.threadLocal.set("db0");
        System.out.println(shardingTableMapper.selectById(1L)); }}Copy the code

The results are as follows:

Logic SQL: SELECT id FROM table1 SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: db0 ::: SELECT id FROM table1 Logic SQL: SELECT id FROM table2 SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: db1 ::: SELECT id FROM table2 Logic SQL: SELECT id FROM sharding_table WHERE id=? SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: db1 ::: SELECT id FROM sharding_table WHERE id=? ::: [1] ShardingTable(id=1) Logic SQL: SELECT id FROM sharding_table WHERE id=? SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty) Actual SQL: db0 ::: SELECT id FROM sharding_table WHERE id=? : : : [1]Copy the code

You can see that with four visits, Actual SQL meets our expectations

conclusion

How to use Shardingsphere JDBC to implement multi-data source access, Shardingsphere JDBC how to implement custom sub-library and sub-table algorithm

Refer to the link

Shardingsphere uses the JAVA API
Shardingsphere YAML configuration data fragments
ThreadLocal usage and principles
Shardingsphere algorithm
How to configure a custom sharding strategy?

ShardingSphere JDBC branch library implements multiple database sources

Introduction to the

Data Source Requirements

Key code Examples

Defining data sources

Entity and Mapper definitions

The validation test

conclusion

Refer to the link

Related Posts

Python’s exception type summary and catch statements

C# introduction series (14) – structure applications

SpringMVC Framework: Chapter 1: An overview of SpringMVC and a preliminary understanding of the use process of SpringMVC