一:pom.xml相关引用
<?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">
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>lab-18-sharding-datasource-01</artifactId>
<dependencies>
<!--web依赖:tomcat,dispatcherServlet,xml..-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.webjars</groupId>
<artifactId>jquery</artifactId>
<version>3.4.1</version>
</dependency>
<!-- 实现对数据库连接池的自动化配置 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency> <!-- 本示例,我们使用 MySQL -->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<!-- 实现对 MyBatis 的自动化配置 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<!-- 实现对 Sharding-JDBC 的自动化配置 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<!-- 保证 Spring AOP 相关的依赖包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
</dependency>
<!-- 方便等会写单元测试 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
</project>
二:启动类Application
@SpringBootApplication
@MapperScan(basePackages = "cn.iocoder.springboot.lab18.shardingdatasource.mapper")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
三:yaml配置
server:
port: 8126
spring:
# ShardingSphere 配置项
shardingsphere:
datasource:
# 所有数据源的名字
names: ds-orders-0, ds-orders-1
# 订单 orders 数据源配置 00
ds-orders-0:
type: com.zaxxer.hikari.HikariDataSource # 使用 Hikari 数据库连接池
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3310/lab18_orders_0?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: root
# 订单 orders 数据源配置 01
ds-orders-1:
type: com.zaxxer.hikari.HikariDataSource # 使用 Hikari 数据库连接池
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3310/lab18_orders_1?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: root
# 分片规则
sharding:
tables:
# orders 表配置
orders:
# actualDataNodes: ds-orders-$->{0..1}.orders_$->{0..4} # 映射到 ds-orders 数据源的 orders 表
# actualDataNodes: ds-orders-0.orders_0, ds-orders-0.orders_2, ds-orders-0.orders_4, ds-orders-0.orders_6, ds-orders-1.orders_1, ds-orders-1.orders_3, ds-orders-1.orders_5, ds-orders-1.orders_7
actualDataNodes: ds-orders-0.orders_$->{[0,2,4,6]}, ds-orders-1.orders_$->{[1,3,5,7]} # 映射到 ds-orders-0 和 ds-orders-1 数据源的 orders 表们
key-generator: # 主键生成策略
column: id
type: SNOWFLAKE
database-strategy:
inline:
algorithm-expression: ds-orders-$->{user_id % 2}
sharding-column: user_id
table-strategy:
inline:
algorithm-expression: orders_$->{user_id % 8}
sharding-column: user_id
# order_config 表配置
order_config:
actualDataNodes: ds-orders-0.order_config # 仅映射到 ds-orders-0 数据源的 order_config 表
# 拓展属性配置
props:
sql:
show: true # 打印 SQL
# mybatis 配置内容
mybatis:
config-location: classpath:mybatis-config.xml # 配置 MyBatis 配置文件路径
mapper-locations: classpath:mapper/*.xml # 配置 Mapper XML 地址
type-aliases-package: cn.iocoder.springboot.lab18.shardingdatasource.dataobject # 配置数据库实体包路径
四:MyBatis配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- 使用驼峰命名法转换字段。 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<typeAlias alias="Integer" type="java.lang.Integer"/>
<typeAlias alias="Long" type="java.lang.Long"/>
<typeAlias alias="HashMap" type="java.util.HashMap"/>
<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
<typeAlias alias="ArrayList" type="java.util.ArrayList"/>
<typeAlias alias="LinkedList" type="java.util.LinkedList"/>
</typeAliases>
</configuration>
五:实体类
五.一:OrderDO
package cn.iocoder.springboot.lab18.shardingdatasource.dataobject;
/**
* 订单 DO
*/
public class OrderDO {
/**
* 订单编号
*/
private Long id;
/**
* 用户编号
*/
private Integer userId;
public Long getId() {
return id;
}
public OrderDO setId(Long id) {
this.id = id;
return this;
}
public Integer getUserId() {
return userId;
}
public OrderDO setUserId(Integer userId) {
this.userId = userId;
return this;
}
@Override
public String toString() {
return "OrderDO{" +
"id=" + id +
", userId=" + userId +
'}';
}
}
在 lab18_orders_0
数据库下,创建 orders_0
、orders_2
、orders_4
、orders_6
数据表。SQL 如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for orders_0
-- ----------------------------
DROP TABLE IF EXISTS `orders_0`;
CREATE TABLE `orders_0` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_2
-- ----------------------------
DROP TABLE IF EXISTS `orders_2`;
CREATE TABLE `orders_2` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_4
-- ----------------------------
DROP TABLE IF EXISTS `orders_4`;
CREATE TABLE `orders_4` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_6
-- ----------------------------
DROP TABLE IF EXISTS `orders_6`;
CREATE TABLE `orders_6` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
SET FOREIGN_KEY_CHECKS = 1;
在 lab18_orders_1
数据库下,创建 orders_1
、orders_3
、orders_5
、orders_7
数据表。SQL 如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for orders_1
-- ----------------------------
DROP TABLE IF EXISTS `orders_1`;
CREATE TABLE `orders_1` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=400675304294580226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_3
-- ----------------------------
DROP TABLE IF EXISTS `orders_3`;
CREATE TABLE `orders_3` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_5
-- ----------------------------
DROP TABLE IF EXISTS `orders_5`;
CREATE TABLE `orders_5` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
-- ----------------------------
-- Table structure for orders_7
-- ----------------------------
DROP TABLE IF EXISTS `orders_7`;
CREATE TABLE `orders_7` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
`user_id` int(16) DEFAULT NULL COMMENT '用户编号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单表';
SET FOREIGN_KEY_CHECKS = 1;
五.二:OrderConfigDO
// OrderConfigDO.java
/**
* 订单配置 DO
*/
public class OrderConfigDO {
/**
* 编号
*/
private Integer id;
/**
* 支付超时时间
*
* 单位:分钟
*/
private Integer payTimeout;
// ... 省略 setting/getting 方法
}
在 lab18_orders_0
数据库下,创建 orders_0
数据表。SQL 如下:
-- ----------------------------
-- Table structure for order_config
-- ----------------------------
DROP TABLE IF EXISTS `order_config`;
CREATE TABLE `order_config` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`pay_timeout` int(11) DEFAULT NULL COMMENT '支付超时时间;单位:分钟',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='订单配置表';
六:Mapper
六.一:OrderMapper
// OrderMapper.java
@Repository
public interface OrderMapper {
OrderDO selectById(@Param("id") Integer id);
List<OrderDO> selectListByUserId(@Param("userId") Integer userId);
void insert(OrderDO order);
}
六.二:OrderMapper.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="cn.iocoder.springboot.lab18.shardingdatasource.mapper.OrderMapper">
<sql id="FIELDS">
id, user_id
</sql>
<select id="selectById" parameterType="Integer" resultType="OrderDO">
SELECT
<include refid="FIELDS" />
FROM orders
WHERE id = #{id}
</select>
<select id="selectListByUserId" parameterType="Integer" resultType="OrderDO">
SELECT
<include refid="FIELDS" />
FROM orders
WHERE user_id = #{userId}
</select>
<insert id="insert" parameterType="OrderDO" useGeneratedKeys="true" keyProperty="id">
INSERT INTO orders (
user_id
) VALUES (
#{userId}
)
</insert>
</mapper>
六.三:OrderConfigMapper
// OrderConfigMapper.java
@Repository
public interface OrderConfigMapper {
OrderConfigDO selectById(@Param("id") Integer id);
}
六.四OrderConfigMapper.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="cn.iocoder.springboot.lab18.shardingdatasource.mapper.OrderConfigMapper">
<sql id="FIELDS">
id, pay_timeout
</sql>
<select id="selectById" parameterType="Integer" resultType="OrderConfigDO">
SELECT
<include refid="FIELDS" />
FROM order_config
WHERE id = #{id}
</select>
</mapper>
总结:
- 在
mybatis
配置项下,设置mybatis-spring-boot-starter
自动化配置 MyBatis 需要的参数。 - 在
spring.shardingsphere
配置项下,设置sharding-jdbc-spring-boot-starter
自动化配置 Sharding-JDBC 需要的参数。比较复杂,我们一个一个来看。
spring.shardingsphere.datasource
配置项,我们配置了 ds-orders-0
和 ds-orders-1
两个数据源,分别对应 lab18_orders_0
和 lab18_orders_1
两个数据库。
spring.shardingsphere.sharding
配置项,我们配置了 orders
和 order_config
逻辑表 。
逻辑表 :水平拆分的数据库(表)的相同逻辑和数据结构表的总称。例:订单数据根据主键尾数拆分为 10 张表,分别是
t_order_0
到t_order_9
,他们的逻辑表名为t_order
。真实表 :在分片的数据库中真实存在的物理表。即上个示例中的
t_order_0
到t_order_9
。数据节点 :数据分片的最小单元。由数据源名称和数据表组成,例:
ds_0.t_order_0
。
orders
配置项,设置orders
逻辑表,使用分库分表的规则。actualDataNodes
:对应的数据节点,使用的是行表达式 。这里的意思是,ds-orders-0.orders_0
,ds-orders-0.orders_2
,ds-orders-0.orders_4
,ds-orders-0.orders_6
,ds-orders-1.orders_1
,ds-orders-1.orders_3
,ds-orders-1.orders_5
,ds-orders-1.orders_7
。key-generator
:主键生成策略。这里采用分布式主键 SNOWFLAKE 方案。更多可以看 《 ShardingSphere > 概念 & 功能 > 数据分片 > 其他功能 > 分布式主键》 文档。database-strategy
:按照index = user_id % 2
分库,路由到ds-orders-${index}
数据源(库)。table-strategy
:index = user_id % 8
分表,路由到orders_${index}
数据表。
order_config
配置项,设置order_config
逻辑表,不使用分库分表。actualDataNodes
:对应的数据节点,只对应数据源(库)为ds-orders-0
的order_config
表。
spring.shardingsphere.props
配置项,设置拓展属性配置。
sql.show
:设置打印 SQL 。因为我们编写的 SQL 会被 Sharding-JDBC 进行处理,实际执行的可能不是我们编写的,通过打印,方便我们观察和理解。