sharding-jdbc four sharding strategies

1. Standard sharding strategy (standard)

1. Precise sharding

Configuration file

spring:
  shardingsphere:
    #Enable sql display
    props:
      sql:
        show: true
    datasource:
      # Configure data source
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
    sharding:
      #Unique database data
      default-data-source-name: db0
      #sub-library
      default-database-strategy:
        standard:
          #Add data sub-database field (insert data into that table based on the field)
          sharding-column: id
          #Accurate sharding
          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
      #subtable
      tables:
        #Table Name
        db_user:
          actual-data-nodes: db$->{<!-- -->0..1}.db_user_$->{<!-- -->0..2}
          key-generator:
            column: id # Primary key ID
            type: SNOWFLAKE # Generate policy snowflake id
          table-strategy:
            standard:
              sharding-column: id
              #Accurate sharding
              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm

Sub-library rules

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * Precise sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabasePreciseAlgorithm implements PreciseShardingAlgorithm<Long> {<!-- -->
    /**
     * Precise sharding
     * @param collection data source collection
     * @param preciseShardingValue Sharding parameters
     * @return database
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {<!-- -->
        //Value of shard key
        Long value = preciseShardingValue.getValue();
        String dbName = "db" + (value % 2);
        if(!collection.contains(dbName)){<!-- -->
           throw new UnsupportedOperationException("data source" + dbName + "does not exist");
        }
        return dbName;
    }
}

Table sub-rules

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * Precise sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabasePreciseAlgorithm implements PreciseShardingAlgorithm<Long> {<!-- -->
    /**
     * Precise sharding
     * @param collection data source collection
     * @param preciseShardingValue Sharding parameters
     * @return database
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {<!-- -->
        //Value of shard key
        Long value = preciseShardingValue.getValue();
        String dbName = "db" + (value % 2);
        if(!collection.contains(dbName)){<!-- -->
           throw new UnsupportedOperationException("data source" + dbName + "does not exist");
        }
        return dbName;
    }
}

2. Range sharding

Configuration file

spring:
  shardingsphere:
    #Enable sql display
    props:
      sql:
        show: true
    datasource:
      # Configure data source
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
    sharding:
      #Unique database data
      default-data-source-name: db0
      #sub-library
      default-database-strategy:
        standard:
          #Add data sub-database field (insert data into that table based on the field)
          sharding-column: id
          #Accurate sharding
          precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
          #range sharding
          range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
      #subtable
      tables:
        #Table Name
        db_user:
          actual-data-nodes: db$->{<!-- -->0..1}.db_user_$->{<!-- -->0..2}
          key-generator:
            column: id # Primary key ID
            type: SNOWFLAKE # Generate policy snowflake id
          table-strategy:
            standard:
              sharding-column: id
              #Accurate sharding
              precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
              #range sharding
              range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm

Sub-library rules

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;

/**
 * Range sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabaseRangeAlgorithm implements RangeShardingAlgorithm<Long> {<!-- -->
    /**
     * Range sharding
     * @param collection data source collection
     * @param rangeShardingValue Sharding parameters
     * @return Return directly to the source
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {<!-- -->
        return collection;
    }
}

Table sub-rules

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * Range sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableRangeAlgorithm implements RangeShardingAlgorithm<Long> {<!-- -->
    /**
     * Range sharding
     * @param collection data source collection
     * @param rangeShardingValue Sharding parameters
     * @return Return directly to the source
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {<!-- -->
        //Logical table name
        String logicTableName = rangeShardingValue.getLogicTableName();
        return Arrays.asList(logicTableName + "_0",logicTableName + "_1",logicTableName + "_2");
    }
}

Row expression fragmentation strategy (inline)

Configuration file (need to comment other sharding rules)

spring:
  shardingsphere:
    #Enable sql display
    props:
      sql:
        show: true
    datasource:
      # Configure data source
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
    sharding:
      #Unique database data
      default-data-source-name: db0
      #sub-library
      default-database-strategy:
#standard:
# # Add data sub-database fields (insert data into that table based on the fields)
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
        inline:
          #Add data table fields (insert data into that table based on the fields)
          sharding-column: id
          # Sharding algorithm expression => take remainder by id
          algorithm-expression: db$->{<!-- -->id % 2}
      #subtable
      tables:
        #Table Name
        db_user:
          actual-data-nodes: db$->{<!-- -->0..1}.db_user_$->{<!-- -->0..2}
          key-generator:
            column: id # Primary key ID
            type: SNOWFLAKE # Generate policy snowflake id
          table-strategy:
#standard:
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
            inline:
              #Add data table fields (insert data into that table based on the fields)
              sharding-column: id
              # Sharding algorithm expression => take remainder by id
              algorithm-expression: db_user_$->{<!-- -->id % 3}

Database sharding rules and table sharding rules can be directly used in groovy scripts
Example: db_user_$->{id % 3}

Composite sharding strategy (complex)

Configuration file

spring:
  shardingsphere:
    #Enable sql display
    props:
      sql:
        show: true
    datasource:
      # Configure data source
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
    sharding:
      #Unique database data
      default-data-source-name: db0
      #sub-library
      default-database-strategy:
#standard:
# # Add data sub-database fields (insert data into that table based on the fields)
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
# #Row sharding
# inline:
# # Add data table fields (insert data into that table based on the fields)
# sharding-column: id
# # Sharding algorithm expression => take remainder by id
# algorithm-expression: db$->{id % 2}
        #Composite sharding
         complex:
           sharding-columns: id,age
           algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm

      #subtable
      tables:
        #Table Name
        db_user:
          actual-data-nodes: db$->{<!-- -->0..1}.db_user_$->{<!-- -->0..2}
          key-generator:
            column: id # Primary key ID
            type: SNOWFLAKE # Generate policy snowflake id
          table-strategy:
#standard:
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
# inline:
# # Add data table fields (insert data into that table based on the fields)
# sharding-column: id
# # Sharding algorithm expression => take remainder by id
# algorithm-expression: db_user_$->{id % 3}
            #Composite sharding
            complex:
              sharding-columns: id,age
              algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm

Sub-library rules

package com.example.sharding_test.strategy.database;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;

import java.util.Collection;
import java.util.List;

/**
 * Composite sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class DatabaseComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {<!-- -->


    /**
     *
     * @param collection data source collection
     * @param complexKeysShardingValue The value collection of sharding keys
     * @return The collection of data sources to be found
     */
    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {<!-- -->
        //Get the value of age
        Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
        List<String> dbs = Lists.newArrayList();
        //Module by age
        ageValues.forEach(item->{<!-- -->
            String dbName = "db" + ((item + 3)%2);
            dbs.add(dbName);
        });
        return dbs;
    }
}

