java分库框架shard_Spring Boot 2.0 分库分表框架Sharding-Sphere 3.x 入门

本文介绍了如何使用Sharding-Sphere 3.x在Spring Boot 2.0项目中实现分库分表。通过配置数据源、创建数据库和表、实现分库分表算法,以及配置Sharding规则,展示了分库分表的完整流程。文章还提到了依赖的添加,包括Sharding-Sphere、MariaDB驱动、MyBatis等,并给出了分库分表策略和Mybatis的配置。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一年多之前我做一个项目的时候,需要分库分表,然后我就在网上搜索这方面的开源软件,遇见了Sharding-JDBC,当时是1.x版本,我发现用它来做分库分表比较容易上手。最近发现它已经是到3.x版本了,而且名字也改了,改为Sharding-Sphere。最近我使用了最新的版本做了一个例子。我使用的不是自己生产环境的例子,而是官网的例子,只是是在Spring Boot 2.0上做的,配置和官网给出的例子稍有不同。

用户首先到官网http://shardingsphere.io/index_zh.html了解一下这个框架。

一,我事先安装好了MariaDB,首先使用Spring Initializr生成项目,再添加sharding-sphere,MariaDB驱动依赖和spring-boot-configuration-processor,jdbc,mybatis,HikariCP依赖。

org.springframework.boot

spring-boot-starter-parent

2.0.4.RELEASE

UTF-8

UTF-8

1.8

3.0.0.M2

org.springframework.boot

spring-boot-starter-web

org.mariadb.jdbc

mariadb-java-client

2.2.6

com.zaxxer

HikariCP

3.2.0

org.springframework.boot

spring-boot-starter-jdbc

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

org.mybatis.generator

mybatis-generator-core

1.3.7

io.shardingsphere

sharding-jdbc

${sharding-sphere.version}

io.shardingsphere

sharding-core

${sharding-sphere.version}

org.springframework.boot

spring-boot-configuration-processor

true

org.projectlombok

lombok

1.18.2

org.springframework.boot

spring-boot-starter-test

test

二,创建数据库和表,以及在application.yml里配置数据源

创建ds_0数据库和表

CREATE DATABASE IF NOT EXISTS `ds_0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

USE `ds_0`;

CREATE TABLE IF NOT EXISTS `t_order_0` (

`order_id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_1` (

`order_id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_item_0` (

`order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

