Sharding-JDBC之垂直分库水平分表
目录
- 一、简介
- 二、maven依赖
- 三、数据库
- 3.1、创建数据库
- 3.2、订单表
- 3.3、用户表
- 四、配置（二选一）
- 4.1、properties配置
- 4.2、yml配置
- 五、实现
- 5.1、实体
- 5.2、持久层
- 5.3、服务层
- 5.4、测试类
- 5.4.1、保存订单数据
- 5.4.2、查询订单数据
- 5.4.3、保存用户数据
- 5.4.4、查询用户数据
一、简介
这里的垂直分库分表是指 垂直分库 + 水平分表 ，怎么解释呢，一般是一个库中同时有订单表和用户表，随着数据增多，就把订单表和用户表单独变成两个库，达到专库专用。当订单库或用户库数据增多，然后分别对订单库和用户库的进行水平分表，一个库多个一样的表。先看下大致架构图：
数据流向图如下：
二、maven依赖
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 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.6.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.alian</groupId>
<artifactId>sharding-jdbc-vertical-database</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-vertical-database</name>
<description>sharding-jdbc-vertical-database</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
有些小伙伴的 druid 可能用的是 druid-spring-boot-starter
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
然后出现可能使用不了的各种问题，这个时候你只需要在主类上添加 @SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class}) 即可
package com.alian.shardingjdbc;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@SpringBootApplication
public class ShardingJdbcApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcApplication.class, args);
}
}
三、数据库
3.1、创建数据库
CREATE DATABASE `sharding_3` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `sharding_4` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3.2、订单表
在数据库sharding_3创建两张表：tb_order_1和tb_order_2，表的结构都是一样的。
tb_order_1
CREATE TABLE `tb_order_1` (
`order_id` bigint(20) NOT NULL COMMENT '主键',
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`price` int unsigned NOT NULL DEFAULT '0' COMMENT '价格（单位：分）',
`order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态(1:待付款,2:已付款,3:已取消)',
`order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '订单标题',
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
tb_order_2
CREATE TABLE `tb_order_2` (
`order_id` bigint(20) NOT NULL COMMENT '主键',
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`price` int unsigned NOT NULL DEFAULT '0' COMMENT '价格（单位：分）',
`order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态(1:待付款,2:已付款,3:已取消)',
`order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '订单标题',
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
3.3、用户表
在数据库sharding_4创建两张表：tb_user_1和tb_user_2，表的结构都是一样的。
CREATE TABLE `tb_user_1` (
`id` bigint(20) NOT NULL COMMENT '主键',
`user_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户姓名',
`age` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
CREATE TABLE `tb_user_2` (
`id` bigint(20) NOT NULL COMMENT '主键',
`user_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户姓名',
`age` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
四、配置（二选一）
4.1、properties配置
application.properties
server.port=8899
server.servlet.context-path=/sharding-jdbc
# 允许定义相同的bean对象去覆盖原有的
spring.main.allow-bean-definition-overriding=true
# 数据源名称，多数据源以逗号分隔
spring.shardingsphere.datasource.names=ds1,ds2
# sharding_1数据库连接池类名称
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
# sharding_1数据库驱动类名
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_1数据库url连接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.19.129:3306/sharding_3?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_1数据库用户名
spring.shardingsphere.datasource.ds1.username=alian
# sharding_1数据库密码
spring.shardingsphere.datasource.ds1.password=123456
# sharding_2数据库连接池类名称
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
# sharding_2数据库驱动类名
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_2数据库url连接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.19.130:3306/sharding_4?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_2数据库用户名
spring.shardingsphere.datasource.ds2.username=alian
# sharding_2数据库密码
spring.shardingsphere.datasource.ds2.password=123456
# 指定tb_order表的数据分布情况，配置数据节点，使用Groovy的表达式,逻辑表tb_order对应的节点是：ds1.tb_order_1, ds1.tb_order_2
spring.shardingsphere.sharding.tables.tb_order.actual-data-nodes=ds1.tb_order_$->{1..2}
# 采用行表达式分片策略：InlineShardingStrategy
# 指定tb_order表的分片策略中的分片键
spring.shardingsphere.sharding.tables.tb_order.table-strategy.inline.sharding-column=order_id
# 指定tb_order表的分片策略中的分片算法表达式,使用Groovy的表达式
spring.shardingsphere.sharding.tables.tb_order.table-strategy.inline.algorithm-expression=tb_order_$->{order_id%2==0?2:1}
# 指定tb_order表的主键为order_id
spring.shardingsphere.sharding.tables.tb_order.key-generator.column=order_id
# 指定tb_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_order.key-generator.type=SNOWFLAKE
# 指定雪花算法的worker.id
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.worker.id=100
# 指定雪花算法的max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.max.tolerate.time.difference.milliseconds=20
# 指定tb_user表的数据分布情况，配置数据节点，使用Groovy的表达式,逻辑表tb_user对应的节点是：ds2.tb_user_1, ds2.tb_user_2
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds2.tb_user_$->{1..2}
# 采用行表达式分片策略：InlineShardingStrategy
# 指定tb_user表的分片策略中的分片键
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=id
# 指定tb_user表的分片策略中的分片算法表达式,使用Groovy的表达式
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user_$->{id%2==0?2:1}
# 指定tb_user表的主键为order_id
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id
# 指定tb_user表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE
# 指定雪花算法的worker.id
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.worker.id=101
# 指定雪花算法的max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.max.tolerate.time.difference.milliseconds=20
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
4.2、yml配置
application.yml
server:
port: 8899
servlet:
context-path: /sharding-jdbc
spring:
main:
# 允许定义相同的bean对象去覆盖原有的
allow-bean-definition-overriding: true
shardingsphere:
props:
sql:
# 打开sql输出日志
show: true
datasource:
# 数据源名称，多数据源以逗号分隔
names: ds1,ds2
ds1:
# 数据库连接池类名称
type: com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据库url连接
url: jdbc:mysql://192.168.19.129:3306/sharding_3?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# 数据库用户名
username: alian
# 数据库密码
password: 123456
ds2:
# 数据库连接池类名称
type: com.alibaba.druid.pool.DruidDataSource
# 数据库驱动类名
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据库url连接
url: jdbc:mysql://192.168.19.130:3306/sharding_4?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# 数据库用户名
username: alian
# 数据库密码
password: 123456
sharding:
# 未配置分片规则的表将通过默认数据源定位
default-data-source-name: ds1
tables:
tb_order:
# 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式
actual-data-nodes: ds1.tb_order_$->{1..2}
# 分表策略
table-strategy:
# 行表达式分片策略
inline:
# 分片键
sharding-column: order_id
# 算法表达式
algorithm-expression: tb_order_$->{order_id%2==0?2:1}
# key生成器
key-generator:
# 自增列名称，缺省表示不使用自增主键生成器
column: order_id
# 自增列值生成器类型，缺省表示使用默认自增列值生成器（SNOWFLAKE/UUID）
type: SNOWFLAKE
# SnowflakeShardingKeyGenerator
props:
# SNOWFLAKE算法的worker.id
worker:
id: 100
# SNOWFLAKE算法的max.tolerate.time.difference.milliseconds
max:
tolerate:
time:
difference:
milliseconds: 20
tb_user:
# 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式
actual-data-nodes: ds2.tb_user_$->{1..2}
# 分表策略
table-strategy:
# 行表达式分片策略
inline:
# 分片键
sharding-column: id
# 算法表达式
algorithm-expression: tb_user_$->{id%2==0?2:1}
# key生成器
key-generator:
# 自增列名称，缺省表示不使用自增主键生成器
column: id
# 自增列值生成器类型，缺省表示使用默认自增列值生成器（SNOWFLAKE/UUID）
type: SNOWFLAKE
# SnowflakeShardingKeyGenerator
props:
# SNOWFLAKE算法的worker.id
worker:
id: 101
# SNOWFLAKE算法的max.tolerate.time.difference.milliseconds
max:
tolerate:
time:
difference:
milliseconds: 20
- 分库策略，这里采用的是行表达式分片策略，对于订单库order_id为奇数就放到ds1.tb_order_1数据源，order_id为偶数就放到ds1.tb_order_2；对于用户库user_id为奇数就放到ds2.tb_user_1数据源，user_id为偶数就放到ds2.tb_user_2，
- actual-data-nodes ：使用Groovy的表达式 ds1.tb_order_$->{1…2}，表示逻辑表tb_order对应的物理表是：ds1.tb_order_1、 ds1.tb_order_2；使用Groovy的表达式 ds2.tb_user_$->{1…2}，表示逻辑表tb_user对应的物理表是：ds2.tb_user_1、 ds2.tb_user_2
- key-generator ：key生成器，需要指定字段和类型，如果是SNOWFLAKE，最好也配置下props中的两个属性： worker.id 与 max.tolerate.time.difference.milliseconds 属性（主要还是yml中配置）
- table-strategy 表的分片策略，这里只是一个简单的奇数偶数，采用的是 行表达式分片策略 ，需要指定分片键和分片算法表达式（算法支持Groovy的表达式）
五、实现
5.1、实体
Order.java
@Data
@Entity
@Table(name = "tb_order")
public class Order implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "order_id")
private Long orderId;
@Column(name = "user_id")
private Integer userId;
@Column(name = "price")
private Integer price;
@Column(name = "order_status")
private Integer orderStatus;
@Column(name = "title")
private String title;
@Column(name = "order_time")
private LocalDateTime orderTime;
}
5.2、持久层
OrderRepository.java
public interface OrderRepository extends PagingAndSortingRepository<Order, Long> {
/**
* 根据订单id查询订单
* @param orderId
* @return
*/
Order findOrderByOrderId(Long orderId);
}
UserRepository.java
public interface UserRepository extends PagingAndSortingRepository<User, Long> {
/**
* 根据用户id查询订单
*
* @param id
* @return
*/
User findUserById(Long id);
}
5.3、服务层
OrderService.java
@Slf4j
@Service
public class OrderService {
@Autowired
private OrderRepository orderRepository;
public void saveOrder(Order order) {
orderRepository.save(order);
}
public Order queryOrder(Long orderId) {
return orderRepository.findOrderByOrderId(orderId);
}
}
UserService.java
@Slf4j
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public void saveUser(User user) {
userRepository.save(user);
}
public User queryUser(Long id) {
return userRepository.findUserById(id);
}
}
5.4、测试类
OrderTests.java
@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class OrderTests {
@Autowired
private OrderService orderService;
@Test
public void saveOrder() {
for (int i = 0; i < 10; i++) {
Order order = new Order();
order.setUserId(1000);
// 随机生成50到100的金额
int price = (int) Math.round(Math.random() * (10000 - 5000) + 5000);
order.setPrice(price);
order.setOrderStatus(2);
order.setOrderTime(LocalDateTime.now());
order.setTitle("");
orderService.saveOrder(order);
}
}
@Test
public void queryOrder() {
Long orderId = 845685274628734976L;
Order order = orderService.queryOrder(orderId);
log.info("查询的结果：{}", order);
}
UserTests.java
@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class UserTests {
@Autowired
private UserService userService;
@Test
public void saveUser() {
for (int i = 0; i < 8; i++) {
User user = new User();
// 随机生成50到100的金额
int age = (int) Math.round(Math.random() * (35 - 15) + 15);
user.setUserName("user-" + age);
user.setAge(age);
userService.saveUser(user);
}
}
@Test
public void queryUser() {
Long orderId = 845687246882754561L;
User user = userService.queryUser(orderId);
log.info("查询的结果：{}", user);
}
}
5.4.1、保存订单数据
运行结果：
15:31:21 465 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 465 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_2 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:20.964, 7687, , 1000, 845685274007977984]
15:31:21 521 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 521 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_1 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.519, 5283, , 1000, 845685274528071681]
15:31:21 544 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 545 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_2 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.543, 7447, , 1000, 845685274628734976]
15:31:21 568 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 569 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_1 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.567, 8478, , 1000, 845685274729398273]
15:31:21 589 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 589 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_2 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.588, 9612, , 1000, 845685274813284352]
15:31:21 610 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 611 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_1 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.609, 5153, , 1000, 845685274905559041]
15:31:21 637 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 638 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_2 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.636, 6311, , 1000, 845685275018805248]
15:31:21 692 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 693 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_1 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.691, 8013, , 1000, 845685275249491969]
15:31:21 715 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 716 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_2 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.714, 8992, , 1000, 845685275345960960]
15:31:21 737 INFO [main]:Logic SQL: insert into tb_order (order_status, order_time, price, title, user_id) values (?, ?, ?, ?, ?)
15:31:21 737 INFO [main]:Actual SQL: ds1 ::: insert into tb_order_1 (order_status, order_time, price, title, user_id, order_id) values (?, ?, ?, ?, ?, ?) ::: [2, 2023-03-23 15:31:21.736, 5229, , 1000, 845685275438235649]
效果图：
5.4.2、查询订单数据
从上面的结果我们可以看到order_id为 845685274628734976 的记录在 sharding_3 库的 tb_order_2 表，实际查询通过 ds1 去查询的请看下面的 Actual SQL
15:33:38 719 INFO [main]:Logic SQL: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order order0_ where order0_.order_id=?
15:33:38 720 INFO [main]:Actual SQL: ds1 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_2 order0_ where order0_.order_id=? ::: [845685274628734976]
15:33:38 772 INFO [main]:查询的结果：Order(orderId=845685274628734976, userId=1000, price=7447, orderStatus=2, title=, orderTime=2023-03-23T15:31:22)
5.4.3、保存用户数据
运行结果：
15:39:11 492 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 492 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_2 (age, user_name, id) values (?, ?, ?) ::: [23, user-23, 845687245477662720]
15:39:11 574 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 574 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_1 (age, user_name, id) values (?, ?, ?) ::: [21, user-21, 845687246077448193]
15:39:11 603 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 603 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_2 (age, user_name, id) values (?, ?, ?) ::: [16, user-16, 845687246194888704]
15:39:11 630 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 631 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_1 (age, user_name, id) values (?, ?, ?) ::: [22, user-22, 845687246312329217]
15:39:11 662 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 662 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_2 (age, user_name, id) values (?, ?, ?) ::: [32, user-32, 845687246446546944]
15:39:11 695 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 695 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_1 (age, user_name, id) values (?, ?, ?) ::: [20, user-20, 845687246580764673]
15:39:11 731 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 731 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_2 (age, user_name, id) values (?, ?, ?) ::: [16, user-16, 845687246731759616]
15:39:11 766 INFO [main]:Logic SQL: insert into tb_user (age, user_name) values (?, ?)
15:39:11 766 INFO [main]:Actual SQL: ds2 ::: insert into tb_user_1 (age, user_name, id) values (?, ?, ?) ::: [17, user-17, 845687246882754561]
效果图：
5.4.4、查询用户数据
运行结果：
15:43:18 497 INFO [main]:Logic SQL: select user0_.id as id1_1_, user0_.age as age2_1_, user0_.create_time as create_t3_1_, user0_.update_time as update_t4_1_, user0_.user_name as user_nam5_1_ from tb_user user0_ where user0_.id=?
15:43:18 498 INFO [main]:Actual SQL: ds2 ::: select user0_.id as id1_1_, user0_.age as age2_1_, user0_.create_time as create_t3_1_, user0_.update_time as update_t4_1_, user0_.user_name as user_nam5_1_ from tb_user_1 user0_ where user0_.id=? ::: [845687246882754561]
15:43:18 577 INFO [main]:查询的结果：User(id=845687246882754561, userName=user-17, age=17, createTime=2023-03-23 07:39:11.0, updateTime=2023-03-23 07:39:11.0)
效果图：
从上面的结果我们可以看到user_id为 845687246882754561 的记录在 sharding_4 库的 tb_user_1 表，实际查询通过 ds2 去查询的，请看下面的 Actual SQL