Sharding Sphere 入门 简单的分表(一)

1、创建数据库

CREATE TABLE `course_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `cstatus` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course_2` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `cstatus` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、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.example</groupId>
    <artifactId>demo1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo1</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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </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>
            <version>5.1.28</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</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>

3、创建entity和dao

package com.example.demo1.entity;

import lombok.Data;

import java.io.Serializable;

/**
 * (Course1)实体类
 *
 * @author makejava
 * @since 2023-03-25 14:38:22
 */
@Data
public class Course implements Serializable {
    private static final long serialVersionUID = -60926304521161551L;
    
    private Long cid;
    
    private String cname;
    
    private Long userId;
    
    private String cstatus;
}

package com.example.demo1.dao;


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

/**
 * (Course1)表数据库访问层
 *
 * @author makejava
 * @since 2023-03-25 14:38:22
 */
@Repository
public interface CourseDao extends BaseMapper <Course>{
}

4、加上@MapperScan(“你的包名”)

package com.example.demo1;

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demo1.dao")
public class Demo1Application {

    public static void main(String[] args) {
        SpringApplication.run(Demo1Application.class, args);
    }

}

5、配置application.properties

#中文官网地址https://shardingsphere.apache.org/index_zh.html
#配置数据源名称，可以随便起
spring.shardingsphere.datasource.names=m1
#配置一个实体累对应两张表，不然会报Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
spring.main.allow-bean-definition-overriding = true 
# 配置m1数据源具体内容，包含数据连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.102.48:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = ****

#指定course表分布情况，配置表在那个数据库里面，表名称都是什么m1是数据库名称，course是表示以course开头的表 m1.course_1,m1.course_2
pring.shardingsphere.sharding.tables.course.actual-data-nodes = m1.course_$->{1..2}
#指定course表里面主键生成策略

#指定course表里面主键生成策略 SNOWFLAKE表示雪花算法 cid为表的主键
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

#指定分片策略，约定cid值偶数添加到course_1表,如果cid是奇数，添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column = cid
#因为数据库的表是1，2所以需要+1,不然的话会把数据库加到不同的表里面
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression = course_$->{cid % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

要注意这里的表名

6、Test类

package com.example.demo1;

import com.example.demo1.dao.CourseDao;
import com.example.demo1.entity.Course;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class Demo1ApplicationTests {
    @Autowired
    private CourseDao courseDao;

    @Test
    void addCourse() {
        //测试添加代码
        Course course=new Course();
        course.setCname("spark");
        course.setUserId(100L);
        course.setCstatus("normal");
        courseDao.insert(course);
    }

}

7、成功

测试代码二

package com.example.demo1;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo1.dao.CourseDao;
import com.example.demo1.entity.Course;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class Demo1ApplicationTests {
    @Autowired
    private CourseDao courseDao;

    @Test
    void addCourse() {
        //测试添加代码
        for (int i = 0; i < 10; i++) {
            Course course = new Course();
            course.setCname("spark");
            course.setUserId(100L);
            course.setCstatus("normal");
            courseDao.insert(course);
        }
    }

    @Test
    void getCourse() {
        //测试添加代码
        QueryWrapper<Course> wrapper = new QueryWrapper<Course>().eq("cid", 846413511193001985L);
        Course course = courseDao.selectOne(wrapper);
        System.out.println(course);

    }


}