Implementation of sharding JDBC custom composite partition (sub database and sub table)


The sharding strategy in sharding JDBC has two dimensions:

  • Database sharding strategy
  • Table sharding strategy

The data source fragmentation strategy represents the physical target data source to which the data is routed, and the table fragmentation strategy represents the target table to which the data is routed.

In particular, the table fragmentation strategy depends on the data source fragmentation strategy, that is, divide the database first and then the table. Of course, you can only divide the table.

Sharding JDBC data slicing strategy

Sharding JDBC’s sharding strategy includes sharding keys and sharding algorithms. Because sharding algorithm is closely related to business implementation, sharding JDBC does not provide built-in sharding algorithm, but extracts various scenarios through sharding strategy, provides high-level abstraction, and allows developers to implement sharding algorithm by providing interfaces.

The following is quoted from official documents.Official documents

Firstly, four segmentation algorithms are introduced.

The data is segmented through the segmentation algorithm, and segmentation through =, between and in is supported.
The slicing algorithm needs to be implemented by the application developer, and the flexibility is very high.

At present, four fragmentation algorithms are provided. Because the sharding algorithm is closely related to business implementation,
Therefore, the built-in segmentation algorithm is not provided, but various scenes are extracted through the segmentation strategy,
It provides a higher level of abstraction and provides an interface for application developers to implement sharding algorithms by themselves.

Slice key

The database field used for sharding is the key field to split the database (table) horizontally. Example: if the mantissa of the order primary key in the order table is taken as a module fragment, the order primary key is a fragment field. If there is no fragment field in SQL, full routing will be executed, and the performance is poor. In addition to the support for single sharding fields, shardingsphere also supports sharding according to multiple fields.

Partition algorithm

The data is segmented through the segmentation algorithm, which supports=BETWEENandINSlice. The slicing algorithm needs to be implemented by the application developer, and the flexibility is very high.

At present, four fragmentation algorithms are provided. Because the sharding algorithm is closely related to business implementation, it does not provide a built-in sharding algorithm, but extracts various scenarios through the sharding strategy, provides a higher level of abstraction, and provides an interface for application developers to implement the sharding algorithm by themselves.

Exact slicing algorithm

Corresponding to preciseshardingalgorithm, it is used to process the scene where = and in are sliced using a single key as the slicing key. It needs to be used with standardshardingstrategy.

Range slicing algorithm

The corresponding rangeshardingalgorithm is used to process the scene of using a single key as the next and for slicing. It needs to be used with standardshardingstrategy.

Composite slicing algorithm

The corresponding complexkeys shardingalgorithm is used to deal with the scenario of using multiple keys as sharding keys. The logic of multiple sharding keys is complex, and the application developer needs to deal with the complexity by himself. It needs to be used in conjunction with the complexshardingstrategy.

Hint slicing algorithm

The corresponding hintshardingalgorithm is used to process scenes using hint row slicing. It needs to be used with hintshardingstrategy.

Partition strategy

It includes partition key and partition algorithm. Due to the independence of partition algorithm, it is separated independently. What can really be used for sharding operation is sharding key + sharding algorithm, that is, sharding strategy. At present, five fragmentation strategies are provided.

Standard fragmentation strategy

Corresponding to standardshardingstrategy. Provides fragment operation support for =, in and between and in SQL statements. Standard shardingstrategy only supports single sharding key and provides two sharding algorithms: precision shardingalgorithm and range shardingalgorithm. Preciseshardingalgorithm is required and is used to process the tiles of = and in. Rangeshardingalgorithm is optional and used to process between and fragmentation. If rangeshardingalgorithm is not configured, between and in SQL will be processed according to the full database route.

Composite fragmentation strategy

Corresponds to the complexshardingstrategy. Composite fragmentation strategy. Provides fragment operation support for =, in and between and in SQL statements. The complex shardingstrategy supports multiple sharding keys. Because the relationship between multiple sharding keys is complex, it does not carry out too much encapsulation. Instead, it directly transmits the sharding key value combination and sharding operator to the sharding algorithm, which is completely implemented by the application developer and provides the greatest flexibility.

Row expression slicing strategy

Corresponds to inlineshardingstrategy. Groovy’s expression is used to provide fragment operation support for = and in in SQL statements, and only single fragment keys are supported. Simple slicing algorithms can be used through simple configuration to avoid cumbersome java code development, such as:t_user_$->{u_id % 8}Represents t_ User table according to u_ ID module 8, which is divided into 8 tables. The table name ist_user_0reacht_user_7

Hint fragmentation strategy

Corresponding to hintshardingstrategy. The strategy of sharding through hint rather than SQL parsing.

Non fragmentation strategy

Corresponding to noneshardingstrategy. No fragmentation strategy.

SQL Hint

For scenarios where the fragment field is not determined by SQL but determined by other external conditions, SQL hint can be used to inject the fragment field flexibly. For example: in the internal system, the database is divided according to the employee login primary key, but there is no such field in the database. SQL hint supports the use of Java API and SQL annotation (to be implemented).

