分库分表ShardingJDBC介绍和实战
Sharding-JDBC是开源的数据库中间件。Sharding-JDBC定位为轻量级数据库驱动，由客户端直连数据库，以jar包形式提供服务，没有使用中间层，无须额外部署，无须其他依赖。Sharding-JDBC可以实现旧代码迁移零成本的目标。Sharding-JDBC与MyCat不同，MyCat本质上是一种数据库代理。
2 实战SQL
2.1 有2个库
shop_0
shop_info_0、shop_info_1
shop_1
shop_info_0、shop_info_1
2.2 表结构SQL
DROP DATABASE IF EXISTS `shop_0`;CREATE DATABASE `shop_0`;
USE `shop_0`;
CREATE TABLE `shop_info_0` (
`shop_id` bigint(80) NOT NULL,
`shop_name` varchar(45) DEFAULT NULL,
`account` varchar(45) NOT NULL,
PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `shop_info_1` (
`shop_id` bigint(80) NOT NULL,
`shop_name` varchar(45) DEFAULT NULL,
`account` varchar(45) NOT NULL,
PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP DATABASE IF EXISTS `shop_1`;
CREATE DATABASE `shop_1`;
USE `shop_1`;
CREATE TABLE `shop_info_0` (
`shop_id` bigint(80) NOT NULL,
`shop_name` varchar(45) DEFAULT NULL,
`account` varchar(45) NOT NULL,
PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `shop_info_1` (
`shop_id` bigint(80) NOT NULL,
`shop_name` varchar(45) DEFAULT NULL,
`account` varchar(45) NOT NULL,
PRIMARY KEY (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3 实战项目
3.1 pom依赖
<dependency> <groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
3.2 项目结构
3.3 路由配置类 DataSourceConfig
package com.test.sharding.config;import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import io.shardingjdbc.core.api.config.TableRuleConfiguration;
import io.shardingjdbc.core.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingjdbc.core.jdbc.core.datasource.ShardingDataSource;
import io.shardingjdbc.core.keygen.DefaultKeyGenerator;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* sharding-jdbc数据源配置
*
* 本例有2个库：
* shop_0 里面有2个表 shop_info_0、shop_info_1
* shop_1 里面有2个表 shop_info_0、shop_info_1
*
* 分库规则实现类(自定义)：{@see DemoDatabaseShardingAlgorithm}
* 分表规则实现类(自定义)：{@see DemoTableShardingAlgorithm}
* 主键生成实现类(包里自带)：{@see DefaultKeyGenerator}
*/
@Configuration
public class DataSourceConfig {
/**
* sharding-jdbc数据源
*/
@Bean(name = "shardingDataSource")
DataSource getShardingDataSource() throws SQLException {
// 分库分表规则配置类
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
// 表规则配置
shardingRuleConfiguration.getTableRuleConfigs().add(getShopTableRuleConfiguration());
// 表的组 shop_info
shardingRuleConfiguration.getBindingTableGroups().add("shop_info");
// DataBase的分片策略，具体规则 DemoDatabaseShardingAlgorithm#doSharding 来实现
String shardingColumnDB = "shop_id";
String preciseAlgorithmClassNameDB = DemoDatabaseShardingAlgorithm.class.getName();
StandardShardingStrategyConfiguration databaseStrategyConfi = new StandardShardingStrategyConfiguration(
shardingColumnDB, preciseAlgorithmClassNameDB);
shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(databaseStrategyConfi);
// Table的分片策略，具体规则 DemoTableShardingAlgorithm#doSharding 来实现
String shardingColumnTable = "shop_id";
String preciseAlgorithmClassNameTable = DemoTableShardingAlgorithm.class.getName();
StandardShardingStrategyConfiguration tableStrategyConfig = new StandardShardingStrategyConfiguration(
shardingColumnTable, preciseAlgorithmClassNameTable);
shardingRuleConfiguration.setDefaultTableShardingStrategyConfig(tableStrategyConfig);
// 根据配置实例化一个ShardingDataSource bean
return new ShardingDataSource(shardingRuleConfiguration.build(createDataSourceMap()));
}
/**
* 商户表规则配置
*/
@Bean
TableRuleConfiguration getShopTableRuleConfiguration() {
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
// 逻辑表是shop_info
orderTableRuleConfig.setLogicTable("shop_info");
// 实际的物理节点是shop_${0..1}.shop_info_${0..1} 即database.table
orderTableRuleConfig.setActualDataNodes("shop_${0..1}.shop_info_${0..1}");
// 主键生成的列名
orderTableRuleConfig.setKeyGeneratorColumnName("shop_id");
// 主键生成类，可根据需要自定义实现
orderTableRuleConfig.setKeyGeneratorClass(DefaultKeyGenerator.class.getName());
return orderTableRuleConfig;
}
/**
* 封装多个MySQL数据源
*/
private Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>(2);
result.put("shop_0", createDataSource("shop_0"));
result.put("shop_1", createDataSource("shop_1"));
return result;
}
/**
* MySQL数据源
*/
private DataSource createDataSource(final String dataSourceName) {
BasicDataSource result = new BasicDataSource();
result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
result.setUrl(String.format("jdbc:mysql://localhost:3306/%s?characterEncoding=utf-8&useSSL=false",
dataSourceName));
result.setUsername("root");
result.setPassword("123456");
return result;
}
}
3.4 分库规则实现类
package com.test.sharding.config;import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/**
* 数据库分片的计算逻辑
*/
public class DemoDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 数据库分片
* @param collection 本例：存的是 shop_0、shop_1
* @param preciseShardingValue 分表主键值，本例是 shop_id
* @return
*/
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Long> preciseShardingValue) {
for (String databaseName : collection) {
// 数据库后缀名
// preciseShardingValue.getValue() % 2 = 1 奇数则匹配 shop_1 库
// preciseShardingValue.getValue() % 2 = 0 偶数则匹配 shop_0 库
String suffix = String.valueOf(preciseShardingValue.getValue() % 2);
//如果数据库后缀 = suffix 则选择这个库
if (databaseName.endsWith(suffix)) {
return databaseName;
}
}
throw new IllegalArgumentException("参数异常");
}
}
3.5 分表规则实现类
package com.test.sharding.config;import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/**
* 数据表的分片规则
*/
public class DemoTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 每个库里有2个表
* @param collection 本实例，里面是 shop_info_0 表、shop_info_1 表
* @param preciseShardingValue 分表主键值，本例是 shop_id
* @return
*/
@Override
public String doSharding(Collection<String> collection,
PreciseShardingValue<Long> preciseShardingValue) {
for (String tableName : collection) {
// 表的后缀名
// preciseShardingValue.getValue() % 2 = 1 奇数则匹配 shop_info_1 表
// preciseShardingValue.getValue() % 2 = 0 偶数则匹配 shop_info_0 表
String suffix = String.valueOf(preciseShardingValue.getValue() % 2);
//如果表的后缀 = suffix 则选择这个表
if (tableName.endsWith(suffix)) {
return tableName;
}
}
throw new IllegalArgumentException("参数异常");
}
}
3.6 xml配置
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.sharding.dao.ShopInfoDao">
<resultMap id="BaseResultMap" type="com.test.sharding.entity.ShopInfo">
<id column="shop_id" jdbcType="BIGINT" property="shopId" />
<result column="shop_name" jdbcType="VARCHAR" property="shopName" />
<result column="account" jdbcType="VARCHAR" property="account" />
</resultMap>
<sql id="Base_Column_List">
shop_id, shop_name, account
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from shop_info
where shop_id = #{shopId,jdbcType=BIGINT}
</select>
<insert id="insert" parameterType="com.test.sharding.entity.ShopInfo">
insert into shop_info
(shop_id, shop_name, account)
values
(
#{shopId,jdbcType=BIGINT},
#{shopName,jdbcType=VARCHAR},
#{account,jdbcType=VARCHAR}
)
</insert>
<!-- 主键根据主键生成策略生成 -->
<insert id="insertAutoIncrement" parameterType="com.test.sharding.entity.ShopInfo">
insert into shop_info (shop_name, account)
values (
#{shopName,jdbcType=VARCHAR},
#{account,jdbcType=VARCHAR}
)
</insert>
</mapper>
3.7 单元测试类
package com.test.sharding;import com.alibaba.fastjson.JSON;
import com.test.sharding.entity.ShopInfo;
import com.test.sharding.service.ShopService;
import io.shardingjdbc.core.api.HintManager;
import io.shardingjdbc.core.hint.HintManagerHolder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.annotation.Resource;
/**
* 单元测试
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring-sharding.xml")
public class ShopInfoTest {
@Resource
ShopService shopService;
public static Long shopId = 0L;
/**
* 模拟保存商户
*/
@Test
public void saveShop() {
//保存10个商户
for (int i = 0; i < 10; i++) {
ShopInfo shopInfo = new ShopInfo();
shopInfo.setShopId(shopId++);
shopInfo.setAccount("Account" + i);
shopInfo.setUserName("name" + i);
shopService.saveShop(shopInfo);
}
}
/**
* 模拟查询商户
*/
@Test
public void queryShop() {
ShopInfo shopInfo = shopService.queryShop(1L);
System.out.printf("商户信息是=%s%n", JSON.toJSONString(shopInfo));
}
/**
* 模拟保存商户
*/
@Test
public void saveShop2() {
//保存10个商户
for (int i = 10; i < 20; i++) {
ShopInfo shopInfo = new ShopInfo();
shopInfo.setShopId(Long.valueOf(i));
shopInfo.setAccount("Account" + i);
shopInfo.setUserName("name" + i);
//强制修改路由规则
if(i == 15) {
HintManagerHolder.clear();
HintManager hintManager = HintManager.getInstance();
hintManager.addDatabaseShardingValue("shop_info", "shop_id", 2L);
hintManager.addTableShardingValue("shop_info", "shop_id", 2L);
}
shopService.saveShop(shopInfo);
}
}
/**
* 测试主键自增
*/
@Test
public void saveShop3() throws InterruptedException {
//保存10个商户
for (int i = 0; i < 10; i++) {
int finalI = i;
new Thread(new Runnable() {
@Override
public void run() {
ShopInfo shopInfo = new ShopInfo();
shopInfo.setAccount("Account" + finalI);
shopInfo.setUserName("name" + finalI);
try {
Thread.sleep(1);
} catch (InterruptedException e) {
e.printStackTrace();
}
shopService.saveShopAutoIncrement(shopInfo);
}
}).start();
}
Thread.sleep(1000);
}
}
以上是 分库分表ShardingJDBC介绍和实战 的全部内容， 来源链接： utcz.com/z/513626.html
