JDBC-垂直分片

垂直分片有什么用？有两个数据库，分别是用户数据库和订单数据库，垂直分片的作用就是，如果操作用户表则去用户数据库操作，如果操作订单表就去订单表操作。

1. 准备服务器

服务器规划：使用docker方式创建如下容器

1.1 创建server-user容器

docker run -d \
-p 3301:3306 \
-v /xk857/server/user/conf:/etc/mysql/conf.d \
-v /xk857/server/user/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-user \
mysql:8.0.29

创建数据库：

CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
    id BIGINT AUTO_INCREMENT,
    uname VARCHAR(30),
    PRIMARY KEY (id)
);

1.2 创建server-order容器

docker run -d \
-p 3302:3306 \
-v /xk857/server/order/conf:/etc/mysql/conf.d \
-v /xk857/server/order/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order \
mysql:8.0.29

创建数据库：

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order (
    id BIGINT AUTO_INCREMENT,
    order_no VARCHAR(30),
    user_id BIGINT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id) 
);

2. 程序实现

2.1 创建实体类

@Data
@TableName("t_order")
public class Order {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String orderNo;
    private Long userId;
    private BigDecimal amount;
}

2.2 创建Mapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

2.3 配置垂直分片

# 应用名称
spring.application.name=distributed-sharding
# 开发环境设置
spring.profiles.active=dev
# 内存模式
spring.shardingsphere.mode.type=Memory

# 配置真实数据源
spring.shardingsphere.datasource.names=server-order,server-user

# 配置第 1 个数据源
spring.shardingsphere.datasource.server-user.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-user.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-user.jdbc-url=jdbc:mysql://192.168.31.115:3301/db_user
spring.shardingsphere.datasource.server-user.username=root
spring.shardingsphere.datasource.server-user.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.server-order.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.server-order.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.server-order.jdbc-url=jdbc:mysql://192.168.31.115:3302/db_order
spring.shardingsphere.datasource.server-order.username=root
spring.shardingsphere.datasource.server-order.password=123456

# 标准分片表配置（数据节点）
# 如果操作t_user表则会到server-user.t_user表操作，t_order亦是如此
spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=server-user.t_user
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=server-order.t_order

# 打印SQl
spring.shardingsphere.props.sql-show=true

3. 测试垂直分片

测试插入数据和查询数据能否正常使用

@SpringBootTest
class DistributedShardingApplicationTests {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void testInsertOrderAndUser() {
        User user = new User();
        user.setUname("强哥");
        userMapper.insert(user);

        Order order = new Order();
        order.setOrderNo("ATGUIGU001");
        order.setUserId(user.getId());
        order.setAmount(new BigDecimal(100));
        orderMapper.insert(order);
    }

    @Test
    public void testSelectFromOrderAndUser() {
        User user = userMapper.selectById(1L);
        Order order = orderMapper.selectById(1L);
    }
}