Actual combat – custom composite fragmentation strategy

Because the purpose is close to the actual combat, it focuses on how to realize the complex fragmentation strategy, that is, to realize the fragmentation strategy available for customized production through the complexshardingstrategy interface.

The code of adminidshardingalgorithm is as follows:

import io.shardingjdbc.core.api.algorithm.sharding.ListShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.RangeShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.ShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import java.util.*;
public class AdminIdShardingAlgorithm implements ComplexKeysShardingAlgorithm {
    private Logger logger = Logger.getLogger(getClass());
    public Collection<String> doSharding(Collection<String> availableTargetNames, Collection<ShardingValue> shardingValues) {
        Collection<String> routTables = new HashSet<String>();
        if (shardingValues != null) {
            for (ShardingValue shardingValue : shardingValues) {
                //EQ in condition
                if (shardingValue instanceof ListShardingValue) {
                    ListShardingValue listShardingValue = (ListShardingValue) shardingValue;
                    Collection<Comparable> values = listShardingValue.getValues();
                    if (values != null) {
                        Iterator<Comparable> it = values.iterator();
                        while (it.hasNext()) {
                            Comparable value =;
                            String routTable = getRoutTable(shardingValue.getLogicTableName(), value);
                            if (StringUtils.isNotBlank(routTable)) {
                    //EQ condition
                } else if (shardingValue instanceof PreciseShardingValue) {
                    PreciseShardingValue preciseShardingValue = (PreciseShardingValue) shardingValue;
                    Comparable value = preciseShardingValue.getValue();
                    String routTable = getRoutTable(shardingValue.getLogicTableName(), value);
                    if (StringUtils.isNotBlank(routTable)) {
                    //Between condition
                } else if (shardingValue instanceof RangeShardingValue) {
                    RangeShardingValue rangeShardingValue = (RangeShardingValue) shardingValue;
                    Range<Comparable> valueRange = rangeShardingValue.getValueRange();
                    Comparable lowerEnd = valueRange.lowerEndpoint();
                    Comparable upperEnd = valueRange.upperEndpoint();
                    Collection<String> tables = getRoutTables(shardingValue.getLogicTableName(), lowerEnd, upperEnd);
                    if (tables != null && tables.size() > 0) {
                if (routTables != null && routTables.size() > 0) {
                    return routTables;
        throw new UnsupportedOperationException();

    private String getRoutTable(String logicTable, Comparable keyValue) {
        Map<String, List<KeyShardingRange>> keyRangeMap = KeyShardingRangeConfig.getKeyRangeMap();
        List<KeyShardingRange> keyShardingRanges = keyRangeMap.get(KeyShardingRangeConfig.SHARDING_ID_KEY);
        if (keyValue != null && keyShardingRanges != null) {
            if (keyValue instanceof Integer) {
                keyValue = Long.valueOf(((Integer) keyValue).intValue());
            for (KeyShardingRange range : keyShardingRanges) {
                if (keyValue.compareTo(range.getMin()) >= 0 && keyValue.compareTo(range.getMax()) <= 0) {
                    return logicTable + range.getTableKey();
        return null;
    private Collection<String> getRoutTables(String logicTable, Comparable lowerEnd, Comparable upperEnd) {
        Map<String, List<KeyShardingRange>> keyRangeMap = KeyShardingRangeConfig.getKeyRangeMap();
        List<KeyShardingRange> keyShardingRanges = keyRangeMap.get(KeyShardingRangeConfig.SHARDING_CONTENT_ID_KEY);
        Set<String> routTables = new HashSet<String>();
        if (lowerEnd != null && upperEnd != null && keyShardingRanges != null) {
            if (lowerEnd instanceof Integer) {
                lowerEnd = Long.valueOf(((Integer) lowerEnd).intValue());
            if (upperEnd instanceof Integer) {
                upperEnd = Long.valueOf(((Integer) upperEnd).intValue());
            boolean start = false;
            for (KeyShardingRange range : keyShardingRanges) {
                if (lowerEnd.compareTo(range.getMin()) >= 0 && lowerEnd.compareTo(range.getMax()) <= 0) {
                    start = true;
                if (start) {
                    routTables.add(logicTable + range.getTableKey());
                if (upperEnd.compareTo(range.getMin()) >= 0 && upperEnd.compareTo(range.getMax()) <= 0) {
        return routTables;

The scope map is as follows:

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
 *Partition key distribution configuration
public class KeyShardingRangeConfig {
    private static Map<String, List<KeyShardingRange>> keyRangeMap = new LinkedHashMap<String, List<KeyShardingRange>>();
    public static final String SHARDING_ORDER_ID_KEY = "id";
    public static final String SHARDING_USER_ID_KEY = "adminId";
    public static final String SHARDING_DATE_KEY = "createTime";
    static {
        List<KeyShardingRange> idRanges = new ArrayList<KeyShardingRange>();
        idRanges.add(new KeyShardingRange(0, "_0", 0L, 4000000L));
        idRanges.add(new KeyShardingRange(1, "_1", 4000001L, 8000000L));
        idRanges.add(new KeyShardingRange(2, "_2", 8000001L, 12000000L));
        idRanges.add(new KeyShardingRange(3, "_3", 12000001L, 16000000L));
        idRanges.add(new KeyShardingRange(4, "_4", 16000001L, 2000000L));
        keyRangeMap.put(SHARDING_ID_KEY, idRanges);
        List<KeyShardingRange> contentIdRanges = new ArrayList<KeyShardingRange>();
        contentIdRanges.add(new KeyShardingRange(0, "_0", 0L, 4000000L));
        contentIdRanges.add(new KeyShardingRange(1, "_1", 4000001L, 8000000L));
        contentIdRanges.add(new KeyShardingRange(2, "_2", 8000001L, 12000000L));
        contentIdRanges.add(new KeyShardingRange(3, "_3", 12000001L, 16000000L));
        contentIdRanges.add(new KeyShardingRange(4, "_4", 16000001L, 2000000L));
        keyRangeMap.put(SHARDING_CONTENT_ID_KEY, contentIdRanges);
        List<KeyShardingRange> timeRanges = new ArrayList<KeyShardingRange>();
        timeRanges.add(new KeyShardingRange("_0", 20170701L, 20171231L));
        timeRanges.add(new KeyShardingRange("_1", 20180101L, 20180630L));
        timeRanges.add(new KeyShardingRange("_2", 20180701L, 20181231L));
        timeRanges.add(new KeyShardingRange("_3", 20190101L, 20190630L));
        timeRanges.add(new KeyShardingRange("_4", 20190701L, 20191231L));
        keyRangeMap.put(SHARDING_DATE_KEY, timeRanges);
    public static Map<String, List<KeyShardingRange>> getKeyRangeMap() {
        return keyRangeMap;

Core logic analysis

To sort out the logic, first introduce the input parameters of this method

Parameter name                                           explain

availableTargetNames      A valid physical data source, t in the configuration file_ order_ 0,t_ order_ 1,t_ order_ 2,t_ order_ three

shardingValues              Partition attributes, such as: {columnname “:” order_id “,” logictable name “:” t_order “,” values “: [” ud020003000119032615436593200002 “]}, include: partition column name, logical table name, and specific partition value of the current column

The return value of this method is

Parameter name                                                  explain

Collection<String>        The fragmentation result can be a target data source or a target data table. Here is the data source

Then come back to the business logic. The pseudo code is as follows

First, print the values of the data source set availabletargetnames and shardingvalues. After executing the test case, the log output is:


It can be seen from the log that we can get the list of physical data sources during configuration in this routing method, and get the routing attributes and their values during this execution at run time

The complete logic flow is as follows:

  • Define a set to place the finally matched routing data source, and then traverse shardingvalues in order to hit at least one routing key
  • Traverse the shardingvalues loop body and print the shardingvalue of the current loop, that is, the value of the actual partition key, such as order number, user ID, etc. Get the physical data source index contained in the partition key value through the getindex method
  • Then traverse the data source list availabletargetnames, intercept the index value corresponding to availabletargetname in the current cycle, (eg: DS0 takes 0, DS1 takes 1… And so on) compare the configured physical data source index with the data source routing index parsed in step 2. If the two are equal, we expect to route the data to the matching data source.
  • This process is executed until a routing key is matched, and the loop will stop. The reason for this is that we are a composite fragment. We can only stop the loop after matching at least one routing rule. Finally, the physical data source (DS0 / DS1 / DS2 / DS3) routed to is added to the pre-defined set through the add method and returned to the framework.
  • Logical end.


In this paper, the user-defined implementation of composite fragment routing algorithm in sharding JDBC is basically completed, and it is tested and verified to meet the expectations. The implementation scheme has been tested in production. The core of defining fragment routing strategy is to be familiar with the complexkeys shardingalgorithm and have a clear understanding of how to parse the parameters of dosharding (collection available targetnames, collection shardingvalues). The simplest way is to actually print the parameters. I believe it will make you feel the author’s excellent interface design ability more intuitively, Standing on the shoulders of giants, we can see farther.

This is the end of this article about the implementation of sharding JDBC custom composite sharding. For more information about sharding JDBC custom composite sharding, please search previous articles of developeppaer or continue to browse the following related articles. I hope you will support developeppaer in the future!

Recommended Today

Design of IOS release self logoff mode

In daily development, we often register some notices and initiate some requests. When we don’t need them, we should cancel the notices and requests in time. Otherwise, there may be problems or crashes. For example, we will register some notifications in the viewdidload of the controller, and then log off the notifications in dealloc. Or […]