1、Sharding-JDBC----水平分表----使用案例

1、Sharding-JDBC----水平分表----使用案例

2、Sharding-JDBC----水平分库----使用案例

3、Sharding-JDBC----垂直分库----使用案例

4、Sharding-JDBC----垂直分表----使用案例

一、创建数据库

-- 创建数据库
CREATE DATABASE course_db;
-- 使用创建的数据库
USE course_db;
-- 设置字符集
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course_1
-- ----------------------------
DROP TABLE IF EXISTS `course_1`;
CREATE TABLE `course_1`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for course_2
-- ----------------------------
DROP TABLE IF EXISTS `course_2`;
CREATE TABLE `course_2`  (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

二、创建一个springboot工程，然后加入下面的依赖

加入依赖
使用的是springboot 2.x版本的默认mysql8.0驱动

<!-- druid数据库连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.20</version>
</dependency>
<!-- Mysql驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- MP 启动器 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.0.5</version>
</dependency>
<!-- lombok依赖 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

加入shardingSphere的spring-boot依赖

<!-- Sharding-jdb的spring-boot依赖 -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<!-- java8的环境下面的依赖不需要加入，我用的是jdk14 在jdk11以后需要加入下面这些依赖 -->
<dependency>
    <groupId>com.sun.xml.bind</groupId>
    <artifactId>jaxb-core</artifactId>
    <version>2.3.0.1</version>
</dependency>
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.1</version>
</dependency>
<dependency>
    <groupId>com.sun.xml.bind</groupId>
    <artifactId>jaxb-impl</artifactId>
    <version>2.3.1</version>
</dependency>
<dependency>
    <groupId>org.javassist</groupId>
    <artifactId>javassist</artifactId>
    <version>3.25.0-GA</version>
</dependency>

yml配置借鉴简书的一篇文章
或者官方文档中的yml配置

添加properties配置

#需要加入下面的配值允许重载bean名称，主要用于后面sql对表的操作，MybatisPlus是根据类名作为表名的
spring.main.allow-bean-definition-overriding=true

#分片策略，多个数据源则用逗号隔开，例如: ds0,ds1
spring.shardingsphere.datasource.names=ds0

#配置数据源内容，下面的ds0 是上面设置的，因此需要同名
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#表的位置 在那个数据源（数据库），那个表。 下面的tables.course中的course是表名以什么开头
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds0.course_$->{ 1..2}

#指定course表里面主键cid的生成策略 SNOWFLAKE是雪花算法
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
#表示加入到course_1或者course_2表中
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_->{ cid % 2 +1}

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

创建实体层

import lombok.Data;

@Data
public class Course { 

    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;

}

创建Dao层

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

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

单元测试代码

import com.example.shardingjdbcdemo.entity.Course;
import com.example.shardingjdbcdemo.mapper.CourseMapper;
import org.junit.jupiter.api.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;

@RunWith(SpringRunner.class)
@SpringBootTest
class ShardingJdbcDemoApplicationTests { 
    @Autowired
    private CourseMapper courseMapper;

    @Test
    void contextLoads() { 
        Course course=new Course();
        course.setCname("java");
        course.setUserId(100L);
        course.setCstatus("Normal");
        courseMapper.insert(course);//主键会根据雪花算法自动生成
    }

}