shardingsphere-jdbc之complex分片(4.1.1)

complex复合分片：基于多个字段进行数据分片。

PS：当前案例根据区域和时间双维度进行分片。

yml

        t_user_activity:
          actualDataNodes: ds0.t_user_activity_south_$->{['2022_0103','2022_0406','2022_0709','2022_1012']},ds0.t_user_activity_north_$->{['2022_0103','2022_0406','2022_0709','2022_1012']}
          tableStrategy:
            #复合条件分片--》多字段分片，字段之间以逗号隔开，PS：不止两个字段，多个也可以
            complex:
              shardingColumns: area,create_date
              algorithmClassName: com.example.skywalkingtest.config.sharding.AreaAndMonthComplexKeysShardingAlgorithm

自定义算法： 

import cn.hutool.core.collection.CollectionUtil;
import com.example.skywalkingtest.utils.DateUtils;
import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.util.*;


/**
 * 4.1version 复合分片
 */

@Slf4j
public class AreaAndMonthComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
    static String tableNamePre = "t_user_activity_";
    static Map<String, List<String>>areaMap;
    static Map<String, List<String>>monthMap;
    static {
        areaMap = new HashMap<String, List<String>>(){{
            put("north",new LinkedList<String>(){{add("天津");add("北京");add("西安");add("咸阳");}});
            put("south",new LinkedList<String>(){{add("苏州");add("南京");add("上海");add("杭州");}});
        }};
        monthMap = new HashMap<String,List<String>>(){{
            put("2022_0103",new LinkedList<String>(){{add("2022_01");add("2022_02");add("2022_03");}});
            put("2022_0406",new LinkedList<String>(){{add("2022_04");add("2022_05");add("2022_06");}});
            put("2022_0709",new LinkedList<String>(){{add("2022_07");add("2022_08");add("2022_09");}});
            put("2022_1012",new LinkedList<String>(){{add("2022_10");add("2022_11");add("2022_12");}});
        }};
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) {
        log.info("collection:{},complexKeysShardingValue:{}",collection,complexKeysShardingValue);
        List<String> areas = new ArrayList<>();
        List<String> months = new ArrayList<>();
        Map<String, Collection<String>> columnNameAndShardingValuesMap = complexKeysShardingValue.getColumnNameAndShardingValuesMap();
        if(CollectionUtil.isNotEmpty(columnNameAndShardingValuesMap)){
            Iterator<Map.Entry<String, Collection<String>>> iterator = columnNameAndShardingValuesMap.entrySet().iterator();
            //筛选出需要拼接的数据信息
            while (iterator.hasNext()){
                Map.Entry<String, Collection<String>> next = iterator.next();
                String key = next.getKey();
                Collection<String> value = next.getValue();
                switch (key){
                    case "area":
                        areas.addAll(this.dataHandler(areaMap,value));
                        break;
                    case "create_date":
                        try {
                            List<String> cllectionValue = new ArrayList<>(value.size());
                            Iterator<String> it = value.iterator();
                            while (it.hasNext()){
                                String next1 = it.next();
                                Date date = DateUtils.strToDateForyyyy_MM_dd(next1);
                                String s = DateUtils.dateToStrForyyyy_MM(date);
                                cllectionValue.add(s);
                            }
                            months.addAll(this.dataHandler(monthMap,cllectionValue));
                        }catch (Exception e){
                            log.info("exception:{}",e);
                        }
                        break;
                    default:
                        break;
                }
            }
        }
        Map<String, Range<String>> columnNameAndRangeValuesMap = complexKeysShardingValue.getColumnNameAndRangeValuesMap();
        if(CollectionUtil.isNotEmpty(columnNameAndRangeValuesMap)){
            Iterator<Map.Entry<String, Range<String>>> rangeIterator = columnNameAndRangeValuesMap.entrySet().iterator();
            //筛选出需要拼接的数据信息
            while (rangeIterator.hasNext()){
                Map.Entry<String, Range<String>> next = rangeIterator.next();
                String key = next.getKey();
                Range<String> value = next.getValue();
                Map<String,String> map = new HashMap<String,String>();
                switch (key){
                    case "area":
                        map.put("lowerEndpoint",value.lowerEndpoint());
                        map.put("upperEndpoint",value.upperEndpoint());
                        areas.addAll(this.rangeDataHandler(areaMap,map));
                        break;
                    case "create_date":
                        try {
                            Date date = DateUtils.strToDateForyyyy_MM_dd(value.lowerEndpoint());
                            String lowerEndpoint = DateUtils.dateToStrForyyyy_MM(date);
                            Date date1 = DateUtils.strToDateForyyyy_MM_dd(value.upperEndpoint());
                            String upperEndpoint = DateUtils.dateToStrForyyyy_MM(date1);
                            map.put("lowerEndpoint",lowerEndpoint);
                            map.put("upperEndpoint",upperEndpoint);
                            months.addAll(this.rangeDataHandler(monthMap,map));
                        }catch (Exception e){
                            log.info("exception:{}",e);
                        }
                        break;
                    default:
                        break;
                }
            }
        }
        Set<String> tableNames = new HashSet<>();
        
/**
         * todo 组装要路由的表
         *  没有区域信息==>全区域查询,有区域信息==>指定区域查询
         *  没有时间范围==>全年查询,有时间范围==>指定时间查询
         */

        Collection<String> areaCollection = CollectionUtil.isEmpty(areas)?areaMap.keySet():areas;
        Collection<String> monthCollection = CollectionUtil.isEmpty(months)?monthMap.keySet():months;
        for (String k : areaCollection) {
            String sb = new StringBuilder(tableNamePre).append(k).append("_").toString();
            for (String m : monthCollection) {
                tableNames.add(new StringBuilder(sb).append(m).toString());
            }
        }
        return tableNames;
    }

    public List<String> dataHandler(Map<String, List<String>> map,Collection<String> value){
        List<String> list = new LinkedList<>();
        for (String k : map.keySet()) {
            List<String> v = map.get(k);
            long count = v.stream().filter(o -> value.contains(o)).count();
            if(count>0)
                list.add(k);
        }
        return list;
    }

    public List<String> rangeDataHandler(Map<String, List<String>> map,Map<String,String> mapRange){
        String upperEndpoint = mapRange.get("upperEndpoint");
        String lowerEndpoint = mapRange.get("lowerEndpoint");
        List<String> list = new LinkedList<>();
        for (String k : map.keySet()) {
            List<String> v = map.get(k);
            long count = v.stream().filter(o -> upperEndpoint.equalsIgnoreCase(o)||lowerEndpoint.equalsIgnoreCase(o)).count();
            if(count>0)
                list.add(k);
        }
        return list;
    }
}

表结构： 

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * 用户参与活动记录
 */
@Data
@TableName("t_user_activity")
public class UserActivityRecordPo implements Serializable {
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;
    @TableField(value = "area")
    private String area;
    @TableField(value = "user_name")
    private String userName;
    @TableField(value = "create_date")
    private String createDate;
    @TableField(value = "activity_id")
    private Long activityId;
    @TableField(value = "activity_name")
    private String activityName;
    @TableField(value = "scope")
    private Integer scope;
}

 测试：

@Test
    void insertUserActivityRecordDemo(){
        Date date = DateUtils.strToDateForyyyy_MM_dd("2022-03-01");
        userActivityMapper.insert(new UserActivityRecordPo("上海","jack","2022-03-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("苏州","jack","2022-05-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("南京","jack","2022-04-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("杭州","jack","2022-07-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("上海","jack","2022-12-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("上海","jack","2022-09-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("北京","jack","2022-03-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("天津","jack","2022-05-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("西安","jack","2022-06-01",1l,"生活常识竞赛",85));
        userActivityMapper.insert(new UserActivityRecordPo("咸阳","jack","2022-07-01",1l,"生活常识竞赛",85));
    }

    @Test
    void queryUserActivityDemo(){
        List<UserActivityRecordPo> list = userActivityMapper.selectList(new LambdaQueryWrapper<UserActivityRecordPo>()
                .between(UserActivityRecordPo::getCreateDate,"2022-04-01","2022-08-01")
                .in(UserActivityRecordPo::getArea,new ArrayList<String>(){{add("咸阳");add("西安");add("上海");}})
                .ge(UserActivityRecordPo::getScope,20));
        System.out.println(list);
    }