一、分库分表环境搭建
1、创建SpringBoot项目
2、引入相关maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roy</groupId>
<artifactId>ShardingSphereDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.3.1.RELEASE</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
</dependencies>
</project>
二、创建数据库和数据库表
1、创建order_1,order_2两个数据库
2、分表在order_1和order_2库中创建 t_order_1和t_order_2两个表
3、按照创建时间create_date 进行分库分表 2020年的订单放到order_1库里面,2021年创建的订单放到order_2库里面,并且订单月份是1-6月的订单放到t_order_1表里面,7-12月份的订单放到t_order_2表里面
三、具体代码实现
1、实体类代码
import com.baomidou.mybatisplus.annotation.TableName; @TableName("t_order") public class Order { private Long id; private String orderSn; private String memberId; private String createDate; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getOrderSn() { return orderSn; } public void setOrderSn(String orderSn) { this.orderSn = orderSn; } public String getMemberId() { return memberId; } public void setMemberId(String memberId) { this.memberId = memberId; } public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } }
2、数据库访问层
import com.roy.shardingDemo.entity.Order; public interface OrderMapper extends BaseMapper<Order> { }
3.application.properties配置分片规则
#配置多个数据源 spring.shardingsphere.datasource.names=m1,m2 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_1?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=liujing@888 spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/order_2?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=liujing@888 #真实表分布,分库,分表 spring.shardingsphere.sharding.tables.t_order.key-generator.column=id spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.t_order_$->{1..2} spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.sharding-columns= create_date, member_id spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.algorithm-class-name=com.xp.shardingDemo.algorithem.MemberScoreTableShardingAlgorithm # spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.sharding-columns=create_date, member_id spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.algorithm-class-name=com.xp.shardingDemo.algorithem.MemberScoreDSShardingAlgorithm spring.shardingsphere.props.sql.show = true spring.main.allow-bean-definition-overriding=true
上面配置文件配置了m1,m2两个数据源,数据真实的存储在m1,m2两个数据源的t_order_1 和t_order_2两个表里面,也就是order_1和order_2两个库的t_order_1和t_order_2里面,这里使用的分片键是create_date 和member_id两个列,具体的分表逻辑和分库逻辑对应了OrderTableShardingAlgorithm,OrderDSShardingAlgorithm这两个实现类
public class OrderDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> { @Override public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) { Collection<String> createDateCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("create_date"); Range<String> createRange = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("create_date"); List<String> res = new ArrayList<>(); if(createDateCol != null){ for(String createDate: createDateCol){ String str = createDate.substring(0,4); String target = ""; if(str.equals("2020")){ target = "1"; }else { target = "2"; } res.add("m"+target); } } if(createRange != null){ String start = createRange.lowerEndpoint(); String end = createRange.upperEndpoint(); return Arrays.asList("m1","m2"); } return res; } }
public class OrderTablehardingAlgorithm implements ComplexKeysShardingAlgorithm<String> { @Override public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) { Collection<String> createDateCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("create_date"); Range<String> createRange = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("create_date"); List<String> res = new ArrayList<>(); List<String> list1 = Arrays.asList("01","02","03","04","05","06"); if(createDateCol != null){ for(String createDate :createDateCol){ String str = createDate.substring(5,7); String target = ""; if(list1.contains(str)){ target = complexKeysShardingValue.getLogicTableName() + "_1"; }else { target = complexKeysShardingValue.getLogicTableName() + "_2"; } res.add(target); } } if(createRange != null) { String taget = complexKeysShardingValue.getLogicTableName() + "_1"; res.add(taget); return res; } return res; } }
@RunWith(SpringRunner.class) @SpringBootTest public class ShardingTest { @Resource private OrderMapper orderMapper; @Test public void saveOrder() { Order order1 = new Order(); order1.setMemberId("2344444"); order1.setOrderSn("20210412001"); order1.setCreateDate("2020-01-04 14:58:17"); orderMapper.insert(order1); Order order2 = new Order(); order2.setMemberId("2344444"); order2.setOrderSn("20210412001"); order2.setCreateDate("2020-10-04 14:58:23"); orderMapper.insert(order2); Order order3 = new Order(); order3.setMemberId("23444478"); order3.setOrderSn("20210412001"); order3.setCreateDate("2021-03-04 14:58:17"); orderMapper.insert(order3); Order order4 = new Order(); order4.setMemberId("23444478"); order4.setOrderSn("20210412001"); order4.setCreateDate("2021-10-04 14:45:17"); orderMapper.insert(order4); } }
执行单元测试插入4条订单数据,按照我们的需求以及分片规则,应该是order1里面t_order_1,t_order_2各有一条订单数据,order_2库里面t_order_1,t_order_2各有一条订单数据
以上就是Sharding-JDBC自定义分片算法的操作。若有问题可留言评论