1.创建一个普通的springboot的javaweb项目
springboot-sharding-jdbc
启动类配置:
/** * * @author player3 */ @SpringBootApplication @MapperScan("com.player3.sharding.jdbc.dao") public class ShardingJdbcApplication { public static void main(String[] args) { SpringApplication.run(ShardingJdbcApplication.class, args); } }
pojo类:
/** * @author player3 */ @Data public class Goods { private Long gid; private String gname; private Long userId; private String gstatus; }
dao层:
/** * @author player3 */ @Repository public interface GoodsMapper extends BaseMapper<Goods> { }
2.pom文件相关配置
<?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"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>springboot-sharding-jdbc</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.1.RELEASE</version> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </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> <scope>test</scope> </dependency> <!-- Druid连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.20</version> </dependency> <!-- Mysql驱动依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- MybatisPlus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.5</version> </dependency> <!-- Sharding-JDBC --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies> </project>
3.properties相关配置
# 配置Sharding-JDBC的分片策略 # 配置数据源,给数据源起名g1,g2...此处可配置多数据源 spring.shardingsphere.datasource.names=g1 # 配置数据源具体内容————————包含 连接池, 驱动, 地址, 用户名, 密码 # 由于上面配置数据源只有g1因此下面只配置g1.type,g1.driver-class-name,g1.url,g1.username,g1.password spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.g1.url=jdbc:mysql://192.168.31.200:3306/sharding_jdbc?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.g1.username=root spring.shardingsphere.datasource.g1.password=player3. # 配置表的分布,表的策略 spring.shardingsphere.sharding.tables.goods.actual-data-nodes=g1.goods_$->{1..2} # 指定goods表 主键gid 生成策略为 SNOWFLAKE spring.shardingsphere.sharding.tables.goods.key-generator.column=gid spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKE # 指定分片策略 约定gid值是偶数添加到goods_1表,如果gid是奇数添加到goods_2表 spring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 2 + 1} # 打开sql输出日志 spring.shardingsphere.props.sql.show=true
#一个pojo对象映射不了多个表,开启它就不报错了 spring.main.allow-bean-definition-overriding=true
4.数据库配置
数据库没用本地,使用的是:https://www.cnblogs.com/q1359720840/p/15687442.html
CREATE TABLE goods_1( gid BIGINT(20) primary key comment '商品id,主键', gname varchar(20) not null comment '商品名称', user_id BIGINT(20) not null COMMENT '用户id', gstatus VARCHAR(10) not null COMMENT '商品状态,已发布 or 未发布'); CREATE TABLE goods_2( gid BIGINT(20) primary key comment '商品id,主键', gname varchar(20) not null comment '商品名称', user_id BIGINT(20) not null COMMENT '用户id', gstatus VARCHAR(10) not null COMMENT '商品状态,已发布 or 未发布');
5.启动运行测试脚本
package com.player3.Shardingjdbc; import com.player3.sharding.jdbc.ShardingJdbcApplication; import com.player3.sharding.jdbc.dao.GoodsMapper; import com.player3.sharding.jdbc.pojo.Goods; import org.junit.jupiter.api.Test; import org.springframework.boot.test.context.SpringBootTest; import javax.annotation.Resource; @SpringBootTest(classes = ShardingJdbcApplication.class) public class ShardingjdbcTests { @Resource GoodsMapper goodsMapper; @Test public void addGoods() { System.out.println("访问成功"); Goods good = new Goods(); good.setGname("小米手机"); good.setUserId(100L); good.setGstatus("已发布"); goodsMapper.insert(good); } }
后语:
使用本地数据源
配置项说明
spring.shardingsphere.datasource.names= # 真实数据源名称,多个数据源用逗号区分
# <actual-data-source-name> 表示真实数据源名称
spring.shardingsphere.datasource.<actual-data-source-name>.type= # 数据库连接池全类名
spring.shardingsphere.datasource.<actual-data-source-name>.driver-class-name= # 数据库驱动类名,以数据库连接池自身配置为准
spring.shardingsphere.datasource.<actual-data-source-name>.jdbc-url= # 数据库 URL 连接,以数据库连接池自身配置为准
spring.shardingsphere.datasource.<actual-data-source-name>.username= # 数据库用户名,以数据库连接池自身配置为准
spring.shardingsphere.datasource.<actual-data-source-name>.password= # 数据库密码,以数据库连接池自身配置为准
spring.shardingsphere.datasource.<actual-data-source-name>.<xxx>= # ... 数据库连接池的其它属性
配置示例
示例的数据库驱动为 MySQL,连接池为 HikariCP,可以更换为其他数据库驱动和连接池。
# 配置真实数据源
spring.shardingsphere.datasource.names=ds1,ds2
# 配置第 1 个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=
# 配置第 2 个数据源
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://localhost:3306/ds2
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=
使用 JNDI 数据源
如果计划使用 JNDI 配置数据库,在应用容器(如 Tomcat)中使用 ShardingSphere-JDBC 时, 可使用 spring.shardingsphere.datasource.${datasourceName}.jndiName
来代替数据源的一系列配置。
配置项说明
spring.shardingsphere.datasource.names= # 真实数据源名称,多个数据源用逗号区分
# <actual-data-source-name> 表示真实数据源名称
spring.shardingsphere.datasource.<actual-data-source-name>.jndi-name= # 数据源 JNDI
配置示例
# 配置真实数据源 spring.shardingsphere.datasource.names=ds1,ds2 # 配置第 1 个数据源 spring.shardingsphere.datasource.ds1.jndi-name=java:comp/env/jdbc/ds1 # 配置第 2 个数据源 spring.shardingsphere.datasource.ds2.jndi-name=java:comp/env/jdbc/ds2
规则配置:
数据分片:
spring.shardingsphere.datasource.names= # 省略数据源配置,请参考使用手册 # 标准分片表配置 spring.shardingsphere.rules.sharding.tables.<table-name>.actual-data-nodes= # 由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点,用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况 # 分库策略,缺省表示使用默认分库策略,以下的分片策略只能选其一 # 用于单分片键的标准分片场景 spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.standard.sharding-column= # 分片列名称 spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.standard.sharding-algorithm-name= # 分片算法名称 # 用于多分片键的复合分片场景 spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.complex.sharding-columns= # 分片列名称,多个列以逗号分隔 spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.complex.sharding-algorithm-name= # 分片算法名称 # 用于Hint 的分片策略 spring.shardingsphere.rules.sharding.tables.<table-name>.database-strategy.hint.sharding-algorithm-name= # 分片算法名称 # 分表策略,同分库策略 spring.shardingsphere.rules.sharding.tables.<table-name>.table-strategy.xxx= # 省略 # 自动分片表配置 spring.shardingsphere.rules.sharding.auto-tables.<auto-table-name>.actual-data-sources= # 数据源名 spring.shardingsphere.rules.sharding.auto-tables.<auto-table-name>.sharding-strategy.standard.sharding-column= # 分片列名称 spring.shardingsphere.rules.sharding.auto-tables.<auto-table-name>.sharding-strategy.standard.sharding-algorithm-name= # 自动分片算法名称 # 分布式序列策略配置 spring.shardingsphere.rules.sharding.tables.<table-name>.key-generate-strategy.column= # 分布式序列列名称 spring.shardingsphere.rules.sharding.tables.<table-name>.key-generate-strategy.key-generator-name= # 分布式序列算法名称 spring.shardingsphere.rules.sharding.binding-tables[0]= # 绑定表规则列表 spring.shardingsphere.rules.sharding.binding-tables[1]= # 绑定表规则列表 spring.shardingsphere.rules.sharding.binding-tables[x]= # 绑定表规则列表 spring.shardingsphere.rules.sharding.broadcast-tables[0]= # 广播表规则列表 spring.shardingsphere.rules.sharding.broadcast-tables[1]= # 广播表规则列表 spring.shardingsphere.rules.sharding.broadcast-tables[x]= # 广播表规则列表 spring.shardingsphere.sharding.default-database-strategy.xxx= # 默认数据库分片策略 spring.shardingsphere.sharding.default-table-strategy.xxx= # 默认表分片策略 spring.shardingsphere.sharding.default-key-generate-strategy.xxx= # 默认分布式序列策略 spring.shardingsphere.sharding.default-sharding-column= # 默认分片列名称 # 分片算法配置 spring.shardingsphere.rules.sharding.sharding-algorithms.<sharding-algorithm-name>.type= # 分片算法类型 spring.shardingsphere.rules.sharding.sharding-algorithms.<sharding-algorithm-name>.props.xxx=# 分片算法属性配置 # 分布式序列算法配置 spring.shardingsphere.rules.sharding.key-generators.<key-generate-algorithm-name>.type= # 分布式序列算法类型 spring.shardingsphere.rules.sharding.key-generators.<key-generate-algorithm-name>.props.xxx= # 分布式序列算法属性配置
读写分离:
spring.shardingsphere.datasource.names= # 省略数据源配置,请参考使用手册 spring.shardingsphere.rules.readwrite-splitting.data-sources.<readwrite-splitting-data-source-name>.auto-aware-data-source-name= # 自动发现数据源名称(与数据库发现配合使用) spring.shardingsphere.rules.readwrite-splitting.data-sources.<readwrite-splitting-data-source-name>.write-data-source-name= # 写数据源名称 spring.shardingsphere.rules.readwrite-splitting.data-sources.<readwrite-splitting-data-source-name>.read-data-source-names= # 读数据源名称,多个从数据源用逗号分隔 spring.shardingsphere.rules.readwrite-splitting.data-sources.<readwrite-splitting-data-source-name>.load-balancer-name= # 负载均衡算法名称 # 负载均衡算法配置 spring.shardingsphere.rules.readwrite-splitting.load-balancers.<load-balance-algorithm-name>.type= # 负载均衡算法类型 spring.shardingsphere.rules.readwrite-splitting.load-balancers.<load-balance-algorithm-name>.props.xxx= # 负载均衡算法属性配置
高可用:
spring.shardingsphere.datasource.names= # 省略数据源配置,请参考使用手册 spring.shardingsphere.rules.database-discovery.data-sources.<database-discovery-data-source-name>.data-source-names= # 数据源名称,多个数据源用逗号分隔 如:ds_0, ds_1 spring.shardingsphere.rules.database-discovery.data-sources.<database-discovery-data-source-name>.discovery-heartbeat-name= # 检测心跳名称 spring.shardingsphere.rules.database-discovery.data-sources.<database-discovery-data-source-name>.discovery-type-name= # 数据库发现类型名称 spring.shardingsphere.rules.database-discovery.discovery-heartbeats.<discovery-heartbeat-name>.props.keep-alive-cron= # cron 表达式,如:'0/5 * * * * ?' spring.shardingsphere.rules.database-discovery.discovery-types.<discovery-type-name>.type= # 数据库发现类型,如: MGR、openGauss spring.shardingsphere.rules.database-discovery.discovery-types.<discovery-type-name>.props.group-name= # 数据库发现类型必要参数,如 MGR 的 group-name
数据加密:
spring.shardingsphere.datasource.names= # 省略数据源配置,请参考使用手册
spring.shardingsphere.rules.encrypt.tables.<table-name>.query-with-cipher-column= # 该表是否使用加密列进行查询
spring.shardingsphere.rules.encrypt.tables.<table-name>.columns.<column-name>.cipher-column= # 加密列名称
spring.shardingsphere.rules.encrypt.tables.<table-name>.columns.<column-name>.assisted-query-column= # 查询列名称
spring.shardingsphere.rules.encrypt.tables.<table-name>.columns.<column-name>.plain-column= # 原文列名称
spring.shardingsphere.rules.encrypt.tables.<table-name>.columns.<column-name>.encryptor-name= # 加密算法名称
# 加密算法配置
spring.shardingsphere.rules.encrypt.encryptors.<encrypt-algorithm-name>.type= # 加密算法类型
spring.shardingsphere.rules.encrypt.encryptors.<encrypt-algorithm-name>.props.xxx= # 加密算法属性配置
spring.shardingsphere.rules.encrypt.queryWithCipherColumn= # 是否使用加密列进行查询。在有原文列的情况下,可以使用原文列进行查询
影子库:
spring.shardingsphere.datasource.names= # 省略数据源配置,请参考使用手册 spring.shardingsphere.rules.shadow.data-sources.shadow-data-source.source-data-source-name= # 生产数据源名称 spring.shardingsphere.rules.shadow.data-sources.shadow-data-source.shadow-data-source-name= # 影子数据源名称 spring.shardingsphere.rules.shadow.tables.<table-name>.data-source-names= # 影子表关联影子数据源名称列表(多个值用","隔开) spring.shardingsphere.rules.shadow.tables.<table-name>.shadow-algorithm-names= # 影子表关联影子算法名称列表(多个值用","隔开) spring.shardingsphere.rules.shadow.defaultShadowAlgorithmName= # 默认影子算法名称,选配项。 spring.shardingsphere.rules.shadow.shadow-algorithms.<shadow-algorithm-name>.type= # 影子算法类型 spring.shardingsphere.rules.shadow.shadow-algorithms.<shadow-algorithm-name>.props.xxx= # 影子算法属性配置
sql解析:
spring.shardingsphere.rules.sql-parser.sql-comment-parse-enabled= # 是否解析SQL注释
spring.shardingsphere.rules.sql-parser.sql-statement-cache.initial-capacity= # SQL语句本地缓存初始容量
spring.shardingsphere.rules.sql-parser.sql-statement-cache.maximum-size= # SQL语句本地缓存最大容量
spring.shardingsphere.rules.sql-parser.sql-statement-cache.concurrency-level= # SQL语句本地缓存并发级别,最多允许线程并发更新的个数
spring.shardingsphere.rules.sql-parser.parse-tree-cache.initial-capacity= # 解析树本地缓存初始容量
spring.shardingsphere.rules.sql-parser.parse-tree-cache.maximum-size= # 解析树本地缓存最大容量
spring.shardingsphere.rules.sql-parser.parse-tree-cache.concurrency-level= # 解析树本地缓存并发级别,最多允许线程并发更新的个数
混合规则:
混合配置的规则项之间的叠加使用是通过数据源名称和表名称关联的。
如果前一个规则是面向数据源聚合的,下一个规则在配置数据源时,则需要使用前一个规则配置的聚合后的逻辑数据源名称; 同理,如果前一个规则是面向表聚合的,下一个规则在配置表时,则需要使用前一个规则配置的聚合后的逻辑表名称。
配置项说明
# 数据源配置 # 数据源名称,多数据源以逗号分隔 spring.shardingsphere.datasource.names= write-ds0,write-ds1,write-ds0-read0,write-ds1-read0 spring.shardingsphere.datasource.write-ds0.url= # 数据库 URL 连接 spring.shardingsphere.datasource.write-ds0.type= # 数据库连接池类名称 spring.shardingsphere.datasource.write-ds0.driver-class-name= # 数据库驱动类名 spring.shardingsphere.datasource.write-ds0.username= # 数据库用户名 spring.shardingsphere.datasource.write-ds0.password= # 数据库密码 spring.shardingsphere.datasource.write-ds0.xxx= # 数据库连接池的其它属性 spring.shardingsphere.datasource.write-ds1.url= # 数据库 URL 连接 # 忽略其他数据库配置项 spring.shardingsphere.datasource.write-ds0-read0.url= # 数据库 URL 连接 # 忽略其他数据库配置项 spring.shardingsphere.datasource.write-ds1-read0.url= # 数据库 URL 连接 # 忽略其他数据库配置项 # 分片规则配置 # 分库策略 spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=default-database-strategy-inline # 绑定表规则,多组绑定规则使用数组形式配置 spring.shardingsphere.rules.sharding.binding-tables[0]=t_user,t_user_detail # 绑定表名称,多个表之间以逗号分隔 spring.shardingsphere.rules.sharding.binding-tables[1]= # 绑定表名称,多个表之间以逗号分隔 spring.shardingsphere.rules.sharding.binding-tables[x]= # 绑定表名称,多个表之间以逗号分隔 # 广播表规则配置 spring.shardingsphere.rules.sharding.broadcast-tables= # 广播表名称,多个表之间以逗号分隔 # 分表策略 # 表达式 `ds_$->{0..1}`枚举的数据源为读写分离配置的逻辑数据源名称 spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=ds_$->{0..1}.t_user_$->{0..1} spring.shardingsphere.rules.sharding.tables.t_user.table-strategy.standard.sharding-column=user_id spring.shardingsphere.rules.sharding.tables.t_user.table-strategy.standard.sharding-algorithm-name=user-table-strategy-inline spring.shardingsphere.rules.sharding.tables.t_user_detail.actual-data-nodes=ds_$->{0..1}.t_user_detail_$->{0..1} spring.shardingsphere.rules.sharding.tables.t_user_detail.table-strategy.standard.sharding-column=user_id spring.shardingsphere.rules.sharding.tables.t_user_detail.table-strategy.standard.sharding-algorithm-name=user-detail-table-strategy-inline # 数据加密配置 # `t_user` 使用分片规则配置的逻辑表名称 spring.shardingsphere.rules.encrypt.tables.t_user.columns.username.cipher-column=username spring.shardingsphere.rules.encrypt.tables.t_user.columns.username.encryptor-name=name-encryptor spring.shardingsphere.rules.encrypt.tables.t_user.columns.pwd.cipher-column=pwd spring.shardingsphere.rules.encrypt.tables.t_user.columns.pwd.encryptor-name=pwd-encryptor # 数据加密算法配置 spring.shardingsphere.rules.encrypt.encryptors.name-encryptor.type=AES spring.shardingsphere.rules.encrypt.encryptors.name-encryptor.props.aes-key-value=123456abc spring.shardingsphere.rules.encrypt.encryptors.pwd-encryptor.type=AES spring.shardingsphere.rules.encrypt.encryptors.pwd-encryptor.props.aes-key-value=123456abc # 分布式序列策略配置 spring.shardingsphere.rules.sharding.tables.t_user.key-generate-strategy.column=user_id spring.shardingsphere.rules.sharding.tables.t_user.key-generate-strategy.key-generator-name=snowflake # 分片算法配置 spring.shardingsphere.rules.sharding.sharding-algorithms.default-database-strategy-inline.type=INLINE # 表达式`ds_$->{user_id % 2}` 枚举的数据源为读写分离配置的逻辑数据源名称 spring.shardingsphere.rules.sharding.sharding-algorithms.default-database-strategy-inline.algorithm-expression=ds_$->{user_id % 2} spring.shardingsphere.rules.sharding.sharding-algorithms.user-table-strategy-inline.type=INLINE spring.shardingsphere.rules.sharding.sharding-algorithms.user-table-strategy-inline.algorithm-expression=t_user_$->{user_id % 2} spring.shardingsphere.rules.sharding.sharding-algorithms.user-detail-table-strategy-inline.type=INLINE spring.shardingsphere.rules.sharding.sharding-algorithms.user-detail-table-strategy-inline.algorithm-expression=t_user_detail_$->{user_id % 2} # 分布式序列算法配置 spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123 # 读写分离策略配置 # ds_0,ds_1为读写分离配置的逻辑数据源名称 spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_0.write-data-source-name=write-ds0 spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_0.read-data-source-names=write-ds0-read0 spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_0.load-balancer-name=read-random spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_1.write-data-source-name=write-ds1 spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_1.read-data-source-names=write-ds1-read0 spring.shardingsphere.rules.readwrite-splitting.data-sources.ds_1.load-balancer-name=read-random # 负载均衡算法配置 spring.shardingsphere.rules.readwrite-splitting.load-balancers.read-random.type=RANDOM
运行模式配置:
spring.shardingsphere.mode.type= # 运行模式类型。可选配置:Memory、Standalone、Cluster
spring.shardingsphere.mode.repository= # 持久化仓库配置。Memory 类型无需持久化
spring.shardingsphere.mode.overwrite= # 是否使用本地配置覆盖持久化配置
内存模式
spring.shardingsphere.mode.type=Memory
单机模式
spring.shardingsphere.mode.type=Standalone
spring.shardingsphere.mode.repository.type= # 持久化仓库类型
spring.shardingsphere.mode.repository.props.<key>= # 持久化仓库所需属性
spring.shardingsphere.mode.overwrite= # 是否使用本地配置覆盖持久化配置
集群模式
spring.shardingsphere.mode.type=Cluster
spring.shardingsphere.mode.repository.type= # 持久化仓库类型
spring.shardingsphere.mode.repository.props.namespace= # 注册中心命名空间
spring.shardingsphere.mode.repository.props.server-lists= # 注册中心连接地址
spring.shardingsphere.mode.repository.props.<key>= # 持久化仓库所需属性
spring.shardingsphere.mode.overwrite= # 是否使用本地配置覆盖持久化配置