Table sub-rules

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.assertj.core.util.Lists;

import java.util.Arrays;
import java.util.Collection;
import java.util.List;

/**
 * Composite sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableComplexAlgorithm implements ComplexKeysShardingAlgorithm<Integer> {<!-- -->

    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<Integer> complexKeysShardingValue) {<!-- -->
        //Get the value of age
        Collection<Integer> ageValues = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
        List<String> dbs = Lists.newArrayList();
        //Module by age
        ageValues.forEach(item->{<!-- -->
            String dbName = "db_user_" + ((item + 3)%3);
            dbs.add(dbName);
        });
        return dbs;
    }
}

Hint sharding strategy (hint)

Configuration file (need to comment other sharding rules)

spring:
  shardingsphere:
    #Enable sql display
    props:
      sql:
        show: true
    datasource:
      # Configure data source
      names: db0,db1
      db0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_0?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
      db1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/sharding_test_1?useUnicode=true & amp;character_set_server=utf8mb4 & amp;useSSL=false & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/ Shanghai
        username: root
        password: root
    sharding:
      #Unique database data
      default-data-source-name: db0
      #sub-library
      default-database-strategy:
#standard:
# # Add data sub-database fields (insert data into that table based on the fields)
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.database.DatabasePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseRangeAlgorithm
        #line sharding
        inline:
          #Add data table fields (insert data into that table based on the fields)
          sharding-column: id
          # Sharding algorithm expression => take remainder by id
          algorithm-expression: db$->{<!-- -->id % 2}
# #Composite sharding
#complex:
# sharding-columns: id,age
# algorithm-class-name: com.example.sharding_test.strategy.database.DatabaseComplexAlgorithm

      #subtable
      tables:
        #Table Name
        db_user:
          actual-data-nodes: db$->{<!-- -->0..1}.db_user_$->{<!-- -->0..2}
          key-generator:
            column: id # Primary key ID
            type: SNOWFLAKE # Generate policy snowflake id
          table-strategy:
#standard:
# sharding-column: id
# #Accurate sharding
# precise-algorithm-class-name: com.example.sharding_test.strategy.table.TablePreciseAlgorithm
# #Range sharding
# range-algorithm-class-name: com.example.sharding_test.strategy.table.TableRangeAlgorithm
# inline:
# # Add data table fields (insert data into that table based on the fields)
# sharding-column: id
# # Sharding algorithm expression => take remainder by id
# algorithm-expression: db_user_$->{id % 3}
# #Composite sharding
#complex:
# sharding-columns: id,age
# algorithm-class-name: com.example.sharding_test.strategy.table.TableComplexAlgorithm
            #Force fragmentation
            hint:
              algorithm-class-name: com.example.sharding_test.strategy.table.TableHintAlgorithm

Table sub-rules

package com.example.sharding_test.strategy.table;

import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * Precise sharding
 *
 * @author shuai
 * @since 2023-03-19
 */
public class TableHintAlgorithm implements HintShardingAlgorithm<Integer> {<!-- -->


    @Override
    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<Integer> hintShardingValue) {<!-- -->
        String logicTableName = hintShardingValue.getLogicTableName();
        String dbName = logicTableName + "_" + hintShardingValue.getValues().toArray()[0];
        return Arrays.asList(dbName);
    }
}

test hints

 @Test
    void selectHintData(){<!-- -->
        HintManager manager = HintManager.getInstance();
        manager.addTableShardingValue("db_user",2);
        LambdaQueryWrapper<DbUser> wrapper = Wrappers.lambdaQuery();
        wrapper.eq(DbUser::getAge,34);
        List<DbUser> dbUsers = dbUserMapper.selectList(wrapper);
        dbUsers.forEach(System.out::println);
    }