`order_id` bigint(20) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`order_item_id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_item_1` (

`order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

`order_id` bigint(20) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`order_item_id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

创建ds_1数据库和表

CREATE DATABASE IF NOT EXISTS `ds_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

USE `ds_1`;

CREATE TABLE IF NOT EXISTS `t_order_0` (

`order_id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_1` (

`order_id` bigint(20) NOT NULL AUTO_INCREMENT,

`user_id` int(11) NOT NULL,

`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_item_0` (

`order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

`order_id` bigint(20) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`order_item_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `t_order_item_1` (

`order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,

`order_id` bigint(20) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`order_item_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

在application.yml里配置数据源

spring:

datasource:

ds-0:

hikari:

jdbc-url: jdbc:mysql://localhost:3306/ds_0

driver-class-name: org.mariadb.jdbc.Driver

username: root

password: password

minimum-idle: 10

maximum-pool-size: 200

pool-name: DS0HikariCP

connection-timeout: 30000

connection-test-query: select 1

max-lifetime: 600000

idle-timeout: 120000

data-source-properties: {cachePrepStmts: true, prepStmtCacheSize: 250, prepStmtCacheSqlLimit: 2048, useServerPrepStmts: true}

ds-1:

hikari:

jdbc-url: jdbc:mysql://localhost:3306/ds_1

driver-class-name: org.mariadb.jdbc.Driver

username: root

password: password

minimum-idle: 10

maximum-pool-size: 200

pool-name: DS1HikariCP

connection-timeout: 30000

connection-test-query: select 1

max-lifetime: 600000

idle-timeout: 120000

data-source-properties: {cachePrepStmts: true, prepStmtCacheSize: 250, prepStmtCacheSqlLimit: 2048, useServerPrepStmts: true}

三,实现分库和分表的算法

分库算法:

public final class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm {

@Override

public String doSharding(final Collection availableTargetNames, final PreciseShardingValue shardingValue) {

int size = availableTargetNames.size();

for (String each : availableTargetNames) {

if (each.endsWith(shardingValue.getValue() % size + "")) {

return each;

}

}

throw new UnsupportedOperationException();

}

}

分表算法:

==和IN的分表算法实现

public final class TablePreciseShardingAlgorithm implements PreciseShardingAlgorithm {

@Override

public String doSharding(final Collection availableTargetNames, final PreciseShardingValue shardingValue) {

int size = availableTargetNames.size();

for (String each : availableTargetNames) {

if (each.endsWith(shardingValue.getValue() % size + "")) {

return each;

}

}

throw new UnsupportedOperationException();

}

}

Between的分表算法实现

public final class TableRangeShardingAlgorithm implements RangeShardingAlgorithm {

@Override

public Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) {

int size = collection.size();

Collection collect = new ArrayList<>();

Range valueRange = rangeShardingValue.getValueRange();

for (Long i = valueRange.lowerEndpoint(); i <= valueRange.upperEndpoint(); i++) {

for (String each : collection) {

if (each.endsWith(i % size + "")) {

collect.add(each);

}

}

}

return collect;

}

}

四,配置Sharding规则

Slf4j

@Configuration

public class ShardingConfig {

@ConfigurationProperties(prefix = "spring.datasource.ds-0.hikari")

@Bean(name = "ds_0")

public DataSource dataSource0() {

return new HikariDataSource();

}

@ConfigurationProperties(prefix = "spring.datasource.ds-1.hikari")

@Bean(name = "ds_1")

public DataSource dataSource1() {

return new HikariDataSource();

}

@Primary

@Bean(name = "shardingDataSource")

public DataSource getDataSource(@Qualifier("ds_0") DataSource ds_0, @Qualifier("ds_1") DataSource ds_1) throws SQLException {

ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());

shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());

shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");

shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DatabaseShardingAlgorithm()));

shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new TablePreciseShardingAlgorithm(), new TableRangeShardingAlgorithm()));

Map dataSourceMap = new HashMap<>();

dataSourceMap.put("ds_0", ds_0);

dataSourceMap.put("ds_1", ds_1);

Properties properties = new Properties();

// properties.setProperty("sql.show", Boolean.TRUE.toString());

return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new HashMap(), properties);

}

private static TableRuleConfiguration getOrderTableRuleConfiguration() {

TableRuleConfiguration result = new TableRuleConfiguration();

result.setLogicTable("t_order");

result.setActualDataNodes("ds_${0..1}.t_order_${[0, 1]}");

result.setKeyGeneratorColumnName("order_id");

return result;

}

private static TableRuleConfiguration getOrderItemTableRuleConfiguration() {

TableRuleConfiguration result = new TableRuleConfiguration();

result.setLogicTable("t_order_item");

result.setActualDataNodes("ds_${0..1}.t_order_item_${[0, 1]}");

return result;

}

}

五,我使用的是mybatis,我使用mybatis-generator生成了model和mapper文件,然后配置mybatis使用配置好的Sharding DataSource.

@Getter

@Setter

@AllArgsConstructor

@NoArgsConstructor

public class Order {

private Long orderId;

private Integer userId;

private String status;

}

@Getter

@Setter

@AllArgsConstructor

@NoArgsConstructor

public class OrderItem {

private Long orderItemId;

private Long orderId;

private Integer userId;

}

public interface OrderMapper {

List selectByUserId(Integer userId);

List selectByOrderIdBetween(@Param("startOrderId") Long startOrderId, @Param("endOrderId") Long endOrderId);

}

public interface OrderItemMapper {

...

}

xml文件我这里省略,不添加上来了,用户可以自己使用generator生成。

配置mybatis使用配置好的Sharding DataSource

@Configuration

@EnableTransactionManagement

@MapperScan(basePackages = "com.vcredit.sharding.mapper", sqlSessionTemplateRef = "shardSqlSessionTemplate")

public class MybatisConfig {

@Bean(name = "shardSqlSessionFactory")

public SqlSessionFactory shardSqlSessionFactory(@Qualifier("shardingDataSource") DataSource dataSource) throws Exception {

SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

bean.setDataSource(dataSource);

bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/shardingMapper/*.xml"));

bean.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis/mybatis-config.xml"));

return bean.getObject();

}

@Bean(name = "shardTransactionManager")

public DataSourceTransactionManager shardTransactionManager(@Qualifier("shardingDataSource") DataSource dataSource) {

return new DataSourceTransactionManager(dataSource);

}

@Bean(name = "shardSqlSessionTemplate")

public SqlSessionTemplate shardSqlSessionTemplate(@Qualifier("shardSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

return new SqlSessionTemplate(sqlSessionFactory);

}

}

最后测试,这里我省略不写,用户自己去完成。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值