Sharding Sphere是一套开源的分布式数据库中间件解决方案。而 sharding-jdbc是ShardingSphere的其中一个模块，它定位为轻量级Java框架， 在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。 Sharding-JDBC 主要作用：简化对分库分表之后数据相关操作
搭建环境
（1）技术：SpringBoot 2.2.1+ MyBatisPlus + Sharding-JDBC + Druid 连接池
（2）创建 SpringBoot 工程
（3）引入需要的依赖
▼▼▼<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</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.0.0-RC1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.0.5</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies>
按照水平分表的方式，创建数据库和数据库表
（1）创建数据库 course_db
（2）在数据库创建两张表 course_1 和 course_2
配置Sharding-JDBC分片策略
在项目 application.properties 配置文件中进行配置
▼▼▼spring.shardingsphere.datasource.names=m1
▼▼▼spring.main.allow-bean-definition-overriding=true
▼▼▼spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSourc e spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db? serverTimezone=GMT%2B8 spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=root
▼▼▼spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
▼▼▼spring.shardingsphere.sharding.tables.course.key-generator.column=cid spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
▼▼▼spring.shardingsphere.sharding.tables.course.table-strategy.inline.shardingcolumn=cid spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithmexpression=course_$->{cid % 2 + 1}
# 打开 sql 输出日志
▼▼▼spring.shardingsphere.props.sql.show=true
编写测试代码
▼▼▼@RunWith(SpringRunner.class)@SpringBootTestpublic classShardingjdbcdemoApplicationTests {//注入 mapper@AutowiredprivateCourseMappercourseMapper;//添加课程的方法@Testpublic voidaddCourse() {for(inti=1;i<=10;i++) {Course course =newCourse();course.setCname("java"+i);course.setUserId(100L);course.setCstatus("Normal"+i);courseMapper.insert(course);}}//查询课程的方法@Testpublic voidfindCourse() {QueryWrapper<Course> wrapper =newQueryWrapper<>();wrapper.eq("cid",465114665106538497L);Course course =courseMapper.selectOne(wrapper);System.out.println(course);}}
（1）上面测试代码执行，报错了
# 一个实体类对应两张表，覆盖
▼▼▼spring.main.allow-bean-definition-overriding=true
数据库搭建
创建两个数据库db1和db2，每个数据库创建两张表tab1和tab2，每个表中包括两个字段cid和userId。
在Spring boot配置文件配置数据库分片规则
▼▼▼spring.shardingsphere.datasource.names=m1,m2
▼▼▼spring.main.allow-bean-definition-overriding=true
▼▼▼spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8spring.shardingsphere.datasource.m1.username=rootspring.shardingsphere.datasource.m1.password=root
#配置第二个数据源具体内容，包含连接池，驱动，地址，用户名和密码
▼▼▼spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8spring.shardingsphere.datasource.m2.username=rootspring.shardingsphere.datasource.m2.password=root
#指定数据库分布情况，数据库里面表分布情况
▼▼▼spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
# 指定 course 表里面主键 cid 生成策略 SNOWFLAKE
▼▼▼spring.shardingsphere.sharding.tables.course.key-generator.column=cidspring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 指定表分片策略 约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到course_2 表
▼▼▼spring.shardingsphere.sharding.tables.course.table-strategy.inline.shardingcolumn=cidspring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithmexpression=course_$->{cid % 2 + 1}
# 指定数据库分片策略 约定 user_id 是偶数添加 m1，是奇数添加 m2
▼▼▼#spring.shardingsphere.sharding.default-database-strategy.inline.shardingcolumn=user_id#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}spring.shardingsphere.sharding.tables.course.databasestrategy.inline..sharding-column=user_idspring.shardingsphere.sharding.tables.course.databasestrategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
# 打开 sql 输出日志
▼▼▼spring.shardingsphere.props.sql.show=true
编写测试方法
▼▼▼//======================测试水平分库=====================//添加操作@Testpublic voidaddCourseDb() {Course course =newCourse();course.setCname("javademo1");//分库根据 user_idcourse.setUserId(111L);course.setCstatus("Normal1");courseMapper.insert(course);}//查询操作@Testpublic voidfindCourseDb() {QueryWrapper<Course> wrapper =newQueryWrapper<>();//设置 userid 值wrapper.eq("user_id",100L);//设置 cid 值wrapper.eq("cid",465162909769531393L);Course course =courseMapper.selectOne(wrapper);System.out.println(course);}
Sharding-JDBC 通过 sql 语句语义分析，实现读写分离过程，不会做数据同步
Sharding-JDBC 操作
配置读写分离策略
▼▼▼spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSourc e spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?se rverTimezone=GMT%2B8 spring.shardingsphere.datasource.s0.username=root spring.shardingsphere.datasource.s0.password=root
# 主库从库逻辑数据源定义 ds0 为 user_db
▼▼▼spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-sourcename=m0spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-sourcenames=s0
▼▼▼spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
编写测试代码
//添加操作@Testpublic voidaddUserDb() {User user =newUser();user.setUsername("lucymary");user.setUstatus("a");userMapper.insert(user);}//查询操作@Testpublic voidfindUserDb() {QueryWrapper<User> wrapper =newQueryWrapper<>();//设置 userid 值wrapper.eq("user_id",465508031619137537L);User user =userMapper.selectOne(wrapper);System.out.println(user);}
更多精彩干货分享
点击下方名片关注
IT那活儿
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
