文章目录
目录
MySQL
一、数据库
id | ip | db |
---|---|---|
1 | 192.168.8.246 | MySQL |
2 | 192.168.8.247 | MySQL |
二、准备DDL
192.168.8.246
CREATE DATABASE sharding_order;
CREATE TABLE `t_order_1` (
`id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_2` (
`id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `area` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
192.168.8.247
CREATE DATABASE shard_order;
CREATE TABLE `t_order_1` (
`id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_2` (
`id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `area` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Code
Github: https://github.com/eddie-code/sharding-jdbc-demo
一、创建项目
SpringBoot
二、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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>sharding-jdbc-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-namespace/ -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.0-RC2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!-- mybatis生成器 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
三、ShardingJdbc - Spring命名空间方式 (原始Spring项目使用)
官方: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-namespace/
3.1、MyBatis生成代码
全局表也称为广播表, 通常用在需要 join 表时候使用
<table/>标签修改如下:
<table schema="sharding_order" tableName="area" domainObjectName="Area" ></table>
3.2、sharding-jdbc.xml 配置 Spring-namespace
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
">
<!-- MySQL数据源 -->
<bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="username" value="eddie" />
<property name="password" value="Abc@123456" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.8.246/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/>
</bean>
<bean id="ds1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="username" value="eddie" />
<property name="password" value="Abc@123456" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.8.247/shard_order?serverTimezone=Asia/Shanghai&useSSL=false"/>
</bean>
<!-- sharding-jdbc数据源 -->
<sharding:data-source id="sharding-data-source">
<!-- 多个数据源用逗号分割 -->
<sharding:sharding-rule data-source-names="ds0,ds1">
<sharding:table-rules>
<!-- 逻辑表名称 logic-table="t_order" 对应Mapper.xml里面的表名 -->
<!-- 数据节点 actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}" -->
<!-- 数据库分片策略 database-strategy-ref="databaseStrategy" -->
<!-- 数据表分片策略 table-strategy-ref="tableStrategy" -->
<sharding:table-rule logic-table="t_order"
actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"
database-strategy-ref="databaseStrategy"
table-strategy-ref="tableStrategy"
/>
</sharding:table-rules>
<!-- 广播表规则列表 -->
<sharding:broadcast-table-rules>
<sharding:broadcast-table-rule table="area"/>
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
<!-- 数据库分片规则 -->
<sharding:inline-strategy id="databaseStrategy"
sharding-column="user_id"
algorithm-expression="ds$->{user_id % 2}"
/>
<!-- 数据表分片规则 -->
<!-- id取模,可能会出现寻找到 t_order_0 的表,但实际上是没有的,所以 +1 -->
<sharding:inline-strategy id="tableStrategy"
sharding-column="id"
algorithm-expression="t_order_$->{id % 2 + 1}"
/>
<!-- 设置 mybatis 数据源 -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="sharding-data-source"/>
<property name="mapperLocations" value="classpath*:/mybatis/*.xml" />
</bean>
</beans>
3.3、 单元测试
@SpringBootTest
class ShardingJdbcDemoApplicationTests {
@Autowired
private OrderMapper orderMapper;
@Test
void contextLoads() {
}
@Test
public void testOrder() {
Order order = new Order();
order.setUserId(19);
order.setId(1);
order.setOrderAmount(BigDecimal.TEN);
order.setOrderStatus(1);
orderMapper.insert(order);
}
}
3.4、 根据分片规则
数据库分片规则: algorithm-expression=“dsKaTeX parse error: Expected group after '_' at position 79: …ession="t_order_̲->{id % 2 + 1}” # 1%2+1=2 既是 t_order_2
四、ShardingJdbc - SpringBoot Starter 方式
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-boot-starter/
4.1、 加入依赖
<!-- https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-namespace/ -->
<!-- <dependency>-->
<!-- <groupId>org.apache.shardingsphere</groupId>-->
<!-- <artifactId>sharding-jdbc-spring-namespace</artifactId>-->
<!-- <version>4.0.0-RC2</version>-->
<!-- </dependency>-->
<!-- https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-boot-starter/ -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
4.2 屏蔽启动类的注解
//@ImportResource(“classpath*:sharding-jdbc.xml”)
4.3 application.properties 配置
####################################################
#
# 博客:blog.eddilee.cn
# 备注:为了方便查看,使用 properties 而不是 yml 格式
#
####################################################
# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.8.246/sharding_order
spring.shardingsphere.datasource.ds0.username=eddie
spring.shardingsphere.datasource.ds0.password=Abc@123456
# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://192.168.8.247/shard_order
spring.shardingsphere.datasource.ds1.username=eddie
spring.shardingsphere.datasource.ds1.password=Abc@123456
# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{1..2}
# 配置 t_order 分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# 配置 t_order 分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy..inline..algorithm-expression=t_order_$->{id % 2 + 1}
# 配置 广播表规则列表
spring.shardingsphere.sharding.broadcast-tables=area
# 配置 mybatis
mybatis.mapper-locations=/mybatis/*.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 设置日志日期格式
logging.pattern.dateformat=yyyy-MM-dd HH:mm:ss.SSS
4.4 单元测试
@Test
public void testOrder() {
Order order = new Order();
order.setUserId(20);
order.setId(4);
order.setOrderAmount(BigDecimal.TEN);
order.setOrderStatus(1);
// 配置 t_order 分库策略
System.out.println("ds" + order.getUserId() % 2);
// 配置 t_order 分表策略
System.out.println("t_order_" + order.getId() % 2 + 1);
orderMapper.insert(order);
}
@Test
public void testSelectOrder() {
OrderExample orderExample = new OrderExample();
// select id, order_amount, order_status, user_id from t_order WHERE ( id = 4 and user_id = 20 )
orderExample.createCriteria().andIdEqualTo(4).andUserIdEqualTo(20);
List<Order> orders = orderMapper.selectByExample(orderExample);
orders.forEach(item -> System.out.println(item.getId() + "---" + item.getUserId()));
}
// 插入 全局表(广播表)
@Test
public void testGlobal() {
Area area = new Area();
area.setId(2);
area.setName("深圳");
areaMapper.insert(area);
}
// 测试 全局表(广播表)唯一性
@Test
public void testSelectGlobal() {
AreaExample areaExample = new AreaExample();
areaExample.createCriteria().andIdEqualTo(2);
List<Area> areas = areaMapper.selectByExample(areaExample);
System.out.println("area.size() = " + areas.size());
}