ShardingJDBC-5.0.0及4.0.0使用示例

Sharding5.0.0使用示例https://download.csdn.net/download/zhaoyanga14/78982040

前言：

官方文档在5.0.0之后，提供的示例越来越少了，所以在配置的时候，可能会有些磕绊，我先踩了一遍坑，把完整的使用示例贴出来，供大家参考。

本篇介绍ShardingJDBC-5.0.0和ShardingJDBC-4.0.0中表分片的使用示例，我用到的：SpringBooot+Druid+ShardingJDBC+MyBatisPlus，数据库是MySQL。

本示例，提供表的两种分片方式：

        ①按某纯数字字段的奇偶性进行分表。

        ②按某日期字段的年月进行分表。

先将建表语句贴出来：

-- 奇偶分片表CREATE TABLE `course_1` ( `cid` bigint(20) NOT NULL, `cname` varchar(50) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `cstatus` varchar(10) DEFAULT NULL, PRIMARY KEY (`cid`));CREATE TABLE `course_2` ( `cid` bigint(20) NOT NULL, `cname` varchar(50) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `cstatus` varchar(10) DEFAULT NULL, PRIMARY KEY (`cid`));-- 年月分片表CREATE TABLE `user_action_log_202201` ( `id` bigint(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '用户姓名', `date` datetime DEFAULT NULL COMMENT '访问时间', `path` varchar(255) DEFAULT NULL COMMENT '用户访问的路径', PRIMARY KEY (`id`));CREATE TABLE `user_action_log_202202` ( `id` bigint(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '用户姓名', `date` datetime DEFAULT NULL COMMENT '访问时间', `path` varchar(255) DEFAULT NULL COMMENT '用户访问的路径', PRIMARY KEY (`id`));

接下来，我主要讲5.0.0，而4.0.0我会贴出pom.xml、application.properties一带而过，请见谅。

就不从新建项目开始说起，因为有些人是要集成到现有项目里，所以这里直接干。

Sharding5.0.0 pom.xml

<?xml version="1.0" encoding="UTF-8"?> 4.0.0 org.springframework.boot spring-boot-starter-parent 2.6.3 org.springblade useraction 0.0.1-SNAPSHOT useraction Demo project for Spring Boot 1.8 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-test test com.alibaba druid 1.1.22 mysql mysql-connector-java org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter 5.0.0 com.baomidou mybatis-plus-boot-starter 3.5.0 org.projectlombok lombok org.springframework.boot spring-boot-maven-plugin

application.yml

# 萨丁5.0.0配置spring: shardingsphere: # 启动萨丁 enabled: true # 配置数据库，连接池、驱动等，ds1为自定义标识 datasource: names: ds1 ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8 username: root password: ffffff # 配置分片策略和主键策略 rules: sharding: # 定义主键算法，key-algorithm为自定义算法名，这里采用了雪花算法，并添加了机器码标识(666) key-generators: key-algorithm: # 算法类型，目前只有雪花 type: SNOWFLAKE # 算法属性 props: worker-id: 666 # 定义分配算法，odd-even-algorithm为自定义算法名，这里采用了标准行表达式算法-奇偶分表的方式，禁用了主键范围查询 sharding-algorithms: # 奇偶性分片是采用的内置行级表达式算法 odd-even-algorithm: # 算法类型 type: INLINE # 算法属性 props: algorithm-expression: course_$->{cid % 2 + 1} allow-range-query-with-inline-sharding: false # 内置的日期算法我没玩明白，如果有调试成功的小伙伴，记得留言告诉我 month-algorithm: # 算法类型 type: INTERVAL # 算法属性 props: datetime-pattern: yyyy-MM-dd HH:mm:ss datetime-lower: 2021-01-01 00:00:00 datetime-upper: 2021-02-28 23:59:59 sharding-suffix-pattern: yyyyMM datetime-interval-amount: 1 datetime-interval-unit: MonTHS # 年月分区是采用的这里的自定义算法 custom-algorithm: # 算法类型 type: CLASS_baseD # 算法属性 props: strategy: STANDARD algorithmClassName: org.springblade.useraction.algorithm.CustomAlgorithm # 对各表进行策略配置，course为表名 tables: course: # 设置表名范围 actual-data-nodes: ds1.course_$->{1..2} # 设置分表(片)策略，及算法所需的字段名 table-strategy: standard: sharding-column: cid sharding-algorithm-name: odd-even-algorithm # 设置主键算法，及主键字段名 key-generate-strategy: column: cid key-generator-name: key-algorithm user_action_log: # 设置表名范围 actual-data-nodes: ds1.user_action_log_2022$->{['01','02']} # 设置分表(片)策略，及算法所需的字段名 table-strategy: standard: sharding-column: date sharding-algorithm-name: custom-algorithm #sharding-algorithm-name: month-algorithm # 设置主键算法，及主键字段名 key-generate-strategy: column: id key-generator-name: key-algorithm # 配置控制台输出SQL语句 props: sql-show: true

项目结构（右边是打开的启动类，记得加Mapper扫描）： CustomAlgorithm算法类：

package org.springblade.useraction.algorithm;import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collection;import java.util.Date;import java.util.List;public class CustomAlgorithm implements StandardShardingAlgorithm { @Override public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) { // 表名前缀 System.out.println(shardingValue.getLogicTableName()); // 分表字段 System.out.println(shardingValue.getColumnName()); // 分表字段的值 System.out.println(shardingValue.getValue()); // 取分表字段中的年、月 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM"); // 拼接表名 String tableName = shardingValue.getLogicTableName()+"_"+sdf.format(shardingValue.getValue()); return tableName; } @Override public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) { // 表名前缀 System.out.println(shardingValue.getLogicTableName()); // 分表字段 System.out.println(shardingValue.getColumnName()); // 分表字段的下限 System.out.println(shardingValue.getValueRange().lowerEndpoint()); // 分表字段的上限 System.out.println(shardingValue.getValueRange().upperEndpoint()); // 这部分没深究，估计是这么玩的 List tableNames = new ArrayList<>(); tableNames.add(shardingValue.getLogicTableName()+"_"+"202201"); tableNames.add(shardingValue.getLogicTableName()+"_"+"202202"); return tableNames; } @Override public void init() { } @Override public String getType() { return null; }}

Course实体类：

package org.springblade.useraction.entity;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import java.io.Serializable;import lombok.Data;// 重要，这里不要加该注解，某则会影响分片//@TableName("course")@Datapublic class Course implements Serializable { @TableId(value = "cid") private Long cid; @TableField(value = "cname") private String cname; @TableField(value = "user_id") private Long userId; @TableField(value = "cstatus") private String cstatus; @TableField(exist = false) private static final long serialVersionUID = 1L;}

UserActionLog实体类：

package org.springblade.useraction.entity;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import java.io.Serializable;import java.text.SimpleDateFormat;import java.util.Date;import lombok.Data;// 重要，这里不要加该注解，某则会影响分片//@TableName("user_action_log")@Datapublic class UserActionLog implements Serializable { @TableId(value = "id") private Long id; @TableField(value = "name") private String name; @TableField(value = "date") private Date date; @TableField(value = "path") private String path; @TableField(exist = false) private static final long serialVersionUID = 1L; @Override public String toString() { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); return "UserActionLog{" + "id=" + id + ", name='" + name + ''' + ", date=" + sdf.format(date) + ", path='" + path + ''' + '}'; }}

Mapper接口（注意这里是两个，比较短，我就放一块了）：

package org.springblade.useraction.mapper;import com.baomidou.mybatisplus.core.mapper.baseMapper;import org.springblade.useraction.entity.Course;public interface CourseMapper extends baseMapper {}package org.springblade.useraction.mapper;import com.baomidou.mybatisplus.core.mapper.baseMapper;import org.springblade.useraction.entity.UserActionLog;public interface UserActionLogMapper extends baseMapper {}

测试类：

package org.springblade.useraction;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import org.junit.jupiter.api.Test;import org.springblade.useraction.entity.Course;import org.springblade.useraction.entity.UserActionLog;import org.springblade.useraction.mapper.CourseMapper;import org.springblade.useraction.mapper.UserActionLogMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Calendar;import java.util.Date;import java.util.List;@SpringBootTestclass UseractionApplicationTests { @Autowired private CourseMapper courseMapper; @Autowired private UserActionLogMapper userActionLogMapper; @Test public void addCourse() { for (int i=0; i<10; i++) { Course course = new Course(); course.setCname("张三"+i); course.setUserId(100L+i); course.setCstatus("Java"+i); courseMapper.insert(course); } } @Test public void addUserActionLog() throws ParseException { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar cal = Calendar.getInstance(); cal.setTime(sdf.parse("2022-01-25 10:00:00")); for (int i=0; i<10; i++) { UserActionLog userActionLog = new UserActionLog(); userActionLog.setName("李四"+i); userActionLog.setDate(cal.getTime()); userActionLog.setPath("path_"+i); userActionLogMapper.insert(userActionLog); cal.add(Calendar.DAY_OF_MONTH, 1); } } @Test public void getUserActionLogs() throws ParseException { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date startDate = sdf.parse("2022-01-30 00:00:00"); Date endDate = sdf.parse("2022-02-02 23:59:59"); LambdaQueryWrapper lqw = new LambdaQueryWrapper<>(); lqw.ge(UserActionLog::getDate, startDate); lqw.le(UserActionLog::getDate, endDate); List userActionLogs = userActionLogMapper.selectList(lqw); for (UserActionLog userActionLog : userActionLogs) { System.out.println(userActionLog); } }}

Sharding4.0.0-RC1 pom.xml

<?xml version="1.0" encoding="UTF-8"?> 4.0.0 org.springframework.boot spring-boot-starter-parent 2.2.1.RELEASE org.springblade useraction 0.0.1-SNAPSHOT useraction Demo project for Spring Boot 1.8 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-test test com.alibaba druid-spring-boot-starter 1.1.22 mysql mysql-connector-java org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.0-RC1 com.baomidou mybatis-plus-boot-starter 3.5.0 org.projectlombok lombok org.springframework.boot spring-boot-maven-plugin

application.properties

# 萨丁4.0.0配置# 配置数据库名称spring.shardingsphere.datasource.names=ds1# 配置实体类通用spring.main.allow-bean-definition-overriding=true# 配置数据库属性spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=ffffff# 配置分表范围spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds1.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.sharding-column=cid# 配置分表策略的算法，采用行级表达式算法spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}# 控制台输出SQLspring.shardingsphere.props.sql.show=true

就说到这里，文章顶部我放了示例项目下载地址，需要花积分下载哟。

不过我把5.0.0的所需文件代码都贴了出来，相信大家也没有问题，拜拜~祝大家新年快乐！！