分享一个利用sharding shpere(jdbc)实现的分库分表源码

在分布式开发中,某些表数据量比较大时,通常会选择分库分表作为解决方案。常用的中间件有mycat, sharding jdbc等。由于sharding jdbc比较轻量,而且是在客户端做的分库分表,使用起来比较方便。所以我也用sharding jdbc 做了一个例子。后面有源码下载。

前期准备:
1. 两个数据库:shard_jdbc_1, shard_jdbc_2
1. 在两个数据库中都创建表 user_info, 另外为了测试不分表的情况,在shard_jdbc_1中创建 user_account 表, 语句分别如下:
程序代码 程序代码

Create TABLE `user_info`  (
  `user_id` bigint(19) NOT NULL,
  `user_name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `account` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

Create TABLE `user_account`  (
  `flow_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `account` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`flow_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


2. 工程搭建,采用spring boot, maven 工程。比较简单:


3. 分库分表的核心代码,采用在java代码中配置,另外也可以采用配置文件方式。可以参考官网说明.
程序代码 程序代码

package com.yihaomen.shardingjava.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

/**
* 分片数据源配置,返回 ShardingDataSource
*/
@Configuration
@MapperScan(basePackages = "com.yihaomen.shardingjava.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {
    @Bean
    @Primary
    public DataSource shardingDataSource() throws SQLException {
        ShardingRuleConfiguration src = new ShardingRuleConfiguration();
        // 默认的分库策略
        src.setDefaultDataSourceName("ds0");
        //src.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DBShardAlgo()));
        // 默认的分表策略
        //src.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",  new TblPreShardAlgo(), new TblRangeShardAlgo()));
        // 为user_info表设置分库分表策略、算法
        src.getTableRuleConfigs().add(getUserTableRuleConfiguration());
        // 数据源名和数据源的映射表
        
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), src, new Properties());
    }

    // 配置数据源
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put("ds0", DataSourceUtil.createDataSource("shard_jdbc_1"));
        result.put("ds1", DataSourceUtil.createDataSource("shard_jdbc_2"));
        return result;
    }

    // 事务管理器
    @Bean
    public DataSourceTransactionManager transactitonManager(DataSource shardingDataSource) {
        return new DataSourceTransactionManager(shardingDataSource);
    }

    // 为user_info表设置分库分表策略、算法
    public TableRuleConfiguration getUserTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("user_info", "ds${0..1}.user_info");
        result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
        // 分库策略
        result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DBShardAlgo(), new DBRangeShardAlgo() ));
        // 分表策略
        result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id",  new TblPreShardAlgo(), new TblRangeShardAlgo() ));

        return result;
    }
    
    private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
        KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "user_id");
        return result;
    }

}

4. 测试方法有两种,一是通过单原测试类:UserShardingTest, 另一种方式,通过Controller 测试都行。
5. 结果对于user_info 成功实现分库分表, 对于user_account 采用默认库,不分库分表.

6. 源代码下载:


下载文件 sharding jdbc 分库分表源码下载


除非申明,文章均为一号门原创,转载请注明本文地址,谢谢!
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: sharding jdbc
相关日志:
评论: 0 | 引用: 0 | 查看次数: -
发表评论
昵 称:
密 码: 游客发言不需要密码.
内 容:
验证码: 验证码
选 项:
虽然发表评论不用注册,但是为了保护您的发言权,建议您注册帐号.