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"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.springblade</groupId>
    <artifactId>useraction</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>useraction</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>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- 不要使用druid-spring-boot-starter，会提示缺少sqlTemplate -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

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;

/**
 * @author RedstoneY
 * @description 年月分片算法
 * @date 2022年02月02日 14:08
 */
public class CustomAlgorithm implements StandardShardingAlgorithm<Date> {

    /**
     * 用于插入时寻表
     * @param availableTargetNames
     * @param shardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> 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;
    }

    /**
     * 用于限制表名的范围
     * @param availableTargetNames
     * @param shardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> shardingValue) {
        // 表名前缀
        System.out.println(shardingValue.getLogicTableName());
        // 分表字段
        System.out.println(shardingValue.getColumnName());
        // 分表字段的下限
        System.out.println(shardingValue.getValueRange().lowerEndpoint());
        // 分表字段的上限
        System.out.println(shardingValue.getValueRange().upperEndpoint());
        // 这部分没深究，估计是这么玩的
        List<String> 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
 */
// 重要，这里不要加该注解，某则会影响分片
//@TableName("course")
@Data
public 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
 */
// 重要，这里不要加该注解，某则会影响分片
//@TableName("user_action_log")
@Data
public 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;

/**
* @Entity org.springblade.useraction.entity.Course
*/
public interface CourseMapper extends BaseMapper<Course> {


}

package org.springblade.useraction.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springblade.useraction.entity.UserActionLog;

/**
* @Entity org.springblade.useraction.entity.UserActionLog202201
*/
public interface UserActionLogMapper extends BaseMapper<UserActionLog> {


}

测试类：

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;

@SpringBootTest
class 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);
        }
    }

    /**
     * 插入用户行为日志，2022年1月25日及后10天的数据。
     * @throws ParseException 抛了个异常
     */
    @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);
        }
    }

    /**
     * 查询用户行为日志，2022年1月30日~2022年2月2日的数据
     * @throws ParseException 抛了个异常
     */
    @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<UserActionLog> lqw = new LambdaQueryWrapper<>();
        lqw.ge(UserActionLog::getDate, startDate);
        lqw.le(UserActionLog::getDate, endDate);
        List<UserActionLog> 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"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <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>org.springblade</groupId>
    <artifactId>useraction</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>useraction</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>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</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.5.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

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.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.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%2B8
spring.shardingsphere.datasource.ds1.username=root
spring.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}
# 控制台输出SQL
spring.shardingsphere.props.sql.show=true

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

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