Sharding-JDBC 按年份水平分表

最新推荐文章于 2023-09-16 13:10:56 发布

chengqiuming

最新推荐文章于 2023-09-16 13:10:56 发布

阅读量1.1k

点赞数

分类专栏： java 文章标签： java 数据库 mybatis 水平分表 Powered by 金山文档

本文链接：https://blog.csdn.net/chengqiuming/article/details/128817865

版权

java 专栏收录该内容

779 篇文章 24 订阅

订阅专栏

一需求

start_time 为 2022 的数据，数据存放到 course_2022 表中。

start_time 为 2023 的数据，数据存放到 course_2023 表中。

二数据库

1 创建数据表 course_2022

CREATE TABLE `course_2022` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  `start_time` varchar(100) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 创建数据表 course_2023

CREATE TABLE `course_2023` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) NOT NULL,
  `start_time` varchar(100) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三 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 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>com.atguigu</groupId>
    <artifactId>shardingjdbcdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>shardingjdbcdemo</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>
        </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>

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

四实体类

package com.atguigu.shardingjdbcdemo.entity;

import lombok.Data;

@Data
public class Course {
    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;
    private String startTime;
}

五 Mapper

package com.atguigu.shardingjdbcdemo.mapper;

import com.atguigu.shardingjdbcdemo.entity.Course;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.stereotype.Repository;

@Repository
public interface CourseMapper extends BaseMapper<Course> {
}

六启动类

package com.atguigu.shardingjdbcdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.atguigu.shardingjdbcdemo.mapper")
public class ShardingjdbcdemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingjdbcdemoApplication.class, args);
    }
}

七资源配置文件

# shardingjdbc 分片策略
# 配置数据源，给数据源起名称
spring.shardingsphere.datasource.names=m1

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

# 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
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=Mima123456

# 指定 course 表分布情况，配置表在哪个数据库里面，表名称都是什么  m1.course_1 , m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{2022..2023}

# 指定 course 表里面主键 cid 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略：约定 start_time 值:当为 2022 添加到 course_2022 表，当为 2023 添加到 course_3 表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=start_time
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{start_time}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

八测试类

package com.atguigu.shardingjdbcdemo;


import com.atguigu.shardingjdbcdemo.entity.Course;
import com.atguigu.shardingjdbcdemo.entity.Udict;
import com.atguigu.shardingjdbcdemo.entity.User;
import com.atguigu.shardingjdbcdemo.mapper.CourseMapper;
import com.atguigu.shardingjdbcdemo.mapper.UdictMapper;
import com.atguigu.shardingjdbcdemo.mapper.UserMapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Random;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingjdbcdemoApplicationTests {
    // 注入 mapper
    @Autowired
    private CourseMapper courseMapper;

    //=======================测试水平分表===================
    // 添加课程的方法
    @Test
    public void addCourse() {
        for (int i = 1; i <= 10; i++) {
            Course course = new Course();
            course.setCname("java" + i);
            course.setUserId(100L);
            course.setCstatus("Normal" + i);
            if(i%2==1){
                course.setStartTime("2022");
            } else{
                course.setStartTime("2023");
            }
            courseMapper.insert(course);
        }
    }


    // 查询课程的方法
    @Test
    public void findCourse() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("cid", 827192767443632128L);
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }
}

九测试

1 执行 addCourse 方法

course_2022 数据表内容如下

course_2023 数据表内容如下

2 测试 findCourse

当 cid = 827196537560891392 时，查询结果如下。

Course(cid=827196537560891392, cname=java2, userId=100, cstatus=Normal2, startTime=2023)
2023-01-31 15:06:51.188  INFO 1340 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2023-01-31 15:06:51.192  INFO 1340 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed

当 cid = 827196537623805953 时，查询结果如下。

Course(cid=827196537623805953, cname=java5, userId=100, cstatus=Normal5, startTime=2022)
2023-01-31 15:08:05.005  INFO 22252 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2023-01-31 15:08:05.013  INFO 22252 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed