10,sharding-jdbc入门-基于Standard模式自定义分表逻辑实现

敲代码的翠花

已于 2024-01-22 20:12:49 修改

阅读量547

点赞数 13

分类专栏：项目：【今日指数金融】 # (九)分库分表专题文章标签： sharding-jdbc java

于 2024-01-12 15:30:45 首次发布

本文链接：https://blog.csdn.net/weixin_44478828/article/details/135552406

版权

项目：【今日指数金融】同时被 2 个专栏收录

70 篇文章 3 订阅

订阅专栏

(九)分库分表专题

10 篇文章 0 订阅

订阅专栏

实现精准查询和范围查询创建了两个类，为了简化，可以创建一个类，实现精准查询和范围查询两个接口，新建src/main/java/com/itheima/sharding/algorithm/common/CommonAlgorithm4Db.java

package com.itheima.sharding.algorithm.common;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

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

/**
 * @author by itheima
 * @Date 2022/6/11
 * @Description 定义精准查询数据库的算法类 接口中的泛型与数据库的片键类型一致
 * 保证片键中使用= in
 */
public class CommonAlgorithm4Db implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {

    /**
     * 定义匹配数据源的方法
     * @param dsNames 所有配置的数据源的集合ds${1..2} 包含ds1 ds2封装到该集合下
     *                说白了就是sharinding把所有的数据源都给你，然后让你根据片键值选择
     * @param shardingValue 封装分片相关信息
     * @return 返回匹配的数据源
     */
    @Override
    public String doSharding(Collection<String> dsNames, PreciseShardingValue<Long> shardingValue) {
        //获取数据库分片的字段名称 user_id = in
        String columnName = shardingValue.getColumnName();
        //获取逻辑表名称
        String logicTableName = shardingValue.getLogicTableName();
        //获取片键对应的值 select * from t_order where user_id=10,这里的value就等于10
        Long value = shardingValue.getValue();
        //一般是根据片键值获取对应的数据源，并返回
        String sufix=String.valueOf(value % 2 +1);
        String dsName = dsNames.stream().filter(ds -> ds.endsWith(sufix)).findFirst().get();
        return dsName;
    }

    /**
     *
     * @param dsNames 数据源集合
     * @param shardingValue 范围查询信息封装
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> dsNames, RangeShardingValue<Long> shardingValue) {
        //获取分片字段
        String columnName = shardingValue.getColumnName();
        //获取逻辑表
        String logicTableName = shardingValue.getLogicTableName();
        //获取范围数据
        Range<Long> valueRange = shardingValue.getValueRange();
        //select * from t_order where user_id between 1 and 20;
        //判断是否有上限值
        if (valueRange.hasUpperBound()) {
            //获取上限值 20
            Long uppper = valueRange.upperEndpoint();
            System.out.println(uppper);
        }
        if (valueRange.hasLowerBound()){
            Long lower = valueRange.lowerEndpoint();
            System.out.println(lower);
        }
        //理论上要根据上限值和下限值获取满足条件的数据源集合
        return Arrays.asList("ds1","ds2");
    }
}

修改test3文件

common.shard4db=com.itheima.sharding.algorithm.common.CommonAlgorithm4Db

#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.precise-algorithm-class-name= #精确分片算法类名称，用于=和IN。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.itheima.sharding.algorithm.MyPreciseShardingAlgorithm4Db

spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=${common.shard4db}

#spring.shardingsphere.sharding.tables.<logic-table-name>.database-strategy.standard.range-algorithm-class-name= #范围分片算法类名称，用于BETWEEN，可选。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
#spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=com.itheima.sharding.algorithm.MyRangeShardingAlgorithm4Db

spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.range-algorithm-class-name=${common.shard4db}

定义精确和范围找表的算法类

新建src/main/java/com/itheima/sharding/algorithm/common/CommonAlgorithm4Tb.java

package com.itheima.sharding.algorithm.common;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Collection;
import java.util.Optional;

public class CommonAlgorithm4Tb implements PreciseShardingAlgorithm<Long>, RangeShardingAlgorithm<Long> {
    /**
     * @param tbNames       数据表集合
     * @param shardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> tbNames, PreciseShardingValue<Long> shardingValue) {
//        获取逻辑表名称
        String logicTableName = shardingValue.getLogicTableName();
//        获取分片列名称
        String columnName = shardingValue.getColumnName();
//       获取分片值
        Long value = shardingValue.getValue();
        Optional<String> result = tbNames.stream().filter(tbName -> tbName.endsWith((value % 2 + 1) + "")).findFirst();
        if (result.isPresent()) {
            return result.get();
        }
        return null;
    }

    @Override
    public Collection<String> doSharding(Collection<String> tbNames, RangeShardingValue<Long> shardingValue) {
        Range<Long> valueRange = shardingValue.getValueRange();
        //判断是否存在下限值
        if (valueRange.hasLowerBound()) {
            //获取下限值
            Long lowerPoint = valueRange.lowerEndpoint();
        }
        if (valueRange.hasUpperBound()) {
            Long upperPoint = valueRange.upperEndpoint();

        }
        return tbNames;
    }

}

修改test3文件

common.shard4tb=com.itheima.sharding.algorithm.common.CommonAlgorithm4Tb

spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=${common.shard4dtb}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.range-algorithm-class-name=${common.shard4tb}

java测试代码

package com.itheima.sharding;

import com.itheima.sharding.entity.TOrder;
import com.itheima.sharding.mapper.TOrderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class MyTest2 {
    @Autowired
    private TOrderMapper tOrderMapper;

    /**
     * @Description 测试精准查询表
     */
    @Test
    public void testPrecTb(){
        //此时没有使用userid字段，所以数据库全量查询，但是每个库对应的表只查询t_order_1
        TOrder tOrder = tOrderMapper.selectByPrimaryKey(44l);
        System.out.println(tOrder);
    }
}

对应的mapper.xml

  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from t_order
        where  order_id = #{orderId,jdbcType=BIGINT}
    </select>

执行完查看控制台

2024-01-12 15:25:45.270 INFO 2004 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select

        order_id,price,user_id,
        status

        from t_order_1
        where order_id = ? ::: [44]
2024-01-12 15:25:45.270 INFO 2004 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds2 ::: select

        order_id,price,user_id,
        status

        from t_order_1
        where order_id = ? ::: [44]

java测试代码

package com.itheima.sharding;

import com.itheima.sharding.entity.TOrder;
import com.itheima.sharding.mapper.TOrderMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
public class MyTest2 {
    @Autowired
    private TOrderMapper tOrderMapper;
    
    @Test
    public void testRangeTable2(){
        List<TOrder> tOrders = tOrderMapper.selectByRange2(2l,1l, 40l);
        System.out.println(tOrders);
    }
}

对应的mapper.xml

 <select id="selectByRange2" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from t_order
        where  order_id between #{start} and #{end} and user_id=#{userId}
    </select>

运行完查看控制台

2024-01-12 15:28:57.305  INFO 7836 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select
         
        order_id,price,user_id,
        status
     
        from t_order_1
        where  order_id between ? and ? and user_id=? ::: [1, 40, 2]
2024-01-12 15:28:57.305  INFO 7836 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: select
         
        order_id,price,user_id,
        status
     
        from t_order_2
        where  order_id between ? and ? and user_id=? ::: [1, 40, 2]