SpringBoot-Sharding-JDBC分片

利用Sharding-JDBC实现分库分表，写了一个demo，做一下总结
分库分表后的结构为

定义分库分表的规则，通过user_id进行分库分表。表的路由逻辑为user_id除以6后的模，分库的路由逻辑为user_id除以3后的模。

/**
     * 获取user表的分片规则
     * @return
     */
    private TableRuleConfiguration getUserRuleConfig(){
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        orderTableRuleConfig.setLogicTable("t_user");
        orderTableRuleConfig.setActualDataNodes("db${0..2}.t_user_0${0..1}");

        // 配置分库策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(
                new InlineShardingStrategyConfiguration("user_id",
                        "db${user_id % 3}"));

        // 配置分表策略
        orderTableRuleConfig.setTableShardingStrategyConfig(
                new InlineShardingStrategyConfiguration(    "user_id",
                        "t_user_0${user_id % 2}"));
        return orderTableRuleConfig;
    }

三个库的连接信息

//------------------------00数据库-----------------------
    @Value("${spring.datasource00.driverClassName}")
    private String driverClassName00;

    @Value("${spring.datasource00.url}")
    private String url00;

    @Value("${spring.datasource00.username}")
    private String username00;

    @Value("${spring.datasource00.password}")
    private String password00;

    //------------------------01数据库-----------------------
    @Value("${spring.datasource01.driverClassName}")
    private String driverClassName01;

    @Value("${spring.datasource01.url}")
    private String url01;

    @Value("${spring.datasource01.username}")
    private String username01;

    @Value("${spring.datasource01.password}")
    private String password01;

    //------------------------02数据库-----------------------
    @Value("${spring.datasource02.driverClassName}")
    private String driverClassName02;

    @Value("${spring.datasource02.url}")
    private String url02;

    @Value("${spring.datasource02.username}")
    private String username02;

    @Value("${spring.datasource02.password}")
    private String password02;

	/**
     * 获取数据源
     * @return
     */
    public Map<String,DataSource> getDataSource(){
        Map<String, DataSource> dataSourceMap=new HashMap<>();

        DruidDataSource dataSource00 =new  DruidDataSource();
        dataSource00.setDriverClassName(this.driverClassName00);
        dataSource00.setUrl(this.url00);
        dataSource00.setUsername(this.username00);
        dataSource00.setPassword(this.password00);
        dataSourceMap.put("db0", dataSource00);

        DruidDataSource dataSource01 =new  DruidDataSource();
        dataSource01.setDriverClassName(this.driverClassName01);
        dataSource01.setUrl(this.url01);
        dataSource01.setUsername(this.username01);
        dataSource01.setPassword(this.password01);
        dataSourceMap.put("db1", dataSource01);

        DruidDataSource dataSource02 =new  DruidDataSource();
        dataSource02.setDriverClassName(this.driverClassName02);
        dataSource02.setUrl(this.url02);
        dataSource02.setUsername(this.username02);
        dataSource02.setPassword(this.password02);
        dataSourceMap.put("db2", dataSource02);
        return dataSourceMap;
    }

配置数据源及添加user表分片逻辑

@Bean(name = "dataSource")
    @Qualifier("dataSource")
    public DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getUserRuleConfig());

        // 获取数据源对象
        DataSource dataSource = null;
        try {
            dataSource = ShardingDataSourceFactory.createDataSource(getDataSource(),
                    shardingRuleConfig,new ConcurrentHashMap<>(),new Properties());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

测试-先生成各个物理表，即ds00.t_user_00,ds00.t_user_01,ds01.t_user_00,ds01.t_user_01,ds02.t_user_00,ds00.t_user_01,
创建表的sql

<update id="createTUserTableIfNotExist">
    CREATE TABLE IF NOT EXISTS `t_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `username` varchar(255) DEFAULT NULL,
    `password` varchar(255) DEFAULT NULL,
    `create_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </update>

执行该接口即可。

@Test
    public void createTable(){
        Integer tUserTableIfNotExist = userMapper.createTUserTableIfNotExist();
        logger.info("---------{}-----------",tUserTableIfNotExist);
        logger.info("run over");
    }

添加数据

<insert id="insertSelective" parameterType="com.test.sharding.jdbc.entity.TUserDO"
          useGeneratedKeys="true" keyProperty="id">
    insert into t_user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      user_id,
      username,
      `password`,
      create_time,
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      #{userId,jdbcType=INTEGER},
      #{username,jdbcType=VARCHAR},
      #{password,jdbcType=VARCHAR},
      #{createTime,jdbcType=TIMESTAMP},
    </trim>
  </insert>

添加20条数据，useri_id自增

@Test
    public void insert(){
        for (int i=0;i<20;i++){
            try {
                Thread.sleep(5000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            TUserDO tUserDO=new TUserDO();
            tUserDO.setUserId(i);
            tUserDO.setCreateTime(new Date());
            userMapper.insertSelective(tUserDO);
        }
    }

查询数据在各个表中的分布请求，通过查询所有的数据

<select id="selectAll" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_user
  </select>

@Test
    public void select(){
        List<TUserDO> lstTUserDOS = userMapper.selectAll();
        List<TUserDO> collect = lstTUserDOS.stream().
                sorted(Comparator.comparing(tUserDOS -> tUserDOS.getUserId())).collect(Collectors.toList());
        for (TUserDO tUserDO : collect) {
            System.out.println(JSONObject.toJSONString(tUserDO));
        }
    }

打印结果为：

{"createTime":1564898840000,"id":1,"userId":0}
{"createTime":1564898846000,"id":1,"userId":1}
{"createTime":1564898851000,"id":1,"userId":2}
{"createTime":1564898856000,"id":1,"userId":3}
{"createTime":1564898861000,"id":1,"userId":4}
{"createTime":1564898866000,"id":1,"userId":5}
{"createTime":1564898871000,"id":2,"userId":6}
{"createTime":1564898876000,"id":2,"userId":7}
{"createTime":1564898881000,"id":2,"userId":8}
{"createTime":1564898886000,"id":2,"userId":9}
{"createTime":1564898891000,"id":2,"userId":10}
{"createTime":1564898896000,"id":2,"userId":11}
{"createTime":1564898901000,"id":3,"userId":12}
{"createTime":1564898906000,"id":3,"userId":13}
{"createTime":1564898911000,"id":3,"userId":14}
{"createTime":1564898916000,"id":3,"userId":15}
{"createTime":1564898921000,"id":3,"userId":16}
{"createTime":1564898926000,"id":3,"userId":17}
{"createTime":1564898931000,"id":4,"userId":18}
{"createTime":1564898936000,"id":4,"userId":19}

可知，均匀的分布到了各个表中

查询

  <select id="selectByUserId" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_user
    WHERE user_id=#{userId}
  </select>

    TUserDO selectByUserId(Integer userId);

参考：
https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/configuration/
https://www.cnblogs.com/mr-yang-localhost/p/8280500.html