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



    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")
@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")
@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;


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;

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

    
    @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



    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.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的所需文件代码都贴了出来，相信大家也没有问题，拜拜~祝大家新年快乐！！

ShardingJDBC-5.0.0及4.0.0使用示例

Java相关栏目本月热门文章