Based on shard JDBC middleware, data is divided into database and table

Introduction to sharding JDBC

Sharding JDBC is positioned as a lightweight Java framework that provides additional services in the JDBC layer of Java. It uses the client to connect directly to the database and provides services in the form of jar packages without additional deployment and dependency. It can be understood as an enhanced jdbc driver and is fully compatible with JDBC and various ORM frameworks.

  • It is applicable to any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or using JDBC directly.
  • Support any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
  • Support any database that implements JDBC specification. Currently, it supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

Sharding configuration diagram

For a simple understanding, see the figure below. Configuring sharding JDBC is actually configuring all tables that need to be partitioned. Table configuration mainly refers to the configuration of sub databases and sub tables. Here, you can divide only databases but not tables, or only tables but not databases, or both database and table logic can be included.

1. Horizontal split

1.1 horizontal sub warehouse

1) . concept:
Based on the field, the data in one database is split into multiple databases according to certain strategies.
2) . results
The structure of each library is the same; Data are different;
The union of all databases is full data;

1.2 horizontal sub table

1) . concept
Split the data in one table into multiple tables according to certain policies based on fields.
2) . results
The structure of each table is the same; Data are different;
The union of all tables is full data;

2. Shard JDBC Middleware

2.1 architecture diagram

2.2 features

  1. Sharding JDBC directly encapsulates the JDBC API, and the cost of old code migration is almost zero.
  2. It is applicable to any Java based ORM framework, such as Hibernate, Mybatis, etc.
  3. It can be based on any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, etc.
  4. The service is provided in the form of jar package, without proxy layer, additional deployment and other dependencies.
  5. The sharding strategy is flexible. It supports multi-dimensional sharding such as equal sign, between, in, and multi sharding keys.
  6. The SQL parsing function is perfect, and supports aggregation, grouping, sorting, limit, or and other queries.

3. Project case

3.1 project structure

springboot     2.0 version
druid          1.1.13 version
sharding-jdbc  3.1 version

3.2 database configuration

One basic library mapping( shard_one)

Two warehouses shall be divided into different warehouses and tables( shard_two,shard_three). 
Table usage: table_one,table_two

3.3 core code block

3.3.1 data source configuration file

spring:
  datasource:
    # Data source: shard_one
    dataOne:
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/shard_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: 123
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        max-evictable-idle-time-millis: 60000
        validation-query: SELECT 1 FROM DUAL
        # validation-query-timeout: 5000
        test-on-borrow: false
        test-on-return: false
        test-while-idle: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # Data source: shard_two
    dataTwo:
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/shard_two?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: 123
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        max-evictable-idle-time-millis: 60000
        validation-query: SELECT 1 FROM DUAL
        # validation-query-timeout: 5000
        test-on-borrow: false
        test-on-return: false
        test-while-idle: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # Data source: shard_three
    dataThree:
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/shard_three?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
        username: root
        password: 123
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        max-evictable-idle-time-millis: 60000
        validation-query: SELECT 1 FROM DUAL
        # validation-query-timeout: 5000
        test-on-borrow: false
        test-on-return: false
        test-while-idle: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

3.3.2 database distribution strategy

/**
 * Database mapping calculation
 */
public class DataSourceAlg implements PreciseShardingAlgorithm<String> {

    private static Logger LOG = LoggerFactory.getLogger(DataSourceAlg.class);
    @Override
    public String doSharding(Collection<String> names, PreciseShardingValue<String> value) {
        LOG.debug("Sub database algorithm parameters {},{}",names,value);
        int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
        return "ds_" + ((hash % 2) + 2) ;
    }
}

3.3.3 data table 1 table splitting strategy

/**
 * Tabulation algorithm
 */
