使用shardingjdbc实现分库分表

近期有个开发消息中心的需求，考虑到数据量大，决定采用分库分表的处理方式，这里选用shardingjdbc来实现分库分表。

github源码下载地址

下面是整体的架构：

1 首先配置多数据源

application.yml文件

spring:
  shardingsphere:
    datasource:
      names: db0,db1
      db0:
        databaseName: db0
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/message0?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
        username: root
        password: root
      db1:
        databaseName: db1
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/message1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
        username: root
        password: root
      druid:
        #消息测试多数据源
        test:
          type: com.alibaba.druid.pool.DruidDataSource
          driverClassName: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/message_center?useUnicode=true&characterEncoding=UTF-8&useSSL=false
          username: root
          password: root
        # 初始连接数
        initialSize: 5
        # 最小连接池数量
        minIdle: 10
        # 最大连接池数量
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        # 配置一个连接在池中最大生存的时间，单位是毫秒
        maxEvictableIdleTimeMillis: 900000
        # 配置检测连接是否有效
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        webStatFilter:
          enabled: true
        statViewServlet:
          enabled: true
          # 设置白名单，不填则允许所有访问
          allow:
          url-pattern: /druid/*
          # 控制台管理用户名和密码
          login-username:
          login-password:
        filter:
          stat:
            enabled: true
            # 慢SQL记录
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true


  jpa:
    show-sql: true
    database-platform: org.hibernate.dialect.MySQL5Dialect
    database: mysql
    hibernate:
      ddl-auto: none
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    open-in-view: true
    properties:
      enable_lazy_load_no_trans: true

（1）数据源配置文件1

@Data
@Component
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db0")
public class DataBase0Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource data = new DruidDataSource();
        data.setDriverClassName(getDriverClassName());
        data.setUrl(getUrl());
        data.setUsername(getUsername());
        data.setPassword(getPassword());
        return data;
    }
}

(2)读取数据源配置文件2

@Data
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.db1")
@Component
public class DataBase1Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource data = new DruidDataSource();
        data.setDriverClassName(getDriverClassName());
        data.setUrl(getUrl());
        data.setUsername(getUsername());
        data.setPassword(getPassword());
        return data;
    }
}

2 配置数据源及分库分表策略

这里的分库、分表的算法可以根据业务需求进行自定义配置，我这里是根据id进行分库，根据acceptId进行分表

@Configuration
public class DataSourceConfig {

    @Autowired
    private DataBase0Config dataBase0Config;

    @Autowired
    private DataBase1Config dataBase1Config;

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        //分库设置
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        //添加两个数据库database0和database1
        dataSourceMap.put(dataBase0Config.getDatabaseName(), dataBase0Config.createDataSource());
        dataSourceMap.put(dataBase1Config.getDatabaseName(), dataBase1Config.createDataSource());

        KeyGeneratorConfiguration keyGeneratorConfiguration = new KeyGeneratorConfiguration("SNOWFLAKE","id");//主键生成策略
        List<TableRuleConfiguration> tableRuleConfigurations = new ArrayList<TableRuleConfiguration>();

        //分表设置，大致思想就是将查询虚拟表,根据一定规则映射到真实表中去
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("message", "db$->{0..1}.message_$->{0..1}");//分表规则
        tableRuleConfiguration.setKeyGeneratorConfig(keyGeneratorConfiguration);//设定主键分库策略
        StandardShardingStrategyConfiguration standardShardingStrategyConfiguration = new StandardShardingStrategyConfiguration("accept_id", new PreciseShardingAlgorithm() {
            @Override
            public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
                Long value = Long.parseLong(preciseShardingValue.getValue().toString());
                //自定义分表规则，根据需求添加分表方法，减轻服务器压力
                if (value % 2 == 0) {
                    return "message_0";
                } else {
                    return "message_1";
                }
            }
        });
        tableRuleConfiguration.setTableShardingStrategyConfig(standardShardingStrategyConfiguration);
        tableRuleConfigurations.add(tableRuleConfiguration);
        //分库分表策略
        ShardingRuleConfiguration configuration = new ShardingRuleConfiguration();
        //分表规则集
        configuration.setTableRuleConfigs(tableRuleConfigurations);
        //默认主键生成策略
        configuration.setDefaultKeyGeneratorConfig(keyGeneratorConfiguration);
        //不分库分表情况下的数据源制定--默认数据库
        configuration.setDefaultDataSourceName(dataBase0Config.getDatabaseName());
        //默认的分表规则
        configuration.setDefaultTableShardingStrategyConfig(standardShardingStrategyConfiguration);
        //单键分库规则
        StandardShardingStrategyConfiguration strategyConfiguration = new StandardShardingStrategyConfiguration("id", new PreciseShardingAlgorithm() {
            @Override
            public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
                Long value = Long.parseLong(preciseShardingValue.getValue().toString());
                if (value % 2 == 0) {
                    return dataBase0Config.getDatabaseName();
                } else {
                    return dataBase1Config.getDatabaseName();
                }
            }
        });
        configuration.setDefaultDatabaseShardingStrategyConfig(strategyConfiguration);
        Properties properties = new Properties();
        properties.setProperty(dataBase0Config.getDatabaseName(),dataBase0Config.createDataSource().toString());
        properties.setProperty(dataBase1Config.getDatabaseName(),dataBase1Config.createDataSource().toString());
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, configuration, null);
        return dataSource;
    }

}

到这里分库分表的配置就结束了，下面写个测试，来验证是否有效

（1）Bean

@Entity
@Table(name = "message")
@Data
@TypeDef(name = "json", typeClass = JsonStringType.class)
public class MessageBean implements Serializable {
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "accept_id")
    private Integer acceptId;//

    @Column(name = "name")
    private String  name;//

    @Column(name = "password")
    private String password;//
}

（2）dao

@Repository
public interface MessageRepository extends JpaRepository<MessageBean,Integer> {
}

(3) service

@Service
public class MessageService {

    @Autowired
    private MessageRepository messageRepository;


    public MessageEntity<?> findAll(MessageBean bean) {
        List<MessageBean> all = messageRepository.findAll();
        return ResultUtil.success(all);
    }

    public MessageEntity<?> add(MessageBean bean) {
        messageRepository.save(bean);
        return ResultUtil.success("新增成功");
    }
}

(4)ctrl

@RestController
@RequestMapping("/test/message")
public class MessageCtrl extends FrontBaseRestCtrl {

    @Autowired
    private MessageService messageService;

    @RequestMapping(value = "/getList",produces = "application/json",method = RequestMethod.POST )
    public MessageEntity<?> getSiteMessageList(@RequestBody MessageBean bean, HttpServletRequest req){
        return messageService.findAll(bean);
    }

    @RequestMapping(value = "/add",produces = "application/json",method = RequestMethod.POST )
    public MessageEntity<?> add(@RequestBody MessageBean bean, HttpServletRequest req){
        return messageService.add(bean);
    }

}

启动类

@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@EnableTransactionManagement(proxyTargetClass = true)
@EnableConfigurationProperties
@EnableJpaAuditing
public class ShardingTestApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingTestApplication .class, args);
    }
}

这里用postman进行数据测试

成功！！！下面看下数据库里的数据

完美实现！！！