shardingspheresharding-jdbc配置和使⽤教程
sharding-jdbc使⽤教程
概念
分库分表
分库：从单个数据库拆分成多个数据库的过程，将数据散落在多个数据库中。
分表：从单张表拆分成多张表的过程，将数据散落在多张表内。
本⽂主要针对⽔平拆分。
Sharding-JDBC
Sharding-JDBC是⼀套开源的分布式数据库中间件解决⽅案，定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使⽤客户端直连数据库，以jar包形式提供服务，⽆需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
逻辑表
⽔平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为2张表，分别是biz_order_1到biz_order_1，他们的逻辑表名为biz _order。
真实表
在分⽚的数据库中真实存在的物理表。即上个⽰例中的biz_order_1到t_order_2。
数据节点
数据分⽚的最⼩单元。由数据源名称和数据表组成，例：m_1.biz_order_1。
环境要求
jdk版本：1.8
mysql版本：5.7
spring-boot版本：2.2.1.RELEASE
sharding-jdbc版本：4.1.1
Sharding-JDBC配置
⾏表达式
⾏表达式标识符可以使⽤${...}或$->{...}，但前者与Spring本⾝的属性⽂件占位符冲突，因此在Spring环境中使⽤⾏表达式标识符建议使⽤$->{...}。
代码
项⽬依赖
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/><!-- lookup parent from repository -->
</parent>
<groupId>com.sharding</groupId>
<artifactId>sharding-demo</artifactId>
<version>1</version>
<name>sharding-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</artifactId>
springboot结构</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.70</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
配置
application.properties分⽚配置。
#定义两个数据源m1,m2
spring.shardingsphere.datasource.names=m1,m2
#配置数据源具体内容，包含连接池，驱动，地址，⽤户名和密码
#m1数据源配置
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.sql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.1.1:3306/edu_db_1?characterEncoding=utf-8&serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
#m2数据源配置
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.sql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.1.2:3306/edu_db_2?characterEncoding=utf-8&serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456
#指定数据库分布情况，数据库⾥⾯表分布情况
# m1 m2 biz_order_1 biz_order_2
spring.shardingsphere.sharding.tables.biz_order.actual-data-nodes=m$->{1..2}.biz_order_$->{1..2}
# 指定 biz_order 表⾥⾯主键 order_id ⽣成策略SNOWFLAKE（雪花算法）
spring.shardingsphere.sharding.tables.biz_lumn=order_id
spring.shardingsphere.sharding.tables.biz_pe=SNOWFLAKE
# 指定数据库分⽚策略是偶数添加 m1，是奇数添加 m2
spring.shardingsphere.sharding.tables.biz_order.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.biz_order.database-strategy.inline.algorithm-expression=m$->{order_id %2+1}
#指定数据表分⽚策略按order_user_id进⾏分表偶数到表1奇数到2
spring.shardingsphere.sharding.tables.biz_order.table-strategy.inline.sharding-column=order_user_id
spring.shardingsphere.sharding.tables.biz_order.table-strategy.inline.algorithm-expression=biz_order_$->{order_user_id%2+1}
#订单item
spring.shardingsphere.sharding.tables.biz_order_item.actual-data-nodes=m$->{1..2}.biz_order_item_$->{1..2}
# 指定 biz_order_item 表⾥⾯主键 cid ⽣成策略SNOWFLAKE
spring.shardingsphere.sharding.tables.biz_order_lumn=order_item_id
spring.shardingsphere.sharding.tables.biz_order_pe=SNOWFLAKE
# 指定数据库分⽚策略是偶数添加 m1，是奇数添加 m2
spring.shardingsphere.sharding.tables.biz_order_item.database-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.biz_order_item.database-strategy.inline.algorithm-expression=m$->{order_id %2+1}
#指定数据表分⽚策略按order_user_id进⾏分表偶数到表1奇数到2
spring.shardingsphere.sharding.tables.biz_order_item.table-strategy.inline.sharding-column=order_user_id
spring.shardingsphere.sharding.tables.biz_order_item.table-strategy.inline.algorithm-expression=biz_order_item_$->{order_user_id%2+1}
#设置绑定关系
spring.shardingsphere.sharding.binding-tables=biz_order,biz_order_item
#默认数据源，即未配置分表规则的表数据存储表
spring.shardingsphere.sharding.default-data-source-name=m1
# 打开 sql 输出⽇志
spring.shardingsphere.props.sql.show=true
# mybatis配置
mybatis-plus.mapper-locations=classpath:mapper/*.xml
数据库初始化
⾸先准备两个数据库，例如本⽂中的为192.168.1.1:3306/edu_db_1,192.168.1.2:3306/edu_db_2.初始化表：
biz_order表：
CREATE TABLE `biz_order_1` (
`order_id` bigint(20) NOT NULL,
`order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
`order_money` decimal(20,2) DEFAULT NULL,
`order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
`order_user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `biz_order_2` (
`order_id` bigint(20) NOT NULL,
`order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
`order_time` datetime DEFAULT NULL,
`order_money` decimal(20,2) DEFAULT NULL,
`order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
`order_user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
biz_order_item表：
CREATE TABLE `biz_order_item_1` (
`order_item_id` bigint(20) NOT NULL,
`order_id` bigint(20) DEFAULT NULL,
`goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`goods_num` int(11) DEFAULT NULL,
`order_user_id` int(11) DEFAULT NULL,
`goods_price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TABLE `biz_order_item_2` (
`order_item_id` bigint(20) NOT NULL,
`order_id` bigint(20) DEFAULT NULL,
`goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`goods_num` int(11) DEFAULT NULL,
`order_user_id` int(11) DEFAULT NULL,
`goods_price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
源码⽚段
代码结构
代码清单
启动类：
@SpringBootApplication
@MapperScan(value ="com.sharding.shardingdemo.dao") public class ShardingDemoApplication {
public static void main(String[] args){
SpringApplication.run(ShardingDemoApplication.class, args); }
}
实体类BizOrder：
@TableId
private Long orderId;
private String orderNo;
private Date orderTime;
private BigDecimal orderMoney;
private String orderStatus;
private Integer orderUserId;
实体类BizOrderItem：
@TableId
private Long orderItemId;
private Long orderId;
private String goodsName;
private int goodsNum;
private Integer orderUserId;
private BigDecimal goodsPrice;
联表查询结果集OrderAndItemModel：
private Long orderId;
private String orderNo;
private Date orderTime;
private BigDecimal orderMoney;
private String orderStatus;
private Integer orderUserId;
private List<BizOrderItem> items;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。