public class TableOneAlg implements PreciseShardingAlgorithm<String> {
    private static Logger LOG = LoggerFactory.getLogger(TableOneAlg.class);
    /**
     * This table is divided into 5 tables for each warehouse
     */
    @Override
    public String doSharding(Collection<String> names, PreciseShardingValue<String> value) {
        LOG.debug("Table splitting algorithm parameters {},{}",names,value);
        int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
        return "table_one_" + (hash % 5+1);
    }
}

3.3.4 data table 2 table splitting strategy

/**
 * Tabulation algorithm
 */
public class TableTwoAlg implements PreciseShardingAlgorithm<String> {
    private static Logger LOG = LoggerFactory.getLogger(TableTwoAlg.class);
    /**
     * This table is divided into 5 tables for each warehouse
     */
    @Override
    public String doSharding(Collection<String> names, PreciseShardingValue<String> value) {
        LOG.debug("Table splitting algorithm parameters {},{}",names,value);
        int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
        return "table_two_" + (hash % 5+1);
    }
}

3.3.5 data source integration configuration

/**
 * Database database and table configuration
 */
@Configuration
public class ShardJdbcConfig {
    // The druid configuration is omitted. The source code contains
    /**
     * Shard-JDBC Sub database configuration
     */
    @Bean
    public DataSource dataSource (@Autowired DruidDataSource dataOneSource,
                                  @Autowired DruidDataSource dataTwoSource,
                                  @Autowired DruidDataSource dataThreeSource) throws Exception {
        ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration();
        shardJdbcConfig.getTableRuleConfigs().add(getTableRule01());
        shardJdbcConfig.getTableRuleConfigs().add(getTableRule02());
        shardJdbcConfig.setDefaultDataSourceName("ds_0");
        Map<String,DataSource> dataMap = new LinkedHashMap<>() ;
        dataMap.put("ds_0",dataOneSource) ;
        dataMap.put("ds_2",dataTwoSource) ;
        dataMap.put("ds_3",dataThreeSource) ;
        Properties prop = new Properties();
        return ShardingDataSourceFactory.createDataSource(dataMap, shardJdbcConfig, new HashMap<>(), prop);
    }

    /**
     * Shard-JDBC Sub table configuration
     */
    private static TableRuleConfiguration getTableRule01() {
        TableRuleConfiguration result = new TableRuleConfiguration();
        result.setLogicTable("table_one");
        result.setActualDataNodes("ds_${2..3}.table_one_${1..5}");
        result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg()));
        result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableOneAlg()));
        return result;
    }
    private static TableRuleConfiguration getTableRule02() {
        TableRuleConfiguration result = new TableRuleConfiguration();
        result.setLogicTable("table_two");
        result.setActualDataNodes("ds_${2..3}.table_two_${1..5}");
        result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg()));
        result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableTwoAlg()));
        return result;
    }
}

3.3.6 test code execution process

@RestController
public class ShardController {
    @Resource
    private ShardService shardService ;
    /**
     * 1,Table creation process
     */
    @RequestMapping("/createTable")
    public String createTable (){
        shardService.createTable();
        return "success" ;
    }
    /**
     * 2,Generate table_one data
     */
    @RequestMapping("/insertOne")
    public String insertOne (){
        shardService.insertOne();
        return "SUCCESS" ;
    }
    /**
     * 3,Generate table_two data
     */
    @RequestMapping("/insertTwo")
    public String insertTwo (){
        shardService.insertTwo();
        return "SUCCESS" ;
    }
    /**
     * 4,Query table_one data
     */
    @RequestMapping("/selectOneByPhone/{phone}")
    public TableOne selectOneByPhone (@PathVariable("phone") String phone){
        return shardService.selectOneByPhone(phone);
    }
    /**
     * 5,Query table_one data
     */
    @RequestMapping("/selectTwoByPhone/{phone}")
    public TableTwo selectTwoByPhone (@PathVariable("phone") String phone){
        return shardService.selectTwoByPhone(phone);
    }
}

Tags: MySQL Java Spring Boot Middleware

Posted by phenley on Tue, 31 May 2022 14:19:45 +0530