ShardingSphere-JDBC垂直分片

文章目录

1、订单库 db_order
- 1.2、创建数据库
2、用户库 db_user
- 2.1、创建 atguigu-mysql-user 容器
- 2.2、登录 atguigu-mysql-user 容器
- 2.3、设置密码
- 2.4、创建数据库
3、创建实体类 TOrder
4、创建实体类 TUser
5、创建 TOrderMapper
6、创建 TUserMapper
7、application.yml
8、shardingsphere.yaml
9、ShardingJdbcDemoApplicationTests

垂直分片：在项目中可以将垂直分片到不同库中的多张表加载到一个项目中使用。

1、订单库 db_order

3306主 -> 3316从：订单库 db_order
订单库将来访问压力比较大，我们要考虑主从复制
3306和3316容器我们已经在 mysql搭建主从复制的时候创建好了，参考这篇博客：mysql-搭建主从复制

1.2、创建数据库

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

创建完之后，我们会发现从库3316没有同步创建数据库 db_order，因为当时我们在搭建mysql的主从复制的时候，只设置了复制mydb2，mydb3数据库到3316从库中。
在这里插入图片描述

2、用户库 db_user

3326主
因为用户的数据量没有那么大，并发操作没那么多，所以我们不需要考虑主从复制

2.1、创建 atguigu-mysql-user 容器

docker run -d \
-p 3326:3306 \
-v mysql-user-conf:/etc/mysql/conf.d \
-v mysql-user-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-user \
mysql:8

[root@localhost ~]# docker run -d \
> -p 3326:3306 \
> -v mysql-user-conf:/etc/mysql/conf.d \
> -v mysql-user-data:/var/lib/mysql \
> -e MYSQL_ROOT_PASSWORD=123456 \
> --name atguigu-mysql-user \
> mysql:8
d2e24da102d53cdeb3db591b8667facec3973a3fa1d2029e2d5306f3441ff574

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE                    COMMAND                   CREATED          STATUS          PORTS                                                                                  NAMES
d2e24da102d5   mysql:8                  "docker-entrypoint.s…"   28 seconds ago   Up 26 seconds   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp                                   atguigu-mysql-user
c236f876ae40   mysql:8                  "docker-entrypoint.s…"   11 days ago      Up 21 hours     33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp                                   atguigu-mysql-slave1
89bb2276fc3d   elasticsearch:8.8.2      "/bin/tini -- /usr/l…"   4 weeks ago      Up 22 hours     0.0.0.0:9200->9200/tcp, :::9200->9200/tcp, 0.0.0.0:9300->9300/tcp, :::9300->9300/tcp   elasticsearch
8c71efe9dca7   wurstmeister/zookeeper   "/bin/sh -c '/usr/sb…"   4 weeks ago      Up 22 hours     22/tcp, 2888/tcp, 3888/tcp, 0.0.0.0:2181->2181/tcp, :::2181->2181/tcp                  zookeeper
c14772057ab8   redis                    "docker-entrypoint.s…"   9 months ago     Up 22 hours     0.0.0.0:6379->6379/tcp, :::6379->6379/tcp                                              spzx-redis
ab66508d9441   mysql:8                  "docker-entrypoint.s…"   9 months ago     Up 22 hours     0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp                                   spzx-mysql

创建完容器，配置文件不需要改，因为 atguigu-mysql-user 容器我们不需要搭建主从复制。

2.2、登录 atguigu-mysql-user 容器

[root@localhost ~]# docker exec -it atguigu-mysql-user /bin/bash
root@d2e24da102d5:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.3、设置密码

-- 设置密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 刷新权限
FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

2.4、创建数据库

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

在这里插入图片描述

3、创建实体类 TOrder

package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("t_order")
public class TOrder {@TableId(type = IdType.AUTO)private Long id;private String orderNo;private Long userId;
}

4、创建实体类 TUser

package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_user")
public class TUser {private Long id;private String uname;}

5、创建 TOrderMapper

package com.atguigu.sharding.jdbc.demo.mapper;import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface TOrderMapper extends BaseMapper<TOrder> {
}

6、创建 TUserMapper

package com.atguigu.sharding.jdbc.demo.mapper;import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface TUserMapper extends BaseMapper<TUser> {
}

7、application.yml

spring:datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver# 驱动类：使用shardingjdbc提供的驱动类driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
#    username: root
#    password: 123456
#    url: jdbc:mysql://192.168.74.148:3306/mydb2?serverTimezone=GMT%2B8url: jdbc:shardingsphere:classpath:shardingsphere.yaml

8、shardingsphere.yaml

# 模式配置
mode:# shardingjdbc：使用的是单机模式type: Standalonerepository:type: JDBC
# 数据源配置
dataSources:write_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3306/mydb2username: rootpassword: 123456read_ds_0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3316/mydb2username: rootpassword: 123456user_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3326/db_userusername: rootpassword: 123456order_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3306/db_orderusername: rootpassword: 123456rules:# 读写分离配置：指定读交给哪个数据源 写交给哪个- !READWRITE_SPLITTINGdataSources:readwrite_ds:writeDataSourceName: write_dsreadDataSourceNames:- read_ds_0transactionalReadQueryStrategy: PRIMARY # 事务内读请求的路由策略，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC# 配置读操作的负载均衡策略loadBalancerName: randomloadBalancers:random:type: RANDOMround_robin:type: ROUND_ROBINweight:type: WEIGHTprops:read_ds_0: 1# 垂直分片配置- !SHARDINGtables:t_user:actualDataNodes: user_ds.t_usert_order:actualDataNodes: order_ds.t_order
# 输出sql：
props:# 打印 sharding 的sql日志sql-show: true

9、ShardingJdbcDemoApplicationTests

package com.atguigu.sharding.jdbc.demo;import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.atguigu.sharding.jdbc.demo.mapper.TOrderMapper;
import com.atguigu.sharding.jdbc.demo.mapper.TUserMapper;
import jakarta.annotation.Resource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest
class ShardingJdbcDemoApplicationTests {@ResourceTUserMapper tUserMapper;@ResourceTOrderMapper torderMapper;@Testvoid contextLoads() {TUser tUser = new TUser();tUser.setUname("张三");tUserMapper.insert(tUser);TOrder tOrder = new TOrder();tOrder.setOrderNo("1234");tOrder.setUserId(1L);torderMapper.insert(tOrder);}}

2024-09-25T11:53:56.395+08:00  INFO 15748 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_user  ( id,
uname )  VALUES  ( ?,
? )
2024-09-25T11:53:56.395+08:00  INFO 15748 --- [           main] ShardingSphere-SQL  Actual SQL: user_ds ::: INSERT INTO t_user  ( id,
uname )  VALUES  (?, ?) ::: [1838788985485291522, 张三]
2024-09-25T11:53:56.436+08:00  INFO 15748 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_order  ( order_no,
user_id )  VALUES  ( ?,
? )
2024-09-25T11:53:56.437+08:00  INFO 15748 --- [           main] ShardingSphere-SQL  Actual SQL: order_ds ::: INSERT INTO t_order  ( order_no,
user_id )  VALUES  (?, ?) ::: [1234, 1]